⚙️ How-To Geocoding & addresses Batch processing Geocoding & Addresses · step 8 of 20 Updated

How to Batch Geocode Thousands of Addresses in Python

Problem statement

Geocoding one address is a three-line script. Geocoding forty thousand is a different problem, and the differences are all operational:

  • At one request per second — the public Nominatim rate — 40,000 rows take eleven hours, and any crash starts them again.
  • The same address appears many times in a real customer file, so a large fraction of those requests are for answers you already have.
  • Rate limits, transient 502s and a laptop lid closing are not exceptional events over eleven hours; they are certainties.
  • Half the failures are silent: a coordinate comes back, at the wrong precision, and looks exactly like a success.

A batch geocoder is therefore a small pipeline: deduplicate, cache, throttle, retry, checkpoint, and report — with the actual API call as the least interesting part.

Quick answer

Geocode distinct addresses, not rows, and write every result to disk as it arrives:

import pandas as pd


def batch_geocode(df, address_col, client, cache):
    """Rows -> distinct keys -> cached lookups -> rows again."""
    keys = df[address_col].map(normalise)             # your normaliser
    todo = sorted(set(keys) - cache.known())
    print(f"{len(df):,} rows, {keys.nunique():,} distinct, {len(todo):,} to fetch")

    for i, key in enumerate(todo, 1):
        cache.put(key, client.geocode(key))           # client throttles itself
        if i % 100 == 0:
            print(f"  {i:,}/{len(todo):,}")

    results = keys.map(cache.get)
    return df.assign(
        lat=[r["lat"] if r else None for r in results],
        lon=[r["lon"] if r else None for r in results],
        precision=[r["precision"] if r else None for r in results],
    )

The first print is the one that saves money. It is common for a 40,000-row file to contain 12,000 distinct addresses, of which 9,000 are already cached from last month — turning eleven hours into fifty minutes.

Funnel from 40,000 rows to 12,000 distinct addresses to 3,000 requests to 50 minutes.
Deduplication and caching are the only speed-ups no usage policy objects to.

Step-by-step solution

1. Deduplicate on the normalised address, not the raw string

Normalisation is what makes the deduplication work: case, accents, punctuation and whitespace all vary without changing the place. In a controlled measurement against a 238,483-row reference set, verbatim string matching found 29.1% of realistically messy queries and normalised matching found 85.5%.

The same effect applies to your own file: normalising before the nunique() count typically collapses another 5–15% of the rows.

2. Make the cache the primary data structure

Not a decorator on a function — a table on disk with a schema:

key            the normalised query
response       the provider's raw JSON, untouched
provider       which service answered
fetched_at     when
status         ok | no_match | error

Storing the raw response means a change to your interpretation logic — a new precision mapping, a stricter filter — is a re-parse rather than a re-fetch. Storing misses means a permanently unmatchable address is asked about once, not once per run.

3. Throttle inside the client

The rate limit belongs to the client object, not to the loop. A client that enforces its own minimum interval cannot be made to breach the limit by a caller who forgot, and it makes the loop honest about the time the job will take.

Do not reach for threads. The public Nominatim policy limits total requests per second, so parallelism converts a slow compliant job into a fast blocked one. Legitimate speed comes from deduplication, caching, and — above a few hundred thousand lookups — running your own instance.

4. Checkpoint continuously, not at the end

Any long job must be restartable from where it stopped. Because the cache is written on every result, the restart logic is already there: recompute set(keys) - cache.known() and the finished work is skipped automatically.

That is the whole benefit of making the cache primary. There is no separate checkpoint file to keep in sync.

5. Classify every result, and report the classes

A batch job that reports "40,000 geocoded" has said nothing. The useful summary is by precision level and status:

rooftop   24,930  62.3%
street     8,470  21.2%
postcode   4,010  10.0%
locality   2,048   5.1%
country      126   0.3%   <- unusable
no match     416   1.0%

Two of those lines are a work queue. Print them at the end of every run and the file's quality stops being a surprise.

