CRS Cascade Failure Runbook

A coordinate reference system cascade begins with one silent projection change — an upstream feed shipping Web Mercator where it historically delivered geographic degrees — and ends with wrong distances, wrong areas, and empty joins spreading through every downstream layer that consumed the mistagged feature. This runbook is the containment-first procedure for that incident: catching the drift the moment gis.spatial.crs_drift_total increments, tracing which downstream layers already ingested the poison, freezing the propagation, and backfilling correct geometry. It is one of the spatial pipeline incident runbooks under the spatial incident response and tooling program.

CRS drift cascade across downstream layers On the left, an upstream source box marked SRID drift feeds a join stage. From the join, three arrows fan out to downstream layers: a distances layer and a spatial-join layer are both marked contaminated, while a tiles layer is marked at risk. A dashed containment-freeze bar sits between the join and the downstream layers, showing where propagation is halted. A backfill arrow loops from a corrected-source box back into the two contaminated layers. Upstream SRID drift 4326→3857 Join stage fan-out freeze Distances layer contaminated Spatial join layer contaminated Tiles layer at risk Corrected source backfill

Problem Framing

CRS drift corrupts by arithmetic, not by structure. A geometry mistagged as 4326 but actually carrying 3857 metres is perfectly valid — ST_IsValid returns true, the ring closes, the vertices are ordered correctly — so the topology gate waves it through. What breaks is every calculation that trusts the declared SRID: ST_Distance returns a number in the wrong unit, ST_Area is off by the square of the projection scale, and ST_Intersects against a correctly-tagged layer returns empty because the coordinates land thousands of kilometres away. The standing coordinate reference system validation practice exists to catch this at ingest; this runbook handles the incident when a change slipped past it and is already propagating.

The word cascade is the whole problem. A topology defect stays in the one corrupt feature. A CRS mistag spreads: the mistagged geometry enters a join, the join writes derived features into a downstream layer, that layer feeds another join, and within a few pipeline stages the original single-source error has contaminated tables that never touched the upstream feed directly. This is why containment — freezing the propagation front — takes priority over repairing the origin. Repairing the source while downstream joins keep running just re-poisons the layers you cleaned.

The signal that distinguishes a cascade from an isolated mistag is the fan-out shape in the metrics: gis.spatial.crs_drift_total increments on one source, and within minutes the derived-freshness or empty-join-rate anomalies appear on layers keyed to entirely different sources. That temporal fan-out is your evidence that the error crossed a join boundary.

Detect

Because a single drift event is enough to start a cascade, the detector fires on any increase — there is no benign threshold. The rule below pages the moment the drift counter moves, carrying the expected_srid and observed_srid labels so the responder knows immediately which projection swap occurred.

groups:
  - name: crs-cascade
    rules:
      - alert: CRSDriftDetected
        expr: increase(gis_spatial_crs_drift_total[10m]) > 0
        for: 0m
        labels: { severity: critical }
        annotations:
          summary: "CRS drift on {{ $labels.layer }}: expected {{ $labels.expected_srid }}, observed {{ $labels.observed_srid }}"
          runbook_url: "/spatial-incident-response-and-tooling/spatial-pipeline-incident-runbooks/crs-cascade-failure-runbook/"
      # Secondary confirmation: coordinates outside the declared SRID domain
      - alert: CRSCoordinateDomainViolation
        expr: |
          max(gis_spatial_coordinate_out_of_domain_ratio) by (layer) > 0
        for: 5m
        labels: { severity: critical }

The drift counter is emitted by comparing each incoming feature’s declared SRID against a ST_Transform round-trip domain check: if coordinates declared as geographic 4326 fall outside the ±180 / ±90 degree envelope, they are not degrees. Confirm the alert is a genuine projection swap and not a one-off malformed record by checking that the increment is sustained across the batch, not a single row.

Triage

Triage for a cascade is a blast-radius trace, and it is the most important step in this runbook. The origin is already named by the alert’s source and layer labels; the job is to enumerate which downstream layers consumed the poisoned features before you froze anything. Walk the lineage from the origin layer forward, checking each dependent layer for coordinates outside their expected SRID domain.

-- Blast-radius sweep: which layers hold coordinates outside their declared SRID domain?
SELECT layer,
       ST_SRID(geom)                                   AS declared_srid,
       COUNT(*) FILTER (
         WHERE ST_SRID(geom) = 4326
           AND (ST_X(ST_PointOnSurface(geom)) NOT BETWEEN -180 AND 180
             OR ST_Y(ST_PointOnSurface(geom)) NOT BETWEEN  -90 AND  90)
       )                                               AS out_of_domain
FROM prod.layer_registry_geoms
WHERE layer = ANY (
  -- downstream dependents of the drifting source, from the lineage graph
  SELECT dependent FROM lineage.edges WHERE ancestor = 'roads_feed'
)
GROUP BY 1, 2
HAVING COUNT(*) FILTER (WHERE ST_SRID(geom) = 4326
         AND (ST_X(ST_PointOnSurface(geom)) NOT BETWEEN -180 AND 180)) > 0;

