Tuning SRID Mismatch Alerts

An SRID mismatch alert that fires on every raw event is worse than silence: upstream providers re-tag coordinate reference systems in bursts, so a single vendor batch can throw hundreds of mismatch events in seconds, bury the on-call engineer in duplicate pages, and let the one genuinely novel re-tag scroll past unread. This page tunes the thresholds, evaluation windows, and hold durations that make an unauthorized-SRID counter page exactly once per real regression rather than once per feature. It builds on defining spatial data trust boundaries and the wider geospatial observability architecture reference, and is written for the data engineers, GIS platform administrators, and SREs who own the pager.

Tuning path from bursty SRID re-tag events to a single page Raw ST_SRID mismatch events enter a rate-evaluation window sized in minutes, pass a burst-envelope threshold, are held by a for-duration debounce, and are then deduplicated and routed by layer and observed SRID so a burst produces one alert instead of one alert per event. Re-tag burst raw ST_SRID events Rate window [5m] · [30m] Threshold burst envelope for: hold debounce 10m Dedup route layer + SRID goal: one page per genuine re-tag, never one page per event

Why SRID mismatch alerts flap

The signal is inherently bursty. A vendor migrates a feed from geographic 4326 to Web Mercator 3857, or a broken export step drops the .prj and everything lands as SRID 0, and the mismatch does not trickle — it arrives as a whole batch at once. If your rule reads an instantaneous rate() over a one-minute window and compares it to a fixed constant, the expression crosses the threshold the moment the batch hits, recovers seconds later when the batch drains, and crosses again on the next partition. Each crossing is a distinct alert lifecycle, so a five-minute ingestion of one bad file can open and close the same alert a dozen times.

There is a second, opposite failure. A slow SRID drift — a feed that mislabels one feature in a thousand because a single upstream worker runs a stale projection table — never produces a burst large enough to trip a spike threshold, so it hides indefinitely. Tuning has to satisfy both: catch the loud burst without flapping, and catch the quiet drift without ignoring it. That tension is why a single window and a single threshold never work, and why this page pairs a short spike window with a long baseline window.

The counter this all hangs on is gis.spatial.crs_drift_total, defined in the geospatial metric taxonomy for ETL as a monotonic counter dimensioned by layer, expected_srid, and observed_srid. Keeping those dimensions is what lets the alert route by which layer re-tagged to which wrong SRID — the deduplication key that collapses a burst into one page.

Emit the counter at the database boundary

Increment the counter from what PostGIS actually stored, not from what the application thinks it sent. A join against a small authority table names the expected SRID per layer and lets a single query surface every mismatch with the labels the alert needs:

-- Every mismatched feature, grouped by the exact dedup key the alert routes on.
SELECT r.layer_name                       AS layer,
       r.expected_srid                    AS expected_srid,
       ST_SRID(f.geom)                     AS observed_srid,
       count(*)                            AS mismatched_features
FROM   staging.feature_ingest  f
JOIN   layer_registry          r USING (layer_name)
WHERE  ST_SRID(f.geom) <> r.expected_srid   -- includes SRID 0 / undefined
GROUP  BY r.layer_name, r.expected_srid, ST_SRID(f.geom);

A thin exporter turns each row into a counter increment. Recording observed_srid is deliberate — a re-tag to 3857 and a dropped-projection 0 are different incidents on the same layer and should not deduplicate into one another:

from opentelemetry import metrics

meter = metrics.get_meter("gis.spatial.crs")
crs_drift = meter.create_counter("gis.spatial.crs_drift_total")

def emit_srid_mismatches(rows):
    for row in rows:
        crs_drift.add(
            row["mismatched_features"],
            {
                "layer": row["layer"],
                "expected_srid": str(row["expected_srid"]),
                "observed_srid": str(row["observed_srid"]),  # keep: it is the incident identity
            },
        )

Choosing the rate window

The window is the single most important knob. It must be wider than a typical re-tag burst so the elevated value persists after the burst drains, instead of spiking and recovering inside a narrow window. Prefer increase() over a moderate window to a raw rate() over a tight one: a burst of 300 mismatches counted over [15m] stays visibly elevated for the whole quarter hour, which gives the for: clause something stable to hold against.

Size the critical threshold from history rather than a guess. Measure the per-window drift on known-clean days, take its mean μ7d\mu_{7d} and standard deviation σ7d\sigma_{7d}, and set the critical envelope a few deviations above the routine background:

θcrit=μ7d+k σ7d,k∈[3,5]\theta_{\text{crit}} = \mu_{7d} + k\,\sigma_{7d}, \qquad k \in [3, 5]

A large kk keeps benign micro-bursts (a handful of mislabeled features that the coordinate reference system validation stage repairs on its own) below the page threshold, while a genuine feed migration blows straight through it. For the slow-drift case, a second rule watches a long [6h] window against a near-zero floor, catching accumulation that no single burst reveals.

PromQL: two windows, tiered severity

Encode both behaviours as separate rules so their severities and durations tune independently. The warning rule watches the long window for accumulation; the critical rule watches the short window for a burst past the envelope. The Prometheus series name follows the counter above:

