⚙️ How-To Geocoding & addresses Pipelines & automation Geocoding & Addresses · step 9 of 20 Updated

How to Cache Geocoding Results So a Rerun Costs Nothing

Problem statement

The customer file is refreshed every month. Ninety-five percent of the addresses are the same as last month. Without a cache, every run pays again — in money against a commercial API, in hours against a rate-limited one, and in goodwill against a free one.

Caching geocodes is not the same as caching an HTTP GET. The requirements are specific:

  • The key must be the normalised address, so that three spellings of one address are one lookup.
  • Failures must be cached too, or the permanently unmatchable rows are re-fetched forever.
  • The raw response must be kept, so that improving your interpretation is a re-parse rather than a re-fetch.
  • Entries must be dateable and expirable, because reference data changes and some licences forbid indefinite storage.

Get those four right and a second run of a 40,000-row file takes seconds instead of hours.

Quick answer

A single SQLite table, keyed on (normalised query, provider), holding the raw response:

import json, sqlite3
from datetime import datetime, timezone, timedelta

SCHEMA = """
create table if not exists geocode (
    key         text not null,        -- normalised query
    provider    text not null,        -- which service answered
    response    text,                 -- raw JSON, untouched
    status      text not null,        -- ok | no_match | error
    fetched_at  text not null,        -- ISO-8601 UTC
    primary key (key, provider)
);
create index if not exists geocode_fetched on geocode (fetched_at);
"""


class Cache:
    def __init__(self, path="geocode.sqlite", ttl_days=180):
        self.db = sqlite3.connect(path)
        self.db.executescript(SCHEMA)
        self.ttl = timedelta(days=ttl_days)

    def get(self, key, provider):
        row = self.db.execute(
            "select response, status, fetched_at from geocode "
            "where key = ? and provider = ?", (key, provider)).fetchone()
        if not row:
            return None
        age = datetime.now(timezone.utc) - datetime.fromisoformat(row[2])
        if age > self.ttl:
            return None                       # stale: treat as a miss
        return {"response": json.loads(row[0]) if row[0] else None,
                "status": row[1], "fetched_at": row[2]}

    def put(self, key, provider, response, status):
        self.db.execute(
            "insert or replace into geocode values (?, ?, ?, ?, ?)",
            (key, provider, json.dumps(response) if response is not None else None,
             status, datetime.now(timezone.utc).isoformat(timespec="seconds")))
        self.db.commit()
Cache table with key, provider, response, status and fetched_at columns.
Storing the raw response makes a change of interpretation a re-parse, not a re-fetch.

Step-by-step solution

1. Choose the key deliberately

The cache key decides the hit rate. Three candidates, in increasing order of usefulness:

  • The raw string. Hits only on byte-identical input. In a controlled test, verbatim matching found 29.1% of realistically varied queries.
  • The normalised string — case-folded, accents stripped, punctuation and whitespace collapsed. The same test found 85.5%.
  • The structured components, serialised in a canonical order. Best of all where you have them, because it also caches across providers that take structured input.

Whatever you choose, put the normalisation in one function and version it. If the normaliser changes, the keys change, and every existing entry silently becomes unreachable — which is a cache miss storm, not an error.

2. Cache the raw response, not your interpretation

Your parsing of the response will change: a new precision mapping, a stricter filter, an extra field somebody wants. If the cache holds only (lat, lon), each of those changes is a re-fetch.

Storing the provider's JSON verbatim costs a few hundred bytes per row and makes re-interpretation free. It is also the only way to answer "why is this point here?" months later.

3. Cache negative results, with a shorter life

A no-match today is a no-match tomorrow, unless the reference data improved. Cache it — with a status of no_match rather than an absent row — so the batch runner skips it.

Give negatives a shorter TTL than positives. Sixty days for a miss and a year for a hit is a reasonable starting point: reference data gains addresses far more often than it moves existing ones.

Errors are different again. A timeout or a 502 is not information about the address, so store it as error and treat it as a miss on the next run.

