Caching, Deduplication & Spatial Indexing

A geocoding pipeline that calls a provider for every address it sees is slow, expensive, and quietly non-compliant. The same addresses recur constantly across batches; unindexed spatial tables force sequential scans over millions of rows; and near-duplicate address strings inflate row counts and corrupt every downstream aggregate. Once a pipeline moves past a few thousand records, throughput and data quality are governed less by how you call the routing engine and more by how you cache its results, deduplicate its inputs, and index its outputs.

This guide surveys the three disciplines that make high-volume geocoding fast and clean: caching geocoding results across Redis and Postgres, deduplicating addresses by a fuzzy canonical key, and designing spatial indexes so proximity and containment queries stay sub-second at scale. Each section frames the failure it prevents, compares the practical approaches, and shows production Python and SQL. It builds directly on the clean, tagged output produced by core address parsing and standardization.

---

Why Redundant Work Dominates Geocoding Cost

Three distinct forms of waste compound as a pipeline grows, and each has a different remedy.

• Redundant API calls. Address data is heavily repetitive — customer records, delivery manifests, and CRM exports contain the same locations over and over. Re-geocoding an address you resolved yesterday burns quota and money for a result you already own, and many provider licenses require you to reuse a cached coordinate rather than re-query it.
• Unindexed spatial queries. A SELECT ... WHERE ST_DWithin(geom, :point, 500) over an unindexed table of geocoded points is a full sequential scan. At a million rows it turns an interactive dashboard query into a multi-second stall, and it gets linearly worse as the table grows.
• Duplicate addresses. "100 Main St", "100 Main Street", and "100 MAIN ST." are the same place but three rows. Left unresolved they triple geocoding spend, split analytics across phantom locations, and produce inflated counts that quietly mislead every report built on top of them.

The fix is a small subsystem that sits between the routing engine and the spatial database: a cache that answers repeat lookups without an API call, a deduplication stage that collapses variant spellings before they reach the provider, and a spatial index that keeps the resulting table queryable. The next section shows how these three pieces connect.

---

Pipeline Architecture Overview

The caching, deduplication, and indexing layer intercepts normalized addresses on their way to the geocoder and shapes the results on their way into storage. Deduplication runs first so the cache and the provider only ever see one canonical form of each address; the cache absorbs repeat lookups; and the spatial index makes the committed points queryable.

Each stage is examined in detail below, starting with the methodology trade-offs and then the production implementations.

---

Core Methodology Survey

Caching, deduplication, and indexing each admit several implementations. The right combination depends on data volume, how repetitive the address stream is, and how much operational surface you can maintain.

In practice, a production pipeline uses several of these together: a Redis tier in front of a Postgres cache, a two-phase deduplication that runs canonical-key collapse before fuzzy matching, and a GiST index (with an optional geohash column) on the final geocoded table. The sections below build each piece.

---

Stage 1: Deduplicating Addresses Before They Cost You

Deduplication belongs upstream of both the cache and the provider. If three spellings of one address reach the geocoder as three distinct strings, you pay three times and produce three cache entries that never share a hit. Collapsing them to one canonical form first is the highest-leverage change in the whole pipeline.

The reliable pattern is two-phase. First, generate a deterministic canonical key by normalizing case, expanding standard abbreviations, stripping punctuation and unit noise, and joining components in a fixed order. Exact matches on that key collapse the overwhelming majority of trivial duplicates in a single hash operation.

import re
import unicodedata
from typing import Dict

# Compile substitution patterns once at module level.
_PUNCT = re.compile(r"[.,#]+")
_MULTI_WS = re.compile(r"\s{2,}")
_UNIT = re.compile(r"\b(?:apt|apartment|unit|ste|suite|fl|floor|rm|room)\b.*$")

# USPS-style suffix and directional abbreviations (abridged).
_SUFFIX: Dict[str, str] = {
    "street": "st", "avenue": "ave", "boulevard": "blvd", "drive": "dr",
    "road": "rd", "lane": "ln", "court": "ct", "place": "pl",
}
_DIRECTION: Dict[str, str] = {
    "north": "n", "south": "s", "east": "e", "west": "w",
    "northeast": "ne", "northwest": "nw", "southeast": "se", "southwest": "sw",
}


