Spatial Reference System Management

Inconsistent coordinate reference systems are the silent killers of spatial analytics. When geometries from disparate sources meet in a spatial join without an explicit, shared spatial reference identifier (SRID), the result is not an error — it is a wrong answer: false-negative joins, distance calculations that drift by kilometres, and KPIs that cannot be reproduced from one run to the next. The failure is silent precisely because every individual function call succeeds; only the combination is incoherent.

Spatial Reference System Management is the discipline that closes that gap. This page is part of the Spatial Data Architecture & Governance practice, and it covers one specific workflow challenge: how to make CRS alignment a deterministic, testable transformation primitive inside dbt rather than an ad-hoc concern scattered across models. The goal is a single canonical storage CRS, enforced at the staging layer, validated by tests, and preserved through every downstream join and aggregation. Once coordinate systems are treated with the same rigour as data types and primary keys, spatial pipelines become predictable, auditable, and safe to scale.

Prerequisites

Before enforcing a canonical CRS in dbt, confirm the following are in place:

  • dbt version: dbt Core 1.5+ (for stable on_schema_change behaviour on incremental models). The patterns below are written for dbt-postgres; cross-engine notes cover dbt-duckdb 1.6+ and dbt-bigquery.
  • Spatial engine: PostGIS 3.1+ on PostgreSQL 13+, or the DuckDB spatial extension 1.1+, or BigQuery GIS. Each ships the ST_Transform, ST_SetSRID, and ST_SRID functions this workflow depends on. See setting up PostGIS with dbt for the adapter baseline.
  • Database permissions: the dbt service role needs SELECT on the raw source tables, CREATE/USAGE on the staging schema, and — for PostGIS — read access to the spatial_ref_sys catalog table that backs every projection lookup.
  • Environment variables: the target schema and the canonical SRID should come from env_var() so the same project compiles cleanly across dev, CI, and prod. Define DBT_PG_SCHEMA and DBT_CANONICAL_SRID in each environment.
  • A declared canonical SRID: decide this before writing models. 4326 (WGS 84 lat/long) maximizes interoperability; a localized projected system such as 26918 (UTM zone 18N) preserves planar distance and area for regional analysis. Pick one storage CRS per dataset and document the rationale.

Architecture context

Spatial Reference System Management lives at the boundary between raw ingestion and the staging layer of the spatial DAG. Raw geometries arrive with implicit, mixed, or missing SRIDs; the normalization model is the gate that converts that uncertainty into a single guaranteed CRS before anything downstream can join on it. How this stage connects to upstream sources and downstream marts is part of the broader spatial model dependency graph.

CRS normalization gate at the staging layer Four raw sources arriving with mixed or missing SRIDs — 4326 WGS 84, 3857 Web Mercator, 26918 UTM zone 18N, and 0 or NULL unknown — converge into a single CRS normalization gate that sits at the staging layer. Inside the gate, three ordered steps run: step one screens and repairs topology with ST_IsValid and ST_MakeValid, step two labels the source projection with ST_SetSRID, and step three reprojects to the canonical CRS with ST_Transform. The gate emits one canonical SRID where every geometry is valid and audited, which then fans out to intermediate models, marts, and downstream consumers. No geometry can reach a downstream join without passing through the gate. Raw sources — mixed / implicit SRIDs SRID 4326 WGS 84 lat/long SRID 3857 Web Mercator SRID 26918 UTM zone 18N SRID 0 / NULL unknown SRS CRS normalization gate staging layer · one gate, one CRS 1 · validate topology ST_IsValid ST_MakeValid 2 · label projection ST_SetSRID 3 · reproject to canonical ST_Transform audits source SRID · order matters Canonical SRID one CRS · valid audited · indexed srs_status = VALID Intermediate models spatial joins Marts aggregate + KPIs Consumers BI · maps · serving No geometry reaches a downstream join without passing the gate — uncertainty becomes one guaranteed CRS.

