Monitoring Point-in-Polygon Match Rates

The point-in-polygon assignment is the workhorse of spatial enrichment: which district is this address in, which catchment does this sensor belong to, which parcel does this incident fall on. It is also the join most likely to fail invisibly, because an inner join drops every unmatched point and the surviving output is internally perfect. This guide covers how to measure the match rate as a first-class metric with a baseline, how to diagnose unmatched points into causes that suggest a fix, and how to alert on a drop without paging every time an ordinary batch has a slightly different geographic mix. It belongs to spatial join and enrichment quality checks under spatial data freshness and quality metrics.

Four causes of an unmatched point, drawn against a polygon coverage A polygon coverage made of three adjacent districts is drawn with a narrow sliver gap between two of them. Four unmatched points are marked. One sits outside the coverage entirely, beyond the outer boundary. One sits in the sliver gap between two adjacent polygons. One sits inside a polygon but is drawn far away because its coordinates are in a different projection. One sits exactly on a shared edge and is rejected by a strict containment predicate. Each is labelled with the diagnostic reason it should be counted under. Four reasons a point does not match — and each wants a different fix district A district B district C outside_coverage coverage_gap — sliver between A and B on_edge — strict containment rejects it srid_mismatch — plotted in another system a collapse to near-zero is almost always this one An inner join deletes all four without comment; only a left join with outcome accounting can count them.

Problem framing: the baseline is the measurement

A point-in-polygon join almost never matches everything, and the residual is stable rather than random. Offshore assets, newly-built addresses ahead of a boundary refresh, and points whose upstream geocoding failed all sit permanently outside the coverage. A layer pair with a steady 98.7% match rate is healthy at 98.7%, and any alert threshold expressed as a distance from 100% either fires constantly or is set so loose it catches nothing.

The consequence is that the useful signal is a ratio to the pair’s own recent baseline. A drop from 98.7% to 96.2% is a two-and-a-half point absolute change and a serious regression; the same absolute change from 60% to 57.5% on a deliberately partial join may be routine. Expressing the detector as a fraction of a rolling baseline handles both without per-pair tuning.

The second framing point is that the match rate must be computed where the unmatched points still exist. Once the inner join has run they are gone, and any measurement taken downstream is measuring the survivors. Either run the join as a left join and aggregate outcomes before filtering, or compute the count of left features separately and compare — the first is cheaper and gives you the diagnosis for free.

Implementation: compute outcomes, then filter

A single pass produces the enriched output and the metrics together.

-- One pass: enrich, and account for every outcome including the missing ones.
CREATE TEMP TABLE assignment AS
SELECT
  a.address_id,
  a.geom,
  d.district_id,
  COUNT(d.district_id) OVER (PARTITION BY a.address_id) AS match_count,
  ST_Distance(a.geom, ST_Boundary(d.geom))              AS margin_m
FROM curated.addresses a
LEFT JOIN curated.districts d
  ON ST_Intersects(d.geom, a.geom);        -- intersects, not within: an edge point matches

-- Metrics, emitted before the enriched output is filtered down to matches.
SELECT
  COUNT(DISTINCT address_id)                                                  AS left_features,
  COUNT(DISTINCT address_id) FILTER (WHERE district_id IS NOT NULL)           AS matched,
  COUNT(DISTINCT address_id) FILTER (WHERE match_count > 1)                   AS ambiguous,
  COUNT(DISTINCT address_id) FILTER (WHERE district_id IS NOT NULL
                                       AND margin_m < 1.0)                    AS within_tolerance
FROM assignment;

-- The enriched output takes only the clean single matches.
CREATE TABLE curated.addresses_enriched AS
SELECT address_id, geom, district_id
FROM assignment
WHERE district_id IS NOT NULL AND match_count = 1;

Using ST_Intersects rather than ST_Within is a deliberate choice worth stating. ST_Within excludes points lying exactly on a shared boundary, which in a tiled administrative coverage is a surprisingly large set — every point whose coordinates happen to land on a shared vertex or edge. ST_Intersects admits them, at the cost of turning them into ambiguous double-matches, which the match_count window then makes visible and resolvable rather than silently dropping them.

The diagnosis of the unmatched set is what converts the counter into an action.

-- Reasons, evaluated in the order that makes the dominant cause obvious.
WITH coverage AS (
  SELECT ST_Union(geom) AS geom, ST_SRID(MIN(geom)::geometry) AS srid
  FROM curated.districts
)
SELECT
  CASE
    WHEN ST_SRID(a.geom) <> c.srid                    THEN 'srid_mismatch'
    WHEN NOT ST_Intersects(a.geom, ST_Envelope(c.geom)) THEN 'outside_coverage'
    WHEN NOT ST_Intersects(a.geom, c.geom)            THEN 'coverage_gap'
    ELSE 'unexplained'
  END AS reason,
  COUNT(*) AS features
FROM assignment a
CROSS JOIN coverage c
WHERE a.district_id IS NULL
GROUP BY 1
ORDER BY 2 DESC;

