Cleaning a property portfolio's addresses at scale with geocoding

The same building lives in your database under five different spellings. Lease records say *1420 Harbor Blvd*. Work orders say *1420 Harbour Boulevard*. The accounting system says *1420 Harbor Blvd Ste 200*. The maintenance ticketing tool says *Harbor Blvd 1420*. The insurance schedule says *1420 Harbor Blvd., Unit 200*. Every one of these refers to the same parcel. No JOIN will fix it. The strings do not match. A fuzzy-match library will get you partway there and then stall on the edge cases where a neighbouring building is at *1422 Harbor Blvd* and your confidence in the match collapses.

The underlying problem is that your address data is untreated text, not a structured identifier. The fix is to stop treating it as text. Geocode every row, get back a normalised address and a coordinate pair from the same authority, and use those as the canonical key. Five spellings of the same building return the same lat/lng and the same normalised form. One line in your canonical table. The duplicates collapse.

This post covers the full pattern end to end: the batch geocoding run, reading confidence scores to split auto-accept from human review, building the stable key that survives unit numbers and edge cases, and wiring up intake validation so the next person who types an address into your leasing tool does not recreate the mess you just cleaned up.

Why address text is the wrong primary key

Most property-management platforms inherit their address data from at least three places: a leasing or ERP system where someone typed the address when the lease was first created, a facilities or work-order system where a technician looked up the property and typed it again, and an accounting system where a finance analyst imported a spreadsheet and the column contained whatever the property manager had written in their own shorthand. Three systems, three humans, three chances to abbreviate *Boulevard* differently or include or exclude a suite number.

The canonical key problem gets sharper when you consider what you actually want to be unique. You do not want *building* to be the unit of uniqueness — a 20-storey office building with 40 tenants has 40 leases and may have 40 entries in a sensible property table, one per leased unit. But you also do not want *unit* to be the unit of uniqueness if your work-order system lumps all work orders for the building under the building address and ignores the suite. The key has to carry enough information to disambiguate, but no more.

The geocoder solves the first half: it tells you definitively whether *1420 Harbor Blvd* and *1420 Harbour Boulevard* are the same parcel. It also normalises the output to a consistent form. What it cannot do on its own is decide whether *Unit 200* and *Ste 200* on the same floor of that parcel are the same leased space. That is a business decision. The pattern below keeps unit information in the key so you can make that decision explicitly, rather than quietly dropping it.

The data you start with

A realistic property-management portfolio has a few hundred to a few thousand distinct physical properties, but might have tens of thousands of rows across the combined systems — leases (possibly multiple per property per year as they renew), work orders (dozens per property per year), accounting line items, inspection records, and insurance schedules. The first step is to extract one table of all addresses from all sources.

The table you want looks like this before geocoding:

source,raw_address,source_id
leases,"1420 Harbor Blvd",lease-10042
leases,"1420 Harbor Blvd Ste 200",lease-10043
work_orders,"1420 Harbour Boulevard",wo-88812
accounting,"1420 Harbor Blvd., Unit 200",acc-9910
insurance,"Harbor Blvd 1420",ins-2201

Do not try to normalise the text before geocoding. The normalisation is the geocoder's job. You will burn a week on regex rules that still miss the *Harbour* / *Harbor* variant because it is a legitimate English-language difference rather than a typo, and you will never get full coverage without essentially rebuilding the reference database yourself.

Export everything, deduplicate by exact string only (so identical strings do not consume two credits), and geocode.

Step 1: Upload the portfolio to the batch tool

CSV2GEO's web batch tool is the right starting point for a one-time or infrequent clean-up run. Upload your CSV or Excel file, map the column that contains the address, and download the enriched result. Credits are consumed per address row — the exact-string deduplication you did before upload saves real money on large portfolios where the same building address appears hundreds of times across leases.

For a portfolio of, say, 8,000 unique address strings (after exact dedup), the batch run costs 8,000 credits. The free tier gives 3,000 calls/day, so a small portfolio can be cleaned at no cost over a couple of days. Paid tiers start at $54/month for 100,000 calls; see csv2geo.com/pricing/api for the current brackets.