The contract this stage publishes is simple and absolute: every geometry leaving staging carries the canonical SRID, is topologically valid, and records where it came from. Establishing that baseline requires three architectural commitments:

  1. Explicit SRID declaration at ingestion — reject or quarantine geometries with 0 or NULL SRIDs before they enter the transformation graph, rather than letting a downstream ST_Transform guess.
  2. Deterministic transformation paths — normalize all incoming data to the canonical CRS in one place, avoiding scattered ST_Transform calls whose order and tolerance you cannot reason about.
  3. Immutable metadata tracking — log the source projection, the transformation timestamp, and a validity status for every row, so lineage and audits can reconstruct exactly how a coordinate reached its stored value.

Configuration walkthrough

CRS handling starts in project configuration, not in model SQL. Externalize the canonical SRID and the set of accepted source projections so they are version-controlled and changeable in one edit.

In dbt_project.yml, declare the canonical target and the registry of projections you trust:

yaml
# dbt_project.yml
vars:
  canonical_srid: "{{ env_var('DBT_CANONICAL_SRID', '4326') }}"
  accepted_source_srids: [4326, 3857, 26918]

models:
  my_spatial_project:
    staging:
      +materialized: incremental
      +tags: ['spatial', 'crs_normalization']

The profile points dbt at the spatial warehouse and keeps secrets out of the repo via env_var():

yaml
# profiles.yml
my_spatial_project:
  target: dev
  outputs:
    dev:
      type: postgres
      host: "{{ env_var('DBT_PG_HOST') }}"
      user: "{{ env_var('DBT_PG_USER') }}"
      password: "{{ env_var('DBT_PG_PASSWORD') }}"
      dbname: "{{ env_var('DBT_PG_DBNAME') }}"
      schema: "{{ env_var('DBT_PG_SCHEMA', 'analytics') }}"
      threads: 4

For PostGIS, confirm the extension is present at the start of every run rather than discovering its absence mid-build. An on-run-start hook makes the dependency explicit:

yaml
# dbt_project.yml
on-run-start:
  - "CREATE EXTENSION IF NOT EXISTS postgis"

With the canonical SRID resolved from a variable, no model hardcodes a projection number. Changing the storage CRS for the whole project — for example, migrating a regional dataset from 4326 to 26918 — becomes a single variable change plus a full refresh, not a hunt through dozens of model files.

Core implementation

The normalization model is the heart of this workflow. It runs incrementally, validates topology, audits the incoming SRID, and emits geometries in exactly one CRS. The following staging model is database-agnostic in shape; the ST_ calls shown are PostGIS, with engine notes below.

sql
{{ config(
    materialized = 'incremental',
    unique_key = 'geo_id',
    on_schema_change = 'sync_all_columns',
    tags = ['spatial', 'staging', 'crs_normalization']
) }}

{% set canonical_srid = var('canonical_srid') | int %}
{% set accepted = var('accepted_source_srids') %}

WITH raw_input AS (
    SELECT
        id AS geo_id,
        geometry_raw,
        COALESCE(source_srid, 0) AS source_srid,
        ingested_at
    FROM {{ source('spatial_ingest', 'raw_geometries') }}
    {% if is_incremental() %}
        WHERE ingested_at > (SELECT MAX(ingested_at) FROM {{ this }})
    {% endif %}
),

validated AS (
    SELECT
        geo_id,
        source_srid,
        ingested_at,
        CASE
            WHEN ST_IsValid(geometry_raw) THEN geometry_raw
            ELSE ST_MakeValid(geometry_raw)
        END AS geometry_validated,
        CASE
            WHEN source_srid = 0 THEN 'UNKNOWN_SRS'
            WHEN source_srid NOT IN ({{ accepted | join(', ') }}) THEN 'NON_STANDARD_SRS'
            ELSE 'VALID'
        END AS srs_status
    FROM raw_input
    WHERE geometry_raw IS NOT NULL
),

normalized AS (
    SELECT
        geo_id,
        CASE
            WHEN source_srid IN ({{ accepted | join(', ') }}) THEN
                ST_Transform(ST_SetSRID(geometry_validated, source_srid), {{ canonical_srid }})
            ELSE NULL
        END AS geometry_canonical,
        source_srid,
        srs_status,
        CURRENT_TIMESTAMP AS normalized_at
    FROM validated
)

