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

Saudações, malta do desenvolvimento e entusiastas de tecnologia! Como especialista na área, analisei o tópico **"Batch Geocoding at Scale with France's Free BAN API: Thresholds, Chunks, and Zero Lock Contention"** e trago aqui uma síntese técnica para explorarmos como podemos aplicar estes conceitos nos nossos projetos aqui em Moçambique.

O artigo foca-se na eficiência máxima ao utilizar a API BAN (Base Adresse Nationale) da França, que é gratuita, mas o conhecimento é perfeitamente aplicável a qualquer sistema de geolocalização de larga escala. Aqui estão os pontos fundamentais:

### 1. Gestão de Limiares (Thresholds) e Rate Limiting
A API BAN, embora gratuita, impõe limites para garantir a estabilidade. O autor destaca a importância de respeitar os *thresholds* para evitar o erro HTTP 429 (Too Many Requests). Para nós, que muitas vezes lidamos com infraestruturas de rede variáveis, implementar um algoritmo de **Exponential Backoff** (espera exponencial após erro) é vital para que o nosso script não seja banido temporariamente.

### 2. Segmentação em Lotes (Chunks)
Processar um milhão de coordenadas de uma só vez é pedir para o sistema falhar. A estratégia de *chunking* consiste em dividir grandes ficheiros CSV ou JSON em pequenos pedaços (ex: 50 a 100 registos por pedido). Isso reduz a latência de rede e permite que, se um lote falhar, não precisemos de recomeçar todo o processo do zero.

### 3. Zero Lock Contention (Concorrência Sem Bloqueios)
Este é o ponto mais "pro" da análise. Ao processar dados em paralelo (usando múltiplas threads ou processos), o perigo é a **Contenção de Bloqueio** na base de dados. Se várias instâncias tentarem escrever no mesmo registo ao mesmo tempo, o sistema abranda. A solução proposta é garantir que cada processo trabalhe numa fatia exclusiva de dados, permitindo uma escrita fluida e ultra-rápida, sem que um processo "atropele" o outro.

---

**Debate no webmastersmz.com:**
Gostaria de levar esta discussão para o nosso fórum em **webmastersmz.com**. Como é que vocês têm lidado com o processamento de grandes volumes de dados geográficos em Moçambique? Já experimentaram usar ferramentas como o PostGIS com processamento assíncrono? Apareçam por lá para partilharmos experiências sobre como adaptar estas técnicas de "Zero Lock Contention" na nossa realidade local.

Para garantir que os vossos projetos e fóruns rodam sem falhas, convido-vos a conhecer as soluções de alojamento de alta performance da AplicHost em [https://aplichost.com](https://aplichost.com). Ter uma infraestrutura robusta é o primeiro passo para implementar estas arquiteturas de escala sem dores de cabeça.

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



Tópico: Batch Geocoding at Scale with France's Free BAN API: Thresholds, Chunks, and Zero Lock Contention
Categoria: Tutoriais | Programação & Tecnologia
Idioma Principal: Português (Conteúdo de Tecnologia)

Descrição do Conteúdo / Informações:
-------------------------------------------------------------------------
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.