4. Key by provider, and keep both

Multi-provider pipelines are normal: an open geocoder for the bulk and a commercial one for the residue. Making provider part of the primary key means the same address can hold an answer from each, and the row's provenance is not lost when you switch.

It also makes provider comparison a SQL query rather than a re-run:

select a.key, a.status as nominatim, b.status as commercial
from geocode a join geocode b using (key)
where a.provider = 'nominatim' and b.provider = 'commercial'
  and a.status <> b.status;

5. Expire on read, not with a cron job

Checking the age when the row is read keeps the cache honest with no maintenance. A row older than the TTL is treated as a miss and re-fetched, and the fresh answer overwrites it.

A periodic vacuum is still worth having, but only to reclaim disk. Nothing depends on it running.

6. Respect the licence

Some providers forbid storing coordinates beyond a short cache window; others require attribution or restrict redistribution. The TTL is where that obligation lives in code — set it from the provider's terms and put the clause reference in a comment so the next person does not "optimise" it away.

Bar chart of cache hit rate by key choice, from raw string to structured components.
The normaliser is part of the cache contract: change it and every existing key becomes unreachable.

Code examples

Example 1 — a cached geocoder that wraps any provider 

class CachedGeocoder:
    """Cache in front, provider behind. The provider never sees a repeat."""

    def __init__(self, provider, cache, normalise, ttl_ok_days=365, ttl_miss_days=60):
        self.provider, self.cache, self.normalise = provider, cache, normalise
        self.ttl = {"ok": ttl_ok_days, "no_match": ttl_miss_days, "error": 0}
        self.hits = self.misses = self.errors = 0

    def geocode(self, query):
        key = self.normalise(query)
        cached = self.cache.get(key, self.provider.name)
        if cached and self._fresh(cached):
            self.hits += 1
            return cached["response"]

        self.misses += 1
        try:
            response = self.provider.search(key)
            status = "ok" if response else "no_match"
        except Exception:
            self.errors += 1
            self.cache.put(key, self.provider.name, None, "error")
            raise
        self.cache.put(key, self.provider.name, response, status)
        return response

    def _fresh(self, entry):
        from datetime import datetime, timezone, timedelta
        days = self.ttl.get(entry["status"], 0)
        if days == 0:
            return False
        age = datetime.now(timezone.utc) - datetime.fromisoformat(entry["fetched_at"])
        return age < timedelta(days=days)

    def report(self):
        total = self.hits + self.misses
        rate = 100 * self.hits / total if total else 0
        print(f"cache: {self.hits:,} hits, {self.misses:,} misses "
              f"({rate:.1f}% hit rate), {self.errors:,} errors")

The hit-rate line is what justifies the cache to whoever is paying for the API. Print it at the end of every run.

Example 2 — warming the cache from an existing geocoded file 

def warm_from_previous(cache, df, provider, normalise,
                       address_col="address", lat_col="lat", lon_col="lon",
                       precision_col="precision"):
    """Seed the cache from work that was already paid for."""
    seeded = 0
    for _, row in df.iterrows():
        if not row.get(lat_col):
            continue
        key = normalise(row[address_col])
        if cache.get(key, provider):
            continue
        cache.put(key, provider,
                  [{"lat": str(row[lat_col]), "lon": str(row[lon_col]),
                    "addresstype": row.get(precision_col), "seeded": True}],
                  "ok")
        seeded += 1
    print(f"seeded {seeded:,} entries from the previous run")

The "seeded": True marker matters. Seeded entries did not come from the provider, so a later audit can tell them apart from real responses — and re-fetch them if the provenance turns out to matter.

Example 3 — a cache health report 

