A client handed Aiden 50,000 addresses and said "give me lat/lng for each one." Google Maps Geocoding API costs $5 per 1,000 requests. Math: 50,000 ÷ 1,000 × $5 = $250. But that's the dumb way. Smart pattern: cache-first architecture in Postgres, request only fresh addresses, throttle to free-tier daily limits, handle AU address ambiguity. Final spend: under $10. This is the geocoding pattern reused across Rebuild Relief location tools and Velocity template projects.
Why Naive Geocoding Is Expensive
Every address-to-lat/lng lookup hits the API. Duplicates? Still hit the API. Address you geocoded last month? Hit the API again. At scale, this bleeds budget fast. The unlock: Postgres geocode cache table keyed by normalized address. Before requesting the API, check the cache. If it's there, free. If it's not, request once and store forever. Simple pattern saves 90%+ on repeat data.
The Architecture: Postgres Cache → Batch Request → Retry Queue
Set up a geocode_cache table with normalized address as the unique key. Address normalization matters: uppercase, trim whitespace, deduplicate suburb variants. When you need to geocode a batch, scan your input list, join against the cache table, and collect only the misses. Request those from the API in a single batch (Google Maps supports batch requests). Store results in the cache. Next time the same address appears — whether in the same batch or six months later — it's a free hit.
CREATE TABLE geocode_cache (
id SERIAL PRIMARY KEY,
normalized_address TEXT UNIQUE NOT NULL,
original_address TEXT,
latitude DECIMAL(9, 6),
longitude DECIMAL(9, 6),
geocode_result JSONB, -- full response for debugging
cached_at TIMESTAMP DEFAULT NOW(),
request_count INT DEFAULT 1
);
-- Fast lookup: normalized_address is unique
SELECT latitude, longitude
FROM geocode_cache
WHERE normalized_address = 'UPPER 123 Main St Qld 4000';Batch request pattern: collect 100 address misses, fire them to Google Maps Batch Geocoding, wait for results, insert into cache. Repeat until all misses are done. Total API calls: 50,000 input ÷ 100 per batch = 500 batches. Cost: 500 requests × $0.016 per request (batch rate) = $8. Compare to naive: $250. Same output, 96% cheaper.
Handling AU Address Chaos — PO Box vs Street + Suburb Disambiguation
Australian addresses are a mess. "123 Main St, Brisbane" might be "123 Main St, Fortitude Valley" (suburb spec changes result). PO boxes resolve to mail distribution centres, not actual locations. Normalize aggressively: strip PO box lines, standardize suburb names against official ABS/Australia Post lists, favour street addresses over PO when both exist. Store the original address and the normalized version separately so you can debug mismatches. If the API returns multiple results (e.g., same address in two suburbs), pick the one closest to your expected region or store both and let the user choose.
-- Normalization function for AU addresses
CREATE OR REPLACE FUNCTION normalize_au_address(addr TEXT) RETURNS TEXT AS $$
BEGIN
RETURN TRIM(
UPPER(
REGEXP_REPLACE(
REGEXP_REPLACE(addr, 'PO BOX \d+,?\s*', ''),
'\s+', ' ', 'g'
)
)
);
END;
$$ LANGUAGE plpgsql;
-- Use in batch geocode:
SELECT normalized_address
FROM (
SELECT DISTINCT normalize_au_address(address) AS normalized_address
FROM input_addresses
) t
WHERE NOT EXISTS (SELECT 1 FROM geocode_cache WHERE geocode_cache.normalized_address = t.normalized_address);Throttling to Free Tier (25,000 requests/day)
Google Maps free tier caps at 25,000 requests per day. If you have 50k addresses, don't fire all 500 batch requests in one hour — spread them over 2 days. Process 250 batches day 1, 250 day 2. Track your request count in a daily quota table and pause if you're near the cap. This keeps you under the free tier indefinitely; no paid plan needed.
-- Track daily quota
CREATE TABLE geocode_quota (
date DATE PRIMARY KEY,
requests_used INT DEFAULT 0,
quota_limit INT DEFAULT 25000
);
-- Before batch: check quota
SELECT (quota_limit - requests_used) AS remaining
FROM geocode_quota
WHERE date = CURRENT_DATE;
-- Pause batch if remaining < batch_size
-- Otherwise: fire request, increment requests_usedRetry Logic: Handle Transient Failures
Google Maps API occasionally times out or rate-limits mid-batch. Don't crash; retry. Queue failed addresses in a geocode_retry table with a backoff strategy: first retry after 1 hour, then 24 hours. If it fails thrice, flag it as unresolvable and move on. Most transients resolve on the second try.
Frequently Asked Questions
Can I use free-tier Google Maps forever?
Yes — 25k free requests/day is ~750k/month. Cache hit rate of 80%+ means you're sending ~20% of your volume to the API. Use cases scale to millions of addresses over months without paid plan.
What if an address is slightly different each time (typos, abbreviations)?
Normalize before lookup. Strip common abbreviations ("St" vs "Street"), uppercase everything, remove punctuation. Fuzzy-match against the cache if exact match fails (Postgres fuzzy string search with pg_trgm extension).
How do I handle overseas addresses?
Same pattern applies globally. AU address normalization is just one rule set. Adjust the normalization function for your region's quirks and you're done.
Should I cache the full API response?
Yes. Store the entire JSONB response. Helps with debugging ("why did this address resolve to two locations?") and adds value later when you need accuracy metadata (confidence scores, address component breakdown).
What if Google Maps adds a new field or changes the response format?
JSONB is schemaless. Backwards-compatible by default. Just store whatever Google sends; extract the fields you need on query time.
The Bottom Line
Naive geocoding at 50k addresses = $250 API bill. Smart geocoding = cache-first architecture, batch requests, daily throttle, retry queue. Cost drops to under $10. The pattern replicates across projects: any address-heavy operation (delivery routing, location analytics, territory mapping) benefits from the same cache layer. See how we use this in location-based SaaS features or check out territory mapping at scale.