How to Cache Geocoding Results So a Rerun Costs Nothing
On this page
Problem statement
The customer file is refreshed every month, and almost all of it is the same as last time: of the distinct addresses in the 29 September 2026 extract of Camden's food hygiene register, 99.3% were already in the extract of 16 September. 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 rerun costs nothing: in the examples below, geocoding the 85 food businesses in Ledbury through Nominatim takes minutes at the one request a second its policy allows, and the second run makes no requests at all.
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()What you get — from 1. Choose the key deliberately
raw string 1,446 lookups for 1,784 rows (18.9% answered from the cache)
normalised 1,444 lookups for 1,784 rows (19.1% answered from the cache)
postcode + first line 1,178 lookups for 1,784 rows (34.0% answered from the cache)
Choose the key, store raw responses, cache failures
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.
- The normalised string — case-folded, punctuation and whitespace collapsed.
- 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.
On Herefordshire's food hygiene register the key decides how many lookups a full run needs:
def structured(row):
return f"{normalise(row['PostCode'])}|{normalise(row['AddressLine1'])}"
for label, keys in (("raw string", est["address"]),
("normalised", est["address"].map(normalise)),
("postcode + first line", est.apply(structured, axis=1))):
print(f"{label:22} {keys.nunique():6,} lookups for {len(est):,} rows "
f"({1 - keys.nunique() / len(est):.1%} answered from the cache)")raw string 1,446 lookups for 1,784 rows (18.9% answered from the cache)
normalised 1,444 lookups for 1,784 rows (19.1% answered from the cache)
postcode + first line 1,178 lookups for 1,784 rows (34.0% answered from the cache)
Every one of those repeats is real: market stalls, units in one shopping centre, a hotel and its restaurant. Normalising adds little on a register that one council types consistently; its value shows when files from different sources meet — on four councils' registers matched against OpenStreetMap street names, case folding alone lifted matches from 32.8% to 44.6%. The structured key catches more still, because it ignores the second and third address lines that one clerk fills and another leaves empty.
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 cost 480 bytes per answered row in the Ledbury cache below, 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;The last example on this page runs that query for real, against Photon.
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.
A cached geocoder for any provider
The data used on this page
The Food Standards Agency's food hygiene register for Herefordshire, with each business's address lines joined into one address column and the FSA's own coordinates kept as fsa_lat/fsa_lon. The live lookups go to the public Nominatim service at its limit of one request a second, so they are confined to the 85 businesses in Ledbury, and to Photon for the provider comparison. The normaliser and the Nominatim client are the ones from batch geocoding thousands of addresses.
import json
import os
import re
import time
import pandas as pd
import requests
if not os.path.exists("herefordshire.json"):
reply = requests.get("https://api.ratings.food.gov.uk/Establishments",
params={"localAuthorityId": 383, "pageSize": 5000},
headers={"x-api-version": "2"}, timeout=300)
reply.raise_for_status()
json.dump(reply.json()["establishments"], open("herefordshire.json", "w"))
est = pd.DataFrame(json.load(open("herefordshire.json")))
lines = ["AddressLine1", "AddressLine2", "AddressLine3", "AddressLine4", "PostCode"]
est["address"] = est[lines].apply(lambda row: ", ".join(x for x in row if x), axis=1)
est["fsa_lat"] = est["geocode"].map(lambda g: float(g["latitude"]) if g and g.get("latitude") else None)
est["fsa_lon"] = est["geocode"].map(lambda g: float(g["longitude"]) if g and g.get("longitude") else None)
ledbury = est[est["AddressLine4"].eq("Ledbury")]
POSTCODE = re.compile(r"\b([a-z]{1,2}\d[a-z\d]?) ?(\d[a-z]{2})\b")
def normalise(address):
text = re.sub(r"[^\w\s]", " ", address.lower())
return re.sub(r"\s+", " ", text).strip()
class Nominatim:
name, min_interval = "nominatim", 1.1
def __init__(self, agent):
self.session = requests.Session()
self.session.headers["User-Agent"] = agent
self.last, self.requests = 0.0, 0
def _get(self, query):
time.sleep(max(0, self.min_interval - (time.monotonic() - self.last)))
self.last = time.monotonic(); self.requests += 1
reply = self.session.get("https://nominatim.openstreetmap.org/search", timeout=60,
params={"q": query, "format": "jsonv2", "limit": 1, "countrycodes": "gb"})
reply.raise_for_status()
return reply.json()
def search(self, key):
hits = self._get(key)
if hits:
return hits
postcode = POSTCODE.search(key)
return self._get(" ".join(postcode.groups())) if postcode else []
for f in ("geocode.sqlite",): # every run of this page starts cold
if os.path.exists(f):
os.remove(f)
print(f"{len(est):,} businesses in Herefordshire, {len(ledbury)} in Ledbury "
f"({ledbury['address'].map(normalise).nunique()} distinct addresses)")1,784 businesses in Herefordshire, 85 in Ledbury (85 distinct addresses)
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")cache = Cache("geocode.sqlite")
geocoder = CachedGeocoder(Nominatim("spatialworkflow.io guide (geocode cache example)"), cache, normalise)
for run in (1, 2):
geocoder.hits = geocoder.misses = geocoder.errors = 0
before, started = geocoder.provider.requests, time.perf_counter()
for address in ledbury["address"]:
geocoder.geocode(address)
print(f"run {run}: {time.perf_counter() - started:6.1f} s, "
f"{geocoder.provider.requests - before} requests to Nominatim")
geocoder.report()run 1: 132.1 s, 121 requests to Nominatim
cache: 0 hits, 85 misses (0.0% hit rate), 0 errors
run 2: 0.0 s, 0 requests to Nominatim
cache: 85 hits, 0 misses (100.0% hit rate), 0 errors
Ledbury's 85 businesses have 85 different addresses, so the first run is all misses — 121 requests, because 36 addresses found nothing whole and were retried on their postcode. The second run makes no requests and takes no measurable time. That is the whole case for the cache: a rerun without it would pay the 132 seconds again.
Example 1b — a second provider in the same table
The provider comparison from step 4, run against Photon for the same Ledbury addresses:
class Photon:
name = "photon"
def search(self, key):
time.sleep(1)
reply = requests.get("https://photon.komoot.io/api/", params={"q": key, "limit": 1},
headers={"User-Agent": "spatialworkflow.io guide"}, timeout=60)
reply.raise_for_status()
return reply.json()["features"]
photon = CachedGeocoder(Photon(), cache, normalise)
for address in ledbury["address"]:
photon.geocode(address)
disagree = cache.db.execute("""
select a.key, a.status as nominatim, b.status as photon
from geocode a join geocode b using (key)
where a.provider = 'nominatim' and b.provider = 'photon'
and a.status <> b.status""").fetchall()
both = cache.db.execute("select count(*) from geocode a join geocode b using (key) "
"where a.provider = 'nominatim' and b.provider = 'photon'").fetchone()[0]
print(f"{both} addresses answered by both; they disagree on {len(disagree)}")
for row in disagree[:3]:
print(" ", row)85 addresses answered by both; they disagree on 12
('units b and d 5a to 9 homend mews the homend ledbury hr8 1bn', 'ok', 'no_match')
('new mills industrial estate ledbury ind estate ledbury hr8 2ss', 'ok', 'no_match')
('fair tree farm ledbury hr8 2jl', 'ok', 'no_match')
Nominatim returned something for every address, thanks to the postcode fallback; Photon found nothing for 12. Because both answers sit in one table under one key, that comparison is a query, and choosing a provider for the residue is a decision you can make from data you already paid for.
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 pd.isna(row.get(lat_col)): # NaN is truthy: `not row.get(...)` lets it through
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")rest = est[~est.index.isin(ledbury.index)]
warm_from_previous(cache, rest, "nominatim", normalise, lat_col="fsa_lat", lon_col="fsa_lon",
precision_col="BusinessType")
nan_rows = cache.db.execute("select count(*) from geocode where response like '%\"lat\": \"nan\"%'").fetchone()[0]
print(f"seeded rows whose coordinates are the string 'nan': {nan_rows:,} "
f"(the file has {rest['fsa_lat'].isna().sum():,} businesses without coordinates)")seeded 1,229 entries from the previous run
seeded rows whose coordinates are the string 'nan': 0 (the file has 411 businesses without coordinates)
The pd.isna test is a fix. The first version skipped rows with if not row.get(lat_col), which is how a missing value looks in a dict — but pandas gives a missing coordinate as NaN, and NaN is truthy. It seeded 130 entries whose latitude was the string "nan", each one a permanent, confident-looking wrong answer that no later run would ever re-fetch.
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")cache_report(cache)
size = cache.db.execute("select avg(length(response)) from geocode "
"where status = 'ok' and response not like '%seeded%'").fetchone()[0]
print(f"\nraw Nominatim responses average {size:,.0f} bytes a row")provider status rows oldest newest
nominatim ok 1,314 2026-09-29 2026-09-29
photon ok 73 2026-09-29 2026-09-29
photon no_match 12 2026-09-29 2026-09-29
0 entries older than 180 days will be re-fetched on next use
raw Nominatim responses average 480 bytes a row
The normaliser is part of the cache contract
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 100%, as Example 1's second run did. A refresh will not, because new addresses arrive and stale entries expire. Between Camden's extracts of 16 and 29 September 2026, 99.3% of the distinct addresses carried over; a monthly cycle on a register like that should hit in the high nineties.
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.
UTC timestamps, replacements and commits
- Store timestamps in UTC, ISO-8601. Local timestamps in a cache make expiry arithmetic wrong twice a year.
insert or replacekeeps one row per key. If you want history, addfetched_atto 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.
Internal links
- How to batch geocode thousands of addresses in Python — the runner that sits on this cache
- How to geocode with Nominatim from Python without being blocked — the provider behind it
- How to parse and normalise addresses in Python — the key function
- How to cache downloaded GIS data in Python — the same discipline for files
- How to cache pipeline steps so unchanged work is skipped — caching at the step level
- Idempotency explained for GIS pipelines — why a rerun should be free
- Choosing a geocoder: coverage, licence and cost compared — where the TTL obligation comes from
- Reproducible GIS workflows in Python — the cache as a reproducibility device
FAQ
What should the cache key be?
The normalised address at least, and structured components if you have them. On Herefordshire's 1,784 food businesses a raw-string key needed 1,446 lookups, a normalised one 1,444, and postcode plus first address line 1,178. Normalisation earns more when files from different sources meet.
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.