Spatial Join and Enrichment Quality Checks

Most spatial quality work checks a layer in isolation: is the geometry valid, is the projection right, is the layer fresh, does it cover its extent. Enrichment breaks all of those assumptions, because it combines two layers that were each individually fine and produces a third that is wrong. A point-in-polygon assignment that silently drops eight percent of points because two boundary layers disagree at their shared edge; an address match that attaches the right street to the wrong side of it; a nearest-neighbour join that returns a feature four kilometres away because the candidate set was empty and nobody checked. None of these produce an error, all of them produce rows, and the resulting table looks complete.

This topic covers the signals that make a spatial join observable: match rate against expectation, the distribution of match distances and ambiguity, the handling of unmatched and multiply-matched features, and the drift that turns a healthy join into a quietly failing one when either input changes. It belongs to spatial data freshness and quality metrics and shares its instrumentation conventions with the geospatial metric taxonomy for ETL.

Why a join needs its own quality signals

Three properties make enrichment a distinct observability problem rather than a special case of layer quality.

Failure removes rows rather than corrupting them. An inner join that fails to match simply omits the left feature. The output is smaller and every row in it is correct, so validity checks pass, projection checks pass, and only a comparison against the expected count reveals anything. This is the mirror image of the vector-layer case, where the usual failure is a bad value in a row that is present.

The predicate is a modelling decision with a tolerance. ST_Intersects between a point and a polygon looks exact, but the point’s positional accuracy, the polygon’s generalisation, and the coordinate precision of both mean that features near a boundary are assigned by an accident of rounding. A join that is 99.4% stable is not the same as one that is exact, and treating it as exact is how a quarterly boundary refresh silently reclassifies thousands of addresses.

Quality depends on two inputs that change independently. A join that was healthy last month can degrade because the left layer gained features in a region the right layer does not cover, or because the right layer’s boundaries were redrawn, or because either side’s projection changed. Neither input looks broken on its own. Only a signal computed on the join itself notices, which is why match rate belongs in the pipeline’s metric set rather than in an occasional analysis notebook.

A fourth property matters for interpretation: the healthy match rate is rarely 100% and rarely constant. Address points genuinely fall outside every administrative polygon when they are offshore, newly built, or mis-geocoded upstream. The useful signal is therefore the deviation from the layer pair’s own baseline, not the distance from a perfect score — the same reasoning that governs freshness bounds in tracking spatial data freshness SLAs.

Metric specification

Keep join metrics under a gis.join.* namespace, dimensioned by the pair being joined rather than by either input alone. The left_layer and right_layer labels together identify the join, and predicate distinguishes a containment join from a proximity one over the same pair.

Two of these repay explanation.

The boundary margin histogram is the single most useful join metric and the least commonly collected. For each matched feature it records how far inside the matched polygon the point sits. A healthy join has most of its mass well away from zero; a join whose margin distribution is piling up near zero is assigning a large fraction of its features by millimetres, and those assignments will flip the next time either layer is regenerated. It converts “this join is fragile” from an intuition into a measurement.

The reassignment counter makes that fragility concrete across runs. Comparing this run’s assignment against the previous one and counting the changes distinguishes a genuine boundary update — a burst of reassignments concentrated in one district — from generalised churn, which indicates the join is riding the noise floor. A composite fragility indicator combines the two:

$$F = \frac{1}{n}\sum_{i=1}^{n}\mathbb{1}\!\left[m_i < \varepsilon\right] \;+\; \frac{r}{n}$$

where $m_i$ is feature $i$’s boundary margin, $\varepsilon$ is the combined positional tolerance of the two layers, $r$ is the reassignment count and $n$ the matched feature count. Values well under 0.01 indicate a stable join; values approaching 0.05 mean a meaningful share of the output is decided by rounding.

Pipeline integration

Instrument the join itself rather than inspecting its output afterwards, so that the unmatched features — which the output does not contain — are counted at the only point where they exist.

-- Enrichment with full outcome accounting. The LEFT JOIN keeps unmatched rows
-- so they can be counted and diagnosed; the aggregate below emits the metrics.
WITH assigned AS (
  SELECT a.address_id,
         a.geom                                   AS point_geom,
         d.district_id,
         COUNT(d.district_id) OVER (PARTITION BY a.address_id) AS match_count,
         -- Margin: how far inside the matched polygon the point sits. Negative
         -- values are impossible here; near-zero values are the fragile ones.
         ST_Distance(a.geom, ST_Boundary(d.geom))  AS boundary_margin_m
  FROM curated.addresses a
  LEFT JOIN curated.districts d
    ON ST_Intersects(d.geom, a.geom)
)
SELECT
  COUNT(*) FILTER (WHERE district_id IS NOT NULL AND match_count = 1) AS matched_once,
  COUNT(*) FILTER (WHERE district_id IS NULL)                          AS unmatched,
  COUNT(*) FILTER (WHERE match_count > 1)                              AS ambiguous,
  COUNT(*) FILTER (WHERE boundary_margin_m < 1.0)                      AS within_tolerance,
  percentile_disc(0.05) WITHIN GROUP (ORDER BY boundary_margin_m)      AS p05_margin_m
