Handling mixed-SRID source feeds

This page normalises a feed whose rows do not share one coordinate reference system: detect the SRID per row, transform each row from its own CRS rather than an assumed one, quarantine geometry whose CRS is unknown, and prove afterwards that exactly one SRID remains.

When to use this approach

  • A single source table contains more than one SRID. Usually because several suppliers write to it, or because a historical load predates a policy.
  • ST_Transform is failing with “unknown spatial reference”. That error means SRID 0 — geometry with no declared CRS — and the fix is upstream of the transform.
  • Your canonical SRID policy exists but the data predates it. The policy itself belongs in CRS governance policy; this page is the migration.

Prerequisites

  • PostGIS with a populated spatial_ref_sys table, or an engine with an equivalent transform capability.
  • A canonical SRID agreed and stored as a project var.
  • A quarantine table, because some rows will have no recoverable CRS.
  • Knowledge of what each supplier actually sends — the audit in step 1 usually produces surprises worth confirming with them.

Step-by-step instructions

1. Audit the mix before changing anything

sql
-- analyses/srid_profile.sql
select
    st_srid(geom)                                        as srid,
    coalesce(srs.srtext, '(unknown)')                    as definition,
    count(*)                                             as features,
    round(st_xmin(st_extent(geom))::numeric, 3)          as xmin,
    round(st_ymin(st_extent(geom))::numeric, 3)          as ymin,
    min(source_system)                                   as example_source
from {{ source('ops', 'zones_raw') }} g
left join spatial_ref_sys srs on srs.srid = st_srid(g.geom)
group by 1, 2
order by features desc
text
 srid | definition          | features |    xmin    |    ymin    | example_source
