Setting Up PostGIS with dbt

Wiring PostGIS into a dbt project is the moment spatial analytics stops being a pile of ad-hoc ST_ scripts and becomes a version-controlled, testable pipeline. The hard part is not calling a single spatial function — it is making the database, the adapter, and the dependency graph agree on extension state, coordinate reference system, geometry types, and index strategy before a single model compiles. Get the bootstrap order wrong and dbt fails to parse spatial SQL; get coordinate handling wrong and ST_Intersects silently returns nulls across your whole mart layer.

This is the production-serving companion to Core Fundamentals & Architecture for dbt Geospatial. Where that overview frames the layered spatial pipeline, this page resolves one concrete integration: making dbt-postgres and the PostGIS extension behave as a reliable, reproducible engine across a laptop, a CI runner, and a production orchestrator. You will end with the hooks, profiles.yml configuration, CRS macros, index declarations, validation tests, and failure-mode table needed to treat spatial data as a first-class citizen in the directed acyclic graph (DAG).

Prerequisites

Before provisioning, confirm the following are in place:

  • dbt Core 1.7 or newer with the dbt-postgres adapter (pip install "dbt-postgres>=1.7"). There is no separate dbt-postgis package — spatial support layers on top of dbt-postgres; see how to install the dbt-postgis adapter step by step for the full dependency walkthrough.
  • PostgreSQL 13+ with PostGIS 3.1+ available on the server (apt install postgis or the equivalent for your distribution / managed service).
  • A database role with CREATE on the target schema and rights to run CREATE EXTENSION — or a superuser to pre-create extensions and a lower-privilege role for builds.
  • Environment variables exported for the connection: DB_HOST, DB_USER, DB_PASS. Never hard-code secrets in profiles.yml.
  • A decision on your canonical SRID (this guide standardizes on 4326 / WGS 84) so every layer aligns to one baseline.

Architecture Context

PostGIS occupies the execution tier of a dbt geospatial project: dbt orchestrates the transformation DAG, while PostgreSQL performs the geometry math natively. A resilient project isolates that math into ordered layers — raw sources are cast and reprojected in staging, joined and buffered in intermediate, then aggregated and indexed in marts for serving. Understanding where this integration sits in the wider topology is covered in spatial model dependency graphs; the diagram below shows the slice this page configures.

Layered dbt-on-PostGIS data flow A left-to-right pipeline of five stages. Raw source tables (mixed SRIDs) feed a staging layer that normalises every geometry to one CRS with ST_Transform as a view. Staging feeds an intermediate layer that performs ST_DWithin spatial joins. Intermediate feeds a marts layer materialized as a table with a GiST index post-hook. Marts feed BI and serving consumers. The staging, intermediate, and marts stages sit inside a dashed boundary labelled PostgreSQL plus the PostGIS extension, where the geometry math executes in-database. A band across the top shows that dbt orchestrates the whole directed acyclic graph in a single run. dbt orchestrates the DAG — one run, every layer PostgreSQL · PostGIS extension Raw sources loaded externally {{ source() }} SRID 0 · 3857 · 26918 Staging enforce one CRS ST_Transform() view Intermediate spatial joins ST_DWithin() view / ephemeral Marts aggregate + index GiST post_hook table BI / serving maps + analytics ST_AsGeoJSON() consumers Geometry math executes in-database; only staging, intermediate, and marts touch the PostGIS extension.

Configuration Walkthrough

Bootstrap the extension with on-run-start hooks

What runs before the first model, and in which order A sequence of four steps that execute before any model builds: the connection opens, the search path is set so PostGIS functions resolve unqualified, the extension presence is asserted, and the canonical SRID variable is logged. Each step is annotated with the failure it prevents, from unqualified function errors to a silent SRID mismatch later in the run. connection opens one per thread search_path set public, postgis extension asserted postgis_version() SRID var logged visible in every run log prevents: function does not exist prevents: building on a database without PostGIS prevents: an unexplained SRID later All four cost milliseconds and run before anything expensive has started.