def canonical_key(street: str, city: str, state: str, postal: str) -> str:
    """
    Build a deterministic, comparison-friendly key for one address.

    Case-folds, expands abbreviations to a single canonical form, drops unit
    designators and punctuation, and joins components in a fixed order. Two
    inputs that describe the same delivery point should map to one key.
    """
    street = unicodedata.normalize("NFKC", street).casefold()
    street = _UNIT.sub("", street)
    street = _PUNCT.sub("", street)
    tokens = [_SUFFIX.get(t, _DIRECTION.get(t, t)) for t in street.split()]
    street = _MULTI_WS.sub(" ", " ".join(tokens)).strip()
    city = _PUNCT.sub("", city.casefold()).strip()
    state = state.casefold().strip()
    postal = postal.split("-")[0].strip()  # collapse ZIP+4 to 5-digit
    return "|".join((street, city, state, postal))

The canonicalization logic leans directly on the parsing rules covered in regex patterns for US address parsing and the case-folding discipline from Unicode and character normalization in Python. Keep the abbreviation tables aligned with USPS Publication 28 so the canonical form matches the postal standard.

For a batch, vectorize the collapse with pandas so duplicates are grouped in one pass rather than row by row:

import pandas as pd


def collapse_duplicates(df: pd.DataFrame) -> pd.DataFrame:
    """
    Add a canonical key column and return one representative row per key.

    Expects columns: street, city, state, postal. Keeps the first occurrence
    of each canonical key and records how many raw rows collapsed into it.
    """
    df = df.copy()
    df["canon"] = [
        canonical_key(s, c, st, p)
        for s, c, st, p in zip(df["street"], df["city"], df["state"], df["postal"])
    ]
    counts = df.groupby("canon", sort=False).size().rename("dup_count")
    deduped = df.drop_duplicates("canon", keep="first").join(counts, on="canon")
    return deduped.reset_index(drop=True)

The residual cases — a transposed digit, a misspelled street name, an abbreviation your table missed — survive exact canonicalization. Those are handled by fuzzy address matching with RapidFuzz, which compares candidate pairs by similarity score. The critical performance rule is blocking: only compare addresses that share a blocking key (postal code, or a geohash prefix once coordinates exist), so you never run an O(n²) comparison across the whole table.

---

Stage 2: Caching Geocoding Results

Once addresses are deduplicated, the cache absorbs every repeat lookup. The standard design is a two-tier read-through cache: Redis on the hot path for sub-millisecond exact-match reads, and a Postgres table as the durable system of record behind it. The full patterns — key design, TTL policy, and refresh — live in Redis and Postgres caching patterns.

The cache key is a hash of the canonical form (never the raw string), namespaced by provider and a normalization schema version so a logic change invalidates old entries cleanly:

import hashlib
import json
from typing import Optional

import redis.asyncio as aioredis

_SCHEMA_VERSION = "v3"  # bump when canonicalization logic changes


def cache_key(canon: str, provider: str) -> str:
    """Namespaced, versioned SHA-256 key for a canonical address + provider."""
    digest = hashlib.sha256(canon.encode("utf-8")).hexdigest()
    return f"geo:{_SCHEMA_VERSION}:{provider}:{digest}"


class GeocodeCache:
    """
    Read-through cache. Redis is the hot tier; a Postgres table (queried via
    the injected `db_lookup`/`db_store` callables) is the durable tier.
    """

    def __init__(self, redis_url: str, default_ttl: int = 30 * 86400) -> None:
        self._redis = aioredis.from_url(redis_url)
        self._default_ttl = default_ttl  # seconds; cap to provider ToS

    async def get(self, canon: str, provider: str) -> Optional[dict]:
        """Return a cached result from Redis, or None on miss."""
        raw = await self._redis.get(cache_key(canon, provider))
        return json.loads(raw) if raw else None

    async def put(
        self, canon: str, provider: str, result: dict, ttl: Optional[int] = None
    ) -> None:
        """Store a result in Redis with a provider-appropriate TTL."""
        await self._redis.set(
            cache_key(canon, provider),
            json.dumps(result, separators=(",", ":")),
            ex=ttl or self._default_ttl,
        )

