Geospatial Schema Standardization & Mapping Automation

Deduplicating Address Points with Spatial Clustering

Municipal address layers accumulate duplicates the way filing cabinets accumulate copies: one point from the building permit system, one from the postal file, one created when a street was renamed and nobody retired the old record. They sit within a metre or two of each other and agree on most attributes, so a human recognizes them instantly and a naive script either merges half of them or merges a whole terrace into one address. This procedure resolves them deterministically at municipal scale, implementing the match-and-merge rules defined by duplicate detection and feature deduplication inside Spatial Data Quality Validation & Geometry Integrity.

The steps map to configure (Step 1, blocking), execute (Steps 2–3, pairs and the rule), validate (Step 4, group resolution), and log (Step 5, the merge record). The single most important design point appears in Step 3: pairs are classified into three outcomes, not two, so the population the rule cannot decide is measured rather than absorbed.

Prerequisites checklist

Step 1: Block the layer, and state what blocking cannot find

python
# geopandas >= 0.14 — Python 3.10+
import geopandas as gpd

CRS = "EPSG:25832"
MAX_BLOCK = 20_000        # a block larger than this is a rule defect, not a performance problem

points = gpd.read_file("input/address_points.gpkg").to_crs(CRS)
assert points.crs.is_projected, "the match radius is in metres"

blocks = dict(tuple(points.groupby("postcode", dropna=False)))

oversized = {key: len(df) for key, df in blocks.items() if len(df) > MAX_BLOCK}
if oversized:
    raise ValueError(f"blocking key produced oversized block(s): {oversized}")

The dropna=False matters: records with a null postcode form their own block rather than disappearing, which is exactly the population most likely to hold duplicates. The oversized-block assertion catches the classic failure where thousands of null-postcode records collapse into one quadratic block — better a clear error at second three than an eleven-hour job.

Blocking is also a documented limitation. Two records for the same building that disagree on postcode will never be compared, and that fact belongs in the quality report next to the match radius so consumers know the shape of what was checked.

Step 2: Find candidate pairs with an indexed radius query

python
# shapely >= 2.0 — Python 3.10+
from shapely import STRtree

RADIUS = 2.0      # metres, from the manifest


def candidate_pairs(block: gpd.GeoDataFrame):
    block = block.sort_values("feature_id").reset_index(drop=True)   # order-independence
    tree = STRtree(block.geometry.values)
    left, right = tree.query(block.geometry.values, predicate="dwithin", distance=RADIUS)
    for i, j in zip(left.tolist(), right.tolist()):
        if i < j:                        # each unordered pair exactly once
            yield i, j

This is where clustering is tempting and where the trap lies. DBSCAN(eps=2.0, min_samples=2) over the same points is fast and returns tidy groups — and in a street of terraced houses whose doorways are 1.8 m apart it returns one cluster of forty addresses, because density-based clustering is transitive by construction. Every one of those forty would be merged into a single address point. If you use clustering at all, use it to generate candidates and then apply the pair rule to each pair within a cluster; never treat a cluster as a merge group.

Transitive Clustering Versus the Pairwise Rule The upper row shows six address points in a terrace, each 1.8 metres from its neighbour, with one point duplicated almost exactly on top of the fourth. A density-based cluster with a 2 metre radius spans all seven points because each is within the radius of the next, producing one merge group and collapsing the whole terrace into a single address. The lower row shows the same points evaluated pairwise: only the duplicated pair agrees on three attributes, so only that pair is merged and the other five addresses survive untouched. DENSITY CLUSTER — transitive, merges the whole terrace every point within 2 m of the next one cluster → 7 records become 1 PAIRWISE RULE — only the genuine duplicate merges 3 attributes agree only for the boxed pair 7 records become 6

Step 3: Apply the agreement rule and classify into three outcomes

python
# Python 3.10+
NORMALIZERS = {
    "casefold_strip_punctuation":
        lambda v: "".join(c for c in (v or "").casefold() if c.isalnum() or c.isspace()).strip(),
    "strip_leading_zeros": lambda v: (v or "").lstrip("0") or "0",
    "casefold": lambda v: (v or "").casefold().strip(),
}

AGREEMENT = [
    {"field": "street_name", "normalize": "casefold_strip_punctuation"},
    {"field": "house_number", "normalize": "strip_leading_zeros"},
    {"field": "unit_designator", "normalize": "casefold"},
]
AUTO_MATCH, REVIEW = 3, 2


