Geospatial Schema Standardization & Mapping Automation

Reconcile Parcel Field Names Across Counties

A statewide parcel layer is assembled from dozens of county extracts that name the same concept differently: the assessor’s parcel number arrives as APN in one county, PIN in another, PARCEL_ID, PARCELNO, or MAPNO elsewhere. Merge those extracts without reconciling field names and a spatial join drops half the attributes, or worse, silently aligns the wrong columns. This procedure sits inside Local Government Data Dictionaries and owns the crosswalk that the dictionary’s alias table depends on: it maps every county’s divergent column names onto one canonical vocabulary. Where the parent stage validates attributes against a canonical schema and where handling missing mandatory fields in municipal GIS exports decides what to do when a canonical field is absent, this page decides which incoming column is which canonical field in the first place.

The walkthrough maps 1:1 to the phases this site standardizes on — configure (Step 1, load the canonical dictionary), execute (Steps 2–3, propose fuzzy candidates then freeze a reviewed alias table), validate (Step 4, apply deterministically and assert coverage), and log (Step 5, coverage report and CI gate). The governing principle is a strict separation: fuzzy matching only proposes candidates for a human to review; the apply step is purely deterministic, driven by a reviewed alias table. A production pipeline must never rename a column on a live fuzzy score — a similarity threshold that silently maps OWNER_ADDR to OWNER_NAME corrupts every downstream record.

Prerequisites checklist

Confirm the toolchain and the canonical dictionary before proposing a single match. Fuzzy matching is only as safe as the review gate behind it, so the reviewed alias table must be the sole input to the apply step.

Step 1: Load the canonical dictionary

Declare the canonical vocabulary once: each canonical key, whether it is mandatory, and the synonyms already accepted for it. This is the target every county column is reconciled against, and it is the same dictionary the parent enforcement stage validates against — so a canonical key here is a canonical key there.

yaml
# canonical_parcels.yaml — the target vocabulary
schema_version: "2.1.0"
fields:
  PARCEL_ID:
    mandatory: true
    synonyms: ["APN", "PIN", "PARCELNO", "PARCEL_NO", "MAPNO", "AIN"]
  OWNER_NAME:
    mandatory: true
    synonyms: ["OWNER", "OWNERNAME", "OWN_NAME", "TAXPAYER"]
  SITUS_ADDRESS:
    mandatory: false
    synonyms: ["SITUS", "SITE_ADDR", "PROP_ADDR", "LOCATION"]
  ASSESSED_VALUE:
    mandatory: false
    synonyms: ["ASSD_VAL", "TOTAL_VAL", "AV_TOTAL"]
python
# requires: python >=3.10, PyYAML >=6.0
from pathlib import Path
import yaml


def load_dictionary(path: Path) -> dict:
    doc = yaml.safe_load(path.read_text(encoding="utf-8"))
    # Build a reverse index: every known synonym -> its canonical key.
    reverse = {}
    for canonical, spec in doc["fields"].items():
        reverse[canonical.upper()] = canonical
        for syn in spec.get("synonyms", []):
            reverse[syn.upper()] = canonical
    return {"fields": doc["fields"], "reverse": reverse, "version": doc["schema_version"]}

Configuration rules:

  • Store the canonical dictionary in version control; the coverage report pins its schema_version.
  • Seed the reverse index with every already-accepted synonym so known aliases need no fuzzy pass.
  • Mark mandatory canonical keys explicitly; only those gate the CI coverage check.
  • Keep the dictionary shared with the parent enforcement stage so the two never drift.

Step 2: Propose candidate matches

For every incoming county column that the reverse index does not already resolve, score it against the canonical vocabulary with normalized fuzzy matching. Normalize aggressively first — uppercase, strip non-alphanumerics — so Parcel No. and PARCEL_NO compare equal. The output is a ranked proposal list for review, never an applied rename.

python
# requires: rapidfuzz >=3.6, pandas >=2.0  (Python 3.10+)
import re
from rapidfuzz import fuzz, process


