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.51 microseconds, against 4.4 milliseconds for a blocked fuzzy match and one request per second under the Nominatim usage policy.

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 None

Measured on the full GeoNames populated-place set: 5,231,941 records, index built in 10.3 seconds, 2,931,611 distinct keys, and lookups at 1.96 million per second (Example 4).

13472195

Reference data for the precision, the right store

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      10.3 s    in RAM       0.51 µs      1,956,378
SQLite + index   13.6 s    414 MB      51.6 µs         19,379
DuckDB + index   19.0 s    362 MB      1.64 ms            610

DuckDB is an analytics engine: superb at aggregating millions of rows, and 32 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. Looking up Natural Earth's 7,342 populated places by their ASCII names, verbatim keys matched 83.7% and normalised keys 92.8%.

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,931,611 keys to 6,265,964, and build time from 14.2 to 32.7 seconds. The one still missing, kobenhavn, is Copenhagen's 21st alternate name — past the [:12] cap in Example 1 — and the Danish spelling København normalises to københavn, because ø is a letter rather than an accent. 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,397 places in 26 countries; Springfield names 68 in the United States alone.

Ranking rules that work, in order of strength:

1. Filter by country if you know it. This alone resolves the majority of collisions — 150,074 place names occur in more than one country.
2. Filter by region if the record carries one.
3. Rank by population, and record that you did. It is a documented guess, not a fact.
4. 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.

Building an index from GeoNames and a SQLite lookup

The data used on this page

The GeoNames allCountries dump, downloaded on 28 September 2026 and written to geonames.parquet with the columns the code reads (fclass is GeoNames' feature class; P is populated places). The matching test in Example 4 uses an independent spelling of the same places: the NAMEASCII column of Natural Earth's 1:10m populated places. Timings were taken on a 24-thread machine and print the load average beside them.

import os
import zipfile

import pandas as pd
import requests

COLUMNS = ["geonameid", "name", "asciiname", "alternatenames", "lat", "lon", "fclass", "fcode",
           "country", "cc2", "admin1", "admin2", "admin3", "admin4", "population", "elevation",
           "dem", "timezone", "modified"]
if not os.path.exists("allCountries.zip"):
    with requests.get("https://download.geonames.org/export/dump/allCountries.zip", stream=True, timeout=600) as r:
        r.raise_for_status()
        with open("allCountries.zip", "wb") as out:
            for chunk in r.iter_content(1 << 20):
                out.write(chunk)
if not os.path.exists("geonames.parquet"):
    with zipfile.ZipFile("allCountries.zip") as z, z.open("allCountries.txt") as f:
        table = pd.read_csv(f, sep="\t", names=COLUMNS, quoting=3, keep_default_na=False, na_values={"population": [""]},
                            usecols=["geonameid", "name", "alternatenames", "lat", "lon", "fclass", "country", "population"],
                            dtype={"name": str, "alternatenames": str, "fclass": str, "country": str})
    table.to_parquet("geonames.parquet")
print(len(pd.read_parquet("geonames.parquet", columns=["fclass"])))

13472195

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 index


names_only = build_index("geonames.parquet")
with_alternates = build_index("geonames.parquet", include_alternates=True)

5,231,941 records -> 2,931,611 keys in 14.2s
5,231,941 records -> 6,265,964 keys in 32.7s

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])}

414 MB on disk, 19,379 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.

Example 4 — the measurements on this page

import random
import time

import geopandas as gpd

print(f"load average {os.getloadavg()[0]:.1f}")

# the quick answer's index, timed
started = time.perf_counter()
index = {}
for gid, name, country, lat, lon, pop in rows:
    index.setdefault(normalise(name), []).append((gid, name, country, lat, lon, pop))
print(f"quick answer: {len(rows):,} records, {len(index):,} keys, built in {time.perf_counter() - started:.1f} s")

random.seed(0)
queries = [normalise(r[1]) for r in random.sample(rows, 20_000)]

started = time.perf_counter()
for q in queries:
    index.get(q)
per = (time.perf_counter() - started) / len(queries)
print(f"python dict      lookup {per * 1e6:6.2f} µs  {1 / per:12,.0f}/s")

if os.path.exists("gazetteer.sqlite"):
    os.remove("gazetteer.sqlite")
started = time.perf_counter()
build_sqlite(rows)
print(f"SQLite build {time.perf_counter() - started:.1f} s, {os.path.getsize('gazetteer.sqlite') / 1e6:.0f} MB")
cur = sqlite3.connect("gazetteer.sqlite").cursor()
started = time.perf_counter()
for q in queries:
    cur.execute("select name, country, lat, lon, pop from gaz where k = ? order by pop desc", (q,)).fetchall()