outside_coverage and coverage_gap want different fixes and it is worth keeping them apart. The first means the left layer extends beyond the right layer and the honest response is usually to exclude the overhang explicitly rather than to chase it forever. The second means the right layer has slivers between adjacent polygons — a topology defect, addressed at source through the checks in geometry validity and topology checks — and it is genuinely fixable.

An unexplained count above zero is the interesting case: it means the point is inside the union of the coverage and still did not match, which usually indicates invalid geometry on the polygon side causing the predicate to behave unexpectedly.

Match rate against its rolling baseline over four weeks, with one true regression A line shows the daily match rate hovering around ninety-eight point seven percent with small day-to-day variation. A shaded band around a rolling seven-day baseline marks the two-percent-of-baseline alert region. On day twenty the line drops sharply below the band to about ninety-six percent and stays there, marking a true regression caused by a district boundary refresh. Two earlier small dips remain inside the band and correctly do not alert. Alert on the ratio to baseline, not on the distance from 100% 99.5% 98.0% 96.5% rolling baseline ± 2% district refresh match rate drops and stays down — alert fires day 0 day 10 day 20 day 30 The two small dips before day 20 are ordinary batch composition; a fixed 99% threshold would have paged on both. Unmatched points by diagnosed reason across one month Four diagnosed reasons for unmatched points are ranked. Points outside the polygon layer's coverage dominate and are a permanent known set. Coverage gaps between adjacent polygons are second and are genuinely fixable. Unexplained matches, where the point lies inside the union but still did not match, are third and usually indicate invalid polygon geometry. Projection mismatch is smallest but produces a total collapse when it occurs. Classify the residual: only two of the four are worth chasing outside_coverage permanent known set coverage_gap fixable at source unexplained invalid polygon geometry srid_mismatch rare, total collapse

Verification: confirm the metric moves when it should

Two tests establish that the accounting is real.

Inject a known number of points outside the coverage — a hundred at a fixed offshore coordinate — and confirm the unmatched counter rises by exactly a hundred with reason="outside_coverage". An off-by-some result usually means the diagnosis query and the assignment query disagree about the coverage geometry, typically because one uses the envelope and the other the union.

Then reproject a copy of the left layer into a different system and run the join against it. The match rate must collapse to approximately zero and the dominant reason must be srid_mismatch. This is the one case where the detector needs to be unambiguous, because when it happens in production every other signal is noise.

Finally, confirm the baseline is not contaminated. If the alert compares against avg_over_time(...[7d]) without an offset, a slow regression drags the baseline down with it and never fires. The offset in the rule below is what keeps the comparison honest.

Gotchas

Computing the match rate from the enriched table. The enriched table contains only matches, so its “match rate” is one by construction. Compute before filtering.

Using ST_Within and losing edge points. In a fully tiled coverage a meaningful number of points land exactly on shared edges. ST_Intersects plus explicit ambiguity handling keeps them countable.

A baseline that includes the regression. Always offset the baseline window past the current period, or a gradual decline is normalised away.

Ignoring ambiguous matches. Double-matched points inflate downstream aggregates while the match rate looks perfect. Count them separately and decide a tie-break policy rather than letting the join duplicate rows.

Per-batch match rate on a geographically skewed batch. A batch covering only one region has a match rate specific to that region’s coverage quality. Where batches vary geographically, compute the rate per region and take the worst, for the reasons set out in alerting on partial-region failures.

FAQ

What is a reasonable alert threshold?

Two percent below the seven-day baseline, with a thirty-minute sustain, works for most address-to-district joins. Tighten it once you have a month of data and can see the natural day-to-day spread; the right threshold sits just outside the observed noise band, which is usually well under one percent for a stable pair.

Should unmatched points be dropped or retained?

Retain them with a null assignment wherever downstream can tolerate it. A dropped point is unrecoverable and invisible; a retained point with a null district is countable, auditable and can be reprocessed after a coverage fix. Dropping should be a deliberate, documented choice rather than a side effect of the join type.

How do I handle points that will never match?

Classify them explicitly — an expected_unmatched flag on the left layer, or an exclusion polygon — and remove them from the denominator. This raises the match rate to something close to one and makes the detector far more sensitive, because the residual noise is no longer dominated by a known permanent set.

Does a high ambiguity count mean the join is wrong?

Not necessarily; some coverages legitimately overlap, such as overlapping service areas. What matters is whether the join has a defined tie-break. An ambiguous match resolved by a documented rule is fine; one resolved by whichever row the database happened to return is not, because it changes between runs and shows up later as unexplained reassignment.

How does this relate to coverage monitoring on the polygon layer?

Directly: most coverage_gap and outside_coverage unmatched points are symptoms of a coverage problem on the right layer. When the match rate drops, checking the polygon layer’s extent and area against its baseline — as described in spatial coverage and extent monitoring — frequently identifies the cause in one query.