Geospatial Portal

Deduplicating Harvested Records by Fingerprint

This guide detects the same dataset arriving from more than one upstream catalogue and resolves the duplicates without destroying information. It belongs to Metadata Quality Assurance & Record Scoring, within the Metadata Catalog Automation & Ingestion Workflows framework.

Prerequisites

  • Provenance on every record — source identifier, source-native identifier and retrieval timestamp.
  • Normalised fields after the crosswalk, so comparisons are made on comparable values.
  • A place to record cluster membership that is separate from the records themselves.
  • A curator who can adjudicate the cases the automation declines to decide.

Duplicates are not one problem

“The same dataset twice” covers several situations with different correct answers, and a deduplicator that treats them identically will merge things that should stay separate.

Four things that look like duplicates Re-harvest, cross-source duplicate, different editions and coincidental similarity, with the correct treatment for each. SITUATION TREATMENT same source, re-harvested the overlap window did its job upsert on the source-native identifier — never a new record two organisations, one dataset a genuine duplicate cluster and prefer one; keep both, linked two editions 2024 and 2025 of the same series NOT duplicates — link as a series, keep separate similar titles, different areas unrelated — the extent is what separates them

The bottom two rows are why title similarity alone is an unsafe signal. “Parcels” from two districts and “Flood risk 2024” versus “Flood risk 2025” are both textually close and semantically distinct, and a deduplicator working on titles will merge them enthusiastically.

Step-by-step implementation

1. Build a fingerprint from several independent signals 

# fingerprint.py — a fingerprint is deliberately coarse: it is a candidate
# generator, not a decision. Precision comes from the comparison in step 2.
import hashlib, re, unicodedata

def norm_text(s: str) -> str:
    s = unicodedata.normalize("NFKD", s or "").encode("ascii", "ignore").decode()
    s = re.sub(r"\b(the|of|for|and|dataset|data|layer)\b", " ", s.lower())
    return re.sub(r"[^a-z0-9]+", " ", s).strip()

def extent_cell(extent: dict, precision: float = 0.1) -> str:
    """Snap the extent centroid to a coarse grid so near-identical areas collide."""
    cx = (extent["minx"] + extent["maxx"]) / 2
    cy = (extent["miny"] + extent["maxy"]) / 2
    return f"{round(cx / precision)}:{round(cy / precision)}"

def fingerprint(record: dict) -> str:
    parts = [
        norm_text(record.get("title", ""))[:60],
        extent_cell(record["extent"]),
        str((record.get("temporal_extent") or {}).get("start", ""))[:4],   # year only
    ]
    return hashlib.sha256("|".join(parts).encode()).hexdigest()[:16]

2. Compare candidates on evidence, not on the fingerprint 

# compare.py — the fingerprint groups candidates; this decides.
def similarity(a: dict, b: dict) -> dict:
    """Return per-signal agreement so a decision can be explained, not just made."""
    return {
        "title":    jaccard(norm_text(a["title"]).split(), norm_text(b["title"]).split()),
        "extent":   iou(a["extent"], b["extent"]),          # intersection over union
        "temporal": temporal_overlap(a, b),                  # 1.0 if identical periods
        "distribution": 1.0 if shared_distribution_host(a, b) else 0.0,
        "identifier": 1.0 if same_external_identifier(a, b) else 0.0,   # DOI, etc.
    }

def verdict(sig: dict) -> str:
    # An authoritative shared identifier settles it outright.
    if sig["identifier"] == 1.0:
        return "duplicate"
    # Different areas are never duplicates, whatever the title says.
    if sig["extent"] < 0.6:
        return "distinct"
    # Different periods are editions of a series, not duplicates.
    if sig["temporal"] < 0.5:
        return "series"
    if sig["title"] >= 0.8 and sig["extent"] >= 0.9:
        return "duplicate"
    return "review"        # the honest answer for everything else

3. Keep every record; express the relationship separately

Merging destroys provenance and makes the next harvest ambiguous. Cluster instead: keep each source’s record intact, record which cluster it belongs to, and mark one as preferred for display.

CREATE TABLE record_cluster (
    cluster_id   uuid PRIMARY KEY,
    preferred_id text NOT NULL REFERENCES record(identifier),
    decided_by   text NOT NULL,          -- 'auto' or a curator's name
    decided_at   timestamptz NOT NULL DEFAULT now(),
    signals      jsonb NOT NULL          -- why, so it can be revisited
);

CREATE TABLE record_cluster_member (
    cluster_id uuid REFERENCES record_cluster(cluster_id),
    identifier text REFERENCES record(identifier),
    PRIMARY KEY (cluster_id, identifier)
);

4. Choose the preferred copy by a stated rule

Choosing the preferred copy, in a stated order Authority, completeness, recency and a stable tie-break, applied in sequence. 1 · authority the custodian of the data outranks an aggregator that republished it 2 · completeness the higher quality score, using the model from the parent topic 3 · recency the more recently updated record, by the source's own datestamp 4 · stable tie-break lowest source identifier — so the choice never oscillates nightly

The fourth criterion exists because of a specific failure: without a deterministic tie-break, two equally complete copies alternate as preferred on every harvest, and the catalogue’s public record identifier for that dataset changes daily. Downstream consumers that hold the identifier notice, and the cause is genuinely hard to find.