The batch tool returns a CSV with, at minimum, a normalised address string, latitude, longitude, and a confidence score for each input row. Those four columns are all you need for the deduplication step.

If you prefer to drive the batch programmatically — useful when you want to integrate the clean-up into a data pipeline that runs on a schedule — the REST API exposes the same geocoding endpoint. A Python script that processes the portfolio in streaming fashion:

import csv
import os
import requests
import time

API = "https://csv2geo.com/api/v1/geocode"
KEY = os.environ["CSV2GEO_API_KEY"]

def geocode(address: str) -> dict:
    r = requests.get(
        API,
        params={"q": address, "api_key": KEY},
        timeout=20,
    )
    r.raise_for_status()
    results = r.json().get("results", [])
    return results[0] if results else {}

with open("portfolio_unique.csv") as fin, \
     open("portfolio_geocoded.csv", "w", newline="") as fout:

    reader = csv.DictReader(fin)
    fields = reader.fieldnames + [
        "lat", "lng", "normalised_address", "confidence"
    ]
    writer = csv.DictWriter(fout, fieldnames=fields)
    writer.writeheader()

    for row in reader:
        result = geocode(row["raw_address"])
        row["lat"] = result.get("lat")
        row["lng"] = result.get("lng")
        row["normalised_address"] = result.get("formatted_address")
        row["confidence"] = result.get("confidence")
        writer.writerow(row)
        time.sleep(0.05)  # stay within rate limits on the free tier

Or in Node using fetch:

import fs from 'node:fs';
import { parse } from 'csv-parse/sync';
import { stringify } from 'csv-stringify/sync';

const API = 'https://csv2geo.com/api/v1/geocode';
const KEY = process.env.CSV2GEO_API_KEY;

async function geocode(address) {
  const url = `${API}?q=${encodeURIComponent(address)}&api_key=${KEY}`;
  const r = await fetch(url);
  if (!r.ok) throw new Error(`http ${r.status}`);
  const data = await r.json();
  return data.results?.[0] ?? {};
}

const rows = parse(fs.readFileSync('portfolio_unique.csv'), { columns: true });
const out = [];

for (const row of rows) {
  const result = await geocode(row.raw_address);
  out.push({
    ...row,
    lat: result.lat ?? null,
    lng: result.lng ?? null,
    normalised_address: result.formatted_address ?? null,
    confidence: result.confidence ?? null,
  });
  await new Promise(r => setTimeout(r, 50));
}

fs.writeFileSync('portfolio_geocoded.csv',
  stringify(out, { header: true }));

Both scripts are thin wrappers around a single HTTP call. No SDK, no library version to pin. The time.sleep / setTimeout is a politeness delay, not a protocol requirement — if you are on a paid tier with a higher rate limit, drop or reduce it.

Step 2: Use confidence scores to split auto-accept from review

The geocoder returns a confidence score per result, typically a float between 0 and 1. This is the signal you use to decide whether to trust the output automatically or route the row for human review. A full treatment of what the score means and how the bands work is in Geocoding Confidence Scores Explained; the short version for this workflow is:

• Above 0.85: auto-accept. The geocoder is highly confident about the match. These rows go straight into the canonical table.
• 0.60 to 0.85: flag for review. The result is probably right but the address was ambiguous — a missing suite number, an abbreviated street type that matched two possibilities, a building at the edge of a city boundary. A human reviewer can confirm or correct in under a minute per row.
• Below 0.60: hold and fix. The address is badly formed, incomplete, or genuinely outside the reference database. These need manual correction before they can join the canonical table.

In Python, splitting the output is a single pass:

import csv

auto_accept = []
review_queue = []
hold_fix = []

with open("portfolio_geocoded.csv") as f:
    for row in csv.DictReader(f):
        score = float(row["confidence"]) if row["confidence"] else 0.0
        if score >= 0.85:
            auto_accept.append(row)
        elif score >= 0.60:
            review_queue.append(row)
        else:
            hold_fix.append(row)