The TTL is not a performance knob — it is a compliance control. Some providers forbid caching results beyond a fixed window, so the maximum retention must be encoded per provider and enforced by expiry. This connects to the licensing discussion in API quota tracking and cost management: a cache hit is a saved API call, and a saved API call is saved quota and money.

For durable, queryable caching that also feeds analytics, a Postgres materialized view geocode cache persists resolved coordinates on disk and can be refreshed incrementally. A minimal cache table plus read-through query looks like this:

CREATE TABLE geocode_cache (
    canon        text        PRIMARY KEY,
    provider     text        NOT NULL,
    lon          double precision NOT NULL,
    lat          double precision NOT NULL,
    precision    text        NOT NULL,
    geom         geometry(Point, 4326)
                 GENERATED ALWAYS AS (ST_SetSRID(ST_MakePoint(lon, lat), 4326)) STORED,
    resolved_at  timestamptz NOT NULL DEFAULT now()
);

-- Durable read-through lookup: returns a row only if fresh enough.
SELECT lon, lat, precision
FROM   geocode_cache
WHERE  canon = :canon
  AND  resolved_at > now() - interval '30 days';

The complete read-through flow is: hash the canonical key, check Redis, fall back to the Postgres cache table on a Redis miss, and only call the provider when both are empty — then backfill both tiers with the fresh result. Short-lived, high-repeat batches see hit rates well above 40%, so most records never reach the provider at all.

---

Stage 3: Designing Spatial Indexes for Geocoded Data

Cached, deduplicated coordinates are only useful if you can query them by location. The moment you write ST_DWithin, ST_Contains, or a nearest-neighbour ORDER BY geom <-> :point, an unindexed table forces a sequential scan. Designing spatial indexes for geocoded data covers the full decision, but the core choice is between GiST, BRIN, and geohash prefixes.

A GiST index is the default for point data. It supports the full range of spatial operators and stays efficient for containment and nearest-neighbour queries:

-- Default choice for mixed proximity / containment workloads.
CREATE INDEX idx_geocode_geom_gist
    ON geocode_cache USING gist (geom);

-- KNN nearest-neighbour query the GiST index accelerates directly.
SELECT canon, lon, lat
FROM   geocode_cache
ORDER BY geom <-> ST_SetSRID(ST_MakePoint(:lon, :lat), 4326)
LIMIT 10;

A BRIN index wins when the table is very large, append-mostly, and physically ordered by location — for example when records are ingested region by region. BRIN stores only per-block min/max summaries, so it is a tiny fraction of a GiST index’s size and almost free to maintain, at the cost of requiring good physical clustering:

-- Cluster physically by location first, then a tiny BRIN summarizes blocks.
CLUSTER geocode_cache USING idx_geocode_geom_gist;
CREATE INDEX idx_geocode_geom_brin
    ON geocode_cache USING brin (geom) WITH (pages_per_range = 64);

The full trade-off — index size, write cost, and query patterns — is worked through in choosing GiST vs BRIN index in PostGIS. As a rule: reach for GiST first, and only switch to BRIN when the table is large, append-heavy, and you can keep it clustered.

When you need proximity buckets without native spatial types — for example to block deduplication candidates or to shard a cache — a geohash encodes a coordinate as a short string whose shared prefixes imply nearby locations. Geohash encoding for address proximity search covers precision selection; a plain B-tree on the geohash column then answers proximity-bucket queries with an ordinary prefix match:

import pandas as pd
import pygeohash as pgh


def add_geohash(df: pd.DataFrame, precision: int = 7) -> pd.DataFrame:
    """
    Add a geohash column for proximity blocking and B-tree indexing.

    Precision 7 encodes roughly a 150 m cell, a good default for grouping
    candidate duplicates within a neighbourhood before fuzzy comparison.
    """
    df = df.copy()
    df["geohash"] = [
        pgh.encode(lat, lon, precision=precision)
        for lat, lon in zip(df["lat"], df["lon"])
    ]
    return df

That same geohash column closes the loop with Stage 1: it is the ideal blocking key for fuzzy deduplication, because two addresses can only be near-duplicates if they are physically close.