def cache_report(cache):
    q = """
    select provider, status, count(*) n,
           min(fetched_at) oldest, max(fetched_at) newest
    from geocode group by 1, 2 order by 1, 3 desc
    """
    print(f"{'provider':14} {'status':10} {'rows':>8}  {'oldest':10} {'newest':10}")
    for provider, status, n, oldest, newest in cache.db.execute(q):
        print(f"{provider:14} {status:10} {n:8,}  {oldest[:10]} {newest[:10]}")

    stale = cache.db.execute(
        "select count(*) from geocode where fetched_at < date('now', '-180 day')"
    ).fetchone()[0]
    print(f"\n{stale:,} entries older than 180 days will be re-fetched on next use")

Explanation

Why the normaliser is part of the cache contract

The cache key is the output of the normaliser, so the two are the same object as far as correctness is concerned. Change the normaliser — add an abbreviation, stop stripping a character — and every key computed by the old version becomes unreachable.

Nothing breaks visibly. The next run simply misses on everything, re-fetches, and writes a second copy under the new keys. On a paid API that is an invoice; on a rate-limited one it is a day.

Two defences: keep the normaliser in one function that both the cache and the batch runner import, and store a normaliser version alongside each row so a change is detectable rather than silent.

Why a hit rate below 100% on a rerun is normal and fine

A re-run of an unchanged file should hit close to 100%. A monthly refresh will not, because new addresses arrive and stale entries expire. A hit rate of 90–97% on a monthly cycle is healthy.

A sudden drop to near zero has three usual causes, in order of likelihood: the normaliser changed, the provider name changed, or the TTL was shortened. All three are visible in the cache report.

Why SQLite rather than a dictionary or a JSON file

A dictionary dies with the process. A JSON file has to be fully rewritten on every change, which at one write per second is both slow and a data-loss window.

SQLite gives durable single-row writes, an index, concurrent readers, and — usefully — the ability to answer questions about the cache in SQL: how many misses, which provider disagrees, what has gone stale. It is a file, so it copies and versions like one.

Why cached geocodes make analyses reproducible

Reference data changes. Re-geocoding the same address a year later can return a different coordinate, so an analysis that geocodes at run time is not reproducible even if the input file is byte-identical.

A cache pins the answers. Ship the cache with the project — or at least record its checksum — and the map you produced in March can be reproduced in November. That is a stronger reason to keep one than the cost saving.

Grid of cache TTLs for ok, no_match and error statuses with the reasoning for each.
Licence terms sometimes cap the TTL — that is where the obligation lives in code.

Edge cases or notes

  • Store timestamps in UTC, ISO-8601. Local timestamps in a cache make expiry arithmetic wrong twice a year.
  • insert or replace keeps one row per key. If you want history, add fetched_at to the primary key and query the latest.
  • Commit per write when the loop is network-bound. The commit is free relative to the request and makes kills safe.
  • Cache the query as sent, including the country code and any structured fields — a query narrowed to one country is a different question.
  • Do not cache across normaliser versions without a version column.
  • Check licence terms for storage limits. Some providers cap the cache lifetime contractually; encode it in the TTL.
  • Back the file up. A geocode cache is accumulated purchased data.
  • Keep it out of git unless it is small — but do version the schema.

FAQ

What should the cache key be?

The normalised address, not the raw string. Verbatim keys matched 29.1% of realistically varied queries in a controlled test; normalised keys matched 85.5%.

Should I cache addresses that returned no match?

Yes, with a shorter TTL than successful lookups — sixty days is a reasonable start. They will fail again, and they are the slowest rows to fail.

How long should cached geocodes live?

A year for successes, a couple of months for misses, and zero for errors — unless the provider's terms impose a shorter limit, in which case that limit is the TTL.

Why SQLite instead of a JSON file?

Durable per-row writes, an index, and the ability to query the cache. A JSON file must be rewritten in full on every change, which is both slow and a data-loss window.

What happens if I change my normalisation function?

Every existing key becomes unreachable and the next run re-fetches everything. Store a normaliser version with each row so the change is detectable rather than silent.

Is caching geocodes allowed?

Usually, but not always: some providers restrict how long you may store results. Encode that limit in the TTL and cite the clause in a comment.