Deduplicating and Caching Geocoding Results

Geocoding APIs are metered — Nominatim’s usage policy caps a single host at one request per second, and hosted providers bill per lookup — while logistics address feeds are rarely as unique as they look. A daily manifest of a few thousand delivery stops routinely resolves to a few hundred distinct locations once repeat customers, recurring depots, and inconsistent formatting are collapsed. This page covers that collapsing step within Batch Geocoding Pipelines for Logistics, and sits alongside the request-dispatch concerns handled in the broader Routing API Automation & Fleet Integration workflow.

The technique is a normalize-hash-cache pattern applied in front of any geocoder client. It pairs directly with async batch geocoding with Nominatim: that page’s semaphore-throttled aiohttp workers should only ever see the residual unique misses this cache produces, not the full raw batch. Skipping deduplication before dispatch means paying the one-request-per-second tax on addresses you already resolved yesterday.

When to Use This Approach

Caching pays off in proportion to how much your address data repeats and how constrained your geocoding throughput is.

Add a cache when:

• The same customer, store, or depot addresses recur across multiple daily batches (recurring delivery routes, subscription orders, retail chains)
• You are rate-limited to roughly one request per second against a self-hosted Nominatim instance and cannot simply add more geocoding capacity
• Batch jobs are re-run during development or after a partial failure, and re-geocoding the entire feed each time is wasteful
• Geocoding cost is metered per lookup against a commercial provider and duplicate lookups translate directly into avoidable spend

Skip or minimize caching when:

• The address set is genuinely one-off (a single bulk import of addresses that will never recur)
• Addresses are volatile enough that stale coordinates create real routing risk faster than the TTL can be tuned to handle
• You already deduplicate upstream in the source system and the feed arrives pre-collapsed

Implementation

The block below is self-contained: normalization, canonical key derivation, a TTL-aware SQLite cache, and a batch resolver that dedupes before calling out to a geocoder function. It does not repeat the async Nominatim client itself — plug in geocode_fn from the async batch geocoding page or any synchronous wrapper around it.

# requires: sqlite3, hashlib, re, dataclasses, time (all stdlib)
# Python 3.9+

import hashlib
import re
import sqlite3
import time
from dataclasses import dataclass
from typing import Optional

_ABBREVIATIONS = {
    r"\bst\b": "street", r"\bave\b": "avenue", r"\brd\b": "road",
    r"\bblvd\b": "boulevard", r"\bapt\b": "apartment", r"\bste\b": "suite",
    r"\bn\b": "north", r"\bs\b": "south", r"\be\b": "east", r"\bw\b": "west",
}


def normalize_address(raw: str) -> str:
    """Lowercase, strip punctuation, collapse whitespace, expand common abbreviations."""
    text = re.sub(r"[.,#]", " ", raw.strip().lower())
    text = re.sub(r"\s+", " ", text).strip()
    for pattern, replacement in _ABBREVIATIONS.items():
        text = re.sub(pattern, replacement, text)
    return text


def canonical_key(raw_address: str, country_hint: str = "") -> str:
    """Deterministic cache key: normalized address plus optional country, SHA-1 hashed."""
    basis = f"{normalize_address(raw_address)}|{country_hint.strip().lower()}"
    return hashlib.sha1(basis.encode("utf-8")).hexdigest()


@dataclass
class GeocodeResult:
    lat: float
    lon: float
    display_name: str
    confidence: float


class GeocodeCache:
    """SQLite-backed cache keyed on canonical_key(), with TTL-based staleness."""

    def __init__(self, db_path: str = "geocode_cache.sqlite3", ttl_seconds: int = 60 * 60 * 24 * 90):
        self.ttl_seconds = ttl_seconds
        self.conn = sqlite3.connect(db_path)
        self.conn.execute(
            """CREATE TABLE IF NOT EXISTS geocode_cache (
                cache_key TEXT PRIMARY KEY, raw_address TEXT NOT NULL,
                lat REAL NOT NULL, lon REAL NOT NULL,
                display_name TEXT, confidence REAL, fetched_at REAL NOT NULL)"""
        )
        self.conn.commit()

    def get(self, cache_key: str) -> Optional[GeocodeResult]:
        row = self.conn.execute(
            "SELECT lat, lon, display_name, confidence, fetched_at "
            "FROM geocode_cache WHERE cache_key = ?", (cache_key,),
        ).fetchone()
        if row is None:
            return None
        lat, lon, display_name, confidence, fetched_at = row
        if time.time() - fetched_at > self.ttl_seconds:
            return None  # stale entry — caller treats this as a miss
        return GeocodeResult(lat, lon, display_name, confidence)

    def put(self, cache_key: str, raw_address: str, result: GeocodeResult) -> None:
        self.conn.execute(
            "INSERT OR REPLACE INTO geocode_cache "
            "(cache_key, raw_address, lat, lon, display_name, confidence, fetched_at) "
            "VALUES (?, ?, ?, ?, ?, ?, ?)",
            (cache_key, raw_address, result.lat, result.lon,
             result.display_name, result.confidence, time.time()),
        )
        self.conn.commit()