SELECT * FROM normalized

Three guarantees are enforced in one pass. First, topology validation: ST_IsValid screens every geometry and ST_MakeValid repairs self-intersections, unclosed rings, and collapsed multiparts before they can crash a spatial operator downstream. Second, SRID auditing: rather than silently coercing, the model tags each row VALID, NON_STANDARD_SRS, or UNKNOWN_SRS, so non-conforming inputs are visible instead of buried. Third, deterministic projection: ST_SetSRID attaches the declared source SRID to geometries that carry coordinates but no metadata, and ST_Transform reprojects them to the single canonical CRS resolved from var('canonical_srid').

Note the ordering — ST_SetSRID before ST_Transform. ST_SetSRID only relabels the geometry’s SRID metadata without moving any coordinates; ST_Transform then does the actual datum/projection math. Reversing them, or calling ST_Transform on a geometry whose SRID is 0, raises an “Input geometry has unknown (0) SRID” error in PostGIS. For the exact parameterized behaviour, align with the official PostGIS ST_Transform documentation, which governs tolerance handling and guards against silent coordinate truncation during reprojection.

Materializing this as an incremental model with on_schema_change = 'sync_all_columns' keeps the pipeline cheap (only new geometries reproject) and resilient to upstream column additions. Because the canonical geometry is computed once at staging, every downstream join operates on identically referenced coordinates — no repeated reprojection, no implicit conversions in the query plan.

Declaring the spatial index

Pre-computed canonical geometries are only fast if the index matches. In PostGIS, attach a GiST index on the canonical column with a post-hook so it is rebuilt with the model:

sql
{{ config(
    post_hook = "CREATE INDEX IF NOT EXISTS idx_{{ this.name }}_geom
                 ON {{ this }} USING GIST (geometry_canonical)"
) }}

A GiST index accelerates ST_Intersects and ST_DWithin predicates through R-tree traversal — but only when both sides of the join share the canonical SRID. Mixing projected and unprojected geometries in one query forces an implicit conversion that bypasses the index and degrades to a full table scan. In BigQuery, GEOGRAPHY columns are indexed automatically and benefit from clustered partitioning when the CRS is deterministic; the same “one canonical CRS” rule applies.

Validation & testing

Normalization is only trustworthy if tests prove it. Start by confirming the engine itself is present and the right version, then assert the contract on the data.

Verify the PostGIS install before relying on its functions:

sql
SELECT PostGIS_Version();
-- expected: 3.1 USE_GEOS=1 USE_PROJ=1 USE_STATS=1

Sweep the staging output for any geometry that escaped normalization — wrong SRID or invalid topology:

sql
SELECT geo_id, ST_SRID(geometry_canonical) AS srid, srs_status
FROM {{ ref('stg_geometries_normalized') }}
WHERE geometry_canonical IS NOT NULL
  AND (ST_SRID(geometry_canonical) <> 4326 OR NOT ST_IsValid(geometry_canonical));
-- expected: 0 rows

Then encode those checks as dbt tests so CI enforces them on every PR. A schema-level test catches the audit-status escapes and null geometries; generic tests assert the SRID contract:

yaml
# models/staging/_staging.yml
version: 2

models:
  - name: stg_geometries_normalized
    description: "Geometries normalized to the canonical CRS at the staging layer."
    columns:
      - name: geo_id
        tests:
          - unique
          - not_null
      - name: srs_status
        tests:
          - accepted_values:
              values: ['VALID']
              config:
                severity: warn   # surface NON_STANDARD_SRS / UNKNOWN_SRS without failing the build
      - name: geometry_canonical
        tests:
          - not_null
          - assert_srid:
              srid: 4326

The assert_srid test is a custom generic test that compiles to an ST_SRID predicate:

sql
-- tests/generic/assert_srid.sql
{% test assert_srid(model, column_name, srid) %}
SELECT {{ column_name }}
FROM {{ model }}
WHERE {{ column_name }} IS NOT NULL
  AND ST_SRID({{ column_name }}) <> {{ srid }}
{% endtest %}

Beyond SRID, add a bounding-box sanity test: assert that canonical coordinates fall within the geographic limits of the expected region (for 4326, longitude within ±180 and latitude within ±90; for a regional UTM zone, the zone’s valid envelope). A geometry that reprojects to coordinates outside that envelope is the clearest signal of a mislabeled source SRID. Wiring these assertions into CI catches projection mismatches before they reach production, where reconciliation is far more expensive.

Advanced patterns

Parameterize the transformation as a macro. Once more than one model needs canonical geometries, the ST_SetSRID + ST_Transform pair belongs in a reusable macro rather than copy-pasted SQL. A normalize_crs(geom_col, source_srid_col, target_srid) macro centralizes the ordering rule and lets the target SRID be overridden per call. Generalizing spatial transforms this way is the subject of building custom spatial macros, and pushing the whole CRS workflow into orchestrated, parameterized models is covered in depth in automating CRS conversions in dbt pipelines.

Multi-engine compatibility. The function names differ by adapter even when the intent is identical. In the DuckDB spatial extension, ST_Transform takes proj-string or authority arguments and is ideal for lightweight CI validation before promotion to PostGIS — see DuckDB spatial extension integration. In BigQuery, the GEOGRAPHY type is implicitly WGS 84, so reprojection is a no-op and the registry instead governs which inputs are accepted. A dispatch macro that branches on target.type keeps one logical model portable across all three; weigh the trade-offs in choosing the right spatial adapter.

Incremental reprojection at scale. When canonical geometries are pre-computed in staging, downstream analytical joins never call ST_Transform again — eliminating repeated reprojection and the memory pressure it causes during spatial aggregations. This matters most when handling large geospatial datasets, where reprojection overhead inside a join is a common bottleneck. For multi-resolution datasets, materialize separate canonical views per use case (one in 4326 for cross-platform analytics, one in a projected system for distance/area work) rather than forcing a single projection across every workload.

CRS as a scoping boundary. Some projections expose location precision that violates regional compliance rules. Canonical generalization, fuzzing, or access-tiered projections must be applied consistently before data reaches external consumers — coordinate handling and data security and scoping rules are tightly coupled, because a scoping predicate is only correct if both sides share the canonical SRID. When the canonical SRID or the accepted-projection registry changes, treat it as a schema event and record it through versioning spatial schemas in dbt so the history stays auditable.

Troubleshooting

Symptom Root cause Fix
ST_Transform raises “Input geometry has unknown (0) SRID” Source geometry carries coordinates but no SRID metadata Call ST_SetSRID(geom, source_srid) to label it before ST_Transform; quarantine rows where srs_status = 'UNKNOWN_SRS'
geometry_canonical is NULL after the run Source SRID not in accepted_source_srids, so the CASE returns NULL Add the projection to the registry var, or route NON_STANDARD_SRS rows to a dedicated review model
Spatial join returns no matches despite overlapping data Both sides compared in different SRIDs; the datum/projection shift moves geometries apart Normalize every input to the canonical SRID at staging; add the assert_srid test to fail the build on drift
ST_Intersects query degrades to a full table scan GiST index missing, or ST_Transform applied inside the join defeating it Pre-compute canonical geometry in staging; add the GIST post-hook; never reproject in the join predicate
Coordinates land in the wrong hemisphere or wildly off Source SRID mislabeled (e.g. 3857 web-mercator metres treated as 4326 degrees) Add the bounding-box envelope test; verify the source’s true projection against its spatial_ref_sys entry before trusting it

For every run, capture which source SRID each row carried, the canonical SRID in force at transformation time, and the validity status. That metadata is what lets a regulated platform reconstruct why a stored coordinate has the value it does — turning CRS management from an invisible failure surface into an auditable, reproducible stage of the spatial DAG.

Up one level: Spatial Data Architecture & Governance

Explore this section