def normalize(name: str) -> str:
    return re.sub(r"[^A-Z0-9]", "", name.upper())


def propose(columns: list[str], dictionary: dict, cutoff: int = 80) -> list[dict]:
    reverse = dictionary["reverse"]
    canon_norm = {normalize(k): k for k in dictionary["fields"]}
    proposals = []
    for col in columns:
        norm = normalize(col)
        if norm in {normalize(k) for k in reverse}:
            continue  # already a known synonym — no proposal needed
        match = process.extractOne(
            norm, canon_norm.keys(), scorer=fuzz.token_sort_ratio, score_cutoff=cutoff
        )
        proposals.append({
            "county_column": col,
            "canonical_candidate": canon_norm[match[0]] if match else None,
            "score": round(match[1], 1) if match else 0.0,
            "status": "needs_review",  # nothing is applied on a score alone
        })
    return proposals

Proposal rules:

  • Skip columns the reverse index already resolves; fuzzy matching is only for the unknowns.
  • Normalize before scoring so punctuation and case never split an obvious match.
  • Set a conservative cutoff (>= 80); a low-confidence proposal is still worth reviewing but is flagged, not applied.
  • Emit needs_review on every proposal — the apply step must not read this file.

Step 3: Freeze a reviewed alias table

A human reviews the proposals and promotes the correct ones into the reviewed alias table. This is the single deterministic input to the apply step. Promotion is an explicit, diffable edit — a pull request that a data steward approves — so the crosswalk is auditable and reproducible. Below is the shape of the reviewed table after a county is onboarded.

yaml
# reviewed_aliases.yaml — only extended by human-reviewed pull requests
schema_version: "2.1.0"
counties:
  clark:
    PARCELNO: PARCEL_ID       # promoted from a 96.0 proposal
    OWN_NAME: OWNER_NAME       # promoted from a 91.2 proposal
    PROP_ADDR: SITUS_ADDRESS
  washoe:
    APN: PARCEL_ID            # already a dictionary synonym; recorded for provenance
    TAXPAYER: OWNER_NAME
python
# requires: python >=3.10, PyYAML >=6.0
from pathlib import Path
import yaml


def load_reviewed(path: Path, county: str) -> dict[str, str]:
    doc = yaml.safe_load(path.read_text(encoding="utf-8"))
    table = doc.get("counties", {}).get(county, {})
    # Uppercase keys so application is case-insensitive but still exact.
    return {k.upper(): v for k, v in table.items()}

Review rules:

  • Extend the reviewed alias table only through a reviewed pull request; no automated writes.
  • Record even already-known synonyms per county so the crosswalk is complete for provenance.
  • Pin the same schema_version as the canonical dictionary; a mismatch fails the load.
  • Treat a proposal a reviewer rejects as a negative example worth keeping in the PR history.

Step 4: Apply the crosswalk deterministically

Rename county columns to canonical keys using only the reviewed alias table and the dictionary’s built-in synonyms — never a live fuzzy score. After renaming, assert that every mandatory canonical field is present; a missing one is a hard stop that routes to the parent stage’s missing-field handling rather than being silently absent.

python
# requires: geopandas >=0.14, pandas >=2.0  (Python 3.10+)
import geopandas as gpd


def apply_crosswalk(gdf: gpd.GeoDataFrame, dictionary: dict, reviewed: dict[str, str]) -> gpd.GeoDataFrame:
    reverse = dictionary["reverse"]
    rename: dict[str, str] = {}
    for col in gdf.columns:
        up = col.upper()
        if up in reviewed:
            rename[col] = reviewed[up]          # reviewed alias wins
        elif up in reverse:
            rename[col] = reverse[up]           # dictionary synonym
    gdf = gdf.rename(columns=rename)

    mandatory = [k for k, s in dictionary["fields"].items() if s.get("mandatory")]
    missing = [k for k in mandatory if k not in gdf.columns]
    if missing:
        raise ValueError(f"unmapped_mandatory: {missing}")  # route to missing-field handling
    return gdf

