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

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.

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

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

Explore this section