The lineage graph that powers this sweep is the same one built for mapping geospatial data lineage for observability; without it you are guessing at the blast radius. Classify each affected layer as contaminated (already holds mistagged features) or at risk (consumes the origin but has not yet run its join). Contaminated layers need a backfill; at-risk layers need only the freeze.

Remediate

Remediation runs freeze, then correct-at-source, then backfill — strictly in that order. Freezing first stops the propagation front so your backfill is not overwritten by an in-flight join.

-- 1. FREEZE: halt jobs that read the origin and its contaminated dependents
--    (executed via the orchestrator; shown here as the lineage set to pause)
SELECT dependent FROM lineage.edges WHERE ancestor = 'roads_feed';

-- 2. CORRECT AT SOURCE: re-tag then transform the mistagged geometries.
--    The feature is physically 3857 but was labelled 4326 — assign the
--    real SRID first, THEN transform to the authoritative storage SRID.
UPDATE staging.roads_feed
SET geom = ST_Transform(ST_SetSRID(geom, 3857), 4326)
WHERE ST_SRID(geom) = 4326          -- mislabelled
  AND ST_X(ST_PointOnSurface(geom)) NOT BETWEEN -180 AND 180;

-- 3. BACKFILL: recompute derived features in each contaminated layer
--    from the corrected source, one dependent at a time, then unfreeze.

The subtlety that trips teams is step two: the fix is not a ST_SetSRID alone. ST_SetSRID only relabels metadata — it does not move coordinates — so relabelling a 3857-metre geometry as 4326 degrees leaves numbers in the ±20,000,000 range tagged as degrees, which is worse. The correct repair assigns the true source SRID (ST_SetSRID(geom, 3857)) and then reprojects to the authoritative storage SRID with ST_Transform. Backfill each contaminated dependent from the corrected origin before unfreezing that dependent’s jobs, propagating the correction forward along the same lineage edges the poison travelled.

Post-mortem and Verification

Verify closure layer by layer, not globally. For each layer that was contaminated, the out-of-domain count must return to zero and a sample distance or area calculation must produce a sane magnitude. The cascade is only closed when the last downstream dependent passes.

-- Closure check per layer: no coordinates outside the declared SRID domain
SELECT layer, COUNT(*) AS out_of_domain
FROM prod.layer_registry_geoms
WHERE ST_SRID(geom) = 4326
  AND ST_X(ST_PointOnSurface(geom)) NOT BETWEEN -180 AND 180
GROUP BY 1;
-- Expect: zero rows

The post-mortem’s central question is why the drift crossed the ingest gate at all — a projection swap should be caught at the source, not discovered three layers downstream. Harden detection by adding the domain check to the ingest gate for that source if it was missing, and add a per-layer out-of-domain gauge so a future cascade lights up on the second layer instead of after a full fan-out. The related practice of detecting CRS drift in CI pipelines is the durable fix: catch the projection change in the pull request that introduced it, before it ever ships.

Gotchas

ST_SetSRID is not a fix, it is a relabel. Assigning a new SRID without transforming leaves coordinates in the old projection’s units under a new label. The two-step re-tag-then-transform in the remediation SQL is mandatory; a lone ST_SetSRID deepens the corruption.

Axis-order swaps masquerade as drift. A feed that flips latitude/longitude order looks like a domain violation but is not a projection change — the fix is a coordinate swap, not a reprojection. Distinguish the two before repairing, using the axis-order checks in catching axis-order swaps in CRS validation.

Empty joins hide the blast radius. A contaminated layer often shows fewer rows downstream, not error rows, because ST_Intersects silently returns false when coordinates are displaced. Do not treat a quiet join as a clean one during triage; run the domain sweep explicitly.

FAQ

Why freeze downstream jobs before fixing the source?

Because the cascade propagates by join. If you correct the origin while dependent jobs keep running, an in-flight join reads a not-yet-corrected snapshot and re-writes poisoned features into a layer you already cleaned. Freezing the propagation front first makes the backfill converge instead of chasing a moving target.

How do I tell CRS drift from an axis-order swap?

Both produce coordinates that look wrong, but a projection swap moves coordinates into a different numeric range (metres vs degrees), while an axis swap keeps the range and transposes X and Y. Check whether the magnitudes are plausible degrees with X and Y exchanged before assuming a reprojection is needed.

Can I just reproject the whole downstream layer to fix it?

No — reprojecting a contaminated layer applies a transform to features that are already displaced, compounding the error for correctly-tagged rows mixed in. The fix must correct the mistagged geometries at the source and backfill derived features, not blanket-transform the affected table.

What if the upstream provider insists nothing changed?

The domain check is objective evidence: coordinates declared as 4326 cannot fall outside ±180 / ±90. Attach the gis.spatial.crs_drift_total series and a sample of out-of-domain coordinates to the provider ticket. Often the change is an unannounced default in their export tool rather than a deliberate reprojection.

Should CRS drift always be critical severity?

Yes, at least until contained. Even a small drift event can seed a cascade, and the cost of the fan-out is far higher than the cost of an early page. The severity can be downgraded once the blast radius is scoped and frozen, but the initial detector should never wait.