How to do geographic enrichment in ETL: step by step

This tutorial shows how to do geographic enrichment in ETL to add latitude/longitude and municipality to customer records. Enriching geographic data makes analyses and visualizations more useful (maps, segmentation by region) and improves business reporting.

Prerequisites

• Python 3.8+ installed
• Libraries: requests, pandas
• CSV file with an address column (address)
• API key for a geocoding service (e.g.: Nominatim or Google Geocoding)

Step 1: Prepare the input data

Start by loading the CSV and ensuring the address column is normalized (trim, remove obvious duplicates). This avoids unnecessary API calls and costs. We will also create a unique identifier for each address.

import pandas as pd

data = pd.read_csv('clientes.csv')
# Normalizar espaços e remover linhas sem morada
data['address'] = data['address'].astype(str).str.strip()
data = data[data['address']!='']
# Criar chave para deduplicação
data['addr_key'] = data['address'].str.lower()

Step 2: Deduplication before geocoding

Geocoding is costly; we deduplicate addresses so we call the API only once per distinct address. We keep a mapping to reapply to the original records.

# Obter moradas únicas
unique_addrs = data[['addr_key','address']].drop_duplicates().reset_index(drop=True)

Step 3: Implement the geocoding API call with error handling

We create a simple function that calls the API (example with Nominatim) and handles basic errors and rate limits. In production use exponential retries and persistent cache.

import requests
import time

def geocode(address, email='seu_email@example.com'):
    # Exemplo com Nominatim (OpenStreetMap) — respeita políticas de uso
    url = 'https://nominatim.openstreetmap.org/search'
    params = {'q': address, 'format': 'json', 'limit': 1, 'addressdetails': 1, 'email': email}
    try:
        resp = requests.get(url, params=params, timeout=10)
        if resp.status_code == 200:
            j = resp.json()
            if j:
                lat = j[0].get('lat')
                lon = j[0].get('lon')
                city = j[0].get('address', {}).get('city') or j[0].get('address', {}).get('town') or j[0].get('address', {}).get('village')
                return {'latitude': float(lat), 'longitude': float(lon), 'city': city}
            return None
        else:
            return None
    except requests.RequestException:
        return None

# Teste rápido (apenas uma morada)
# print(geocode('Praça do Comércio, Lisbon'))

Step 4: Run batch geocoding with in-memory cache

We iterate over the unique addresses, store results in a dictionary (cache) and apply a small delay to avoid rate limits. In larger scenarios, use a persistent cache (Redis, file) and parallel workers with throttling.

cache = {}
results = []
for idx, row in unique_addrs.iterrows():
    key = row['addr_key']
    addr = row['address']
    if key in cache:
        results.append({'addr_key': key, **cache[key]})
        continue
    res = geocode(addr)
    if res:
        cache[key] = res
        results.append({'addr_key': key, **res})
    else:
        cache[key] = {'latitude': None, 'longitude': None, 'city': None}
        results.append({'addr_key': key, 'latitude': None, 'longitude': None, 'city': None})
    time.sleep(1)  # Respeitar limites do serviço

geo_df = pd.DataFrame(results)

Step 5: Merge the geocoded data with the original dataset

We perform a join between the geocoding results and the original dataset on the addr_key field. This way each customer receives latitude, longitude and city.

final = data.merge(geo_df, on='addr_key', how='left')
# Remover colunas temporárias se necessário
final = final.drop(columns=['addr_key'])
final.to_csv('clientes_enriquecidos.csv', index=False)

Verify the result

Open the clientes_enriquecidos.csv file and confirm that each record has valid latitude and longitude or controlled null values. Do sampling: check 10 addresses with and without coordinates. To validate geographically, load into a visualization tool (e.g.: Power BI or QGIS) and confirm the points fall in the expected locations.

Conclusion

You have just implemented a simple geographic enrichment flow in ETL: normalization, deduplication, geocoding with basic error handling and joining the results. Next steps: add persistent cache, exponential retries, controlled parallelism and accuracy validation (distance checks). Tip: start testing with a subset before processing the entire dataset to avoid costs and API blocks.