The review queue is the design, not an admission of failure

Every honest deduplicator produces a set of pairs it declines to decide, and the instinct to tune that set to zero is the wrong one. A queue of ambiguous pairs is a feature: it is where the automation says, correctly, that the evidence is insufficient.

Three bands, and how wide each should be Confident duplicate, review, and confident distinct bands with the sizing rationale for each. Where a candidate pair lands duplicate review distinct duplicate: strong agreement on extent AND title, or a shared authoritative identifier review: small enough to clear weekly, and never empty — an empty queue means the thresholds are guessing distinct: the widest band, because most candidate pairs are genuinely unrelated A curator's decision is recorded and never overwritten by a later automatic pass.

Give the queue an owner and a cadence — twenty minutes a week clears a surprising number — and record every decision with its signals, so a later change to the thresholds can be checked against what humans actually concluded.

Verification 

# 1. A re-harvest does not create a second record
python3 harvest.py --source county-highways && python3 harvest.py --source county-highways
psql -Atc "SELECT count(*) FROM record WHERE source_id='county-highways';"
#   expect: unchanged between the two runs

# 2. Two districts' identically titled layers are NOT clustered
psql -Atc "
  SELECT count(*) FROM record_cluster_member m
  JOIN record r USING (identifier)
  WHERE r.title = 'Parcels'
  GROUP BY m.cluster_id HAVING count(*) > 1;"
#   expect: no rows — the extents differ, so they are distinct

# 3. Two editions of a series are linked as a series, not merged
psql -Atc "SELECT relation FROM record_relation WHERE identifier='flood-risk-2025';"
#   expect: 'series' referencing the 2024 record, and both still present

# 4. The preferred copy is stable across harvests
for i in 1 2 3; do psql -Atc "SELECT preferred_id FROM record_cluster WHERE cluster_id='$CID';"; done
#   expect: the same identifier every time

# 5. Every automatic decision is explainable
psql -Atc "SELECT signals FROM record_cluster WHERE decided_by='auto' LIMIT 1;" | jq .
#   expect: the per-signal values that produced the verdict

Troubleshooting matrix

Symptom Likely cause Fix
Unrelated datasets are merged Deciding on title similarity alone Require extent agreement; treat differing extents as decisive
Yearly editions collapse into one record No temporal comparison Compare periods; classify as a series rather than as duplicates
The preferred record changes every night No deterministic tie-break Add a stable final criterion and re-verify across three harvests
Duplicates reappear after each harvest Clustering computed but not persisted, or keyed on a volatile field Persist membership; key on source-native identifiers
A curator’s decision is overwritten automatically Automation not respecting manual verdicts Record decided_by; never let ‘auto’ overwrite a curator
The review queue grows without bound Thresholds set so most pairs are ambiguous Widen the confident bands; a wrong-but-reviewable pair is worse than none
Provenance is lost after deduplication Records merged rather than clustered Keep every record; express the relationship in a separate table

FAQ

Why not just merge duplicates into one record?

Because a merge destroys the ability to re-harvest either source cleanly, loses each publisher’s own wording, and makes it impossible to answer which organisation asserted what. Clustering keeps every source’s record intact and adds a relationship, which costs one join and preserves everything.

How aggressive should the automatic threshold be?

Conservative. A missed duplicate is a minor untidiness that a user can see through; a wrong merge presents one organisation’s data under another’s name and is discovered by somebody who relied on it. Set the confident band narrowly and let the review queue be larger than feels comfortable.

What identifiers can be trusted outright?

Persistent external identifiers — a DOI, or an authoritative national dataset identifier — where both records carry the same value. Those settle the question without any similarity comparison. Source-native identifiers cannot be compared across sources, because two catalogues will happily use the same local identifier for unrelated things.

Should deduplication run at ingestion or afterwards?

Afterwards, as a separate pass. At ingestion the comparison set is only the current batch, so duplicates arriving from different sources on different schedules are never compared. A nightly pass over the whole catalogue is cheap and sees everything.

How should clusters be presented to users?

As one result with the alternatives listed, rather than as several results or as one result with the others hidden. A user searching for a dataset wants one answer and occasionally wants to know that another organisation also publishes it — particularly when the other copy is the authoritative one and the preferred choice was wrong.

What happens when a source withdraws its copy?

The cluster loses a member and, if that member was preferred, the preference is recomputed by the same stated order. Record the change rather than performing it silently: a downstream consumer holding the previous preferred identifier needs to know it now resolves to a different record.

How should clusters behave when a source changes a record substantially?

Re-evaluate the cluster rather than assuming it still holds. A record whose extent or title changed materially may no longer belong with its cluster, and leaving it there presents two genuinely different datasets as one. Recompute membership whenever a record’s fingerprint changes, and send the pair back to review rather than silently splitting or keeping it.

Is there value in publishing the cluster relationships?

Yes, as links on the record: naming the other organisations that also publish this dataset is genuinely useful information for somebody deciding which copy to rely on, and it makes the catalogue’s aggregation visible rather than hidden. It also produces a useful pressure — organisations noticing that a neighbour’s copy is preferred tend to ask why, and the answer is usually a fixable gap in their own record.

Up one level: Metadata Quality Assurance & Record Scoring.