Batch Geocoding at Scale with France's Free BAN API: Thresholds, Chunks, and Zero Lock Contention

We needed GPS coordinates for 14.4 million French business establishments to power radius-based searches. Here's the pipeline we built — free, no third-party services, and designed to run daily alongside a live application without ever locking the database.

The Starting Point

The French SIRENE registry lists every active business establishment in the country: 14,387,175 of them as of July 2026. When we needed to enable queries like "find all plumbers within 20 km of Bordeaux," we had a problem: SIRENE includes postal addresses but no GPS coordinates.

INSEE (France's national statistics institute) publishes an official geocoded dataset — GeoSirene — but it's updated monthly. New establishments registered between releases have no coordinates. For any product that processes fresh SIRENE data daily, you need to geocode incrementally.

Why the BAN API

France's Base Adresse Nationale at api-adresse.data.gouv.fr is purpose-built for this:

• Free, no API key — government infrastructure, openly accessible
• Bulk CSV endpoint — submit thousands of rows at once, get coordinates and confidence scores back
• FR-specific precision — trained on the official national address database, not crowd-sourced data
• No ban on server requests — unlike the INSEE search API, the BAN accepts requests from datacenter IPs

We evaluated Nominatim as an alternative. We ruled it out: their bulk usage policy limits automated queries, and their precision on small French establishments (artisans, micro-entrepreneurs registered at a home address) is lower than BAN.

Two-Layer Architecture

We separated the work into two distinct layers:

Monthly: A full rebuild from the official GeoSirene dataset. INSEE releases a geocoded stock file each month — more precise than the API alone because it uses additional cross-referencing sources. This handles the bulk.

Daily: An incremental pass that picks up new establishments from the SIRENE flux that have lat IS NULL. This is where the BAN API comes in.

The query that identifies what needs geocoding:

sql = (
    "SELECT id, adresse, code_postal, commune FROM etablissement "
    "WHERE lat IS NULL AND adresse IS NOT NULL AND code_postal IS NOT NULL "
    "AND code_postal GLOB '[0-9][0-9][0-9][0-9][0-9]' "
    "AND code_postal NOT LIKE '99%' "
    "AND code_postal NOT LIKE '987%' AND code_postal NOT LIKE '988%'"
)

The exclusion filters matter:

• 99xxx codes are foreign addresses (legitimate in SIRENE for companies with foreign legal seats)
• 987, 988 are French Polynesia and New Caledonia — territories not covered by the BAN

Batch Size and Politeness

The BAN accepts CSV POSTs up to ~50 MB. We settled on 8,000 rows per request — well within the limit even with verbose addresses, and keeping individual response times manageable (30–90 seconds depending on server load).

Between requests, we pause 1 second:

CHUNK = 8000
PAUSE = 1.0

The BAN is shared public infrastructure. Slamming it with no pause isn't respectful and risks throttling during peak hours.

Score Threshold

The API returns a result_score between 0 and 1. We reject anything below 0.4:

MIN_SCORE = 0.4

In practice, real French business addresses with a valid postal code and commune rarely fall below 0.4 — the BAN finds them. Scores below this threshold typically indicate:

• Non-standard industrial zones or new subdivisions not yet in the address database
• CEDEX routing codes without a physical street address
• Data-entry errors in the source (address and commune from different cities)

We do not try to force a geocode for low-confidence records. A wrong coordinate is worse than no coordinate when you're doing radius queries: a business that shows up in the wrong city will appear in searches it shouldn't, and disappear from searches it should be in.

Retry Logic

for attempt in range(5):
    try:
        raw = urllib.request.urlopen(req, timeout=180).read()
        break
    except Exception as ex:
        if attempt == 4:
            log(f"batch KO ({ex}) → skipped")
        time.sleep(3 + attempt * 3)

Five attempts, with 3/6/9/12-second backoff. The 180-second timeout is intentionally generous — large batches on a loaded BAN server take longer than you'd expect.

WAL Mode: Zero Lock Contention

The geocoding script runs at 04:30 daily, while the application is serving user requests. Without care, each commit would grab an exclusive write lock and stall every concurrent reader.

conn.execute("PRAGMA journal_mode=WAL")
conn.execute("PRAGMA busy_timeout=60000")

WAL (Write-Ahead Logging) lets readers continue against the last committed snapshot while the writer appends to the WAL file. Readers never wait for the geocoder. The busy_timeout gives any competing writer 60 seconds before returning an error — in practice this never fires.

This matters more than it sounds. The etablissement table holds 14.4 million rows. Even with targeted WHERE lat IS NULL updates, a batch commit touches thousands of rows. In default journal mode, that's thousands of readers paused per commit cycle.

R-Tree Index Updates

Geocoding isn't just writing lat/lon. We also maintain a spatial R-tree index for fast radius queries:

conn.execute("DELETE FROM etab_rtree WHERE id=?", (eid,))
conn.execute("INSERT INTO etab_rtree VALUES(?,?,?,?,?)",
             (eid, lat, lat, lon, lon))

This happens atomically within the same transaction as the coordinate update, so the index never diverges from the data.

Results

After the initial geocoding run over the full 14.387M-establishment database:

• Overall GPS coverage: 98.4% — 14,156,534 establishments with coordinates
• France-only coverage: 99.9% of eligible FR addresses (after applying the postal code filter)
• The BAN API returned a valid result above the 0.4 threshold for ~93% of submitted French address records in our production monitoring — the remaining ~7% are genuinely hard cases the API cannot resolve confidently

The ~12,000 records still without coordinates are the true hard cases: incomplete addresses, non-standard zones, or data-entry errors in the source. They stay at lat IS NULL rather than receiving a wrong coordinate.

Integration

The geocoder chains directly after the daily SIRENE flux import:

# cron 04:30 daily, as the application user
flux_daily.py && geocode.py

Both scripts run against the same SQLite database the live application reads, using WAL mode throughout. This pipeline is part of what powers the map search in OutSend, letting users filter 14 million business records by radius at query time — with no external geocoding service involved.

What We'd Do Differently

Normalize addresses before submitting. Stripping CEDEX suffixes, collapsing double spaces, and removing newlines from address fields before submission would likely recover a few percentage points of the 7% failure rate.

Track score distribution, not just totals. We log how many records were geocoded but not the histogram of scores. Knowing whether failures cluster at 0.3–0.4 (recoverable with better normalization) versus 0.0–0.1 (genuinely unparseable) would help prioritize address cleanup.

Commune-level fallback for hard cases. For the ~12,000 records that remain unresolved, a centroid at the commune level would be better than nothing for approximate radius queries. We haven't needed it yet.