HTML 82.1%
Python 14.6%
TypeScript 1.9%
CSS 1%
JavaScript 0.5%
1# =============================================================================2# Job·Ka — Groupe KA3# Auteur : Simon-Pierre Boucher4# Contact : contact@spboucher.ai5# Fichier : jobka/geocode.py6# Rôle : Géocodage des lieux de travail — Nominatim + repli Adresses Québec,7# cache persistant, centroïde de ville quand l'adresse est absente8# Créé : 2026-08-17 Modifié : 2026-08-179# =============================================================================10"""Géocodage des offres (pipeline hérité de Lou·Ka).1112- cache persistant (table geocode_cache) : un lieu n'est géocodé qu'une seule13 fois, par adresse normalisée — ou par VILLE quand l'offre n'a pas d'adresse14 (cas majoritaire pour l'emploi : le centroïde de ville suffit à la carte).15- politesse : 1 requête/seconde max (règle Nominatim), User-Agent identifiable.16- validation : bounding box provinciale — jamais de coordonnées inventées.17"""18from __future__ import annotations1920import re21import time2223import requests2425from . import db26from .normalize import strip_accents2728NOMINATIM_URL = "https://nominatim.openstreetmap.org/search"29# Repli officiel du gouvernement du Québec (Adresses Québec / MERN) : couvre30# les adresses trop récentes pour OpenStreetMap.31AQ_URL = ("https://servicescarto.mern.gouv.qc.ca/pes/rest/services/Territoire/"32 "Adresse_Geocodage/GeocodeServer/findAddressCandidates")33AQ_BATCH_URL = ("https://servicescarto.mern.gouv.qc.ca/pes/rest/services/Territoire/"34 "Adresse_Geocodage/GeocodeServer/geocodeAddresses")35AQ_MIN_SCORE = 7536BATCH_SIZE = 20037# ASCII pur : le serveur ArcGIS d'Adresses Québec retourne 500 si l'en-tête38# User-Agent contient des accents (latin-1)39USER_AGENT = "JobKaBot/1.0 (agregateur emplois Quebec; +contact@spboucher.ai)"40REQUEST_DELAY = 1.1 # règle Nominatim : max 1 req/s41RETRY_FAILED_AFTER = 30 * 86400 # re-tenter les échecs après 30 jours4243# Garde-fou provincial (lat_min, lat_max, lng_min, lng_max) — même boîte que44# schema.finalize()45_BBOX_QC = (44.50, 63.00, -80.00, -56.00)464748def _in_bbox(lat: float, lng: float) -> bool:49 return _BBOX_QC[0] <= lat <= _BBOX_QC[1] and _BBOX_QC[2] <= lng <= _BBOX_QC[3]505152def norm_key(address: str, city: str = "") -> str:53 """Clé de cache : adresse normalisée, ou « ville <ville> » sans adresse."""54 s = strip_accents((address or "").lower())55 s = re.sub(r"\b(?:bureau|suite|local|etage|app?t?|#)\.?\s*[\w-]+\b", " ", s)56 s = re.sub(r"[^a-z0-9]+", " ", s)57 s = re.sub(r"\s+", " ", s).strip()58 if s and len(s) >= 6:59 return s60 c = strip_accents((city or "").lower())61 c = re.sub(r"[^a-z0-9]+", " ", c).strip()62 return f"ville {c}" if c else ""636465class Geocoder:66 def __init__(self, con) -> None:67 self.con = con68 self.session = requests.Session()69 self.session.headers["User-Agent"] = USER_AGENT70 self._last = 0.07172 @staticmethod73 def _clean(address: str) -> str:74 """Nettoyages qui aident Nominatim sur les adresses québécoises."""75 s = (address or "").strip()76 s = re.sub(r"^(\d+)-(\d+)\s", r"\2 ", s) # unité-civique77 s = re.sub(r",?\s*(?:bureau|suite|local|app\.?|apt\.?|etage|étage)\s*[\w-]+\b",78 "", s, flags=re.I)79 s = re.sub(r"\s*\((qu[ée]bec)\)", r", \1", s, flags=re.I)80 return s8182 def _query_nominatim(self, params: dict) -> tuple[float, float] | None:83 """Une requête Nominatim, throttlée."""84 wait = REQUEST_DELAY - (time.time() - self._last)85 if wait > 0:86 time.sleep(wait)87 try:88 resp = self.session.get(NOMINATIM_URL, params={89 "format": "jsonv2", "limit": 1, "countrycodes": "ca", **params,90 }, timeout=20)91 self._last = time.time()92 resp.raise_for_status()93 hits = resp.json()94 except Exception:95 self._last = time.time()96 return None97 if not hits:98 return None99 try:100 return float(hits[0]["lat"]), float(hits[0]["lon"])101 except (KeyError, ValueError):102 return None103104 def _query_adresses_quebec(self, address: str, city: str) -> tuple[float, float] | None:105 """Repli : géocodeur officiel Adresses Québec (MERN, ArcGIS)."""106 premier = self._clean(address).split(",")[0].strip()107 ville = (city or "Québec").strip()108 wait = REQUEST_DELAY - (time.time() - self._last)109 if wait > 0:110 time.sleep(wait)111 try:112 resp = self.session.get(AQ_URL, params={113 "SingleLine": f"{premier}, {ville}",114 "f": "json", "outSR": 4326, "maxLocations": 1,115 }, timeout=20)116 self._last = time.time()117 resp.raise_for_status()118 cands = resp.json().get("candidates") or []119 except Exception:120 self._last = time.time()121 return None122 if not cands or cands[0].get("score", 0) < AQ_MIN_SCORE:123 return None124 loc = cands[0].get("location") or {}125 try:126 return float(loc["y"]), float(loc["x"])127 except (KeyError, ValueError):128 return None129130 def _attempts(self, address: str, city: str) -> list[dict]:131 """Stratégies de requête, de la plus précise à la moins précise."""132 ville = (city or "Québec").strip()133 commun = {"city": ville, "state": "Québec", "country": "Canada"}134 tries: list[dict] = []135 premier = self._clean(address).split(",")[0].strip() if address else ""136 if premier:137 m = re.match(r"^(\d+)[,\s]+(.{4,})$", premier)138 if m:139 tries.append({"street": f"{m.group(1)} {m.group(2)}", **commun})140 tries.append({"q": f"{premier}, {ville}, Québec, Canada"})141 if m:142 tries.append({"street": m.group(2), **commun})143 else:144 # sans adresse : centroïde de ville (suffisant pour la carte)145 tries.append(commun)146 return tries147148 def resolve(self, address: str, city: str) -> tuple[float, float] | None:149 """Lieu -> (lat, lng), via cache puis Nominatim/AQ. None si introuvable."""150 key = norm_key(address, city)151 if not key:152 return None153 row = self.con.execute(154 "SELECT lat, lng, failed, ts FROM geocode_cache WHERE address=?",155 (key,)).fetchone()156 if row is not None:157 if not row["failed"]:158 return (row["lat"], row["lng"])159 if time.time() - (row["ts"] or 0) < RETRY_FAILED_AFTER:160 return None # échec récent : ne pas marteler l'API161162 coords = None163 provider = "nominatim"164 for params in self._attempts(address, city):165 c = self._query_nominatim(params)166 if c is not None and _in_bbox(*c):167 coords = c168 break169 if coords is None and address:170 c = self._query_adresses_quebec(address, city)171 if c is not None and _in_bbox(*c):172 coords = c173 provider = "adresses_quebec"174 ok = coords is not None175 self.con.execute(176 "INSERT INTO geocode_cache (address, lat, lng, provider, failed, ts)"177 " VALUES (?,?,?,?,?,?)"178 " ON CONFLICT(address) DO UPDATE SET lat=excluded.lat,"179 " lng=excluded.lng, provider=excluded.provider,"180 " failed=excluded.failed, ts=excluded.ts",181 (key, coords[0] if ok else None, coords[1] if ok else None,182 provider, 0 if ok else 1, time.time()))183 self.con.commit()184 return coords if ok else None185186187def run(limit: int | None = None) -> dict:188 """Géocode les offres actives sans coordonnées, par lieu unique.189190 `limit` borne le nombre de NOUVELLES requêtes réseau (les hits de cache191 sont gratuits et toujours appliqués).192 """193 con = db.connect()194 geo = Geocoder(con)195 rows = con.execute(196 """SELECT uid, address, city FROM jobs197 WHERE active=1 AND lat IS NULL AND geocode_failed=0198 AND (address<>'' OR city<>'') ORDER BY city, address""").fetchall()199200 # regrouper par lieu (adresse normalisée, ou ville)201 groupes: dict[str, list] = {}202 for r in rows:203 groupes.setdefault(norm_key(r["address"], r["city"]), []).append(r)204205 done = failed = requests_made = 0206 for key, members in groupes.items():207 if not key:208 continue209 cached = con.execute(210 "SELECT failed FROM geocode_cache WHERE address=?", (key,)).fetchone()211 if cached is None:212 if limit is not None and requests_made >= limit:213 continue214 requests_made += 1215 coords = geo.resolve(members[0]["address"], members[0]["city"])216 if coords:217 for r in members:218 con.execute("UPDATE jobs SET lat=?, lng=? WHERE uid=?",219 (coords[0], coords[1], r["uid"]))220 done += len(members)221 else:222 # introuvable/hors zone : flag pour révision, jamais de coordonnées bidon223 for r in members:224 con.execute("UPDATE jobs SET geocode_failed=1 WHERE uid=?",225 (r["uid"],))226 failed += len(members)227 con.commit()228229 con.close()230 stats = {"geocoded": done, "failed": failed,231 "unique_places": len(groupes), "api_requests": requests_made}232 print(f"[job-ka] geocode {stats}")233 return stats234235236def run_batch(limit: int | None = None) -> dict:237 """Géocodage EN LOT : les offres AVEC adresse passent par Adresses Québec238 (geocodeAddresses, lots de 200) ; celles avec seulement une ville passent239 par le mode 1-par-1 (centroïdes de ville — peu nombreuses et vite en cache)."""240 import json as _json241242 con = db.connect()243 geo = Geocoder(con)244 rows = con.execute(245 """SELECT uid, address, city FROM jobs246 WHERE active=1 AND lat IS NULL AND geocode_failed=0247 AND address IS NOT NULL AND address<>'' ORDER BY address""").fetchall()248 uniq: dict[str, dict] = {}249 for r in rows:250 k = norm_key(r["address"], r["city"])251 if not k or k.startswith("ville "):252 continue253 u = uniq.setdefault(k, {"address": r["address"], "city": r["city"],254 "members": []})255 u["members"].append(r["uid"])256 pending = []257 for k, u in uniq.items():258 c = con.execute("SELECT lat,lng,failed FROM geocode_cache WHERE address=?",259 (k,)).fetchone()260 if c is not None and not c["failed"]:261 for uid in u["members"]:262 con.execute("UPDATE jobs SET lat=?, lng=? WHERE uid=?",263 (c["lat"], c["lng"], uid))264 continue265 if c is not None and c["failed"]:266 continue267 pending.append((k, u))268 con.commit()269 if limit is not None:270 pending = pending[:limit]271272 session = requests.Session()273 session.headers["User-Agent"] = USER_AGENT274 done = failed = 0275 for i in range(0, len(pending), BATCH_SIZE):276 chunk = pending[i:i + BATCH_SIZE]277 records = {"records": [278 {"attributes": {"OBJECTID": j,279 "SingleLine": f"{geo._clean(u['address']).split(',')[0].strip()}, "280 f"{(u['city'] or 'Québec').strip()}"}}281 for j, (_k, u) in enumerate(chunk)]}282 try:283 resp = session.post(AQ_BATCH_URL, data={284 "addresses": _json.dumps(records, ensure_ascii=False),285 "f": "json", "outSR": 4326}, timeout=90)286 locs = resp.json().get("locations", [])287 except Exception as e:288 print(f"[job-ka] geocode-batch lot {i//BATCH_SIZE} ERREUR: {str(e)[:80]}")289 time.sleep(1.0)290 continue291 by_id = {l["attributes"].get("ResultID"): l for l in locs}292 for j, (k, u) in enumerate(chunk):293 loc = by_id.get(j)294 coords = None295 if loc and loc["attributes"].get("Score", 0) >= AQ_MIN_SCORE:296 lc = loc.get("location") or {}297 try:298 cand = (float(lc["y"]), float(lc["x"]))299 if _in_bbox(*cand):300 coords = cand301 except (KeyError, ValueError, TypeError):302 coords = None303 if coords:304 for uid in u["members"]:305 con.execute("UPDATE jobs SET lat=?, lng=? WHERE uid=?",306 (coords[0], coords[1], uid))307 con.execute(308 "INSERT INTO geocode_cache (address,lat,lng,provider,failed,ts)"309 " VALUES (?,?,?,?,0,?) ON CONFLICT(address) DO UPDATE SET"310 " lat=excluded.lat, lng=excluded.lng, provider=excluded.provider,"311 " failed=0, ts=excluded.ts",312 (k, coords[0], coords[1], "adresses_quebec_batch", time.time()))313 done += len(u["members"])314 else:315 # l'adresse a échoué : retomber sur le centroïde de ville plutôt316 # que d'abandonner (l'offre reste cartographiable)317 cv = geo.resolve("", u["city"])318 if cv:319 for uid in u["members"]:320 con.execute("UPDATE jobs SET lat=?, lng=? WHERE uid=?",321 (cv[0], cv[1], uid))322 done += len(u["members"])323 else:324 for uid in u["members"]:325 con.execute("UPDATE jobs SET geocode_failed=1 WHERE uid=?",326 (uid,))327 failed += len(u["members"])328 con.execute(329 "INSERT INTO geocode_cache (address,lat,lng,provider,failed,ts)"330 " VALUES (?,?,?,?,1,?) ON CONFLICT(address) DO UPDATE SET"331 " failed=1, ts=excluded.ts",332 (k, None, None, "adresses_quebec_batch", time.time()))333 con.commit()334 print(f"[job-ka] geocode-batch {i+len(chunk)}/{len(pending)} "335 f"(résolues {done}, échecs {failed})")336337 con.close()338 # offres sans adresse : centroïdes de ville en mode 1-par-1 (cache partagé)339 stats_villes = run()340 stats = {"geocoded": done + stats_villes["geocoded"],341 "failed": failed + stats_villes["failed"],342 "batches": (len(pending) + BATCH_SIZE - 1) // BATCH_SIZE}343 print(f"[job-ka] geocode-batch {stats}")344 return stats345