spb/immo-ka

1# -----------------------------------------------------------------------------2# Immo-Ka — Agrégateur de propriétés à vendre (province de Québec)3# Auteur : Simon-Pierre Boucher — contact@spboucher.ai4# geocode.py : géocodage des adresses via Nominatim (OpenStreetMap)5#   - cache persistant (table geocode_cache) : une adresse n'est géocodée6#     qu'une seule fois, et par immeuble (adresse normalisée), pas par annonce7#   - politesse : 1 requête/seconde max, User-Agent identifiable8#   - validation : les coordonnées doivent tomber dans la région attendue9#     (bounding box par ville), sinon flag geocode_failed — jamais de10#     coordonnées bidon11# -----------------------------------------------------------------------------12from __future__ import annotations1314import sqlite31516import re17import time1819import requests2021from . import db22from .normalize import strip_accents2324NOMINATIM_URL = "https://nominatim.openstreetmap.org/search"25# Repli officiel du gouvernement du Québec (Adresses Québec / MERN) : couvre26# les rues trop récentes pour OpenStreetMap (développements neufs de Lévis…)27AQ_URL = ("https://servicescarto.mern.gouv.qc.ca/pes/rest/services/Territoire/"28          "Adresse_Geocodage/GeocodeServer/findAddressCandidates")29AQ_MIN_SCORE = 7530# ASCII pur : le serveur ArcGIS d'Adresses Québec retourne 500 si l'en-tête31# User-Agent contient des accents (latin-1)32USER_AGENT = "ImmoKaBot/1.0 (agregateur logements Quebec; +contact@spboucher.ai)"33REQUEST_DELAY = 1.1          # règle Nominatim : max 1 req/s34REQUEST_DELAY_AQ = 0.12      # Adresses Québec (MERN) : pas de limite stricte35RETRY_FAILED_AFTER = 30 * 86400   # re-tenter les échecs après 30 jours3637# Bounding boxes (lat_min, lat_max, lng_min, lng_max)38_BBOX_QUEBEC = (46.55, 47.10, -71.75, -70.85)     # Québec, Lévis, St-Augustin39# garde-fou provincial : couvre Gatineau, l'Abitibi, le Saguenay, la Côte-Nord40# et la Gaspésie (expansion provinciale) — même boîte que schema.finalize()41_BBOX_SUD_QC = (44.50, 63.00, -80.00, -56.00)4243_VILLES_QUEBEC = {"quebec", "levis", "saint-augustin-de-desmaures",44                  "l'ancienne-lorette", "ancienne-lorette"}454647def _bbox_for(city: str) -> tuple[float, float, float, float]:48    key = strip_accents((city or "").strip().lower())49    return _BBOX_QUEBEC if key in _VILLES_QUEBEC else _BBOX_SUD_QC505152def _in_bbox(lat: float, lng: float, bbox: tuple) -> bool:53    return bbox[0] <= lat <= bbox[1] and bbox[2] <= lng <= bbox[3]545556def norm_key(address: str) -> str:57    """Clé de cache : adresse normalisée (casse, accents, espaces, n° d'app.)."""58    s = strip_accents((address or "").lower())59    s = re.sub(r"\b(?:app?t?|appartement|unite|suite|#)\.?\s*[\w-]+\b", " ", s)60    s = re.sub(r"[^a-z0-9]+", " ", s)61    return re.sub(r"\s+", " ", s).strip()626364class Geocoder:65    def __init__(self, con) -> None:66        self.con = con67        self.session = requests.Session()68        self.session.headers["User-Agent"] = USER_AGENT69        self._last = 0.07071    @staticmethod72    def _clean(address: str) -> str:73        """Nettoyages qui aident Nominatim sur les adresses québécoises."""74        s = address.strip()75        # « 101-2905 Rue X » = unité 101, civique 2905 -> garder le civique76        s = re.sub(r"^(\d+)-(\d+)\s", r"\2 ", s)77        # « 6275 et 6375 boulevard X » -> premier civique78        s = re.sub(r"^(\d+)\s+et\s+\d+\s", r"\1 ", s)79        # « Montréal - Laval » / « Montréal - Île-des-Soeurs » -> garder le vrai lieu80        s = re.sub(r"montr[ée]al\s*-\s*", "", s, flags=re.I)81        # « bureau 105 » / « suite 3 » / « local B » : suffixes de bureau82        s = re.sub(r",?\s*(?:bureau|suite|local|app\.?|apt\.?)\s*[\w-]+\b", "", s, flags=re.I)83        # « Vanier (Québec) » -> « Vanier, Québec »84        s = re.sub(r"\s*\((qu[ée]bec)\)", r", \1", s, flags=re.I)85        # abréviations cardinales : « Rue Salaberry O » -> « Ouest »86        s = re.sub(r"\bO\.?(?=,|\s*$)", "Ouest", s)87        s = re.sub(r"\bE\.?(?=,|\s*$)", "Est", s)88        return s8990    def _build_query(self, address: str, city: str) -> str:91        q = self._clean(address)92        key = strip_accents(q.lower())93        if city and strip_accents(city.lower()) not in key:94            q += f", {city}"95        if "quebec" not in strip_accents(q.lower()) and "qc" not in q.lower():96            q += ", Québec"97        return q + ", Canada"9899    def _query_nominatim(self, params: dict) -> tuple[float, float] | None:100        """Une requête Nominatim, throttlée."""101        wait = REQUEST_DELAY - (time.time() - self._last)102        if wait > 0:103            time.sleep(wait)104        try:105            resp = self.session.get(NOMINATIM_URL, params={106                "format": "jsonv2", "limit": 1, "countrycodes": "ca", **params,107            }, timeout=20)108            self._last = time.time()109            resp.raise_for_status()110            hits = resp.json()111        except Exception:112            self._last = time.time()113            return None114        if not hits:115            return None116        try:117            return float(hits[0]["lat"]), float(hits[0]["lon"])118        except (KeyError, ValueError):119            return None120121    def _query_adresses_quebec(self, address: str, city: str) -> tuple[float, float] | None:122        """Repli : géocodeur officiel Adresses Québec (MERN, ArcGIS).123124        Couvre les rues trop récentes pour OSM. Seuil de score AQ_MIN_SCORE125        pour éviter les correspondances approximatives sur une autre rue.126        """127        premier = self._clean(address).split(",")[0].strip()128        ville = (city or "Québec").strip()129        wait = REQUEST_DELAY_AQ - (time.time() - self._last)130        if wait > 0:131            time.sleep(wait)132        try:133            resp = self.session.get(AQ_URL, params={134                "SingleLine": f"{premier}, {ville}",135                "f": "json", "outSR": 4326, "maxLocations": 1,136            }, timeout=20)137            self._last = time.time()138            resp.raise_for_status()139            cands = resp.json().get("candidates") or []140        except Exception:141            self._last = time.time()142            return None143        if not cands or cands[0].get("score", 0) < AQ_MIN_SCORE:144            return None145        loc = cands[0].get("location") or {}146        try:147            return float(loc["y"]), float(loc["x"])148        except (KeyError, ValueError):149            return None150151    def _attempts(self, address: str, city: str) -> list[dict]:152        """Stratégies de requête, de la plus précise à la moins précise.153154        1) structurée civique+rue (évite l'ambiguïté « Québec » ville/province)155        2) recherche libre complète156        3) structurée rue seule -> centroïde de rue (repli acceptable pour la157           carte quand le numéro civique est absent d'OpenStreetMap)158        """159        premier = self._clean(address).split(",")[0].strip()160        ville = (city or "").strip()161        if not ville:162            k = strip_accents(address.lower())163            ville = "Lévis" if "levis" in k else "Québec"164        commun = {"city": ville, "state": "Québec", "country": "Canada"}165166        tries: list[dict] = []167        m = re.match(r"^(\d+)[,\s]+(.{4,})$", premier)168        if m:169            tries.append({"street": f"{m.group(1)} {m.group(2)}", **commun})170        tries.append({"q": self._build_query(address, city)})171        if m:172            tries.append({"street": m.group(2), **commun})173        return tries174175    def resolve(self, address: str, city: str) -> tuple[float, float] | None:176        """Adresse -> (lat, lng), via cache puis Nominatim. None si introuvable."""177        key = norm_key(address)178        if not key or len(key) < 6:179            return None180        row = self.con.execute(181            "SELECT lat, lng, failed, ts FROM geocode_cache WHERE address=?",182            (key,)).fetchone()183        if row is not None:184            if not row["failed"]:185                return (row["lat"], row["lng"])186            if time.time() - (row["ts"] or 0) < RETRY_FAILED_AFTER:187                return None   # échec récent : ne pas marteler l'API188189        bbox = _bbox_for(city)190        coords = None191        # Adresses Québec (MERN) d'abord : rapide, autoritatif au Québec, pas de192        # limite stricte. Nominatim (1 req/s) seulement en repli.193        provider = "adresses_quebec"194        c = self._query_adresses_quebec(address, city)195        if c is not None and _in_bbox(*c, bbox):196            coords = c197        if coords is None:198            provider = "nominatim"199            for params in self._attempts(address, city):200                c = self._query_nominatim(params)201                if c is not None and _in_bbox(*c, bbox):202                    coords = c203                    break204        ok = coords is not None205        self.con.execute(206            "INSERT INTO geocode_cache (address, lat, lng, provider, failed, ts)"207            " VALUES (?,?,?,?,?,?)"208            " ON CONFLICT(address) DO UPDATE SET lat=excluded.lat,"209            " lng=excluded.lng, provider=excluded.provider,"210            " failed=excluded.failed, ts=excluded.ts",211            (key, coords[0] if ok else None, coords[1] if ok else None,212             provider, 0 if ok else 1, time.time()))213        self.con.commit()214        return coords if ok else None215216217def run(limit: int | None = None) -> dict:218    """Géocode les annonces actives sans coordonnées, par adresse unique.219220    `limit` borne le nombre de NOUVELLES requêtes Nominatim (les hits de221    cache sont gratuits et toujours appliqués).222    """223    con = db.connect()224    geo = Geocoder(con)225    rows = con.execute(226        """SELECT uid, address, city FROM listings227           WHERE active=1 AND lat IS NULL AND address<>'' AND geocode_failed=0228             AND dup_hidden=0229           ORDER BY address""").fetchall()230231    # regrouper par immeuble (adresse normalisée)232    groupes: dict[str, list] = {}233    for r in rows:234        groupes.setdefault(norm_key(r["address"]), []).append(r)235236    done = failed = requests_made = 0237    for key, members in groupes.items():238        if not key:239            continue240        cached = con.execute(241            "SELECT failed FROM geocode_cache WHERE address=?", (key,)).fetchone()242        if cached is None:243            if limit is not None and requests_made >= limit:244                continue245            requests_made += 1246        try:247            coords = geo.resolve(members[0]["address"], members[0]["city"])248            if coords:249                for r in members:250                    con.execute("UPDATE listings SET lat=?, lng=? WHERE uid=?",251                                (coords[0], coords[1], r["uid"]))252                done += len(members)253            else:254                # introuvable/hors zone : flag ; jamais de coordonnées bidon255                for r in members:256                    con.execute("UPDATE listings SET geocode_failed=1 WHERE uid=?",257                                (r["uid"],))258                failed += len(members)259            con.commit()260        except sqlite3.OperationalError:261            # verrou transitoire (watcher/refresh_dedup) : on saute cette adresse,262            # elle sera reprise au prochain passage — jamais crasher tout le run.263            try:264                con.rollback()265            except sqlite3.Error:266                pass267            time.sleep(1.0)268            continue269270    con.close()271    stats = {"geocoded": done, "failed": failed,272             "unique_addresses": len(groupes), "api_requests": requests_made}273    print(f"[immo-ka] geocode {stats}")274    return stats275276277AQ_BATCH_URL = ("https://servicescarto.mern.gouv.qc.ca/pes/rest/services/Territoire/"278                "Adresse_Geocodage/GeocodeServer/geocodeAddresses")279BATCH_SIZE = 200280281282def run_batch(limit: int | None = None) -> dict:283    """Géocodage EN LOT via Adresses Québec (`geocodeAddresses`, jusqu'à 1000284    adresses/requête) — ~35 requêtes pour tout le parc au lieu de dizaines de285    milliers. Beaucoup plus rapide que le mode 1-par-1 (et que Nominatim)."""286    con = db.connect()287    geo = Geocoder(con)     # pour réutiliser _clean / bbox288    rows = con.execute(289        """SELECT uid, address, city FROM listings290           WHERE active=1 AND lat IS NULL AND address<>'' AND geocode_failed=0291             AND dup_hidden=0 ORDER BY address""").fetchall()292    # 1 entrée par immeuble (adresse normalisée) ; on saute les échecs en cache293    uniq: dict[str, dict] = {}294    for r in rows:295        k = norm_key(r["address"])296        if not k or len(k) < 6:297            continue298        u = uniq.setdefault(k, {"address": r["address"], "city": r["city"],299                                "members": []})300        u["members"].append(r["uid"])301    pending = []302    for k, u in uniq.items():303        c = con.execute("SELECT lat,lng,failed FROM geocode_cache WHERE address=?",304                        (k,)).fetchone()305        if c is not None and not c["failed"]:      # déjà résolu : appliquer direct306            for uid in u["members"]:307                con.execute("UPDATE listings SET lat=?, lng=? WHERE uid=?",308                            (c["lat"], c["lng"], uid))309            continue310        if c is not None and c["failed"]:311            continue312        pending.append((k, u))313    con.commit()314    if limit is not None:315        pending = pending[:limit]316317    session = requests.Session()318    session.headers["User-Agent"] = USER_AGENT319    done = failed = 0320    for i in range(0, len(pending), BATCH_SIZE):321        chunk = pending[i:i + BATCH_SIZE]322        records = {"records": [323            {"attributes": {"OBJECTID": j,324                            "SingleLine": f"{geo._clean(u['address']).split(',')[0].strip()}, "325                                          f"{(u['city'] or 'Québec').strip()}"}}326            for j, (_k, u) in enumerate(chunk)]}327        try:328            resp = session.get(AQ_BATCH_URL, params={329                "addresses": json.dumps(records, ensure_ascii=False),330                "f": "json", "outSR": 4326}, timeout=60)331            locs = resp.json().get("locations", [])332        except Exception:333            time.sleep(1.0)334            continue335        by_id = {l["attributes"].get("ResultID"): l for l in locs}336        for j, (k, u) in enumerate(chunk):337            loc = by_id.get(j)338            coords = None339            if loc and loc["attributes"].get("Score", 0) >= AQ_MIN_SCORE:340                lc = loc.get("location") or {}341                try:342                    cand = (float(lc["y"]), float(lc["x"]))343                    if _in_bbox(*cand, _bbox_for(u["city"])):344                        coords = cand345                except (KeyError, ValueError, TypeError):346                    coords = None347            try:348                if coords:349                    for uid in u["members"]:350                        con.execute("UPDATE listings SET lat=?, lng=? WHERE uid=?",351                                    (coords[0], coords[1], uid))352                    con.execute(353                        "INSERT INTO geocode_cache (address,lat,lng,provider,failed,ts)"354                        " VALUES (?,?,?,?,0,?) ON CONFLICT(address) DO UPDATE SET"355                        " lat=excluded.lat, lng=excluded.lng, provider=excluded.provider,"356                        " failed=0, ts=excluded.ts",357                        (k, coords[0], coords[1], "adresses_quebec_batch", time.time()))358                    done += len(u["members"])359                else:360                    for uid in u["members"]:361                        con.execute("UPDATE listings SET geocode_failed=1 WHERE uid=?", (uid,))362                    con.execute(363                        "INSERT INTO geocode_cache (address,lat,lng,provider,failed,ts)"364                        " VALUES (?,?,?,?,1,?) ON CONFLICT(address) DO UPDATE SET"365                        " failed=1, ts=excluded.ts",366                        (k, None, None, "adresses_quebec_batch", time.time()))367                    failed += len(u["members"])368                con.commit()369            except sqlite3.OperationalError:370                try: con.rollback()371                except sqlite3.Error: pass372                time.sleep(1.0)373        print(f"[immo-ka] geocode-batch {i+len(chunk)}/{len(pending)} "374              f"(résolues {done}, échecs {failed})")375    con.close()376    stats = {"geocoded": done, "failed": failed, "batches": (len(pending)+BATCH_SIZE-1)//BATCH_SIZE}377    print(f"[immo-ka] geocode-batch {stats}")378    return stats379