spb/ora-ka Public

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 json15import sqlite31617import re18import time1920import requests2122from . import db23from .normalize import strip_accents2425NOMINATIM_URL = "https://nominatim.openstreetmap.org/search"26# Repli officiel du gouvernement du Québec (Adresses Québec / MERN) : couvre27# les rues trop récentes pour OpenStreetMap (développements neufs de Lévis…)28AQ_URL = ("https://servicescarto.mern.gouv.qc.ca/pes/rest/services/Territoire/"29          "Adresse_Geocodage/GeocodeServer/findAddressCandidates")30AQ_MIN_SCORE = 7531# ASCII pur : le serveur ArcGIS d'Adresses Québec retourne 500 si l'en-tête32# User-Agent contient des accents (latin-1)33USER_AGENT = "ImmoKaBot/1.0 (agregateur logements Quebec; +contact@spboucher.ai)"34REQUEST_DELAY = 1.1          # règle Nominatim : max 1 req/s35REQUEST_DELAY_AQ = 0.12      # Adresses Québec (MERN) : pas de limite stricte36RETRY_FAILED_AFTER = 30 * 86400   # re-tenter les échecs après 30 jours3738# Bounding boxes (lat_min, lat_max, lng_min, lng_max)39_BBOX_QUEBEC = (46.55, 47.10, -71.75, -70.85)     # Québec, Lévis, St-Augustin40# garde-fou provincial : couvre Gatineau, l'Abitibi, le Saguenay, la Côte-Nord41# et la Gaspésie (expansion provinciale) — même boîte que schema.finalize()42_BBOX_SUD_QC = (44.50, 63.00, -80.00, -56.00)4344_VILLES_QUEBEC = {"quebec", "levis", "saint-augustin-de-desmaures",45                  "l'ancienne-lorette", "ancienne-lorette"}464748def _bbox_for(city: str) -> tuple[float, float, float, float]:49    key = strip_accents((city or "").strip().lower())50    return _BBOX_QUEBEC if key in _VILLES_QUEBEC else _BBOX_SUD_QC515253def _in_bbox(lat: float, lng: float, bbox: tuple) -> bool:54    return bbox[0] <= lat <= bbox[1] and bbox[2] <= lng <= bbox[3]555657def norm_key(address: str) -> str:58    """Clé de cache : adresse normalisée (casse, accents, espaces, n° d'app.)."""59    s = strip_accents((address or "").lower())60    s = re.sub(r"\b(?:app?t?|appartement|unite|suite|#)\.?\s*[\w-]+\b", " ", s)61    s = re.sub(r"[^a-z0-9]+", " ", s)62    return re.sub(r"\s+", " ", s).strip()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.strip()76        # « 101-2905 Rue X » = unité 101, civique 2905 -> garder le civique77        s = re.sub(r"^(\d+)-(\d+)\s", r"\2 ", s)78        # « 6275 et 6375 boulevard X » -> premier civique79        s = re.sub(r"^(\d+)\s+et\s+\d+\s", r"\1 ", s)80        # « Montréal - Laval » / « Montréal - Île-des-Soeurs » -> garder le vrai lieu81        s = re.sub(r"montr[ée]al\s*-\s*", "", s, flags=re.I)82        # « bureau 105 » / « suite 3 » / « local B » : suffixes de bureau83        s = re.sub(r",?\s*(?:bureau|suite|local|app\.?|apt\.?)\s*[\w-]+\b", "", s, flags=re.I)84        # « Vanier (Québec) » -> « Vanier, Québec »85        s = re.sub(r"\s*\((qu[ée]bec)\)", r", \1", s, flags=re.I)86        # abréviations cardinales : « Rue Salaberry O » -> « Ouest »87        s = re.sub(r"\bO\.?(?=,|\s*$)", "Ouest", s)88        s = re.sub(r"\bE\.?(?=,|\s*$)", "Est", s)89        return s9091    def _build_query(self, address: str, city: str) -> str:92        q = self._clean(address)93        key = strip_accents(q.lower())94        if city and strip_accents(city.lower()) not in key:95            q += f", {city}"96        if "quebec" not in strip_accents(q.lower()) and "qc" not in q.lower():97            q += ", Québec"98        return q + ", Canada"99100    def _query_nominatim(self, params: dict) -> tuple[float, float] | None:101        """Une requête Nominatim, throttlée."""102        wait = REQUEST_DELAY - (time.time() - self._last)103        if wait > 0:104            time.sleep(wait)105        try:106            resp = self.session.get(NOMINATIM_URL, params={107                "format": "jsonv2", "limit": 1, "countrycodes": "ca", **params,108            }, timeout=20)109            self._last = time.time()110            resp.raise_for_status()111            hits = resp.json()112        except Exception:113            self._last = time.time()114            return None115        if not hits:116            return None117        try:118            return float(hits[0]["lat"]), float(hits[0]["lon"])119        except (KeyError, ValueError):120            return None121122    def _query_adresses_quebec(self, address: str, city: str) -> tuple[float, float] | None:123        """Repli : géocodeur officiel Adresses Québec (MERN, ArcGIS).124125        Couvre les rues trop récentes pour OSM. Seuil de score AQ_MIN_SCORE126        pour éviter les correspondances approximatives sur une autre rue.127        """128        premier = self._clean(address).split(",")[0].strip()129        ville = (city or "Québec").strip()130        wait = REQUEST_DELAY_AQ - (time.time() - self._last)131        if wait > 0:132            time.sleep(wait)133        try:134            resp = self.session.get(AQ_URL, params={135                "SingleLine": f"{premier}, {ville}",136                "f": "json", "outSR": 4326, "maxLocations": 1,137            }, timeout=20)138            self._last = time.time()139            resp.raise_for_status()140            cands = resp.json().get("candidates") or []141        except Exception:142            self._last = time.time()143            return None144        if not cands or cands[0].get("score", 0) < AQ_MIN_SCORE:145            return None146        loc = cands[0].get("location") or {}147        try:148            return float(loc["y"]), float(loc["x"])149        except (KeyError, ValueError):150            return None151152    def _attempts(self, address: str, city: str) -> list[dict]:153        """Stratégies de requête, de la plus précise à la moins précise.154155        1) structurée civique+rue (évite l'ambiguïté « Québec » ville/province)156        2) recherche libre complète157        3) structurée rue seule -> centroïde de rue (repli acceptable pour la158           carte quand le numéro civique est absent d'OpenStreetMap)159        """160        premier = self._clean(address).split(",")[0].strip()161        ville = (city or "").strip()162        if not ville:163            k = strip_accents(address.lower())164            ville = "Lévis" if "levis" in k else "Québec"165        commun = {"city": ville, "state": "Québec", "country": "Canada"}166167        tries: list[dict] = []168        m = re.match(r"^(\d+)[,\s]+(.{4,})$", premier)169        if m:170            tries.append({"street": f"{m.group(1)} {m.group(2)}", **commun})171        tries.append({"q": self._build_query(address, city)})172        if m:173            tries.append({"street": m.group(2), **commun})174        return tries175176    def resolve(self, address: str, city: str) -> tuple[float, float] | None:177        """Adresse -> (lat, lng), via cache puis Nominatim. None si introuvable."""178        key = norm_key(address)179        if not key or len(key) < 6:180            return None181        row = self.con.execute(182            "SELECT lat, lng, failed, ts FROM geocode_cache WHERE address=?",183            (key,)).fetchone()184        if row is not None:185            if not row["failed"]:186                return (row["lat"], row["lng"])187            if time.time() - (row["ts"] or 0) < RETRY_FAILED_AFTER:188                return None   # échec récent : ne pas marteler l'API189190        bbox = _bbox_for(city)191        coords = None192        # Adresses Québec (MERN) d'abord : rapide, autoritatif au Québec, pas de193        # limite stricte. Nominatim (1 req/s) seulement en repli.194        provider = "adresses_quebec"195        c = self._query_adresses_quebec(address, city)196        if c is not None and _in_bbox(*c, bbox):197            coords = c198        if coords is None:199            provider = "nominatim"200            for params in self._attempts(address, city):201                c = self._query_nominatim(params)202                if c is not None and _in_bbox(*c, bbox):203                    coords = c204                    break205        ok = coords is not None206        self.con.execute(207            "INSERT INTO geocode_cache (address, lat, lng, provider, failed, ts)"208            " VALUES (?,?,?,?,?,?)"209            " ON CONFLICT(address) DO UPDATE SET lat=excluded.lat,"210            " lng=excluded.lng, provider=excluded.provider,"211            " failed=excluded.failed, ts=excluded.ts",212            (key, coords[0] if ok else None, coords[1] if ok else None,213             provider, 0 if ok else 1, time.time()))214        self.con.commit()215        return coords if ok else None216217218def run(limit: int | None = None) -> dict:219    """Géocode les annonces actives sans coordonnées, par adresse unique.220221    `limit` borne le nombre de NOUVELLES requêtes Nominatim (les hits de222    cache sont gratuits et toujours appliqués).223    """224    con = db.connect()225    geo = Geocoder(con)226    rows = con.execute(227        """SELECT uid, address, city FROM listings228           WHERE active=1 AND lat IS NULL AND address<>'' AND geocode_failed=0229             AND dup_hidden=0230           ORDER BY address""").fetchall()231232    # regrouper par immeuble (adresse normalisée)233    groupes: dict[str, list] = {}234    for r in rows:235        groupes.setdefault(norm_key(r["address"]), []).append(r)236237    done = failed = requests_made = 0238    for key, members in groupes.items():239        if not key:240            continue241        cached = con.execute(242            "SELECT failed FROM geocode_cache WHERE address=?", (key,)).fetchone()243        if cached is None:244            if limit is not None and requests_made >= limit:245                continue246            requests_made += 1247        try:248            coords = geo.resolve(members[0]["address"], members[0]["city"])249            if coords:250                for r in members:251                    con.execute("UPDATE listings SET lat=?, lng=? WHERE uid=?",252                                (coords[0], coords[1], r["uid"]))253                done += len(members)254            else:255                # introuvable/hors zone : flag ; jamais de coordonnées bidon256                for r in members:257                    con.execute("UPDATE listings SET geocode_failed=1 WHERE uid=?",258                                (r["uid"],))259                failed += len(members)260            con.commit()261        except sqlite3.OperationalError:262            # verrou transitoire (watcher/refresh_dedup) : on saute cette adresse,263            # elle sera reprise au prochain passage — jamais crasher tout le run.264            try:265                con.rollback()266            except sqlite3.Error:267                pass268            time.sleep(1.0)269            continue270271    con.close()272    stats = {"geocoded": done, "failed": failed,273             "unique_addresses": len(groupes), "api_requests": requests_made}274    print(f"[immo-ka] geocode {stats}")275    return stats276277278AQ_BATCH_URL = ("https://servicescarto.mern.gouv.qc.ca/pes/rest/services/Territoire/"279                "Adresse_Geocodage/GeocodeServer/geocodeAddresses")280BATCH_SIZE = 200281282283def run_batch(limit: int | None = None) -> dict:284    """Géocodage EN LOT via Adresses Québec (`geocodeAddresses`, jusqu'à 1000285    adresses/requête) — ~35 requêtes pour tout le parc au lieu de dizaines de286    milliers. Beaucoup plus rapide que le mode 1-par-1 (et que Nominatim)."""287    con = db.connect()288    geo = Geocoder(con)     # pour réutiliser _clean / bbox289    rows = con.execute(290        """SELECT uid, address, city FROM listings291           WHERE active=1 AND lat IS NULL AND address<>'' AND geocode_failed=0292             AND dup_hidden=0 ORDER BY address""").fetchall()293    # 1 entrée par immeuble (adresse normalisée) ; on saute les échecs en cache294    uniq: dict[str, dict] = {}295    for r in rows:296        k = norm_key(r["address"])297        if not k or len(k) < 6:298            continue299        u = uniq.setdefault(k, {"address": r["address"], "city": r["city"],300                                "members": []})301        u["members"].append(r["uid"])302    pending = []303    for k, u in uniq.items():304        c = con.execute("SELECT lat,lng,failed FROM geocode_cache WHERE address=?",305                        (k,)).fetchone()306        if c is not None and not c["failed"]:      # déjà résolu : appliquer direct307            for uid in u["members"]:308                con.execute("UPDATE listings SET lat=?, lng=? WHERE uid=?",309                            (c["lat"], c["lng"], uid))310            continue311        if c is not None and c["failed"]:312            continue313        pending.append((k, u))314    con.commit()315    if limit is not None:316        pending = pending[:limit]317318    session = requests.Session()319    session.headers["User-Agent"] = USER_AGENT320    done = failed = 0321    for i in range(0, len(pending), BATCH_SIZE):322        chunk = pending[i:i + BATCH_SIZE]323        records = {"records": [324            {"attributes": {"OBJECTID": j,325                            "SingleLine": f"{geo._clean(u['address']).split(',')[0].strip()}, "326                                          f"{(u['city'] or 'Québec').strip()}"}}327            for j, (_k, u) in enumerate(chunk)]}328        try:329            # POST obligatoire : le param `addresses` (JSON de N records) est trop330            # long pour une URL GET dès quelques dizaines d'adresses.331            resp = session.post(AQ_BATCH_URL, data={332                "addresses": json.dumps(records, ensure_ascii=False),333                "f": "json", "outSR": 4326}, timeout=90)334            locs = resp.json().get("locations", [])335        except Exception as e:336            print(f"[immo-ka] geocode-batch lot {i//BATCH_SIZE} ERREUR: {str(e)[:80]}")337            time.sleep(1.0)338            continue339        by_id = {l["attributes"].get("ResultID"): l for l in locs}340        for j, (k, u) in enumerate(chunk):341            loc = by_id.get(j)342            coords = None343            if loc and loc["attributes"].get("Score", 0) >= AQ_MIN_SCORE:344                lc = loc.get("location") or {}345                try:346                    cand = (float(lc["y"]), float(lc["x"]))347                    if _in_bbox(*cand, _bbox_for(u["city"])):348                        coords = cand349                except (KeyError, ValueError, TypeError):350                    coords = None351            try:352                if coords:353                    for uid in u["members"]:354                        con.execute("UPDATE listings SET lat=?, lng=? WHERE uid=?",355                                    (coords[0], coords[1], uid))356                    con.execute(357                        "INSERT INTO geocode_cache (address,lat,lng,provider,failed,ts)"358                        " VALUES (?,?,?,?,0,?) ON CONFLICT(address) DO UPDATE SET"359                        " lat=excluded.lat, lng=excluded.lng, provider=excluded.provider,"360                        " failed=0, ts=excluded.ts",361                        (k, coords[0], coords[1], "adresses_quebec_batch", time.time()))362                    done += len(u["members"])363                else:364                    for uid in u["members"]:365                        con.execute("UPDATE listings SET geocode_failed=1 WHERE uid=?", (uid,))366                    con.execute(367                        "INSERT INTO geocode_cache (address,lat,lng,provider,failed,ts)"368                        " VALUES (?,?,?,?,1,?) ON CONFLICT(address) DO UPDATE SET"369                        " failed=1, ts=excluded.ts",370                        (k, None, None, "adresses_quebec_batch", time.time()))371                    failed += len(u["members"])372                con.commit()373            except sqlite3.OperationalError:374                try: con.rollback()375                except sqlite3.Error: pass376                time.sleep(1.0)377        print(f"[immo-ka] geocode-batch {i+len(chunk)}/{len(pending)} "378              f"(résolues {done}, échecs {failed})")379    con.close()380    stats = {"geocoded": done, "failed": failed, "batches": (len(pending)+BATCH_SIZE-1)//BATCH_SIZE}381    print(f"[immo-ka] geocode-batch {stats}")382    return stats383