6. Decide what happens to the unusable rows before you start

Drop, keep-and-label, or review. Write the choice into the config next to the analysis, because it differs per analysis: a national choropleth can live with locality centroids and a delivery route cannot.

Five-step batch loop from to-do list to precision classification and stopping on a block.
A hard kill costs at most one lookup.

Code examples

Example 1 — the cache, as a table 

import json
import sqlite3
from datetime import datetime, timezone


class GeocodeCache:
    def __init__(self, path="geocode_cache.sqlite", provider="nominatim"):
        self.provider = provider
        self.db = sqlite3.connect(path)
        self.db.execute("""
            create table if not exists geocode (
                key text not null, provider text not null,
                response text, status text not null, fetched_at text not null,
                primary key (key, provider))""")
        self.db.commit()

    def known(self) -> set[str]:
        rows = self.db.execute(
            "select key from geocode where provider = ?", (self.provider,))
        return {r[0] for r in rows}

    def get(self, key):
        row = self.db.execute(
            "select response, status from geocode where key = ? and provider = ?",
            (key, self.provider)).fetchone()
        if not row or row[1] != "ok":
            return None
        return json.loads(row[0])

    def put(self, key, result, status=None):
        status = status or ("ok" if result else "no_match")
        self.db.execute(
            "insert or replace into geocode values (?, ?, ?, ?, ?)",
            (key, self.provider, json.dumps(result) if result else None,
             status, datetime.now(timezone.utc).isoformat(timespec="seconds")))
        self.db.commit()

    def stats(self):
        return dict(self.db.execute(
            "select status, count(*) from geocode where provider = ? group by 1",
            (self.provider,)).fetchall())

One commit() per row is deliberate. It is slower than batching and it means a hard kill loses at most one lookup — which, at a second per lookup, is the right trade.

Example 2 — the runner, with progress, retry and a final report 

import time
from collections import Counter


def run_batch(df, address_col, client, cache, normalise, report_every=100):
    keys = df[address_col].fillna("").map(normalise)
    distinct = sorted(k for k in set(keys) if k)
    todo = [k for k in distinct if k not in cache.known()]

    print(f"rows          {len(df):,}")
    print(f"distinct      {len(distinct):,}   ({100 * len(distinct) / len(df):.1f}% of rows)")
    print(f"already known {len(distinct) - len(todo):,}")
    print(f"to fetch      {len(todo):,}   (~{len(todo) * client.min_interval / 3600:.1f} h)")

    started, errors = time.monotonic(), 0
    for i, key in enumerate(todo, 1):
        try:
            hits = client.search(key)
            cache.put(key, interpret(hits) if hits else None)
        except PermissionError:
            print("blocked by the provider — stopping cleanly, cache is intact")
            break
        except Exception as exc:                     # transient: record and move on
            errors += 1
            cache.put(key, None, status="error")
            print(f"  ! {key[:50]}: {type(exc).__name__}")
        if i % report_every == 0:
            rate = i / (time.monotonic() - started)
            print(f"  {i:,}/{len(todo):,}  {rate:.2f}/s  eta "
                  f"{(len(todo) - i) / rate / 60:.0f} min")

    resolved = keys.map(cache.get)
    out = df.assign(
        lat=[r["lat"] if r else None for r in resolved],
        lon=[r["lon"] if r else None for r in resolved],
        precision=[r["precision"] if r else "no_match" for r in resolved],
    )
    print("\nprecision breakdown")
    for level, n in Counter(out["precision"]).most_common():
        print(f"  {level:10s} {n:7,}  {100 * n / len(out):5.1f}%")
    print(f"errors this run: {errors}")
    return out

Example 3 — turning the result into a GeoDataFrame, safely 

import geopandas as gpd