def resolve_batch(addresses: list[str], cache: GeocodeCache, geocode_fn) -> dict[str, GeocodeResult]:
    """Group addresses by canonical key, serve hits from cache, geocode only unique misses."""
    key_to_addresses: dict[str, list[str]] = {}
    for addr in addresses:
        key_to_addresses.setdefault(canonical_key(addr), []).append(addr)

    results: dict[str, GeocodeResult] = {}
    misses: dict[str, str] = {}
    for key, group in key_to_addresses.items():
        cached = cache.get(key)
        if cached is not None:
            for addr in group:
                results[addr] = cached
        else:
            misses[key] = group[0]  # geocode using the first raw variant seen

    for key, sample_addr in misses.items():
        result = geocode_fn(sample_addr)
        cache.put(key, sample_addr, result)
        for addr in key_to_addresses[key]:
            results[addr] = result

    return results

resolve_batch() groups first, checks the cache once per unique key, and only invokes geocode_fn for the residual misses — a batch of 5,000 raw addresses with a 70% duplicate rate results in roughly 1,500 actual geocoding calls instead of 5,000.

Key Parameters and Tuning

WAL mode for concurrent readers. If a webhook process reads the cache while a nightly batch job writes to it, enable SQLite’s write-ahead log with conn.execute("PRAGMA journal_mode=WAL") immediately after connecting. This lets reads proceed without blocking on the batch writer.

Integration Points

Feeding the async geocoding client. resolve_batch()'s geocode_fn argument is the seam where this cache meets the dispatch layer described in async batch geocoding with Nominatim. In practice, wrap the async semaphore-throttled client in a small synchronous adapter (or run resolve_batch inside an event loop with asyncio.gather over the miss list) so only unique addresses ever touch the rate-limited path.

Feeding route-matrix computation. Once addresses resolve to coordinates, those (lat, lon) pairs become the origin and destination inputs for Async Route Matrix Automation — specifically the request fan-out described in parallel distance matrix requests with aiohttp. Cache the geocode step and the matrix step separately; they have different staleness tolerances (addresses rarely move, but traffic-aware travel times change constantly).

Cache warming. For fixed infrastructure — depots, distribution centers, retail store fronts — pre-populate the cache once from a curated address-to-coordinate table rather than relying on organic cache hits. This guarantees your highest-traffic keys never pay the geocoder latency, even on a cold cache after a deploy.

Confidence-gated writes. Only call cache.put() when GeocodeResult.confidence clears a minimum threshold (commonly 0.5–0.7 depending on the geocoder). Caching a low-confidence match locks in a bad coordinate for the full TTL; better to re-attempt on the next run than to persist an unreliable result.

Validation Checklist

1. Normalization idempotence. normalize_address(normalize_address(x)) == normalize_address(x) must hold for every address in a sample batch — a non-idempotent normalizer indicates a regex ordering bug that will fragment otherwise-identical addresses across two keys.
2. Key collision correctness. Manually verify that a handful of known-duplicate address pairs (same store, different formatting) produce identical canonical_key() output.
3. TTL expiry behavior. Insert a row with a fetched_at timestamp older than ttl_seconds, call cache.get(), and confirm it returns None rather than stale coordinates.
4. Dedup ratio monitoring. Log len(key_to_addresses) / len(addresses) per batch run. A ratio near 1.0 on a feed you expect to be repetitive suggests normalization is under-collapsing; alert on the metric rather than discovering it in a cost report.
5. Concurrent-write safety. Under SQLite, run a simulated concurrent read-during-write test with WAL mode enabled and confirm no database is locked errors under your expected worker count; move to Redis if they appear.
6. Confidence-threshold enforcement. Assert that no cached row has confidence below your configured minimum by scanning the table after a batch run.

Related

• Batch Geocoding Pipelines for Logistics — parent guide covering rate limiting, retries, and confidence filtering around the geocoder itself
• Async Batch Geocoding with Nominatim — the semaphore-throttled aiohttp dispatch layer this cache should sit in front of
• Async Route Matrix Automation — where deduplicated, cached coordinates feed into origin-destination matrix computation
• Parallel Distance Matrix Requests with aiohttp — the downstream request pattern that consumes resolved coordinates at scale