print(f"Auto-accept: {len(auto_accept)}")
print(f"Review queue: {len(review_queue)}")
print(f"Hold/fix: {len(hold_fix)}")

The review queue is the deliverable you hand to a property manager or data steward. They do not need to know anything about geocoding — they see a short list of addresses with a "suggested normalised form" and a "does this look right?" checkbox. Most rows in the 0.60–0.85 band are confirmed within seconds. The hold-and-fix rows are the genuinely bad data — addresses that someone entered incorrectly years ago — and these need a data-quality conversation with the source system owner, not a geocoding fix.

Do not skip the review step for a real portfolio. Blindly accepting all results regardless of confidence will silently introduce errors into the canonical table, and those errors are much harder to find after the fact than a one-time human review pass during the clean-up.

Step 3: Build the stable canonical key

Once you have accepted a geocoded result for a given address, you need a stable identifier that survives reformatting, minor updates to the reference data, and the inevitable future geocoding run that might return slightly different coordinates as the underlying address database improves.

The stable key combines three things:

1. A truncated and rounded lat/lng (6 decimal places gives ~0.1 m precision — enough to identify a building without being sensitive to tiny DEM or geocoder updates)
2. The normalised street address from the geocoder output
3. The unit or suite identifier, preserved exactly as the geocoder returns it

That third point deserves emphasis. A common mistake when building canonical keys for property data is to drop the unit component because "the building is the real unit of uniqueness." For some tables that is true — a maintenance record for the building's HVAC system belongs at the building level, not the unit level. But a lease belongs to a unit. An insurance schedule may itemise per unit. If your key drops Apt 4B, you cannot distinguish two leases in the same building, and your deduplication logic will incorrectly merge records that should stay separate.

The geocoder does not discard unit information — it surfaces it in the normalised output. Keep it in the key. Let your application logic decide, downstream, whether a given query needs the building key or the unit key. Both are derivable from the same geocoded record.

A Python function that produces the key:

import math

def canonical_key(lat: float, lng: float,
                  normalised_address: str,
                  unit: str | None = None) -> str:
    """
    Stable key for a geocoded property address.
    lat/lng rounded to 6dp; unit appended if present.
    """
    lat_r = round(lat, 6)
    lng_r = round(lng, 6)
    base = f"{lat_r},{lng_r}|{normalised_address.strip().upper()}"
    if unit:
        base += f"|{unit.strip().upper()}"
    return base

Run this over every auto-accepted and reviewed row. Two rows that produced the same key are the same physical location (and the same unit, if applicable). Group by key, and the deduplication is done — you see immediately which source_id values from leases, work orders, accounting, and insurance all belong to the same canonical entry.

Step 4: Collapse the duplicates into a canonical table

With keys in hand, the merge is a standard group-by. The business logic for how to merge the per-source fields is yours — there is no API endpoint for that, and there should not be. You know which source of truth wins when lease.property_name conflicts with accounting.property_name. The geocoder's job was to tell you which rows belong together; your application logic decides what the merged row looks like.

A minimal canonical table schema:

CREATE TABLE canonical_properties (
    canonical_key       TEXT PRIMARY KEY,
    normalised_address  TEXT NOT NULL,
    unit                TEXT,
    lat                 NUMERIC(9,6) NOT NULL,
    lng                 NUMERIC(9,6) NOT NULL,
    confidence_band     TEXT CHECK (confidence_band IN ('auto','reviewed')),
    source_ids          JSONB,         -- array of all source_id values that mapped here
    created_at          TIMESTAMPTZ DEFAULT NOW(),
    updated_at          TIMESTAMPTZ DEFAULT NOW()
);

source_ids carries the provenance — you can always trace back which lease record, work order, and accounting line maps to a canonical entry. When a dispute arises about whether two entries were correctly merged, you have the receipts.

After the first population run, distribute the canonical_key back to the source systems. Every lease record gets a canonical_key column. Every work order gets one. The JOIN that was previously impossible — "give me all work orders for the property this lease is for" — is now a trivial WHERE canonical_key = ?.

