How to Build an Offline Geocoder from Open Address Data
Problem statement
Above a certain volume, calling a geocoding API stops making sense. Ten million lookups at one per second is four months. At a commercial rate it is a budget line. And in either case the pipeline now depends on a network service being up, unchanged and willing.
An offline geocoder removes all three problems. You download a gazetteer once, build an index, and every lookup is a local dictionary hit — measured below at 0.68 microseconds, against 134 milliseconds for a fuzzy comparison and roughly a second per request against a public API.
The trade is coverage and precision. An open gazetteer of populated places will tell you where a town is; it will not tell you where number 14 is. Knowing which of those your work needs is the whole decision.
Quick answer
Build a normalised index once, query it in memory:
import duckdb, re, unicodedata
def normalise(s: str) -> str:
s = unicodedata.normalize("NFKD", s)
s = "".join(c for c in s if not unicodedata.combining(c))
return re.sub(r"\s+", " ", re.sub(r"[^\w\s]", " ", s.casefold())).strip()
con = duckdb.connect()
rows = con.execute("""
select geonameid, name, country, lat, lon, coalesce(population, 0) as pop
from read_parquet('geonames.parquet')
where fclass = 'P' -- populated places only
""").fetchall()
index = {}
for gid, name, country, lat, lon, pop in rows:
index.setdefault(normalise(name), []).append((gid, name, country, lat, lon, pop))
def geocode(place, country=None):
hits = index.get(normalise(place), [])
if country:
hits = [h for h in hits if h[2] == country]
return max(hits, key=lambda h: h[5]) if hits else NoneMeasured on the full GeoNames populated-place set: 5,226,942 records, index built in 6.1 seconds, 2,928,860 distinct keys, and lookups at 1.48 million per second.
Step-by-step solution
1. Choose the reference data for the precision you need
Two families of open data, and they answer different questions:
- Gazetteers — GeoNames, Who's on First, Natural Earth. Places, not addresses: towns, villages, districts, features. Global, small, permissively licensed. Precision: locality.
- Address point files — OpenAddresses, national registers (Ordnance Survey, BAG, BANO, NAD). Individual buildings. Precision: rooftop. Coverage is national and patchy globally, and licences vary from fully open to restricted.
For "which town is this record in", a gazetteer is complete and free. For "where is number 14", nothing but an address file will do, and the honest answer in many countries is that no open one exists.
2. Pick the right store for the access pattern
This is where most offline geocoders are built wrong, and the numbers are stark. The same 5.2 million records, the same lookup, three stores:
store build size per lookup lookups/s
python dict 6.1 s in RAM 0.68 µs 1,476,388
SQLite + index 9.5 s 415 MB 58 µs 17,217
DuckDB + index 13.7 s 812 MB 13.4 ms 75
DuckDB is an analytics engine: superb at aggregating millions of rows, and 230 times slower than SQLite at fetching one. If your geocoder is a loop of single lookups, use a dictionary or SQLite. Use DuckDB to build the index and to answer set-shaped questions — never as the point-lookup store.
3. Normalise the keys, once, on both sides
The index key and the query must go through the same function. Measured on a matching experiment, verbatim keys matched 29.1% of realistically varied queries and normalised keys 85.5%.
Store the normaliser in one module and import it in both places. If it changes, the index has to be rebuilt — treat it as part of the index's version.
4. Add the alternate names, and know what they cost
Gazetteer name fields are usually the English or official form. Users type endonyms. Probing ten European capitals and cities by their local names — warszawa, kobenhavn, munchen, wien, praha, lisboa, firenze, moskva, roma, koln — the name-only index found 7 of 10. Adding GeoNames' alternate names found 9 of 10.
The cost: the index grew from 2,928,860 keys to 5,178,402, and build time from 6.1 to 33.8 seconds. That is a good trade for user-facing search and an unnecessary one for a pipeline that only ever sees official names.
5. Rank the candidates, because ambiguity is the norm
A gazetteer lookup returns a list. San Antonio names 2,382 places in 25 countries; Springfield names 68 in the United States alone.
Ranking rules that work, in order of strength:
- Filter by country if you know it. This alone resolves the majority of collisions — 132,528 place names occur in more than one country.
- Filter by region if the record carries one.
- Rank by population, and record that you did. It is a documented guess, not a fact.
- Rank by distance to a known nearby point, if the record has any spatial context.
Always return the candidate count alongside the winner. A lookup that chose between fourteen equal candidates should not look like one that found a unique answer.
6. Handle the misses your way, offline
Without a network fallback, the residue is yours to solve: a fuzzy pass over a blocked candidate set, a synonym table for the abbreviations your data uses, or a manual mapping file for the fifty place names your organisation spells its own way.
The manual mapping file is underrated. Fifty hand-written lines usually fix more rows than a week of algorithm work.
Code examples
Example 1 — building the index from the raw dump
import duckdb, os, time
def build_index(parquet_path, feature_class="P", include_alternates=False):
"""Returns {normalised_name: [(id, name, country, lat, lon, population), ...]}."""
con = duckdb.connect()
con.execute("set enable_progress_bar = false")
started = time.perf_counter()
rows = con.execute(f"""
select geonameid, name, alternatenames, country, lat, lon,
coalesce(population, 0) as pop
from read_parquet('{parquet_path}')
where fclass = '{feature_class}'
""").fetchall()
index = {}
for gid, name, alternates, country, lat, lon, pop in rows:
record = (gid, name, country, lat, lon, pop)
index.setdefault(normalise(name), []).append(record)
if include_alternates and alternates:
for alt in alternates.split(",")[:12]: # the tail is transliteration noise
key = normalise(alt)
if key:
index.setdefault(key, []).append(record)
print(f"{len(rows):,} records -> {len(index):,} keys "
f"in {time.perf_counter() - started:.1f}s")
return index5,226,942 records -> 2,928,860 keys in 6.1s # names only
5,226,942 records -> 5,178,402 keys in 33.8s # with alternate names
The [:12] slice is a judgement call worth making explicit: GeoNames alternate-name lists run to hundreds of entries for major cities, most of them scripts and transliterations your data will never contain, and each one costs a key.
Example 2 — a SQLite-backed geocoder for when memory is scarce
import sqlite3
def build_sqlite(rows, path="gazetteer.sqlite"):
db = sqlite3.connect(path)
db.execute("pragma journal_mode = off")
db.execute("pragma synchronous = off")
db.execute("""create table gaz (
id integer primary key, k text, name text, country text,
lat real, lon real, pop integer)""")
db.executemany("insert into gaz values (?,?,?,?,?,?,?)",
[(r[0], normalise(r[1]), r[1], r[2], r[3], r[4], r[5]) for r in rows])
db.execute("create index gaz_k on gaz (k)")
db.commit()
return db
class SqliteGeocoder:
def __init__(self, path="gazetteer.sqlite"):
self.db = sqlite3.connect(path, check_same_thread=False)
self.cur = self.db.cursor()
def geocode(self, place, country=None):
sql = "select name, country, lat, lon, pop from gaz where k = ?"
args = [normalise(place)]
if country:
sql += " and country = ?"
args.append(country)
hits = self.cur.execute(sql + " order by pop desc", args).fetchall()
if not hits:
return None
return {"name": hits[0][0], "country": hits[0][1],
"lat": hits[0][2], "lon": hits[0][3],
"candidates": len(hits),
"ambiguous": len(hits) > 1 and hits[0][4] < 10 * max(1, hits[1][4])}415 MB on disk, 17,217 lookups per second, and no import step at start-up. That is the right shape for a long-running service; the dictionary is the right shape for a batch job that runs once.
Example 3 — the fuzzy fallback, blocked
import difflib
from collections import defaultdict
def build_blocks(index, prefix=3):
blocks = defaultdict(list)
for key in index:
blocks[key[:prefix]].append(key)
return blocks
def geocode_fuzzy(query, index, blocks, cutoff=0.85, prefix=3):
key = normalise(query)
if key in index:
return index[key], "exact", 1.0
candidates = blocks.get(key[:prefix], [])
if not candidates:
return None, "none", 0.0
best = difflib.get_close_matches(key, candidates, n=1, cutoff=cutoff)
if not best:
return None, "none", 0.0
score = difflib.SequenceMatcher(None, key, best[0]).ratio()
return index[best[0]], "fuzzy", round(score, 3)Blocking on the first three characters keeps the comparison set to hundreds instead of millions. It also means a typo in the first three characters is unrecoverable — which is a known limitation to write down, not a bug to hide.
Explanation
Why the analytics engine is the wrong point-lookup store
DuckDB reads columns in vectors and is built to scan millions of rows efficiently. A single-row lookup pays the whole machinery — query parsing, planning, vector allocation — for one value, and the measurement shows the result: 13.4 ms per lookup, 75 per second.
SQLite is a B-tree keyed store and does the same lookup in 58 microseconds. A Python dictionary does it in 0.68 microseconds. The right tool depends entirely on the shape of the access, and "it is a database" is not a shape.
Where DuckDB genuinely wins is the build and the set-shaped questions: reading the parquet dump, filtering 13.5 million rows to 5.2 million, computing the normalised key in SQL. Use it there, then hand the result to whichever store matches the query pattern.
Why an offline gazetteer cannot be a rooftop geocoder
GeoNames records places. The lookup returns the coordinate of a town, which is a locality-level match — measured elsewhere at a bounding box spanning tens of kilometres.
For a national analysis at local-authority level that is exactly right, and free. For a delivery route it is unusable, and no amount of engineering on the index changes that: the information is not in the file. Address-level offline geocoding requires an address-point file, which exists for some countries and not others.
Why coverage of local names is a separate problem from matching
Normalisation handles representation: case, accents, punctuation. It does nothing for translation: München and Munich share no letters after the umlaut is folded, and Warszawa and Warsaw diverge after four.
Only an alternate-names table connects them. In the ten-city probe the names-only index found 7 of 10 and the alternates index found 9 of 10 — a real gain, at a 77% larger index and five times the build time.
Why a manual override file belongs in every offline geocoder
Every organisation has place names it spells its own way: internal site codes, historical names, abbreviations from a legacy system. These are not typos and no algorithm infers them.
A CSV of our_name,geonameid checked into the repository fixes them permanently, is reviewable by the people who know the answers, and is applied before the index lookup. It is usually the highest-yield fifty lines in the project.
Edge cases or notes
- Feature classes matter. GeoNames
fclass = 'P'is populated places;Ais administrative areas,Tterrain features. Filter deliberately or a mountain will answer to a town's name. - The dump is 1.79 GB of TSV. Convert to parquet once (446 MB) and build from that; it made a repeat aggregation 17 times faster in measurement.
- Population zero is common and does not mean uninhabited — it means unknown. Rank with care.
- Alternate names include scripts you cannot match — Cyrillic, Han, Arabic. Slice the list unless you need them.
- Licence: GeoNames is CC BY, which requires attribution. OpenAddresses and national registers vary; check per source.
- Rebuild when the dump updates, monthly at most. Record the dump date with the index.
- In-memory indexes do not fork cheaply. A 3-million-key dictionary is copied per worker process unless you use a shared store.
- Always return the candidate count. A unique match and a choice between fourteen must not look identical.
Internal links
- Choosing a geocoder: coverage, licence and cost compared — when offline is the right answer
- How to parse and normalise addresses in Python — the shared normaliser
- Address matching explained: why exact string equality fails — the matching tiers used here
- How to fuzzy match place names in Python — the fallback tier
- How to read shapefiles, GeoJSON and GeoParquet in DuckDB — building the index from files
- Rooftop, interpolated or centroid: geocoding precision levels — what a gazetteer can and cannot give you
- How to batch geocode thousands of addresses in Python — the API path this replaces
- GIS data sources explained — where the reference files come from
FAQ
Can an offline geocoder find street addresses?
Only with an address-point file. An open gazetteer such as GeoNames contains places, not addresses, so its best precision is locality level.
How fast is an offline geocoder?
Measured on 5,226,942 GeoNames populated places: 0.68 microseconds per lookup from a Python dictionary (1.48 million per second) and 58 microseconds from SQLite (17,217 per second).
Should I store the gazetteer in DuckDB?
Build it with DuckDB, serve it from SQLite or a dictionary. DuckDB took 13.4 ms per single-row lookup — 230 times slower than SQLite — because it is an analytics engine, not a key-value store.
How much does adding alternate names help?
In a ten-city endonym probe, the names-only index matched 7 of 10 and the alternate-names index matched 9 of 10. The index grew from 2.9 million keys to 5.2 million and build time from 6.1 to 33.8 seconds.
How do I handle ambiguous place names?
Filter by country first — 132,528 place names occur in more than one country — then by region, then rank by population and record that you did. Always return the candidate count.
What licence does GeoNames carry?
CC BY, which requires attribution. National address registers and OpenAddresses vary considerably; check each source before redistributing anything derived from it.