The dbt-postgres adapter natively recognizes PostGIS geometry and geography types, but it will not create the extension for you. If a model references a spatial function before the extension is registered, compilation fails. Bootstrap PostGIS idempotently with an on-run-start hook in dbt_project.yml so every invocation guarantees the extension exists:

yaml
# dbt_project.yml
on-run-start:
  - "CREATE EXTENSION IF NOT EXISTS postgis SCHEMA public;"
  - "CREATE EXTENSION IF NOT EXISTS postgis_raster SCHEMA public;"
  - "CREATE EXTENSION IF NOT EXISTS postgis_topology SCHEMA public;"

vars:
  canonical_srid: 4326

If your build role lacks CREATE EXTENSION rights, pre-create the extensions once as a superuser and drop the hooks — but keep a PostGIS_Version() assertion in CI so a missing extension fails loudly rather than mid-DAG.

Tune profiles.yml for spatial type resolution

Connection parameters mirror a standard PostgreSQL profile, but search_path matters more here: it tells the planner which schema to resolve spatial functions against, which is essential when extensions are isolated or strict role-based access control is enforced.

yaml
# profiles.yml
analytics_platform:
  target: prod
  outputs:
    prod:
      type: postgres
      host: "{{ env_var('DB_HOST') }}"
      port: 5432
      user: "{{ env_var('DB_USER') }}"
      password: "{{ env_var('DB_PASS') }}"
      dbname: analytics
      schema: dbt_spatial
      threads: 12
      search_path: dbt_spatial,public,postgis
      keepalives_idle: 300

Listing postgis last in search_path keeps your transformation schema authoritative while still resolving ST_ functions when the extension lives in a dedicated schema. Tune threads to the warehouse’s spatial-join capacity; spatial operations are CPU-heavy, so more threads is not always faster.

Core Implementation

Enforce coordinate reference systems at staging

Spatial accuracy collapses when a pipeline silently mixes geometry (planar, Euclidean) and geography (spheroidal, great-circle) types, or when SRIDs drift across layers. Ingestion commonly delivers mixed projections — EPSG:3857 for web tiles, EPSG:26918 for regional surveys — so the coordinate reference system must be normalized before any join. Wrap the projection logic in reusable macros so business models never hand-roll an ST_Transform:

sql
-- macros/enforce_crs.sql
{% macro standardize_geometry(column_name, source_srid, target_srid=4326) %}
  ST_Transform(
    ST_SetSRID({{ column_name }}::geometry, {{ source_srid }}),
    {{ target_srid }}
  )
{% endmacro %}

{% macro cast_to_geography(column_name, source_srid, target_srid=4326) %}
  ST_Transform(
    ST_SetSRID({{ column_name }}::geometry, {{ source_srid }}),
    {{ target_srid }}
  )::geography
{% endmacro %}

Apply the macros in staging models so type and projection are locked before any downstream logic runs:

sql
-- models/staging/stg_municipal_boundaries.sql
{{ config(materialized='view') }}

select
  parcel_id,
  jurisdiction,
  {{ standardize_geometry('raw_boundary_geom', 3857, 4326) }} as geometry_wgs84,
  {{ cast_to_geography('raw_boundary_geom', 3857) }}        as geography_spheroid,
  ST_Area({{ cast_to_geography('raw_boundary_geom', 3857) }}) as area_sq_meters
from {{ source('raw_ingestion', 'municipal_boundaries') }}

The ST_SetSRID call inside the macro is deliberate: raw imports frequently arrive tagged SRID 0, and ST_Transform returns NULL when it has no source projection to transform from. Setting the SRID first, then transforming, eliminates the most common cause of silent null geometries. For authoritative type and transform semantics, reference the OGC Simple Features specification.

Declare spatial indexes alongside the model