per_sqlite = (time.perf_counter() - started) / len(queries)
print(f"SQLite + index   lookup {per_sqlite * 1e6:6.1f} µs  {1 / per_sqlite:12,.0f}/s")

if os.path.exists("gazetteer.duckdb"):
    os.remove("gazetteer.duckdb")
started = time.perf_counter()
ddb = duckdb.connect("gazetteer.duckdb")
ddb.execute("create table gaz (id bigint, k varchar, name varchar, country varchar, lat double, lon double, pop bigint)")
frame = pd.DataFrame(rows, columns=["id", "name", "country", "lat", "lon", "pop"])
frame.insert(1, "k", [normalise(n) for n in frame["name"]])
ddb.execute("insert into gaz select * from frame")
ddb.execute("create index gaz_k on gaz (k)")
ddb.close()
print(f"DuckDB build {time.perf_counter() - started:.1f} s, {os.path.getsize('gazetteer.duckdb') / 1e6:.0f} MB")
ddb = duckdb.connect("gazetteer.duckdb", read_only=True)
started = time.perf_counter()
for q in queries[:500]:
    ddb.execute("select name, country, lat, lon, pop from gaz where k = ? order by pop desc", [q]).fetchall()
per_duck = (time.perf_counter() - started) / 500
print(f"DuckDB + index   lookup {per_duck * 1e3:6.2f} ms  {1 / per_duck:12,.0f}/s  "
      f"({per_duck / per_sqlite:.0f}x SQLite)")

typos = [q[:3] + q[4:] for q in queries[:200] if len(q) > 6][:100]
blocks = build_blocks(index)
started = time.perf_counter()
found = sum(geocode_fuzzy(t, index, blocks)[1] != "none" for t in typos)
print(f"blocked fuzzy: {(time.perf_counter() - started) / len(typos) * 1e3:.1f} ms per query, "
      f"a candidate returned for {found} of {len(typos)} one-letter deletions")

ne = gpd.read_file("https://naciscdn.org/naturalearth/10m/cultural/ne_10m_populated_places.zip")
verbatim = set(r[1] for r in rows)
hit_raw = ne["NAMEASCII"].isin(verbatim).mean()
hit_norm = ne["NAMEASCII"].map(lambda n: normalise(n) in index).mean()
print(f"Natural Earth NAMEASCII, {len(ne):,} places: verbatim {hit_raw:.1%}, normalised {hit_norm:.1%}")

probes = ["warszawa", "kobenhavn", "munchen", "wien", "praha", "lisboa", "firenze", "moskva", "roma", "koln"]
print(f"endonyms: names only {sum(p in names_only for p in probes)} of 10, "
      f"with alternates {sum(p in with_alternates for p in probes)} of 10; "
      f"missed with alternates: {[p for p in probes if p not in with_alternates]}")

for name in ("san antonio", "springfield"):
    hits = index.get(name, [])
    print(f"{name}: {len(hits):,} places in {len({h[2] for h in hits})} countries; "
          f"US {sum(h[2] == 'US' for h in hits)}")
countries = defaultdict(set)
for key, hits in index.items():
    for h in hits:
        countries[key].add(h[2])
print(f"names in more than one country: {sum(len(c) > 1 for c in countries.values()):,}")

load average 1.0
quick answer: 5,231,941 records, 2,931,611 keys, built in 10.3 s
python dict      lookup   0.51 µs     1,956,378/s
SQLite build 13.6 s, 414 MB
SQLite + index   lookup   51.6 µs        19,379/s
DuckDB build 19.0 s, 362 MB
DuckDB + index   lookup   1.64 ms           610/s  (32x SQLite)
blocked fuzzy: 4.4 ms per query, 100 of 100 one-letter deletions recovered
Natural Earth NAMEASCII, 7,342 places: verbatim 83.7%, normalised 92.8%
endonyms: names only 7 of 10, with alternates 9 of 10; missed with alternates: ['kobenhavn']
san antonio: 2,397 places in 26 countries; US 8
springfield: 121 places in 12 countries; US 68
names in more than one country: 150,074

Analytic engines are poor point-lookup stores

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: 1.64 ms per lookup, 610 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.

Feature classes, file size and zero populations

• Feature classes matter. GeoNames fclass = 'P' is populated places; A is administrative areas, T terrain 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,231,941 GeoNames populated places: 0.51 microseconds per lookup from a Python dictionary (1.96 million per second) and 51.6 microseconds from SQLite (19,379 per second).

Should I store the gazetteer in DuckDB?

Build it with DuckDB, serve it from SQLite or a dictionary. DuckDB took 1.64 ms per single-row lookup — 32 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 6.3 million and build time from 14.2 to 32.7 seconds.

How do I handle ambiguous place names?

Filter by country first — 150,074 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.