FROM assigned;

Diagnosing why a feature is unmatched is what makes the counter actionable, and the diagnosis is cheap because the candidate geometry is already in hand. Three causes cover nearly everything: the point falls outside the union of the right layer’s coverage, the point falls in a gap between right-layer polygons, or the two layers are in different projections and nothing matches anywhere.

-- Diagnose the unmatched, so the counter carries a reason label.
SELECT a.address_id,
       CASE
         WHEN ST_SRID(a.geom) <> (SELECT ST_SRID(geom) FROM curated.districts LIMIT 1)
           THEN 'srid_mismatch'
         WHEN NOT ST_Intersects(a.geom, (SELECT ST_Extent(geom)::geometry FROM curated.districts))
           THEN 'outside_coverage'
         ELSE 'coverage_gap'
       END AS reason
FROM curated.addresses a
LEFT JOIN curated.districts d ON ST_Intersects(d.geom, a.geom)
WHERE d.district_id IS NULL;

The srid_mismatch branch is worth checking first even though it feels unlikely, because when it happens the match rate collapses to zero and every other diagnosis is noise. It is the join-side symptom of the projection faults handled in coordinate reference system validation.

Threshold design and alerting

Alert on deviation from the pair’s own baseline, with separate rules for the three distinct failure shapes.

groups:
  - name: spatial-join-quality
    rules:
      # 1. Match rate fell against its own 7-day baseline — the general detector.
      - alert: JoinMatchRateDrop
        expr: |
          gis_join_match_ratio
            < 0.98 * avg_over_time(gis_join_match_ratio[7d] offset 1d)
        for: 30m
        labels: { severity: critical, data_domain: spatial }
        annotations:
          summary: >-
            Match rate for {{ $labels.left_layer }} → {{ $labels.right_layer }}
            fell to {{ $value | humanizePercentage }} of baseline

      # 2. Collapse — a projection or coverage fault, not a quality drift.
      - alert: JoinMatchRateCollapse
        expr: gis_join_match_ratio < 0.5
        for: 5m
        labels: { severity: critical, data_domain: spatial }

      # 3. Fragility — assignments increasingly decided within tolerance.
      - alert: JoinBoundaryFragility
        expr: |
          histogram_quantile(0.05,
            sum by (le, left_layer, right_layer) (rate(gis_join_boundary_margin_meters_bucket[1h])))
          < 1.0
        for: 2h
        labels: { severity: warning, data_domain: spatial }

      # 4. Ambiguity rising — overlapping right-layer polygons.
      - alert: JoinAmbiguityRising
        expr: |
          rate(gis_join_ambiguous_total[1h])
            / clamp_min(rate(gis_join_evaluated_total[1h]), 1) > 0.005
        for: 1h
        labels: { severity: warning, data_domain: spatial }

Separating the drop detector from the collapse detector matters operationally. A two-percent drop against baseline is a quality regression to investigate during the day; a match rate under fifty percent is a broken join, almost always a projection or coverage fault, and it belongs on the pager immediately with a different runbook.

Choosing and bounding the predicate

The predicate is where most enrichment quality is won or lost, and the choice is rarely as constrained as it looks. Three families cover almost all spatial joins, and each carries a different failure profile that the metrics above are designed to expose.

Containment joins — ST_Intersects, ST_Within, ST_Contains — answer “which region is this in”. They are the most common and the most deceptively exact. Their characteristic failure is the boundary case: a feature sitting within the combined positional tolerance of the two layers is assigned by rounding, and the assignment flips whenever either layer is regenerated. Containment joins therefore need the boundary-margin histogram more than any other family, and they benefit from an explicit policy for the tie: assigning consistently to the region with the lower identifier, or to the previously-assigned region when the margin is inside tolerance, both produce a stable answer where a naive predicate produces a coin flip.

Proximity joins — ST_DWithin combined with a nearest-neighbour ordering — answer “which is the closest feature”. Their characteristic failure is the unbounded match: without a distance limit the query always returns something, and an empty local candidate set silently yields a match from far away. The distance histogram is the detector, and the bound belongs in the predicate rather than in a downstream filter, both because it lets the index prune the search and because a filter applied after the fact cannot distinguish “matched far” from “did not match”.