Apply rules:

  • Drive renames only from the reviewed alias table and dictionary synonyms — no scoring at apply time.
  • Let a reviewed alias override a dictionary synonym so a county-specific override is honoured.
  • Raise unmapped_mandatory on any absent required field; hand it to missing-field handling.
  • Preserve the geometry column untouched; only attribute headers are reconciled here.

Step 5: Report coverage and gate in CI

Emit a per-county coverage report — how many mandatory canonical fields resolved, and via which route — and fail CI when a county introduces an unreviewed or unmapped mandatory column. The date and legacy-format coercion that follows renaming is owned by field renaming and type coercion rules; this stage stops once the headers are canonical.

yaml
# .github/workflows/parcel-crosswalk.yml
name: Parcel Field Crosswalk
on:
  pull_request:
    paths:
      - 'config/canonical_parcels.yaml'
      - 'config/reviewed_aliases.yaml'
      - 'data/counties/**'
jobs:
  reconcile:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-python@v5
        with:
          python-version: "3.11"
      - name: Install dependencies
        run: pip install "geopandas>=0.14" "pandas>=2.0" "rapidfuzz>=3.6" "PyYAML>=6.0"
      - name: Apply crosswalk and check coverage
        run: python -m pipeline.reconcile data/counties/ --min-mandatory-coverage 1.0
      - name: Upload coverage report
        if: always()
        uses: actions/upload-artifact@v4
        with:
          name: parcel-crosswalk-report
          path: reports/coverage_*.json

CI rules:

  • Require 100% mandatory coverage per county; a missing canonical PARCEL_ID must block the merge.
  • Fail the build when a county has an unresolved column that scored above the review cutoff — it needs a review decision.
  • Pin the dictionary and alias-table commits in the report so a mapping is reproducible.
  • Keep the proposal step out of the merge gate; only the reviewed table drives the pass/fail outcome.

Verification

Confirm the crosswalk reconciles known aliases and refuses to guess unknown ones — a pipeline that maps everything is silently aligning wrong columns. Assert on a county with reviewed aliases and one with an unmapped mandatory field:

python
gdf = apply_crosswalk(clark_extract, dictionary, load_reviewed(path, "clark"))
assert "PARCEL_ID" in gdf.columns, "reviewed alias PARCELNO->PARCEL_ID not applied"
assert "PARCELNO" not in gdf.columns, "original county column survived the rename"

# A county whose parcel id column is neither a synonym nor reviewed must hard-stop.
import pytest
with pytest.raises(ValueError, match="unmapped_mandatory"):
    apply_crosswalk(unknown_extract, dictionary, reviewed={})

A healthy run prints no unmapped_mandatory for onboarded counties, and the proposal report contains zero needs_review rows above the cutoff for a merged branch. On the CLI, python -m pipeline.reconcile --dry-run data/counties/clark/ should list every rename it would make and its source (reviewed or synonym), so a reviewer can audit the crosswalk before it runs.

Troubleshooting

Symptom Likely cause Fix
A column was renamed to the wrong canonical key An apply step read a live fuzzy score instead of the reviewed table Remove scoring from the apply path; drive renames only from reviewed_aliases.yaml.
unmapped_mandatory: ['PARCEL_ID'] on a new county The county’s parcel column is neither a synonym nor reviewed Review the proposal, promote the correct alias in a PR, then re-run.
Parcel No. and PARCEL_NO score below the cutoff Normalization not applied before scoring Ensure normalize() strips punctuation and case before rapidfuzz compares.
Two county columns map to the same canonical key A genuine duplicate or an over-broad synonym Split the synonym, add a county-specific reviewed override, or reject the extract for correction.
Coverage passes but downstream dates fail to parse Field names are canonical but values are still legacy formats Hand the reconciled layer to field renaming and type coercion before validation.