------+---------------------+----------+------------+------------+----------------
 4326 | GEOGCS["WGS 84"…    |   184220 |      5.866 |     47.270 | supplier_a
 25832| PROJCS["ETRS89 …    |    41880 | 280000.000 | 5230000.00 | supplier_b
    0 | (unknown)           |     6402 |    448120. |   5411900. | legacy_load

The extent column is the diagnostic that matters. SRID 0 rows whose coordinates are in the hundreds of thousands are projected data that lost its declaration — recoverable, because the extent identifies the likely CRS. SRID 0 rows in the range ±180 are almost certainly WGS84.

Verify each guess against the supplier rather than the extent alone. An extent narrows the candidates to a family of projections; only the supplier can tell you which member of it.

Reading coordinate magnitude to identify an undeclared coordinate system A number line of coordinate magnitudes with three labelled bands. Values under about 180 indicate degrees and therefore a geographic system such as WGS84. Values in the hundreds of thousands indicate a metre-based projected system such as a UTM zone. Values in the millions indicate a web mercator or a national grid with a large false northing. A caption warns that the band narrows the candidates but does not identify the exact system. |x| ≤ 180 degrees — geographic likely EPSG:4326 10⁵ – 10⁶ metres — projected a UTM or national zone > 10⁶ metres with a large false origin web mercator or a national grid The magnitude narrows the family. Only the supplier identifies the member.

2. Build an explicit SRID map

Guesswork belongs in one reviewable place, not scattered through a case expression in a model.

yaml
# dbt_project.yml
vars:
  canonical_srid: 4326
  srid_recovery_map:
    - {source_system: 'legacy_load', assumed_srid: 25832, evidence: 'confirmed with supplier 2026-07-30'}
    - {source_system: 'partner_feed_c', assumed_srid: 3035, evidence: 'extent matches ETRS89-LAEA; unconfirmed'}
sql
-- models/staging/stg_zones_srid_resolved.sql
{{ config(materialized = 'table') }}

with mapped as (
    select
        z.zone_id,
        z.source_system,
        z.geom,
        st_srid(z.geom) as declared_srid,
        case
            when st_srid(z.geom) > 0 then st_srid(z.geom)
            {%- for m in var('srid_recovery_map') %}
            when z.source_system = '{{ m.source_system }}' then {{ m.assumed_srid }}
            {%- endfor %}
            else null
        end as effective_srid
    from {{ source('ops', 'zones_raw') }} z
)

select * from mapped

Verify the map covers what it should and nothing more:

sql
select source_system, declared_srid, effective_srid, count(*)
from {{ ref('stg_zones_srid_resolved') }}
group by 1, 2, 3 order by 4 desc;
-- Rows with a null effective_srid are the quarantine population

3. Transform from each row’s own SRID

The mistake this whole page exists to prevent is a single ST_Transform(geom, 4326) applied to a mixed feed: it succeeds for rows already carrying a declaration and silently mangles nothing — but it also cannot fix rows whose declaration is missing or wrong, because the transform trusts the declared SRID.

sql
-- models/staging/stg_zones.sql
{{ config(materialized = 'table') }}

select
    zone_id,
    source_system,
    declared_srid,
    effective_srid,
    st_transform(
        st_setsrid(geom, effective_srid),   -- stamp the recovered SRID first
        {{ var('canonical_srid') }}
    ) as geom
from {{ ref('stg_zones_srid_resolved') }}
where effective_srid is not null

ST_SetSRID before ST_Transform is the whole trick: it declares what the coordinates are without moving them, so the transform then has a correct source CRS to work from. Calling ST_Transform on SRID 0 geometry raises an error; calling it after stamping the wrong SRID moves the geometry to the wrong place, silently, which is worse.

Verify the transformed extent lands where the data belongs:

sql
select st_extent(geom) from {{ ref('stg_zones') }};
-- Compare against the known footprint; a country-sized dataset in the Gulf of Guinea means a wrong stamp

4. Quarantine the unrecoverable rows

sql
-- models/staging/stg_zones_quarantine.sql
select
    zone_id, source_system, declared_srid,
    st_extent(geom)::text as extent_hint,
    'no SRID declared and no recovery rule' as quarantine_reason,
    '{{ run_started_at }}'::timestamp as quarantined_at
from {{ ref('stg_zones_srid_resolved') }}
where effective_srid is null
group by 1, 2, 3, geom
sql
-- tests/assert_quarantine_bounded.sql
select count(*) as unrecoverable
from {{ ref('stg_zones_quarantine') }}
where quarantined_at > current_date - interval '1 day'
having count(*) > {{ var('max_daily_quarantine', 100) }}

Verify the quarantine shrinks over time as recovery rules are confirmed. A quarantine that only grows is a backlog, not a control.

5. Prove the mix is gone

yaml
models:
  - name: stg_zones
    columns:
      - name: geom
        tests:
          - not_null
          - dbt_utils.expression_is_true:
              expression: "st_srid(geom) = {{ var('canonical_srid') }}"
sql
-- tests/assert_single_srid.sql
select st_srid(geom) as srid, count(*)
from {{ ref('stg_zones') }}
group by 1
having count(distinct st_srid(geom)) over () > 1
The order of operations that makes a mixed-SRID feed recoverable Rows enter with three states: a correct declared SRID, no declaration but a recovery rule, and no declaration and no rule. The first passes straight to transform. The second is stamped with the recovered SRID and then transformed. The third is quarantined. A warning below shows the wrong order — transforming before stamping — which errors on undeclared rows and silently relocates wrongly stamped ones. SRID declared and correct undeclared, rule exists undeclared, no rule ST_SetSRID ST_Transform → canonical quarantine wrong order: transform before stamping — errors, or relocates silently What a wrong SRID stamp does to a dataset's position A schematic map with a country outline. Correctly stamped and transformed data sits inside the outline. The same coordinates stamped with a neighbouring UTM zone land about four hundred kilometres to the east, still on land and still plausible. The same coordinates stamped as degrees land in the Gulf of Guinea near zero, zero, which is obviously wrong. A caption notes that only the middle case survives review unnoticed. correct stamp inside the known footprint neighbouring zone stamped 400 km east — still plausible stamped as degrees off the map — caught immediately The dangerous error is the one that still looks like data.

Configuration reference

Setting Where Example Note
canonical_srid project var 4326 One value for the whole project
srid_recovery_map project var list of rules Each rule carries its evidence; unconfirmed rules are visible
ST_SetSRID staging SQL before transform Declares, does not move
ST_Transform staging SQL after stamping Moves, trusting the declaration
max_daily_quarantine project var 100 Turns a growing backlog into a failing build
extent assertion test known footprint Catches a wrong stamp, which no SRID test can see

Gotchas & edge cases

  • A wrong SRID stamp is worse than none. No declaration produces an error; a wrong one produces plausible coordinates in the wrong place. Confirm recovery rules rather than inferring them.
  • ST_SetSRID does not reproject. It relabels. Using it where ST_Transform was meant leaves the coordinates untouched under a new label — the classic cause of data appearing off the coast of Africa.
  • Mixed SRIDs make joins silently empty. PostGIS raises an error for mismatched SRIDs in most predicates, but a geography cast or a grid-key join will happily return nothing at all.
  • The recovery map is a living document. Add the evidence and the date; an unconfirmed rule that survives a year becomes folklore.
  • Some feeds change SRID between deliveries. Test per delivery rather than once, and let the drift monitoring in alerting on geometry drift between runs catch the change.

FAQ

Can I infer the SRID reliably from the coordinate extent?

You can narrow it to a family — degrees versus metres, and roughly which zone — but not to a specific system, because neighbouring projections produce very similar coordinates over a small area. Use the extent to form a hypothesis and confirm it with the supplier or with a known control point whose true position you can check.

Should I fix the SRID in the source system instead?

Always, if you can. The staging recovery is a compensating control for something that should be declared at the point of export, and every recovery rule is a permanent liability. Raise it with the supplier and treat the map as a migration aid rather than architecture.

What about feeds that mix SRIDs within one delivery?

The per-row approach here handles that already: effective_srid is computed per row and the transform reads it per row. What it cannot handle is a delivery where the same source system sends two SRIDs without declaring either — there, a single recovery rule will be wrong for half the rows, and the honest answer is to quarantine and escalate.

Does this apply to engines without an SRID concept?

The transform step does not — BigQuery has no SRID, and reprojection must happen before load. The audit and quarantine steps still apply, because the same undeclared-projected-coordinates problem arrives as a range check failure instead, as described in running dbt spatial models on BigQuery GIS.

Up: Part of CRS Governance Policy.