Resolving Duplicate Features After a Parallel Import

Two people ran the import, or one person ran it twice after a timeout, and the layer now holds two of everything — with edits accumulating on both copies. This page is a focused companion to attribute reconciliation for tabular spatial data.

Concept & Context

A duplicate import is easy to create and awkward to undo. The obvious remedy — delete everything from the second run — stops working the moment anyone has edited a feature that happened to come from it, and the second run is usually discovered days later, after exactly that.

So deduplication becomes a reconciliation problem: find the pairs, decide which survives, merge what the loser knows that the survivor does not, and redirect anything pointing at it. Each of those steps has a way of going wrong quietly, and the worst is deleting a feature that other tables still reference.

Detection needs both signals. Geometry alone over-matches, because two imports rarely produce byte-identical coordinates and because distinct features legitimately share a footprint. Attributes alone under-match, because the second import may have arrived with a different identifier scheme. Together they separate cleanly.

Core Algorithmic Pipeline

  1. Cluster spatially using the index, so only features occupying the same ground are ever compared.
  2. Score each candidate pair on geometry overlap and attribute agreement.
  3. Accept pairs above a threshold as duplicates, and report the band just below it for review.
  4. Choose a survivor by a recorded rule, and merge non-conflicting attributes from the loser into it.
  5. Redirect references, then retire the loser with a tombstone naming the survivor.
Why geometry alone cannot separate a duplicate from a neighbour Three panes. Two duplicated imports of one parcel overlap almost entirely; two terraced parcels share a long boundary but almost no area; and a building sits entirely inside its parcel. Only the first is a duplicate, and intersection over union separates all three. DUPLICATE — IoU 0.98 Two imports of one feature. NEIGHBOUR — IoU 0.00 A shared boundary is not an overlap. CONTAINMENT — IoU 0.21 A building inside its parcel — distinct features. Intersection over union is near one only for the first pane, which is why it beats a shared-boundary test.

Working Implementation

"""Detect and resolve duplicate features created by a repeated import."""
from __future__ import annotations

from dataclasses import dataclass

import geopandas as gpd
import pandas as pd


@dataclass
class Pair:
    left: str
    right: str
    geometry_score: float
    attribute_score: float

    @property
    def score(self) -> float:
        # Geometry weighted higher: two features on the same ground with
        # different attributes are more likely duplicates than two features
        # with identical attributes in different places.
        return 0.65 * self.geometry_score + 0.35 * self.attribute_score


def geometry_similarity(a, b) -> float:
    """Intersection over union — 1.0 for identical, 0.0 for disjoint."""
    inter = a.intersection(b).area
    union = a.union(b).area
    return 0.0 if union == 0 else inter / union


def attribute_similarity(a: pd.Series, b: pd.Series, columns: list[str]) -> float:
    """Fraction of compared columns that agree, ignoring pairs where both are null."""
    compared = agreed = 0
    for col in columns:
        av, bv = a.get(col), b.get(col)
        if pd.isna(av) and pd.isna(bv):
            continue
        compared += 1
        if str(av) == str(bv):
            agreed += 1
    return 1.0 if compared == 0 else agreed / compared


def find_duplicates(gdf: gpd.GeoDataFrame, key: str, columns: list[str],
                    accept: float = 0.85, review: float = 0.60) -> tuple[list[Pair], list[Pair]]:
    """Candidate pairs, split into accepted duplicates and ones needing review."""
    joined = gpd.sjoin(
        gdf[[key, "geometry"]], gdf[[key, "geometry"]],
        predicate="intersects", how="inner", lsuffix="l", rsuffix="r",
    )
    joined = joined[joined[f"{key}_l"] < joined[f"{key}_r"]]      # each pair once

    indexed = gdf.set_index(key)
    accepted, to_review = [], []
    for _, row in joined.iterrows():
        left, right = row[f"{key}_l"], row[f"{key}_r"]
        a, b = indexed.loc[left], indexed.loc[right]
        pair = Pair(
            left, right,
            geometry_similarity(a.geometry, b.geometry),
            attribute_similarity(a, b, columns),
        )
        if pair.score >= accept:
            accepted.append(pair)
        elif pair.score >= review:
            to_review.append(pair)
    return accepted, to_review


def choose_survivor(a: pd.Series, b: pd.Series, key: str) -> tuple[str, str, str]:
    """(survivor, loser, rule). The rule is recorded, so the choice is explainable."""
    a_filled = a.notna().sum()
    b_filled = b.notna().sum()
    if a_filled != b_filled:
        winner = a if a_filled > b_filled else b
        loser = b if a_filled > b_filled else a
        return winner.name, loser.name, "more complete attributes"

    # Identifiers are time-ordered, so the earlier one is the original import.
    first, second = sorted([a.name, b.name])
    return first, second, "earlier identifier — the original import"