---

Edge Cases & Named Failure Modes

Stale Cache After a Street Rename

Municipalities rename streets and reassign postal boundaries. A permanently cached coordinate keeps serving the old location long after it changed. Bound this with a TTL sized to your staleness tolerance and a schema-versioned key, so a normalization or dataset change invalidates old entries instead of silently serving them. High-value records should be scheduled for periodic refresh rather than trusted forever.

Canonical Key Collisions

Aggressive canonicalization can map two genuinely different addresses to the same key — for example when unit designators are stripped and "5 Oak St Apt 1" and "5 Oak St Apt 2" collapse to the same delivery point. Decide deliberately whether unit-level resolution matters for your use case. If it does, keep the unit in the key; if you are geocoding to a building, dropping it is correct. Never let collision behaviour be accidental.

Antimeridian and Pole-Crossing Queries

Bounding-box and ST_DWithin queries near longitude ±180° or the poles can silently return wrong results if you compute distances in planar coordinates. Use the PostGIS geography type (or a projected CRS appropriate to the region) for distance math so calculations follow the curved surface, and test explicitly with coordinates that straddle the antimeridian.

Duplicate Explosion From Bad Blocking

Fuzzy deduplication without blocking is O(n²): a million-row table implies 10¹² comparisons. But a blocking key that is too coarse (state-level) recreates the same blow-up inside each block, while one that is too fine (full ZIP+4) hides real duplicates in separate blocks. Block on 5-digit postal code or a geohash prefix of precision 5–7, and monitor the largest block size as a guardrail.

Cache Stampede on Cold Start

When a cache is empty or freshly invalidated, a burst of concurrent workers can all miss the same key and call the provider simultaneously for one address. Guard hot keys with a short-lived lock (a Redis SET key NX sentinel) so the first worker resolves and backfills while the others wait or retry the cache, preventing a spike of redundant billed calls.

---

Validation & Quality Assurance

Caching and deduplication introduce their own correctness risks — a bad cache entry propagates to every future read, and an over-aggressive merge destroys real records. Apply three checks before trusting the layer.

Cache integrity. Periodically sample cached entries and re-geocode them against the provider, comparing coordinates within a tolerance (for example 50 m). Drift beyond tolerance signals a provider dataset change or a normalization bug and should invalidate the affected keys.

Deduplication precision and recall. Maintain a labelled set of known duplicate and known distinct address pairs. Measure how many true duplicates the canonical key plus fuzzy threshold actually collapse (recall) and how many distinct addresses are wrongly merged (false-merge rate). Tune the similarity threshold against this set rather than by intuition.

Spatial index correctness. After building or rebuilding an index, run representative proximity and containment queries with EXPLAIN ANALYZE and confirm the planner uses the index (an Index Scan or Bitmap Index Scan, not a Seq Scan). A query that silently falls back to a sequential scan — often because of a type mismatch between geometry and geography — defeats the whole exercise.

Stratify the deduplication test set across regions and address types so international formats and PO Box records, which behave differently under canonicalization, are represented.

---

Performance & Scaling

The remaining lever is concurrency on the cache-miss path. Deduplication and caching shrink the volume that reaches the provider, and optimizing batch geocoding throughput then processes that residual in parallel — for CPU-bound canonicalization and hashing, parallel geocoding with multiprocessing spreads the work across cores, while I/O-bound provider calls belong on the async path described in building async geocoding requests.

---

Troubleshooting: Common Failure Patterns

Cache Hit Rate Is Unexpectedly Low

If a repetitive dataset produces few cache hits, the canonical key is not deterministic. A common cause is non-deterministic ordering (dict iteration, locale-dependent case folding) or a raw string leaking into the key instead of the canonical form. Log the canonical key alongside the raw input for a sample and confirm that known-equivalent addresses produce byte-identical keys.

Spatial Query Still Does a Sequential Scan

EXPLAIN shows a Seq Scan despite an existing GiST index. Usually the query column and index column disagree — you indexed geometry but filtered on a geography cast, or the SRID differs. Ensure the predicate operates on the exact indexed expression, and rebuild statistics with ANALYZE so the planner has accurate row estimates.