Step 5: Validate at intake so the mess never comes back

The batch clean-up is a one-time event. The mess will return in six months if the intake path — where a property manager types a new address into the leasing tool — does not validate and normalise on the way in.

Wire the REST geocoding endpoint into your intake form as a real-time validation call. When the user finishes typing an address and tabs away (or clicks a "Verify address" button), the frontend fires a single geocoding call, displays the normalised result and a confidence indicator, and asks the user to confirm.

A minimal JavaScript snippet for the validation call:

async function validateAddress(rawAddress) {
  const url = 'https://csv2geo.com/api/v1/geocode?' +
    new URLSearchParams({
      q: rawAddress,
      api_key: process.env.NEXT_PUBLIC_CSV2GEO_KEY,
    });

  const r = await fetch(url);
  if (!r.ok) throw new Error(`geocode error: ${r.status}`);
  const data = await r.json();
  const top = data.results?.[0];

  if (!top || top.confidence < 0.60) {
    return { valid: false, message: 'Address not recognised — please check and retype.' };
  }

  return {
    valid: true,
    normalisedAddress: top.formatted_address,
    lat: top.lat,
    lng: top.lng,
    confidence: top.confidence,
    canonicalKey: canonicalKey(top.lat, top.lng, top.formatted_address),
  };
}

Display normalisedAddress to the user as a confirmation: *"We found: 1420 Harbor Boulevard, Suite 200, San Jose, CA 95112 — is this correct?"* If they confirm, store the normalised form and the canonical key, not the raw typed string. The problem never enters the database in the first place.

The api_key in a browser-side call is a read-only geocoding key scoped to a single origin domain — generate a separate key for frontend use at /api-keys rather than exposing your server-side key. See the API keys documentation for key scoping options.

This single change — validate and normalise at intake — eliminates the class of problem you just spent the batch run fixing. The batch run is the debt paydown. The intake validation is the new discipline.

What not to build yourself

A few tempting shortcuts that quietly fail.

Do not build a fuzzy-match deduplicator in lieu of geocoding. Levenshtein distance and similar algorithms do well on typos and swapped letters. They do not do well on transposed tokens (*1420 Harbor Blvd* vs *Harbor Blvd 1420*), legitimate spelling differences (*Harbour* vs *Harbor*), or abbreviated versus spelled-out forms (*Blvd* vs *Boulevard*). These are exactly the variants that accumulate in real property portfolios over years. Fuzzy matching will give you a deduplication rate that looks impressive on the edge cases it handles and quietly misses a meaningful fraction of the rest.

Do not build a unit-stripping normaliser. The urge is to strip everything after the comma — "just match on the primary address, ignore the unit" — and then deduplicate. This correctly merges variants of the building-level address, but it merges records that should not be merged: *Unit 201* and *Unit 202* in the same building are different spaces with different leases and different work-order histories. Build the unit into the key, as above, and let downstream queries choose the level of aggregation they need.

Do not assume that identical normalised strings from two separate geocoding runs are guaranteed to produce the same coordinates. Reference databases are updated. A property that geocoded to lat/lng *X* last year might geocode to lat/lng *X + ε* this year as the underlying address data is corrected. This is why the stable key includes both the normalised string and the rounded coordinate — it is stable enough for your internal use but you should re-geocode and re-derive keys when you do a major reference-data refresh, and treat the merge as an update, not a conflict.

Confidence scores in production monitoring

Once the canonical table exists and intake validation is live, add observability to the ongoing geocoding calls so you catch data-quality drift before it becomes a problem. The two metrics that matter:

Confidence distribution over time. Plot a histogram of confidence scores for all intake geocoding calls, bucketed weekly. A shift toward lower scores — more rows in the 0.60–0.85 band, more rows below 0.60 — is a signal that address input quality is degrading. Maybe a new data entry clerk is typing addresses in a new abbreviated form. Maybe a new market you entered has address formats that the geocoder handles less well. Catching this early, before thousands of low-confidence rows accumulate, means a smaller remediation job.

