DEV Community: OutSend

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

OutSend — Mon, 27 Jul 2026 08:14:41 +0000

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.

Reconcile before you round: lessons from a 14M-row open data study

OutSend — Fri, 24 Jul 2026 08:52:11 +0000

We recently published a data study on the French business registry — 14.4 million active establishments, sliced by workforce, sector and geography. The numbers themselves aren't the interesting part for this post. What is: the rules we forced ourselves to follow so that anyone re-running the same queries on the same open dataset would get the exact same numbers, and so every headline percentage could be traced back to a total someone else can check.

This is a walkthrough of that process, using INSEE's SIRENE registry (France's official open-data business registry) as the example dataset, because it's public, it's messy in the ways real government data tends to be, and the mistakes we ran into are the boring, common kind — not exotic edge cases.

Reconcile before you round

The registry snapshot we worked from (dated July 1, 2026) contains 14,387,175 active, publishable establishments and 26,756,173 legal units in total, of which 14,470,762 are active. Those three numbers are the anchor for everything else: before computing a single percentage, we re-derived each of them independently from the raw stock and diffed against the reference totals published alongside the dataset. Zero-diff or the query is wrong — not the data.

That discipline caught a real bug early on: our first pass at "establishments with no declared workforce" summed the wrong INSEE status codes and landed a few points off. The correct answer splits into two codes — NN (workforce not reported, 12,486,437 establishments) and 00 (zero salaried staff, 71,325) — for a combined 12,557,762, or 87.28% of the active stock. Small distinction, but it changes the number, and it only surfaces if you reconcile against a known total instead of trusting the first query that runs without an error.

Document exclusions, don't quietly drop them

Any geographic breakdown of this dataset means deriving a département (the French equivalent of a county) from a postal code — and postal codes in a 14-million-row open dataset are not clean. Corsica is aggregated under a synthetic "20" code, overseas départships need 3-character handling, and there's a long tail of malformed or foreign entries.

Rather than silently filtering rows that didn't fit and reporting a tidy total, we counted and published every excluded bucket: 157,187 establishments with no postal code at all, 27,525 under code "99" (foreign addresses), 5,107 under the "98x" range (Monaco, French Polynesia), 1,290 in Saint-Pierre-et-Miquelon, and 4,574 residual malformed codes (stray formats like partial UK postcodes that clearly don't belong in a French address field). None of this moves the order of magnitude of the geographic ranking — Paris still dominates with 1,159,693 establishments — but it's the difference between a defensible percentage and one that quietly excludes 200k rows without saying so.

Treat a surprising ranking as a prompt to investigate, not a headline

Rank establishments by sector and the top result is "real estate activities" — 3,326,963 establishments, 23.1% of the entire stock. That's not a booming property market; it's an artifact. Sociétés civiles immobilières (SCIs), a common French legal wrapper used purely to hold or transfer property, register as establishments like any operating business, despite rarely being a sales or outreach target for anyone. A naive "top sectors" chart would have reported this uncritically. Flagging it meant going back to first principles for every ranking in the study: is this number describing economic activity, or a legal-structure artifact that happens to be countable?

Sanity-check date boundaries

The "new registrations in 2026" count needed a lower bound (start of year) and, less obviously, an upper one. Filtering the raw creation-date field turned up 102 records dated in 2027 or later — clearly bad input, not a preview of the future. They were excluded after inspecting the bound, and the exclusion was logged rather than silently absorbed into the total.

What this adds up to

None of these four steps are novel techniques. They're closer to a checklist: reconcile every aggregate against an independent total before publishing it, count and disclose what you exclude instead of hiding it in a WHERE clause, question rankings that look too clean, and check your date boundaries before you trust a "recent activity" number. The interesting part isn't any single check — it's that skipping any one of them produces a plausible-looking, wrong number, and open government datasets this large will always have enough edge cases to hide one.

We do this kind of data engineering as part of building OutSend, a lead-generation platform — the reconciliation habits above came directly from having to trust the same pipelines in production, not just for a one-off report.

Verifying B2B emails without sending a single one: how MX-only validation actually works

OutSend — Tue, 21 Jul 2026 12:08:08 +0000

When you're building or using a B2B prospecting tool, "verified email" usually means one of two things: a real SMTP handshake (connect, MAIL FROM, RCPT TO, read the response, disconnect without sending) or a lighter DNS-only check. Most articles about email verification gloss over why you'd pick one over the other. This post walks through the mechanics, the tradeoffs, and a real-world constraint that forces the decision for a lot of small/medium senders: outbound port 25 being blocked.