Spatial joins and proximity predicates are expensive, and without an index the planner falls back to a sequential scan that exhausts warehouse resources. A GiST index is mandatory on any geometry column a mart joins or filters on. Declare it as part of the model with a post_hook so the index is recreated on every full build and never drifts from the table:

sql
-- models/marts/fact_property_proximity.sql
{{ config(
    materialized='table',
    post_hook=[
        "CREATE INDEX IF NOT EXISTS idx_fact_property_geom ON {{ this }} USING GIST (geometry_wgs84);",
        "ANALYZE {{ this }};"
    ]
) }}

select
  p.property_id,
  p.address,
  p.geometry_wgs84,
  s.school_name,
  s.geometry_wgs84 as school_geom,
  ST_Distance(p.geometry_wgs84::geography, s.geometry_wgs84::geography) as distance_meters
from {{ ref('stg_properties') }} p
cross join {{ ref('stg_schools') }} s
where ST_DWithin(p.geometry_wgs84::geography, s.geometry_wgs84::geography, 5000)

The trailing ANALYZE refreshes planner statistics so the new GiST index is actually chosen on the next query rather than ignored in favor of a scan.

Validation & Testing

Standard unique and not_null tests do not catch the failures spatial pipelines actually hit: SRID drift, self-intersecting polygons, and empty geometries that pass scalar checks but break joins and map renders.

What each setup check actually proves Four verification queries paired with the claim each supports. A version query proves the extension is installed. A round-trip geometry construction proves the type is usable through the adapter. An index listing proves the post-hook ran. A transform of a known point proves the projection library is present and working. A note distinguishes the first two, which prove connectivity, from the last two, which prove the pipeline will behave. the check what it proves select postgis_full_version() the extension exists here select st_astext(st_makepoint(1, 2)) the type survives the adapter select indexname from pg_indexes the post-hook actually ran select st_transform(p, 25832) PROJ is present and correct

First, verify the runtime itself before any model runs — fail fast if the extension is missing or the wrong version:

sql
-- analyses/check_postgis_runtime.sql
select
  PostGIS_Version()             as postgis_version,
  PostGIS_GEOS_Version()        as geos_version,
  (select count(*) from spatial_ref_sys where srid = 4326) as has_wgs84;

Then add a singular test that sweeps marts for invalid, null, or empty geometry — the three states that silently corrupt downstream layers:

sql
-- tests/assert_valid_geometries.sql
select
  property_id,
  geometry_wgs84
from {{ ref('fact_property_proximity') }}
where
  geometry_wgs84 is null
  or not ST_IsValid(geometry_wgs84)
  or ST_IsEmpty(geometry_wgs84)

Pin SRID and not-null expectations declaratively in the model’s schema YAML so the contract is visible in code review:

yaml
# models/marts/_marts.yml
models:
  - name: fact_property_proximity
    columns:
      - name: geometry_wgs84
        tests:
          - not_null
          - dbt_utils.expression_is_true:
              expression: "ST_SRID(geometry_wgs84) = 4326"

Wire these into CI with dbt build --select +fact_property_proximity, which runs the upstream models and their tests together so a bad geometry never reaches the serving layer.

Advanced Patterns

Incremental spatial models. For tables exceeding millions of rows, switch from full rebuilds to incremental materialization with a unique_key and is_incremental() block. Apply a bounding-box filter with the && operator inside the incremental predicate to restrict the scan to recently changed geometries, which typically cuts compute 60–80% versus a full-table rebuild:

sql
{{ config(materialized='incremental', unique_key='property_id') }}
-- ...
{% if is_incremental() %}
  where p.updated_at > (select max(updated_at) from {{ this }})
    and p.geometry_wgs84 && (select ST_Extent(geometry_wgs84) from {{ this }})
{% endif %}

