Alerting on Shrinking Spatial Coverage Extent

A layer’s footprint can contract without a single row-count alarm firing: an upstream tasking change drops a swath of edge tiles, a partition predicate silently excludes a region, or a clipped bounding box lands in the warehouse looking like a smaller-but-complete dataset. The features that remain are valid, the load succeeds, and the dashboards stay green while an entire metro area quietly disappears from the map. This guide is the focused procedure for turning coverage shrinkage into a deterministic alert — baselining the true extent, tracking an area ratio against it, and shaping the drop-detection rule so a real contraction pages while a legitimate seasonal edge does not. It sits under Spatial Coverage & Extent Monitoring and within the broader Spatial Data Freshness & Quality Metrics program, written for the data engineers, GIS platform administrators, and SREs who own spatial completeness.

Coverage area ratio stepping down past thresholds and firing an alert A time series of the coverage area ratio plotted across successive ingest runs. The ratio holds near one for several runs, drops slightly below the dashed warning line at ninety-two percent, then falls sharply below the dashed critical line at eighty-five percent, where an alert marker fires. A small inset shows a reference envelope with a full tile grid beside a current envelope missing its right-hand tile column. 1.0 0.9 0.8 successive ingest runs → coverage_area_ratio warn 0.92 crit 0.85 alert reference vs current extent current dropped col

Why extent shrinks silently

A shrinking footprint is invisible to the checks most pipelines already run. Row counts can hold steady while the surviving rows cluster into a smaller area, because a dropped edge region is often the sparsest part of the dataset — losing a rural tile column costs few features but a large fraction of the envelope. Freshness stays green because the partition that did land is recent. Geometry validity passes because each remaining polygon is structurally sound. The defect lives in the aggregate spatial footprint, which no per-row test inspects, so it must be measured as a first-class signal alongside the freshness gate defined in Tracking Spatial Data Freshness SLAs.

The usual triggers are mechanical. A tiling service re-tasks and stops emitting a quadrant. An ST_Intersects filter against a boundary table is silently trimmed when the boundary itself is updated. A reprojection clips geometries that fall outside the new projection’s valid area. A GeoParquet writer partitions on a column whose value set shrank upstream. Each produces the same signature: the dissolved area of the current batch is materially smaller than the area the layer has held for weeks, while nothing else looks wrong.

Baseline the true extent first

You cannot alert on a contraction without a trustworthy reference to measure against, and a raw bounding box is the wrong reference — ST_Extent returns the axis-aligned envelope, which stays large as long as any single feature clings to a corner even after the interior collapses. Capture two complementary baselines: the bounding envelope for a fast sanity check, and the dissolved area for a true completeness measure. Before either is meaningful, assert a single SRID so an implicit projection change cannot masquerade as a shrink, exactly as the upstream Coordinate Reference System Validation gate requires.

-- Baseline: bbox, dissolved area, and SRID for one dataset.
-- Run over a trailing window of known-good loads to seed the reference.
SELECT
  ST_SRID(geom)                              AS srid,
  ST_Extent(geom)                            AS bbox,          -- axis-aligned envelope
  ST_Area(ST_Union(geom))                    AS dissolved_area, -- true covered area
  ST_Area(ST_Extent(geom)::geometry)         AS bbox_area,      -- envelope area
  COUNT(*)                                   AS feature_count
FROM curated.parcels
WHERE load_date >= now() - interval '30 days'
GROUP BY ST_SRID(geom);

A single mixed SRID in that result is a stop-the-line condition: an envelope measured across two datums is meaningless, so resolve the projection before trusting any area number. Store the dissolved area as the reference denominator, not the bbox area — the divergence between the two is itself a signal that the interior is hollowing out.

The coverage area ratio metric

The alertable signal is gis.spatial.coverage_area_ratio: the dissolved area of the current batch divided by the registered reference area, exported as a Prometheus gauge (gis_spatial_coverage_area_ratio). It is deliberately area-based rather than count-based so that losing a sparse region registers proportionally to the ground it covers, not to the handful of features it held. Compute it per batch against the reference extent:

-- Current-batch coverage against the registered reference area.
WITH cur AS (
  SELECT ST_Area(ST_Union(geom)) AS area, ST_SRID(geom) AS srid
  FROM staging.parcels_batch
  WHERE geom IS NOT NULL AND ST_IsValid(geom)   -- never area-sum invalid geometry
  GROUP BY ST_SRID(geom)
)
SELECT
  cur.area / NULLIF(r.dissolved_area, 0) AS coverage_area_ratio,
  cur.srid, r.srid AS ref_srid
FROM cur
JOIN reference_extents r ON r.dataset_id = 'parcels'
WHERE cur.srid = r.srid;   -- refuse to compare across datums

Two guards make this honest. Filtering to ST_IsValid(geom) keeps a self-intersecting polygon from inflating or deflating the area — the same reason coverage checks run downstream of Geometry Validity & Topology Checks. Joining on matching SRID refuses any cross-datum comparison outright. Emit the ratio with the dataset and region as attributes so an alert can name the exact layer and shard that shrank, following the attribute conventions in the Geospatial Metric Taxonomy for ETL.

