#!/usr/bin/env python3 """Geocode company addresses (Nominatim/OSM) with a deterministic variant waterfall. Why this script is serial and proxy-free ---------------------------------------- The public Nominatim instance allows at most 1 request/second and no parallel bursts. The earlier threaded version (`geocode_companies_v3.py`) ran ~15 workers x nested pools at 0.5s spacing (~30 req/s); that got this IP rate-limited within minutes, after which every query answered HTTP 429 and the output was mostly empty. Requests here are paced >= REQUEST_GAP apart, and 429 answers back off before retrying. Free public proxies add nothing: they are unreliable, and a geocoding result has to be reproducible. If a local OpenStreetMap extract is available, this script can instead query a self-hosted Nominatim built from it (no rate limit): set GEOCODE_BASE_URL and lower GEOCODE_GAP, e.g. a container imported from a `vietnam-*.osm.pbf` dump. Waterfall rules --------------- - Variants are tried from most specific to least specific; the first hit wins. - Bare building names are tried last, because they can match unrelated POIs anywhere (e.g. "Geek Hub" for GEEK Up's address). - A hit is accepted only if it shares a distinctive token with the address (guards against degraded variants matching a same-named place elsewhere). Resumability ------------ - Every company is saved immediately (progress JSON + CSV, both atomic writes). - Rerunning retries companies that are not resolved yet; resolved ones are never re-queried. Usage ----- python3 geocode_companies_v4.py [--limit N] """ import argparse import csv import json import os import re import sys import time import unicodedata import urllib.error import urllib.parse import urllib.request INPUT_FILE = "forms/company_info.json" OUTPUT_CSV = "forms/company_info_geocoded.csv" PROGRESS_FILE = "forms/geocode_progress.json" USER_AGENT = "InternshipCSE-Geocoder/1.0" COUNTRY_CODE = "vn" REQUEST_TIMEOUT = 30 # Point these at a self-hosted Nominatim (e.g. one built from a local .osm.pbf) # to drop the public-instance rate limit: GEOCODE_BASE_URL + GEOCODE_GAP. NOMINATIM_URL = os.environ.get("GEOCODE_BASE_URL", "https://nominatim.openstreetmap.org") REQUEST_GAP = float(os.environ.get("GEOCODE_GAP", "1.1")) # seconds between requests MAX_ATTEMPTS = 4 # per query, with backoff on 429/5xx/network errors PROGRESS_VERSION = 3 # bumped so runs from earlier script versions start clean CSV_FIELDS = ["id", "shortname", "fullname", "location", "latitude", "longitude", "method", "matched_variant", "matched_address"] def normalize_vn_address(addr: str) -> str: if not addr: return addr result = addr result = re.sub(r'\bTP\.?\s*H\.?\s*C\.?\s*M\.?\b', 'Thành phố Hồ Chí Minh', result, flags=re.IGNORECASE) result = re.sub(r'\bTP\.?\s*HCM\b', 'Thành phố Hồ Chí Minh', result, flags=re.IGNORECASE) result = re.sub(r'\bTp\.?\s*Hồ Chí Minh\b', 'Thành phố Hồ Chí Minh', result, flags=re.IGNORECASE) result = re.sub(r'\bTP\s+Hồ Chí Minh\b', 'Thành phố Hồ Chí Minh', result, flags=re.IGNORECASE) result = re.sub(r'\bTP\.?\s*Hà Nội\b', 'Thành phố Hà Nội', result, flags=re.IGNORECASE) result = re.sub(r'\bTP\.?\s*Đà Nẵng\b', 'Thành phố Đà Nẵng', result, flags=re.IGNORECASE) result = re.sub(r'\bTP\.?\s*Cần Thơ\b', 'Thành phố Cần Thơ', result, flags=re.IGNORECASE) result = re.sub(r'\bTP\.?\s*Hải Phòng\b', 'Thành phố Hải Phòng', result, flags=re.IGNORECASE) result = re.sub(r'\bQ\.?\s*(\d+)\b', r'Quận \1', result, flags=re.IGNORECASE) result = re.sub(r'\bP\.?\s*(\d+)\b', r'Phường \1', result, flags=re.IGNORECASE) result = re.sub(r'\bP\.?\s+([A-ZÀ-Ỹ][a-zà-ỹ]+(?:\s+[A-ZÀ-Ỹ][a-zà-ỹ]+)*)', r'Phường \1', result) result = re.sub(r'\s*\([^)]*cũ[^)]*\)', '', result, flags=re.IGNORECASE) result = re.sub(r'\s*>>.*$', '', result) result = result.strip() result = re.sub(r'\s+', ' ', result) result = re.sub(r'\s*,\s*', ', ', result) return result def strip_building(addr: str) -> str: if not addr: return addr result = addr result = re.sub(r'Tầng\s+\w+,\s*', '', result, flags=re.IGNORECASE) result = re.sub(r'Lầu\s+\w+,\s*', '', result, flags=re.IGNORECASE) result = re.sub(r'Phòng\s+[\w\.]+\s+Floor\s+\w+,\s*', '', result, flags=re.IGNORECASE) result = re.sub(r'Floor\s+\w+,\s*', '', result, flags=re.IGNORECASE) result = re.sub(r'Phòng\s+[\w\.]+,\s*', '', result, flags=re.IGNORECASE) result = re.sub(r'Căn\s+[\w\-]+,\s*', '', result, flags=re.IGNORECASE) result = re.sub(r'Block\s+\w+,\s*', '', result, flags=re.IGNORECASE) result = re.sub(r'Tòa nhà\s+[^,]+,\s*', '', result, flags=re.IGNORECASE) result = re.sub(r'Toà nhà\s+[^,]+,\s*', '', result, flags=re.IGNORECASE) result = re.sub(r'cao ốc\s+[^,]+,\s*', '', result, flags=re.IGNORECASE) result = re.sub(r'Tower\s+[^,]+,\s*', '', result, flags=re.IGNORECASE) result = re.sub(r'Plaza\s+[^,]+,\s*', '', result, flags=re.IGNORECASE) result = re.sub(r'Building\s+[^,]+,\s*', '', result, flags=re.IGNORECASE) result = re.sub(r'Tổng Công nghệ[^,]*,\s*', '', result, flags=re.IGNORECASE) result = re.sub(r'Khu Công nghệ\s+[^,]+,\s*', '', result, flags=re.IGNORECASE) result = re.sub(r'Khu Chế Xuất\s+[^,]+,\s*', '', result, flags=re.IGNORECASE) result = re.sub(r'Khu CNC\s*,\s*', '', result, flags=re.IGNORECASE) result = re.sub(r'KCX\s+[^,]+,\s*', '', result, flags=re.IGNORECASE) result = re.sub(r'KDC\s+[^,]+,\s*', '', result, flags=re.IGNORECASE) result = re.sub(r'CVPM\s+[^,]+,\s*', '', result, flags=re.IGNORECASE) result = re.sub(r'Công viên Phần Mềm\s+[^,]+,\s*', '', result, flags=re.IGNORECASE) result = re.sub(r'Công viên\s+[^,]+,\s*', '', result, flags=re.IGNORECASE) result = re.sub(r'dự án\s+[^,]+,\s*', '', result, flags=re.IGNORECASE) result = re.sub(r'Khu nhà ở\s+[^,]+,\s*', '', result, flags=re.IGNORECASE) result = re.sub(r'Khu phố\s+[^,]+,\s*', '', result, flags=re.IGNORECASE) result = re.sub(r'Khu vực\s+[^,]+,\s*', '', result, flags=re.IGNORECASE) result = re.sub(r'Khu đô thị\s+[^,]+,\s*', '', result, flags=re.IGNORECASE) result = re.sub(r'Khu\s+[^,]+,\s*', '', result, flags=re.IGNORECASE) result = re.sub(r'Lô\s+[\w\.]+,\s*', '', result, flags=re.IGNORECASE) result = re.sub(r'TT\s+[^,]+,\s*', '', result, flags=re.IGNORECASE) result = re.sub(r'KV\s+[^,]+,\s*', '', result, flags=re.IGNORECASE) result = re.sub(r'WeWork\s*,\s*', '', result, flags=re.IGNORECASE) result = re.sub(r'FPT Software HCM\s*,\s*', '', result, flags=re.IGNORECASE) result = re.sub(r'FPT\s*,\s*', '', result, flags=re.IGNORECASE) result = re.sub(r'Viettel\s*[–-]\s*', '', result, flags=re.IGNORECASE) result = re.sub(r'Zalo Headquarters\s*,\s*', '', result, flags=re.IGNORECASE) result = re.sub(r'The Manor Officetel\s*,\s*', '', result, flags=re.IGNORECASE) result = re.sub(r'Nikko Building\s*,\s*', '', result, flags=re.IGNORECASE) result = re.sub(r'Ban Vien Tower\s*,\s*', '', result, flags=re.IGNORECASE) result = re.sub(r'Sorimachi Dai-3 Building\s*,\s*', '', result, flags=re.IGNORECASE) result = re.sub(r'Epic Tower\s*,\s*', '', result, flags=re.IGNORECASE) result = re.sub(r'Officehaus\s*,\s*', '', result, flags=re.IGNORECASE) result = re.sub(r'Saigon Paragon\s*,\s*', '', result, flags=re.IGNORECASE) result = re.sub(r'CII Tower\s*,\s*', '', result, flags=re.IGNORECASE) result = re.sub(r'Huy Sơn\s*,\s*', '', result, flags=re.IGNORECASE) result = re.sub(r'Scetpa\s*,\s*', '', result, flags=re.IGNORECASE) result = re.sub(r'E\.town\s*\d*\s*Building\s*,\s*', '', result, flags=re.IGNORECASE) result = re.sub(r'Waseco\s*,\s*', '', result, flags=re.IGNORECASE) result = re.sub(r'Lữ Gia Plaza\s*,\s*', '', result, flags=re.IGNORECASE) result = re.sub(r'TMA\s*,\s*', '', result, flags=re.IGNORECASE) result = re.sub(r'The Mett\s*,\s*', '', result, flags=re.IGNORECASE) result = re.sub(r'Tuổi Trẻ\s*,\s*', '', result, flags=re.IGNORECASE) result = re.sub(r'AP Tower\s*,\s*', '', result, flags=re.IGNORECASE) result = re.sub(r'Saigon Trade Center\s*,\s*', '', result, flags=re.IGNORECASE) result = re.sub(r'COBI Tower\s*,\s*', '', result, flags=re.IGNORECASE) result = re.sub(r'Hallmark\s*,\s*', '', result, flags=re.IGNORECASE) result = re.sub(r'St\.Moritz\s*,\s*', '', result, flags=re.IGNORECASE) result = re.sub(r'Ereka\s*,\s*', '', result, flags=re.IGNORECASE) result = re.sub(r'Vina\s*,\s*', '', result, flags=re.IGNORECASE) result = re.sub(r'SBI\s*,\s*', '', result, flags=re.IGNORECASE) result = re.sub(r'ICT2\s*,\s*', '', result, flags=re.IGNORECASE) result = re.sub(r'Thảo Điền Pearl\s*,\s*', '', result, flags=re.IGNORECASE) result = re.sub(r'Cư xá Lữ Gia\s*,\s*', '', result, flags=re.IGNORECASE) result = re.sub(r'Khu\s+\w+,\s*', '', result, flags=re.IGNORECASE) result = re.sub(r'Khu phố\s+\d+,\s*', '', result, flags=re.IGNORECASE) result = re.sub(r'Khu nhà ở\s+[^,]+,\s*', '', result, flags=re.IGNORECASE) result = re.sub(r'dự án\s+[^,]+,\s*', '', result, flags=re.IGNORECASE) result = re.sub(r'\s*\(gần[^)]*\)', '', result, flags=re.IGNORECASE) result = re.sub(r',\s*VN\s*$', '', result, flags=re.IGNORECASE) result = re.sub(r',\s*Việt Nam\s*$', '', result, flags=re.IGNORECASE) # a floor/room prefix left glued to the rest after a building name was removed result = re.sub( r'^(?:Tầng|Lầu|Phòng|Căn|Lô)\s+[\w\.]+\s*[-–,]?\s*' r'(?=(?:\d+[A-Za-z]?\s+)?(?:số|Phường|Quận|Xã|Huyện|Đường|Khu|Thành phố|Tỉnh|Tòa|Toà)\b)', '', result, flags=re.IGNORECASE) result = result.strip() result = re.sub(r'\s+', ' ', result) result = re.sub(r'^,\s*', '', result) result = re.sub(r'\s*,\s*$', '', result) return result def extract_building(addr: str) -> str: if not addr: return "" patterns = [ r'Tòa nhà\s+[^,]+', r'Toà nhà\s+[^,]+', r'cao ốc\s+[^,]+', r'Tower\s+[^,]+', r'Plaza\s+[^,]+', r'Building\s+[^,]+', r'FPT Software HCM', r'Viettel', r'Zalo Headquarters', r'The Manor Officetel', r'Nikko Building', r'Ban Vien Tower', r'Sorimachi Dai-3 Building', r'Epic Tower', r'Officehaus', r'Saigon Paragon', r'CII Tower', r'Huy Sơn', r'Scetpa', r'E\.town\s*\d*\s*Building', r'Waseco', r'Lữ Gia Plaza', r'TMA', r'The Mett', r'Tuổi Trẻ', r'AP Tower', r'Saigon Trade Center', r'COBI Tower', r'Hallmark', r'St\.Moritz', r'Ereka', r'Vina', r'SBI', r'ICT2', r'Thảo Điền Pearl', r'Lữ Gia', r'Cư xá Lữ Gia', r'WeWork', ] for pattern in patterns: match = re.search(pattern, addr, re.IGNORECASE) if match: return match.group(0) return "" def remove_parentheses(addr: str) -> str: if not addr: return addr result = re.sub(r'\s*\([^)]*\)', '', addr) result = result.strip() result = re.sub(r'\s+', ' ', result) return result def simplify_house_number(addr: str) -> str: if not addr: return addr result = addr result = re.sub(r'\bSố\s+', '', result, flags=re.IGNORECASE) result = re.sub(r'(\d+)\s*/\s*\w+', r'\1', result) result = re.sub(r'(\d+)\s*-\s*\d+', r'\1', result) result = re.sub(r'(\d+)[A-Z]\b', r'\1', result) result = result.strip() result = re.sub(r'\s+', ' ', result) return result def remove_ward(addr: str) -> str: if not addr: return addr result = re.sub(r',\s*Phường\s+[^,]+', '', addr, flags=re.IGNORECASE) result = re.sub(r',\s*Quận\s+[^,]+', '', result, flags=re.IGNORECASE) result = result.strip() result = re.sub(r'\s+', ' ', result) return result def remove_district(addr: str) -> str: if not addr: return addr result = re.sub(r',\s*Quận\s+[^,]+', '', addr, flags=re.IGNORECASE) result = result.strip() result = re.sub(r'\s+', ' ', result) return result def first_house_number(addr: str) -> str: if not addr: return addr result = re.sub(r'(\d+)\s*-\s*\d+', r'\1', addr) result = re.sub(r'(\d+)\s*/\s*\w+', r'\1', result) result = result.strip() result = re.sub(r'\s+', ' ', result) return result def remove_duong_so(addr: str) -> str: if not addr: return addr result = re.sub(r'Đường\s+số\s+', '', addr, flags=re.IGNORECASE) result = result.strip() result = re.sub(r'\s+', ' ', result) return result _FOREIGN_HINT = re.compile( r'\b(?:Japan|Singapore|Taiwan|Thailand|Korea|Seoul|Tokyo|Niigata|Taipei|Chia-Yi|' r'China|Malaysia|Indonesia|Philippines|India|United States|USA|Australia)\b', re.IGNORECASE) def restrict_to_vn(raw_addr: str) -> bool: """True for addresses that look Vietnamese; foreign offices must not get countrycodes=vn.""" return not _FOREIGN_HINT.search(raw_addr or "") def _fold(text: str) -> str: """Lowercase and strip diacritics, for token comparison.""" text = unicodedata.normalize("NFD", text.casefold()) text = "".join(c for c in text if not unicodedata.combining(c)) return text.replace("đ", "d") # administrative words that appear in nearly every address; useless for a match check _STOPWORDS = {"viet", "nam", "thanh", "pho", "tinh", "phuong", "quan", "huyen", "xa", "duong", "khu", "tp", "hcm", "vn", "lau", "tang", "phong", "toa", "nha", "block"} def _distinctive_tokens(text: str) -> set[str]: return {w for w in re.findall(r"[0-9a-z]+", _fold(text)) if len(w) >= 4 and w not in _STOPWORDS} def plausible_hit(raw_addr: str, display_name: str) -> bool: """Reject a hit that shares no distinctive token with the address we asked about. Guards against degraded variants resolving to a same-named place elsewhere (observed: "Toà nhà B ... Quận 7" matching a building in Hưng Yên province). """ wanted = _distinctive_tokens(raw_addr) if not wanted: return True # nothing distinctive to check against return bool(wanted & _distinctive_tokens(display_name)) _FLOORISH = re.compile(r'^\s*(?:(?:Tầng|Lầu|Phòng|Căn)\b|\d+(?:st|nd|rd|th)?\s*Floor\b)', re.IGNORECASE) _TRAILING_NUM = re.compile(r'^\s*[^\d,]*\d+\s*$') # "Etown 2", "Tầng 6", "A24" def drop_leading_poi(addr: str) -> str: """Strip leading POI/unit segments so the street anchor comes first. "Etown 2, 364 Cộng Hòa, phường Tân Bình, ..." -> "364 Cộng Hòa, phường Tân Bình, ..." "Tầng 6, Tòa nhà Phú Mỹ Hưng, số 8 Hoàng Văn Thái, ..." -> "số 8 Hoàng Văn Thái, ..." """ parts = [p.strip() for p in addr.split(",")] for idx, part in enumerate(parts): if _FLOORISH.match(part) or _TRAILING_NUM.match(part) or not re.search(r'\d', part): continue return ", ".join(parts[idx:]) return ", ".join(parts[1:]) if len(parts) > 2 else addr def build_variants(raw_addr: str) -> list[str]: """Ordered most-specific -> least-specific query variants; first hit wins.""" n = normalize_vn_address(raw_addr) stripped = strip_building(n) building = extract_building(n) ordered = [ n, # full normalized address remove_parentheses(n), # ... without parenthetical notes stripped, # ... without floor/room and building name simplify_house_number(stripped), # ... with house-number range/suffix simplified drop_leading_poi(n), # ... without leading POI/unit segments remove_ward(n), # ward names are the most volatile token simplify_house_number(n), first_house_number(n), remove_duong_so(n), remove_district(n), ] if building and stripped: street = stripped.split(",")[0].strip() if street: ordered.append(f"{building}, {street}") # building + street number ordered.append(building) # last resort: bare building name (POI-match risk) variants, seen = [], set() for v in ordered: v = (v or "").strip() if v and v not in seen: variants.append(v) seen.add(v) return variants class Nominatim: """Serial, rate-limited Nominatim client.""" def __init__(self): self._last_request = 0.0 self.requests = 0 def _pace(self): wait = REQUEST_GAP - (time.monotonic() - self._last_request) if wait > 0: time.sleep(wait) self._last_request = time.monotonic() def query(self, address: str, country: str | None = COUNTRY_CODE) -> tuple[float, float, str] | None: """Return (lat, lon, display_name) for the best match, or None.""" params = {"q": address, "format": "json", "limit": 1} if country: params["countrycodes"] = country url = f"{NOMINATIM_URL}/search?{urllib.parse.urlencode(params)}" req = urllib.request.Request(url, headers={"User-Agent": USER_AGENT}) for attempt in range(1, MAX_ATTEMPTS + 1): self._pace() self.requests += 1 try: with urllib.request.urlopen(req, timeout=REQUEST_TIMEOUT) as resp: data = json.loads(resp.read().decode()) if not data: return None top = data[0] return float(top["lat"]), float(top["lon"]), top.get("display_name", "") except urllib.error.HTTPError as e: if e.code == 429: wait = 30 * attempt print(f" rate limited (429), waiting {wait}s ...", file=sys.stderr) time.sleep(wait) elif 500 <= e.code < 600: time.sleep(5) else: print(f" HTTP {e.code}: {e.reason}", file=sys.stderr) return None except Exception as e: print(f" request failed: {e}", file=sys.stderr) time.sleep(3) print(" giving up on this query after repeated failures", file=sys.stderr) return None def load_results() -> dict: if not os.path.exists(PROGRESS_FILE): return {} with open(PROGRESS_FILE, "r", encoding="utf-8") as f: state = json.load(f) if state.get("version") != PROGRESS_VERSION: print("progress file from an earlier script version ignored (starting fresh)") return {} return state.get("results", {}) def save_progress(results: dict): tmp = PROGRESS_FILE + ".tmp" with open(tmp, "w", encoding="utf-8") as f: json.dump({"version": PROGRESS_VERSION, "results": results}, f, ensure_ascii=False, indent=1) os.replace(tmp, PROGRESS_FILE) def save_csv(companies: list, results: dict): tmp = OUTPUT_CSV + ".tmp" with open(tmp, "w", encoding="utf-8", newline="") as f: writer = csv.DictWriter(f, fieldnames=CSV_FIELDS) writer.writeheader() for company in companies: row = results.get(company["id"]) if row is None: continue writer.writerow({k: ("" if row.get(k) is None else row.get(k, "")) for k in CSV_FIELDS}) os.replace(tmp, OUTPUT_CSV) def is_resolved(row: dict | None) -> bool: if row is None: return False if row.get("latitude") is not None: return True return row.get("method") == "no-address" def main(): parser = argparse.ArgumentParser(description=__doc__, formatter_class=argparse.RawDescriptionHelpFormatter) parser.add_argument("--limit", type=int, default=None, help="process at most N pending companies (default: all)") args = parser.parse_args() with open(INPUT_FILE, "r", encoding="utf-8") as f: companies = json.load(f) results = load_results() resolved_before = sum(1 for c in companies if is_resolved(results.get(c.get("id")))) pending_all = [c for c in companies if not is_resolved(results.get(c.get("id")))] pending = pending_all[:args.limit] if args.limit is not None else pending_all print(f"{len(companies)} companies | {resolved_before} resolved | " f"{len(pending_all)} pending | processing {len(pending)} this run") if not pending: print("nothing to do") return client = Nominatim() started = time.time() for i, company in enumerate(pending, 1): cid = company.get("id", "") name = company.get("shortname", "?") raw_addr = company.get("location", "") print(f"[{i}/{len(pending)}] {name}: {raw_addr}", flush=True) row = { "id": cid, "shortname": name, "fullname": company.get("fullname", ""), "location": raw_addr, "latitude": None, "longitude": None, "method": None, "matched_variant": "", "matched_address": "", } if not (raw_addr or "").strip(): row["method"] = "no-address" else: variants = build_variants(raw_addr) country = COUNTRY_CODE if restrict_to_vn(raw_addr) else None rejected = 0 for j, variant in enumerate(variants, 1): hit = client.query(variant, country) if not hit: continue # the plausibility guard exists for the messy VN addresses; for # foreign ones Nominatim often answers in native script if country and not plausible_hit(raw_addr, hit[2]): rejected += 1 print(f" x rejected unrelated result for v{j}: {hit[2][:90]}", file=sys.stderr, flush=True) continue row["latitude"], row["longitude"], row["matched_address"] = hit row["method"] = f"v{j}" row["matched_variant"] = variant print(f" -> ({hit[0]:.6f}, {hit[1]:.6f}) [v{j}] {hit[2][:90]}") break else: note = f" ({rejected} unrelated results rejected)" if rejected else "" print(f" -> NOT FOUND after {len(variants)} variants{note}") results[cid] = row save_progress(results) save_csv(companies, results) if i % 25 == 0: resolved = sum(1 for r in results.values() if r.get("latitude") is not None) elapsed = (time.time() - started) / 60 print(f"--- {i}/{len(pending)} this run | {resolved}/{len(companies)} resolved | " f"{elapsed:.1f} min | {client.requests} requests ---", flush=True) save_progress(results) save_csv(companies, results) resolved = sum(1 for r in results.values() if r.get("latitude") is not None) unresolved = sum(1 for c in companies if not is_resolved(results.get(c.get("id")))) print(f"\nDone: {resolved}/{len(companies)} geocoded " f"({client.requests} requests this run) -> {OUTPUT_CSV}") if unresolved: print(f"{unresolved} companies still unresolved; rerun to retry them") if __name__ == "__main__": try: main() except KeyboardInterrupt: print("\ninterrupted - progress is saved per company, rerun to resume", file=sys.stderr) sys.exit(130)