def to_geodataframe(df, worst_allowed="street", crs="EPSG:4326"):
    """Only rows that met the precision floor become geometry."""
    usable = df["precision"].map(PRECISION_RANK).fillna(9) <= PRECISION_RANK[worst_allowed]
    dropped = (~usable).sum()
    if dropped:
        print(f"excluding {dropped:,} rows below '{worst_allowed}' precision")

    good = df[usable & df["lat"].notna()].copy()
    gdf = gpd.GeoDataFrame(
        good,
        geometry=gpd.points_from_xy(good["lon"], good["lat"]),   # x, y — lon first
        crs=crs,
    )
    gdf.attrs["excluded_rows"] = int(dropped)
    return gdf

points_from_xy(lon, lat) — in that order — is worth writing out every time. Swapping them produces points off the coast of Africa or, more insidiously, in a plausible-looking wrong country.

Explanation

Why deduplication is the largest single saving

Address files repeat because the world does: households order twice, businesses have many contacts at one site, and the same depot appears on every delivery row. A file where distinct addresses are 30% of rows is ordinary.

Deduplicating on the normalised key rather than the raw string compounds the effect, because the same address written three ways collapses to one lookup. Every subsequent optimisation — caching, self-hosting, buying a bulk tier — operates on a smaller number.

Why the cache has to store failures

An address that cannot be geocoded today cannot be geocoded tomorrow either, unless the reference data changed. Without negative caching, every run pays full price for the permanently unmatchable rows — which are, by definition, the slowest ones to fail.

Store the status and the timestamp, and expire negatives after a few months rather than never. Reference data does improve.

Why parallelism is the wrong first answer

The arithmetic that tempts everyone: 40,000 rows at 1/s is eleven hours; with sixteen threads it is forty minutes. The policy the public service enforces is on total requests per second, so the sixteen-thread version is a violation that ends in a 403 for your whole IP address.

The ordering that works: deduplicate (often a 3× reduction), cache (a rerun costs nothing), and only then consider capacity — a paid bulk endpoint, or your own Nominatim instance where concurrency is a tuning parameter rather than a breach.

Why the precision breakdown belongs in the run output

A batch job's log is the only place most people will ever see the quality of the geocoding. If it prints a single count, the file's 5% locality centroids travel silently into every downstream analysis.

Printing the breakdown makes the trade-off visible at the moment it is made, and it turns "the geocoding is done" into "the geocoding is done, and 2,174 rows need a decision".

Bar chart of a batch run broken down by precision level, ending in unusable and no-match rows.
The bottom two bars are the reason the job existed.

Edge cases or notes

  • Empty and near-empty addresses match countries and regions with high scores. Filter rows with fewer than about three tokens before sending.
  • Country codes narrow the search and remove most cross-border mismatches: pass countrycodes when you know it.
  • Structured queries beat free text if you have already parsed the components.
  • A run that takes hours needs nohup or a scheduler, not a laptop.
  • Commit per row. The cost is invisible next to a network round trip and it makes kills safe.
  • Do not sort the input by anything meaningful — if the job stops early, a random order leaves you with a representative sample rather than only the As.
  • Watch for stacked coordinates afterwards: many rows sharing one point is the signature of a fallback.
  • Re-geocoding is a new measurement. Store the provider and date, or a rerun will silently change coordinates.

FAQ

How long does it take to geocode 40,000 addresses?

Against the public Nominatim service at one request per second, about eleven hours — before deduplication. Distinct-address counts of 30% and a warm cache routinely cut that to under an hour.

Can I use threads to speed it up?

Not against a service whose policy limits total requests per second; that is how you get a 403 for your whole IP. Deduplicate, cache, then self-host if the volume justifies it.

Should I cache addresses that failed to geocode?

Yes. They will fail again, and they are the slowest rows to fail. Store the status and expire negative entries after a few months.

How do I make the job resumable?

Write every result to the cache as it arrives, and compute the to-do list as distinct keys minus cached keys. There is then no separate checkpoint to maintain.

What should the batch job print at the end?

The breakdown by precision level and status. A single "geocoded 40,000" hides the rows that are unusable, and those are the reason the job existed.

Do I geocode rows or distinct addresses?

Distinct normalised addresses, then join the results back onto the rows. It is usually the single largest saving in the pipeline.