groups:
  - name: srid_mismatch_alerts
    rules:
      # WARNING (ticket) — slow drift: a small but non-zero rate sustained over hours.
      - alert: SridDriftSustained
        expr: |
          sum by (layer, observed_srid) (increase(gis_spatial_crs_drift_total[6h])) > 20
        for: 30m                      # long hold: this is chronic, not urgent
        labels: { severity: warning, team: gis-platform }
        annotations:
          summary: "Sustained SRID drift on {{ $labels.layer }} → {{ $labels.observed_srid }}"

      # CRITICAL (page) — burst past the 7-day envelope on a 15m window.
      - alert: SridReTagBurst
        expr: |
          sum by (layer, observed_srid) (increase(gis_spatial_crs_drift_total[15m]))
            > 3 * avg_over_time(
                sum by (layer, observed_srid) (increase(gis_spatial_crs_drift_total[15m]))[7d:15m]
              )
        for: 5m                       # short hold: a re-tag should page fast, but not on one feature
        labels: { severity: critical, team: gis-platform }
        annotations:
          summary: "SRID re-tag burst: {{ $labels.layer }} re-tagged to {{ $labels.observed_srid }}"

The for: 5m on the critical rule is the anti-flap valve. Because the underlying expression uses a [15m] window, a real burst keeps the value above threshold well past five minutes, so the alert fires once and stays firing; a single stray feature decays out of the window before the hold elapses and never pages. Set for: shorter than the window feeds it, or the alert will resolve mid-hold and never fire at all.

Deduplicate in the router

The rule tiers stop repeated firing; Alertmanager stops repeated paging. Grouping on the same (layer, observed_srid) key the rules carry means every feature in a 40,000-row bad batch folds into one notification, and a re-tag to a second wrong SRID opens a genuinely separate one:

route:
  group_by: ['layer', 'observed_srid']   # the incident identity, not the feature
  group_wait: 30s          # let the whole burst arrive before the first notification
  group_interval: 5m       # batch follow-ups while the burst is still draining
  repeat_interval: 4h      # re-page only if it is genuinely unresolved

group_wait deliberately delays the first page by half a minute so the notification carries the full burst count instead of paging on its leading edge. Once the incident is open, tracing it back to the offending ingestion event is the job of the sibling guide on how to map geospatial data lineage for observability, which resolves the burst to the exact upstream batch that changed projection.

Verification

Prove the tuning before you trust it. Replay a captured re-tag burst — a few thousand features artificially stamped with a wrong SRID — into staging and assert three things: the critical alert fires exactly once, Alertmanager sends exactly one notification for the affected (layer, observed_srid) pair, and the alert clears within one group_interval after the burst stops. Then inject a trickle of one mismatched feature per minute for an hour and confirm the warning rule, not the critical rule, is what eventually opens. If the burst produces more than one page, group_by is missing a label; if the trickle pages critically, the envelope multiplier is too tight.

# Sanity check during replay: how many distinct incidents are actually live?
count(ALERTS{alertname="SridReTagBurst", alertstate="firing"})
# Expect 1 per genuinely re-tagged layer, regardless of feature volume.

For pipelines that route around a degraded upstream, confirm the counter still increments on the fallback path — a cached geometry store that serves stale-but-correct SRIDs should read zero drift, which is exactly the behaviour the fallback chains for spatial API failures design depends on to avoid masking a real re-tag behind a green cache.

FAQ

Should a single mismatched feature page the on-call engineer?

No. One feature with a wrong SRID is a data-quality ticket, not a 3 a.m. page. Route it through the warning tier that watches the long [6h] window, and reserve the critical page for a burst that clears the 7-day envelope. The whole point of the for: hold and the envelope multiplier is to keep a lone stray feature below the paging line while a feed migration still fires within minutes.

Why does increase() over 15m beat rate() over 1m here?

A one-minute rate() tracks the burst’s instantaneous edge, so it spikes when the batch lands and collapses when it drains — producing repeated fire-and-resolve cycles. increase() over [15m] integrates the burst into a value that stays elevated for the whole window, giving the for: clause a stable signal to debounce against. Wider windows trade a little detection latency for a lot less flapping.

How do I stop a re-tag burst from firing hundreds of alerts?

Two mechanisms stack. The alert rule already aggregates with sum by (layer, observed_srid), so 40,000 features collapse into one series and one alert. Alertmanager’s group_by on the same key then collapses any residual duplicates into a single notification, and group_wait holds the first page long enough to count the whole burst. Never omit the aggregation and never group by a per-feature label.

What window catches a slow SRID drift that never bursts?

The long-window warning rule. A feed mislabeling one feature in a thousand never trips a spike threshold, but increase(gis_spatial_crs_drift_total[6h]) accumulates those stragglers until they cross a low floor. Pair it with a for: 30m hold so a brief blip does not open a ticket, and let it run at warning severity — chronic drift is a backlog item, not an incident.

Where should the counter be incremented — the collector or the database?

At the database boundary, from ST_SRID on what PostGIS actually stored. Deriving the mismatch in application code trusts the value the app intended to write, which is precisely the value a silent re-projection has already corrupted. Emitting from a post-insert query means the counter reflects ground truth, and it keeps the observed_srid label honest.