The three layers of "is this email real"

Syntax check — regex/RFC 5322 parsing. Catches typos (jhon@gmial.com), catches obviously malformed input. Cheap, instant, but tells you almost nothing about deliverability.
DNS / MX check — does the domain have valid MX records? Does it resolve? This confirms the domain can receive mail, not that the mailbox exists.
SMTP handshake (no send) — connect to the MX host on port 25, issue RCPT TO:<address>, read the server's response code (250 = accepted, 550 = mailbox doesn't exist, etc.), then QUIT before DATA. This is the only layer that actually confirms mailbox existence — and it's also the layer most likely to break in production.

Why the SMTP handshake breaks for so many senders

Outbound TCP port 25 is blocked by default on most consumer ISPs and a growing number of cloud/VPS providers (it's one of the oldest anti-spam measures on the internet — stops compromised machines from becoming open relays). If your verification worker runs on a box where port 25 egress is blocked, every RCPT TO attempt just times out. You get zero signal, not a "550 doesn't exist" — just silence, indistinguishable from a slow mail server.

Three ways to deal with this:

Route SMTP checks through infrastructure with confirmed port 25 egress (a subset of your VPS fleet, or a dedicated relay you control). Works, but adds an ops dependency you now have to monitor.
Pay for a third-party verification API that already solved this problem. Fine at low volume, expensive fast, and you're trusting an external service with your prospect list.
Fall back to MX-only + heuristics when port 25 is unavailable, and be honest about the confidence level of the result instead of pretending it's a full SMTP verification.

We went with the third option for our own scraper/enrichment pipeline: when port 25 egress isn't available on a given worker, the check degrades gracefully to "domain has valid MX, catch-all detection where possible, syntax valid" rather than silently returning a false "verified." The label shown to the end user reflects which layer actually ran — "MX confirmed" is not the same claim as "mailbox confirmed," and conflating the two is how verification tools end up with inflated "95% deliverable" numbers that don't survive contact with a real campaign.

Catch-all domains are the other trap

Even with full SMTP access, a lot of corporate mail servers respond 250 OK to any RCPT TO on their domain (catch-all configuration) — meaning a successful handshake doesn't actually prove the specific mailbox exists. Standard mitigation: probe a random, almost-certainly-nonexistent local part (asdkjh1928@domain.com) on the same domain. If that also comes back 250, the domain is catch-all and your original address's "verified" status should be downgraded to "plausible, unconfirmed" rather than reported as a clean hit.

The practical takeaway

If you're evaluating (or building) an email verification step in a prospecting pipeline, ask two questions before trusting a "valid" badge:

Did this go through an actual SMTP handshake, or just an MX/DNS check? (Both are useful — they are not the same guarantee.)
Was catch-all detection run, and how does the tool label a catch-all hit vs. a genuinely confirmed mailbox?

Most vendors don't surface this distinction because "98% verified" sells better than "72% SMTP-confirmed, 26% MX-only (port 25 blocked on this worker), catch-all flagged separately." We'd rather ship the second, less flattering number, because it's the one that actually predicts bounce rate.

We build OutSend, an all-in-one B2B lead generation platform — Google Maps scraping, email enrichment, deliverability verification and no-code pipelines chained end-to-end. Free during alpha, no credit card required. This post describes real constraints we hit building the verification layer, not a product pitch — happy to go deeper on any of it in the comments.

OutSend: an all-in-one B2B lead generation tool (Google Maps scraping, email finder, deliverability check)

OutSend — Fri, 17 Jul 2026 09:51:20 +0000

We built OutSend because prospecting for B2B leads usually means stitching together 3-4 different tools: a Google Maps scraper, an email finder, an email verifier, and a spreadsheet to tie it all together.

What it does

OutSend is an all-in-one B2B lead generation platform:

Scrape business leads from Google Maps by city, category and keyword
Enrich them with emails (email finder)
Verify deliverability before you send anything (anti-bounce check)
Chain scraping, enrichment and validation end-to-end with no-code pipelines
Export clean CSV files in seconds

Stack

FastAPI + SQLite on the backend, Alpine.js on the frontend, self-hosted (no paid third-party SaaS dependency in the core pipeline).

Who it's for

Sales teams, agencies and founders who prospect at scale and are tired of paying for (and wiring up) four different subscriptions to do one job.

It's free during the alpha, no credit card required: https://outsend.xyz

Happy to answer any questions about the scraping/enrichment pipeline in the comments.