Deduplication Merges Distinct Addresses

If reports show suspiciously few locations, the similarity threshold is too loose or the canonical key drops a discriminating component. Lower the fuzzy match acceptance, re-introduce the unit or secondary designator into the key where unit-level resolution matters, and audit merges against the labelled test set before applying them to production data.

Redis Memory Grows Without Bound

An ever-growing Redis footprint means entries are written without a TTL, or with a TTL longer than the working set justifies. Verify every SET passes an ex argument, configure a maxmemory policy such as allkeys-lru as a safety net, and separate long-lived durable data into the Postgres tier rather than holding it all in RAM.

Materialized View Refresh Locks Readers

A plain REFRESH MATERIALIZED VIEW takes an exclusive lock and blocks queries for its duration. Use REFRESH MATERIALIZED VIEW CONCURRENTLY (which requires a unique index on the view) so readers continue during the refresh, and schedule refreshes as incremental deltas keyed on resolved_at rather than full rebuilds.

Geohash Buckets Miss Nearby Neighbours

Two addresses a few metres apart can fall on opposite sides of a geohash cell boundary and land in different buckets. When using geohash prefixes for proximity search or blocking, always also check the eight neighbouring cells, not just the exact-prefix bucket, or a real duplicate straddling a boundary will be missed.

---

FAQ

Should I cache geocoding results in Redis or Postgres?

Use both in tiers. Redis serves the hot path with sub-millisecond exact-match lookups keyed on a hash of the normalized address, with a TTL that respects provider terms of service. Postgres is the durable system of record: it survives restarts, supports spatial queries, and can be exposed as a materialized view for analytics. A read-through pattern checks Redis first, falls back to Postgres, then calls the provider and backfills both layers.

How long can I legally cache geocoding results?

It depends entirely on the provider’s terms of service. Google Maps Platform prohibits caching most results beyond 30 days and forbids storing latitude/longitude for Places content indefinitely; OpenStreetMap-based services like Nominatim are far more permissive under ODbL with attribution. Always encode the maximum retention as a per-provider TTL in your cache layer so an expired entry forces a fresh lookup rather than serving stale, non-compliant data.

What spatial index type should I use for geocoded points?

For general nearest-neighbour and containment queries on point data, a PostGIS GiST index on the geometry or geography column is the default choice. Use a BRIN index instead when the table is very large, append-mostly, and physically ordered by location (for example ingested per region), because BRIN is dramatically smaller and cheaper to maintain. Geohash string prefixes are useful when you need index-friendly proximity buckets in a system without native spatial types.

How do I deduplicate addresses that are spelled slightly differently?

Generate a deterministic canonical key by normalizing case, expanding USPS abbreviations, stripping punctuation and unit designators, and concatenating the components in a fixed order. Exact matches on the canonical key collapse most trivial variants. For the residual near-duplicates that survive canonicalization, apply fuzzy matching with a library like RapidFuzz and a similarity threshold, blocking candidate comparisons by postal code or geohash so you never compare every row against every other row.

Does caching break when the underlying address data changes?

Yes, if you cache indefinitely. Streets are renamed, ZIP codes are reassigned, and providers improve their datasets. Bound this risk with a TTL sized to your tolerance for staleness and a versioned cache key that includes a normalization schema version. When you change the normalization logic, bump the version so old entries are ignored rather than silently served, and schedule an incremental refresh of high-value records.

---

Related

• Redis and Postgres caching patterns — the two-tier read-through cache, key and TTL design, and refresh strategy for geocoding results.
• Designing spatial indexes for geocoded data — choosing and building GiST, BRIN, and geohash indexes so proximity queries stay fast at scale.
• Deduplicating addresses by fuzzy canonical key — the two-phase canonical-key-then-fuzzy method that collapses variant spellings before they cost you.
• Optimizing batch geocoding throughput — parallelizing the cache-miss path across cores and async I/O to maximize records per minute.
• Multi-API routing & fallback chains — the upstream routing engine whose results this layer caches, deduplicates, and indexes.
• Core address parsing & standardization — the parsing rules that produce the clean components a canonical key is built from.