Attribute-assisted joins combine a spatial predicate with a non-spatial one — match the nearest road segment with the same street name, or the containing parcel with a compatible land-use class. These are markedly more robust than pure geometry for address and asset work, because the attribute breaks ties that geometry cannot. Their characteristic failure moves to the attribute side: a domain re-code on either input silently eliminates matches that used to succeed, which is why the unmatched counter’s reason label should include an attribute_mismatch branch when the join uses one, and why the drift detection in schema and attribute drift detection is a prerequisite rather than an optional extra.

Whichever family you use, bound the predicate explicitly and record the bound alongside the join’s metrics. An unbounded predicate makes “no match” indistinguishable from “an implausible match”, and that distinction is the whole of the quality signal. It also has a direct performance consequence: a bounded predicate lets the spatial index restrict the candidate set, so the same discipline that improves correctness usually improves the join’s cost profile too — the interaction covered from the index side in spatial index health monitoring.

Finally, treat the predicate as part of the join’s identity. Two joins over the same layer pair with different predicates have different baselines, different healthy match rates and different failure modes, which is why predicate is a label on the metric set rather than an implementation detail. Changing the predicate invalidates the baseline, and a match-rate alert that fires the day after a predicate change is usually reporting the change rather than a fault.

Failure modes and edge cases

Silent shrinkage through an inner join. The output is smaller and internally consistent, so every downstream check passes. Only match-rate accounting notices, which is why the metric must be computed inside the join rather than inferred from output row counts.

Overlapping right-layer polygons duplicating left features. An address falling in two overlapping districts produces two output rows, inflating counts and double-counting in any aggregate. The ambiguity counter catches it; the fix belongs upstream, in the topology checks described in geometry validity and topology checks.

Nearest-neighbour joins with no distance bound. A proximity join always returns something, so an empty candidate set yields a match four kilometres away with full confidence. Always bound the predicate with ST_DWithin and count the features that fall outside the bound rather than letting them match.

A boundary refresh reclassifying features en masse. Legitimate and expected — but indistinguishable from a fault unless reassignments are counted and localised. A burst confined to one district is a boundary update; a diffuse burst across the whole layer is a projection or precision change.

Coverage mismatch at the edges. The left layer extends beyond the right layer’s extent, so every feature in the overhang is unmatched forever. This reads as a permanently depressed match rate that everyone learns to ignore. Diagnose it once with the outside_coverage reason and either extend the right layer or exclude the overhang explicitly.

Troubleshooting checklist

1. Compare ST_SRID on both inputs before anything else; a mismatch explains a total collapse and invalidates every other measurement.
2. Check the match ratio against the pair’s own baseline, not against 1.0.
3. Break the unmatched count down by reason — outside_coverage, coverage_gap, srid_mismatch — and treat the dominant reason as the lead.
4. Inspect the boundary-margin distribution’s lower percentiles; mass near zero means the join is riding the tolerance floor.
5. Count reassignments against the previous run and check whether they are geographically concentrated or diffuse.
6. For proximity joins, check the match-distance histogram for a tail beyond the plausible bound.
7. Confirm the right layer’s coverage has not shrunk, using the extent checks from spatial coverage and extent monitoring.
8. Re-run the join with the previous version of each input in turn to isolate which side changed.

That last step deserves emphasis, because it is the one that reliably terminates an investigation. A join has two inputs and a predicate; a regression comes from exactly one of the three. Holding two constant and swapping the third for its previous version identifies the culprit in at most three runs, and it does so without requiring anyone to reason about which change was more likely. Keeping one prior version of each input available specifically to support this bisect is a small storage cost against a large diagnostic saving, and it is the same retention that makes the reassignment diff possible.

One organisational point closes the topic. Enrichment usually sits between two teams: one owns the left layer, another owns the right, and the join belongs to neither. Naming an owner for the join itself — with the match-rate baseline, the predicate and its bound recorded under that ownership — is what stops a quality regression bouncing between the two input owners while the output stays wrong.

Finally, keep the join’s own configuration — predicate, bound, tie-break rule and baseline — recorded alongside its metrics rather than only in the code that runs it. An investigation that has to read the query to discover what the join was doing spends its first ten minutes on a lookup.

Related

• Spatial data freshness and quality metrics — the parent section this topic belongs to.
• Geometry validity and topology checks — the overlap and gap faults that drive join ambiguity.
• Coordinate reference system validation — the projection contract a join silently depends on.
• Spatial coverage and extent monitoring — the coverage signal behind most unmatched features.