def resolve(gdf: gpd.GeoDataFrame, pairs: list[Pair], key: str,
            columns: list[str]) -> tuple[gpd.GeoDataFrame, list[dict]]:
    """Merge each pair into its survivor and produce the tombstone records."""
    indexed = gdf.set_index(key)
    tombstones = []

    for pair in pairs:
        a, b = indexed.loc[pair.left], indexed.loc[pair.right]
        survivor_id, loser_id, rule = choose_survivor(a, b, key)
        survivor, loser = indexed.loc[survivor_id], indexed.loc[loser_id]

        # Carry over anything the loser knows and the survivor does not.
        filled = []
        for col in columns:
            if pd.isna(survivor.get(col)) and not pd.isna(loser.get(col)):
                indexed.at[survivor_id, col] = loser[col]
                filled.append(col)

        tombstones.append({
            "retired": loser_id,
            "survivor": survivor_id,
            "rule": rule,
            "score": round(pair.score, 3),
            "attributes_carried_over": filled,
        })

    survivors = indexed.drop(index=[t["retired"] for t in tombstones])
    return survivors.reset_index(), tombstones

Redirection happens before the retirement is written, and the order is not negotiable:

-- 1. Redirect every inbound reference to the survivor
UPDATE building_parcel_link l
   SET parcel_uid = t.survivor
  FROM tombstones t
 WHERE l.parcel_uid = t.retired;

-- 2. Prove nothing still points at a retired feature
SELECT l.parcel_uid FROM building_parcel_link l
  JOIN tombstones t ON t.retired = l.parcel_uid;
-- expected: zero rows

-- 3. Only now record the retirement
INSERT INTO parcel_tombstones (retired_uid, survivor_uid, rule, retired_at)
SELECT retired, survivor, rule, now() FROM tombstones;
Redirect before you retire Four steps in the only safe order: choose the survivor by a recorded rule, carry over attributes the survivor lacks, redirect every inbound reference to the survivor, and only then write the tombstone retiring the loser. 1 Choose a survivor by a recorded rule, not by row order 2 Carry over what the loser knows any field the survivor lacks 3 Redirect inbound references and prove none remain 4 Write the tombstone retired, never deleted Reverse the last two steps and a join that used to return rows starts returning nulls, with nothing to trace.

Validation & Output Verification

# The detector must find planted duplicates and not flag genuine neighbours
import geopandas as gpd
from dedupe import find_duplicates

gdf = gpd.read_file("data/parcels_after_double_import.gpkg")
accepted, review = find_duplicates(
    gdf, key="parcel_uid", columns=["land_use", "owner_ref", "area_m2"]
)
print(f"{len(accepted)} duplicate pair(s), {len(review)} for review")

# Terraced houses share long boundaries and are NOT duplicates
terrace = gdf[gdf["land_use"] == "RES_HIGH"]
terrace_pairs, _ = find_duplicates(terrace, key="parcel_uid",
                                   columns=["land_use", "owner_ref", "area_m2"])
assert not terrace_pairs, "adjacent terraced parcels flagged as duplicates"
# Feature count must drop by exactly the number of retirements
python - <<'PY'
import geopandas as gpd, json
before = len(gpd.read_file("data/parcels_after_double_import.gpkg"))
after = len(gpd.read_file("data/parcels_deduplicated.gpkg"))
tombs = json.load(open("data/tombstones.json"))
assert before - after == len(tombs), f"{before - after} removed, {len(tombs)} recorded"
print(f"{len(tombs)} feature(s) retired, all recorded")
PY

# And the layer must still pass its ordinary gates
python scripts/validate_spatial.py data/parcels_deduplicated.gpkg \
  --crs EPSG:3035 --topology

Failure Modes

  • Adjacent features merged — symptom: terraced parcels collapse into one. Root cause: geometry similarity measured by shared boundary rather than overlap. Fix: use intersection over union, which is near zero for neighbours and near one for duplicates.

  • A retired feature is still referenced — symptom: a join produces nulls where it used to produce rows. Root cause: deletion before redirection. Fix: redirect, verify no inbound references remain, then retire.

  • Edits made on the loser are lost — symptom: a corrected attribute reverts. Root cause: the survivor chosen without merging attributes. Fix: carry over every field the survivor lacks, and route genuine value conflicts to attribute reconciliation rather than dropping one side.

  • The same import runs again next month — symptom: a third copy. Root cause: a non-idempotent import that always inserts. Fix: derive identifiers from stable source attributes so a re-run updates, as covered in designing conflict-free identifiers.

Why the import will be run again Two panels. An insert-only import creates a fresh copy on every run, so a timeout, a retry or a second operator produces duplicates. An import keyed on a derived identifier updates in place, so a re-run is a no-op. INSERT-ONLY IMPORT A retry after a timeout doubles the layer Two operators running it doubles it again Nothing in the import reports a problem Discovered days later, after edits have landed on both copies KEYED, IDEMPOTENT IMPORT Identifier derived from stable source attributes A re-run updates rather than inserting Retries and concurrent runs converge Deduplication becomes a one-off cleanup, not a habit Any import that cannot be re-run safely will eventually be re-run unsafely. Deduplication fixes today's layer; the derived key is what stops the same afternoon recurring.

Back to Attribute Reconciliation for Tabular Spatial Data