For high-mobility feeds whose footprint legitimately moves, a static ratio is the wrong tool; those belong on the drift and dynamic-baseline path in Setting Up Freshness Alerts for Real-Time GPS Feeds instead.

Drop-detection alerting in PromQL

A raw threshold on the ratio catches a cliff but misses a slow bleed, and it false-pages on a one-run blip. Combine an absolute floor with a relative drop against the recent maximum so both failure shapes are covered. The relative rule compares the current value to max_over_time of the trailing window — a proportional collapse fires even when the absolute value is still respectable.

groups:
  - name: spatial_coverage_shrink
    rules:
      # WARNING: coverage slipped below the reference floor, sustained
      - alert: CoverageAreaRatioLow
        expr: |
          min by (dataset_id, region) (gis_spatial_coverage_area_ratio) < 0.92
        for: 2 runs
        labels: { severity: warning, team: data-engineering }
        annotations:
          summary: "Coverage for {{ $labels.dataset_id }}/{{ $labels.region }} below 0.92"

      # CRITICAL: sharp relative drop vs the trailing 6h max — catches partial loads
      - alert: CoverageAreaRatioDrop
        expr: |
          (
            gis_spatial_coverage_area_ratio
            / max_over_time(gis_spatial_coverage_area_ratio[6h])
          ) < 0.90
          and gis_spatial_coverage_area_ratio < 0.95
        for: 10m
        labels: { severity: critical, team: sre-gis }
        annotations:
          summary: "Extent for {{ $labels.dataset_id }} dropped >10% vs recent baseline"

      # CRITICAL: the metric stopped arriving — an empty batch reads as no data, not full
      - alert: CoverageAreaRatioMissing
        expr: absent_over_time(gis_spatial_coverage_area_ratio[30m])
        for: 0m
        labels: { severity: critical, team: sre-gis }

The third rule matters as much as the first two. An empty or failed batch emits no ratio, and a monitor that only evaluates present series treats absence as health — the most complete-looking outage there is. absent_over_time converts “no data” into a page. Keep the and clause on the drop rule so a dataset that legitimately sits at 0.9 does not fire every time it wobbles within its own band.

Verify the alert before you trust it

Prove the rule fires by manufacturing a contraction rather than waiting for one. Clip the staging batch to drop an edge column, recompute the ratio, and confirm both the SQL number and the alert transition. A synthetic clip against a known reference is deterministic and repeatable:

-- Simulate a dropped eastern tile column by clipping to 80% of the bbox width.
WITH b AS (SELECT ST_Extent(geom)::geometry AS e FROM curated.parcels)
INSERT INTO staging.parcels_batch (geom)
SELECT ST_Intersection(p.geom,
         ST_MakeEnvelope(
           ST_XMin(b.e), ST_YMin(b.e),
           ST_XMin(b.e) + 0.8 * (ST_XMax(b.e) - ST_XMin(b.e)),
           ST_YMax(b.e), ST_SRID(p.geom)))
FROM curated.parcels p, b
WHERE ST_Intersects(p.geom, b.e);

Recompute coverage_area_ratio and expect roughly 0.8; confirm CoverageAreaRatioDrop moves to firing within its for window. A green verification is three things at once: the ratio lands near the expected fraction, the critical alert fires, and clearing the synthetic batch returns the series above 0.95. If the alert never trips, the reference denominator or the SRID join is wrong.

FAQ

Why measure dissolved area instead of the bounding box?

Because a bounding box lies about completeness. ST_Extent stays large as long as one feature holds a far corner, so an interior collapse or a doughnut-shaped gap reads as full coverage. ST_Area(ST_Union(geom)) measures the ground actually covered. Keep the bbox as a cheap first look, but alert on the dissolved area — and watch the divergence between the two, which widens exactly when the interior hollows out.

How do I keep a legitimate seasonal edge from paging?

Use the relative drop rule with a for window spanning at least two runs, and scope every alert by (dataset_id, region) so one shard cannot mask another. A feed with a real seasonal boundary should have its reference extent refreshed on the same cadence, or moved onto a dynamic baseline that compares each run against its own trailing mean rather than a fixed floor.

The ratio jumped above 1.0 — is that a problem?

Yes, treat it as a defect, not a bonus. A ratio meaningfully above 1.0 usually means an SRID mismatch inflated the area, a duplicate load double-counted geometry, or a reprojection stretched the footprint. Run SELECT ST_SRID(geom), COUNT(*) FROM staging.parcels_batch GROUP BY 1; more than one SRID is the smoking gun. Never update the reference from a run that overshot.

Should this run before or after freshness checks?

After. Coverage assumes the batch that arrived is recent and structurally sound, so it sits downstream of the freshness gate and the geometry checks. If coverage collapses because an upstream tile service is down rather than because the data genuinely shrank, degrade through the tiers in Fallback Chains for Spatial API Failures rather than paging on a supplier outage.