def classify_pair(left: dict, right: dict) -> str:
    agree = sum(
        NORMALIZERS[spec["normalize"]](left.get(spec["field"]))
        == NORMALIZERS[spec["normalize"]](right.get(spec["field"]))
        for spec in AGREEMENT
    )
    if agree >= AUTO_MATCH:
        return "match"
    if agree >= REVIEW:
        return "review"
    return "no_match"

One subtlety decides how well this behaves on real data: two nulls should not count as agreement. unit_designator is empty for most houses, so a rule that treats empty-equals-empty as a vote gives every pair of ordinary houses a free point and pushes them into review. Either exclude commonly-null fields from the vote, or require both sides to be non-null for the field to count — and state which in the manifest.

Step 4: Resolve groups by precedence, requiring a clique

python
# Python 3.10+
import networkx as nx        # networkx >= 3.2 for connected components


def resolve_groups(matches: list[tuple[str, str]], records: dict[str, dict], precedence):
    graph = nx.Graph()
    graph.add_edges_from(matches)
    merges, review_groups = [], []

    for component in nx.connected_components(graph):
        members = sorted(component)
        subgraph = graph.subgraph(members)
        expected_edges = len(members) * (len(members) - 1) // 2
        if subgraph.number_of_edges() != expected_edges:
            review_groups.append(members)          # a chain, not a clique — a human decides
            continue

        survivor = choose_survivor([records[m] for m in members], precedence)
        for member in members:
            if member != survivor["feature_id"]:
                merges.append((survivor["feature_id"], member))
    return merges, review_groups

The clique requirement is the guard against transitive over-merging that clustering lacks. If A matches B and B matches C but A does not match C, the three records are not “obviously the same thing” — they are a chain, and the usual cause is a radius slightly too generous for the local density. Sending chains to review keeps the automatic path conservative and gives you a measurement of how often the radius is borderline.

choose_survivor applies the precedence list from the manifest — lowest source_rank, then most recent last_verified, then highest completeness, then feature_id as a total order. That last key is not optional: without it, ties are broken by row order and two runs over the same input can keep different records.

Step 5: Record every merge so the absorbed identifiers stay resolvable

python
# pyarrow >= 14 — Python 3.10+
rows = [{
    "run_id":            run_id,
    "survivor_id":       survivor_id,
    "absorbed_id":       absorbed_id,
    "agreeing_fields":   3,
    "distance":          round(records[absorbed_id]["geometry"].distance(
                               records[survivor_id]["geometry"]), 4),
    "precedence_step":   "source_rank",
    "attributes_filled": "unit_designator,postal_locality",
} for survivor_id, absorbed_id in merges]

Publish this mapping alongside the dataset. Citizens’ correspondence, printed notices and downstream systems all carry the absorbed identifiers, and a record that disappears without a forwarding address becomes a support ticket months later. The mapping is small, it costs nothing to serve, and it answers the question directly.

Verification

python
# pytest >= 7
def test_terrace_is_not_collapsed(terrace_fixture):
    merges, review = run_dedup(terrace_fixture)
    assert len(merges) == 1                       # only the true duplicate
    assert len(terrace_fixture) - len(merges) == 6

def test_result_is_stable_under_shuffling(terrace_fixture):
    a = run_dedup(terrace_fixture)
    b = run_dedup(terrace_fixture.sample(frac=1.0, random_state=11))
    assert a == b

def test_chain_goes_to_review_not_merge(chain_fixture):
    merges, review = run_dedup(chain_fixture)
    assert merges == []
    assert len(review) == 1

A municipal run logs the three numbers worth watching — the review rate is the health signal, and a rate that climbs over successive deliveries means the sources are diverging:

text
INFO quality.dedup blocks=1,204 records=318,442 pairs_compared=41,987
INFO quality.dedup auto_matched=2,118 review_pending=143 chains=11
INFO quality.dedup records_out=316,324 radius=2.0 m blocking=postcode

Troubleshooting

Symptom Likely cause Fix
A whole street merged into one point A cluster was treated as a merge group Apply the pair rule inside the cluster; require a clique before merging
Review queue is enormous Commonly-null fields counting as agreement, or a radius far above source accuracy Require both sides non-null for a field to vote; derive the radius from positional accuracy
Two runs produce different survivors The precedence list has no total-order final key Append feature_id ascending as the last precedence step
Job never finishes One oversized block, usually from null blocking keys Assert MAX_BLOCK; give nulls their own smaller sub-blocking key such as street name
Duplicates across postcode boundaries are missed Blocking excludes the pair by design Add a second rule with a different blocking key and union the results; state both in the report
Merged records lose a phone number or a reference merge.attributes set to none Use fill_nulls_from_losers and record which fields were inherited