Multi-source geocoding pipeline with country-aware postcode validation, coordinate sanity checks, and SQLite caching. Built from production patterns used across a 40-country business directory project — where every record required verified lat/lng, validated postcodes, and flagged anomalies before client delivery.
When you're geocoding thousands of business addresses across 40 countries:
- Nominatim is free but fails on ~15–30% of addresses (non-standard formatting, rural areas, local-language addresses)
- Google Places API is reliable but at $0.032/call, geocoding 50,000 rows = $1,600
- Postcode formats differ entirely by country —
2000is valid in Australia, invalid in France (needs 5 digits), formatted differently in Canada (M5V 3A8) and the UK (SW1A 1AA) - A re-run after a crash shouldn't re-geocode rows you already paid for
This pipeline handles all of that.
Phase 0 — SQLite cache lookup
Already-geocoded addresses return instantly. FREE.
Phase 0b — Intra-file cross-reference
If city X in country Y has a geocoded row, copy coords to all
other rows with the same city+country still missing lat/lng. FREE.
Recovers 20–40% of rows on dense urban datasets with no API cost.
Phase 1 — Nominatim (OpenStreetMap)
Structured query: street + city + state + country.
Falls back to city + country if full address fails.
Respects 1 req/sec fair-use policy.
Covers ~70–85% of remaining rows.
Phase 2 — Google Places API (fallback only)
Runs ONLY on rows that failed Phase 1.
Hard budget cap — stops before the limit even if rows remain.
$0.032 per call.
Validation pass (all records):
├── Postcode format check (regex, 50+ countries)
├── Coordinate bounding box check (coords outside country → flagged)
└── Zero-coordinate detection (0.0, 0.0 → flagged for review)
Validates postcode format for 50+ countries including:
| Region | Countries |
|---|---|
| English-speaking | Australia, Canada, UK, USA, New Zealand, Ireland, South Africa |
| Western Europe | France, Germany, Belgium, Netherlands, Switzerland, Austria, Spain, Italy, Portugal, Sweden, Norway, Denmark, Finland |
| Eastern Europe | Poland, Czech Republic, Hungary, Romania, Bulgaria, Estonia, Malta |
| Middle East | UAE, Saudi Arabia, Egypt, Algeria, Iraq |
| Americas | Brazil, Peru |
| Pacific | Papua New Guinea |
Each country has its own regex pattern + normaliser function. For example:
Australia: 4 digits 2000 → valid
Canada: A1A 1A1 format M5V3A8 → normalised to M5V 3A8
United Kingdom: variable length sw1a1aa → normalised to SW1A 1AA
France: 5 digits 75001 → valid
pip install -r requirements.txt
# Run with Nominatim only (free, no API key needed)
python pipeline.py --input sample_input.csv
# Add Google fallback with a $5 budget cap
python pipeline.py --input sample_input.csv \
--google-key AIza... \
--budget 5.0
# Your CSV uses different column names
python pipeline.py --input data.csv \
--col-map address=street city=locality country=nation postcode=zip
# Dry run — validate postcodes only, no geocoding
python pipeline.py --input sample_input.csv --dry-run
# Wipe cache and re-geocode from scratch
python pipeline.py --input sample_input.csv --reset-cacheTry it immediately with the included sample:
python pipeline.py --input sample_input.csv| File | Description |
|---|---|
{input}_geocoded.csv |
All records enriched with geocoding + validation results |
{input}_geocoded.xlsx |
Same, formatted — rows needing review highlighted in yellow |
{input}_failed.csv |
Records that failed all geocoding phases (for manual review) |
geocode_cache.db |
SQLite cache — re-runs skip already-geocoded rows |
{input}_pipeline.log |
Full run log with phase-by-phase counts |
| Column | Description |
|---|---|
latitude / longitude |
Decimal degree coordinates |
geocode_source |
cross_ref / nominatim / google / failed |
geocode_confidence |
exact / city / country / failed |
display_name |
Full address string returned by geocoder |
postcode_valid |
Boolean — does postcode match country format? |
postcode_normalised |
Postcode in canonical format for that country |
coords_in_bbox |
Boolean — do coordinates fall inside the country's bounding box? |
needs_review |
Boolean — any flag triggered |
review_reason |
Human-readable explanation of why the row is flagged |
09:14:22 INFO Loaded 23 records from sample_input.csv
09:14:22 INFO Phase 0 (cache): 0 hits
09:14:22 INFO Phase 0b (cross-ref): 3 rows filled
09:14:22 INFO Phase 1 (Nominatim): 20 rows to geocode
09:14:48 INFO Phase 1 done: 17 geocoded, 3 failed
09:14:48 INFO Phase 2 (Google): disabled
09:14:48 INFO ============================================================
09:14:48 INFO PIPELINE SUMMARY
09:14:48 INFO Total records : 23
09:14:48 INFO Geocoded : 20 (87.0%)
09:14:48 INFO Failed geocoding : 3
09:14:48 INFO Needs review : 5
09:14:48 INFO Invalid postcodes : 2
09:14:48 INFO ============================================================
Done.
Geocoded: 20/23 (87.0%)
Review: 5 rows flagged
Output: sample_input_geocoded.csv
XLSX: sample_input_geocoded.xlsx
Failed: sample_input_failed.csv (3 rows)
Log: sample_input_pipeline.log
Nominatim first, always. At $0.032/call, using Google on every row for a 50,000-row dataset costs $1,600. Nominatim handles 70–85% for free. Google gets called only on Nominatim failures.
Intra-file cross-reference. In a business directory dataset, dozens of branches share the same city. Geocoding one Woolworths in Melbourne fills coordinates for all other Melbourne rows — for free.
dtype=str on all Excel reads. Pandas coerces 4-digit postcodes (e.g. 2000) to integers. str(2000) → '2000' works, but str(2000.0) → '2000.0' breaks every regex match. Forcing string dtype at load time prevents silent failures.
Hard budget cap with pre-flight check. budget.can_call() checks spend before every Google API call, not after. This ensures the pipeline never overshoots the budget even if a batch completes mid-loop.
SQLite cache keyed on address|city|country. Crash mid-run, re-run — zero duplicated API calls. The cache key normalises to lowercase so "Sydney" and "sydney" hit the same cache entry.
- Python 3.10+
requests,openpyxl,pandas- Google Places API key (optional — Nominatim is free and runs without a key)
- OpenStreetMap Nominatim (free, no key required, rate-limited to 1 req/sec)