Review queue depth. If your intake validation routes low-confidence results to a human review queue, track how long rows sit in that queue before being confirmed or corrected. A queue that is growing faster than it is being resolved is a capacity problem or a training problem — either way, it is a leading indicator of a future data mess.

A detailed treatment of what to instrument and how is in Observability for Geocoding Pipelines.

Cost structure for a real portfolio

A 10,000-property portfolio across all source systems, deduplicated to roughly 8,000 unique address strings before the batch run:

• Initial batch geocoding: 8,000 credits (one per unique address string)
• Ongoing intake validation: roughly 1 credit per new lease or work order created — for a portfolio of this size, perhaps 500–1,000 new records per month
• Annual re-geocode for reference-data refresh (optional, recommended annually): 8,000 credits, decreasing as the canonical table stabilises

At the entry paid tier ($54/month for 100,000 calls), the initial batch run and the first six months of intake validation sit well within a single month's allocation. The portfolio is cleaned once, the intake is locked down permanently, and the ongoing credit cost is modest.

For larger portfolios or more frequent re-geocoding cycles, see csv2geo.com/pricing/api for the bracket that fits.

Frequently Asked Questions

Why geocode rather than just normalise the address text with a rule-based system? Rule-based normalisation can standardise abbreviations (*Blvd* to *Boulevard*) but cannot resolve transposed token order (*1420 Harbor Blvd* vs *Harbor Blvd 1420*) or legitimate spelling variants (*Harbour* vs *Harbor*). More importantly, it cannot tell you whether two addresses that look different actually refer to the same physical parcel. Geocoding resolves the spatial identity question definitively — the coordinate is the ground truth, not the string.

What do I do with rows that come back with confidence below 0.60? Route them to a human review queue with the raw input and, if available, any other fields that might help identify the property (tenant name, unit, floor). The reviewer either corrects the address text and re-geocodes, or flags the record as genuinely bad data that needs to be fixed in the source system. Do not auto-accept low-confidence results into the canonical table.

Should unit numbers like "Apt 4B" be included in the canonical key? Yes, always. Dropping unit information merges records that belong to different leased spaces. The geocoder surfaces unit information in the normalised output — keep it in the key. Your application queries can always aggregate up to the building level by dropping the unit component of the key for a given query; you cannot disaggregate back down if you have already merged.

How do I handle a property that has been renumbered or re-addressed since the original records were created? Re-geocode the old address and the new address. If they return the same or very nearby coordinates, you have confirmed they are the same building. Update the canonical key to the new normalised address, archive the old key as an alias, and update the canonical_key column in all source systems that referenced the old key.

Is there a merge or deduplication endpoint in the API? No — and there should not be. The geocoding API gives you the normalised address and coordinates per input row. The merge logic is yours: which source wins on conflicts, how to handle partially overlapping unit lists, which records to archive versus update. Those decisions encode your business rules, not geography. Keep them in your application layer.

How often should I re-run the batch clean-up after the initial pass? With intake validation in place, continuous re-runs are rarely necessary. A quarterly pass over any records that entered without geocoding (imports, bulk uploads from external partners) is a reasonable discipline. An annual full re-geocode of the canonical table against the latest reference data catches any properties whose official address changed at the source.

Does the batch tool handle Excel files or only CSV? The web batch tool accepts both CSV and Excel files. Map the address column in the column-mapping step. Credits are consumed per address row regardless of file format.

Related Articles

• Geocoding confidence scores explained — what the score means, how the bands work, and how to set thresholds for your use case
• Benchmarking geocoding APIs — honest numbers — what to measure when evaluating geocoding quality on your own address data
• Caching geocoding results — 90% cost reduction — how to cache geocoding calls so a portfolio re-run does not double-bill on addresses you have already resolved
• Idempotent geocoding — safe to retry — how to structure geocoding calls so interrupted batch runs resume cleanly without duplicate credits
• Observability for geocoding pipelines — what to instrument, what metrics to track, and how to catch data-quality drift before it becomes a remediation project

---

*I.A. / CSV2GEO Creator*