Quarantine over silent repair. When source geometries are invalid, resist auto-correcting with ST_MakeValid() in place. Route invalid rows to a quarantine table so GIS teams can audit ingestion errors and lineage is preserved, rather than masking upstream corruption.

Multi-engine portability. If you run lightweight validation on the DuckDB spatial extension in CI before promoting to PostGIS in production, keep models portable by routing engine-specific ST_ aggregate names through dispatched macros. Deciding which engine owns which workload is the subject of choosing the right spatial adapter.

Troubleshooting

Symptom Root cause Fix
type "geometry" does not exist at compile Extension not created before the model parsed Add the CREATE EXTENSION IF NOT EXISTS postgis on-run-start hook, or pre-create it as superuser
ST_Transform returns NULL Geometry tagged SRID 0 — no source projection to transform from Call ST_SetSRID with the true source SRID before ST_Transform
Spatial join runs a Seq Scan and times out No GiST index, or stale statistics after a bulk load Add a USING GIST (...) index in a post_hook and run ANALYZE; confirm with EXPLAIN
ST_Intersects / ST_DWithin returns wrong or empty results Mismatched SRIDs across joined layers Normalize every layer to the canonical SRID in staging via the CRS macros
function st_area(geometry) ... ambiguous or huge area values Mixing geometry and geography; planar area on lon/lat Cast to ::geography for true-metre measures, or transform to a metric SRID first

What the search path actually decides

The most common first-day failure with dbt on PostGIS is not the installation but the search path. PostGIS installs its functions into a schema, and unless that schema is on the connection’s search_path, every ST_ call must be schema-qualified or it fails with a message that suggests the extension is missing when it is merely invisible. Because dbt opens a fresh connection per thread, setting the path once in a session or in a psql shell proves nothing about what the build will see.

The reliable arrangement is to set the search path in the profile so it applies to every connection dbt opens, and to assert the extension’s presence in an on-run-start hook so a database without it fails in seconds rather than on the first model that needs geometry. Doing both is worth the two lines: the profile setting makes the ordinary case work, and the assertion makes the broken case legible instead of confusing.

Keeping the installation stable over time

Getting PostGIS working is a one-day task; keeping three environments agreeing about it is an ongoing one. PostGIS is really three versions stacked — the extension itself, the GEOS geometry engine underneath it, and PROJ for coordinate transformation — and each can drift independently. Two of those drift silently. A missing function from an older extension fails loudly and is fixed in minutes; a different GEOS build that repairs an invalid polygon slightly differently, or a PROJ upgrade that changes the datum grid used for a transformation, produces different numbers with no error at all.

The defence is small and worth adding on day one. An on-run-start hook that queries the library version and raises a compiler error when it does not match the project’s expectation costs milliseconds and fails before any model builds. Pinning the exact image tag wherever an environment is created from code — never latest — stops the drift from happening rather than merely reporting it. And a transformation parity test, asserting that a known coordinate lands within half a metre of a known projected position, catches the class of change that version assertions cannot see. The whole sequence, including a safe upgrade order that keeps production out of the experiment, is in managing PostGIS extension versions across environments.

One operational habit is worth adopting alongside it: log postgis_full_version() on every run. It costs one line in a hook and turns “when did this start?” from archaeology into a search through build logs.

Frequently asked questions

Should PostGIS live in its own schema or in public?

Its own schema, with that schema on the search path. Keeping the extension out of public means a model that accidentally creates a table with a colliding name cannot shadow a PostGIS function, and it makes the grant surface explicit. The cost is one line of profile configuration, applied to every connection.

Does the adapter need any spatial-specific configuration?

No — dbt-postgres has no spatial awareness, and that is a feature rather than a gap. Geometry is an ordinary column type as far as the adapter is concerned, so everything spatial lives in your SQL, your hooks and your tests, where it is visible and versioned rather than hidden in adapter behaviour.

Up: Part of Core Fundamentals & Architecture for dbt Geospatial.

Explore this section