spb/lou-ka Public

1# -----------------------------------------------------------------------------2# Lou-Ka — Agrégateur de logements à louer (province de Québec)3# Auteur : Simon-Pierre Boucher — contact@spboucher.ai4# quartier.py : statistiques de quartier par annonce (à la Centris, en libre)5#   Base statique data/quartier.db construite par scripts/build_*.py :6#     - da_poly / da_stats : aires de diffusion 2021 + profil du recensement7#     - da_pmd : mesures de proximité StatCan (scores 0..1)8#     - da_defav : défavorisation matérielle/sociale INSPQ (quintiles)9#     - heat : classe d'îlot de chaleur/fraîcheur INSPQ par immeuble10#     - crime_mtl / igc : actes criminels SPVM (points) + indice de gravité11#   Jointure : lat/lng -> DAUID par point-dans-polygone local (préfiltre bbox),12#   mémorisée dans listings.dauid à l'enrichissement (boucle watch).13# -----------------------------------------------------------------------------14from __future__ import annotations1516import json17import math18import sqlite319import time20from pathlib import Path2122from . import db2324QUARTIER_DB = Path(__file__).resolve().parent.parent / "data" / "quartier.db"2526# villes couvertes par les points SPVM (agglomération de Montréal)27_VILLES_SPVM = {"montreal", "montreal-est", "montreal-ouest", "westmount",28                "cote saint-luc", "cote-saint-luc", "hampstead", "mont-royal",29                "outremont", "verdun", "lasalle", "lachine", "anjou",30                "saint-leonard", "saint-laurent", "ahuntsic", "dorval",31                "pointe-claire", "kirkland", "beaconsfield", "dollard-des-ormeaux"}3233# correspondance ville -> fragment du nom de service dans la table igc34_IGC_SERVICE = {35    "quebec": "SPVQ", "levis": "Lévis", "montreal": "SPVM",36    "laval": "Laval", "longueuil": "Longueuil",37}383940def disponible() -> bool:41    return QUARTIER_DB.exists()424344def _connect() -> sqlite3.Connection:45    con = sqlite3.connect(f"file:{QUARTIER_DB}?mode=ro", uri=True)46    con.row_factory = sqlite3.Row47    return con484950# ---------------------------------------------------------------------------51# lat/lng -> DAUID (point dans polygone, préfiltre bbox)52# ---------------------------------------------------------------------------5354def _dans_anneau(lat: float, lng: float, anneau: list) -> bool:55    """Lancer de rayon (even-odd). anneau = [[lng, lat], ...]."""56    dedans = False57    n = len(anneau)58    j = n - 159    for i in range(n):60        xi, yi = anneau[i][0], anneau[i][1]61        xj, yj = anneau[j][0], anneau[j][1]62        if (yi > lat) != (yj > lat) and \63                lng < (xj - xi) * (lat - yi) / (yj - yi + 1e-12) + xi:64            dedans = not dedans65        j = i66    return dedans676869def dauid_for(qcon: sqlite3.Connection, lat: float, lng: float) -> str | None:70    rows = qcon.execute(71        "SELECT dauid, poly FROM da_poly WHERE lat_min<=? AND lat_max>=?"72        " AND lng_min<=? AND lng_max>=?", (lat, lat, lng, lng)).fetchall()73    for r in rows:74        anneaux = json.loads(r["poly"])75        # even-odd sur tous les anneaux (les trous annulent)76        compte = sum(1 for a in anneaux if _dans_anneau(lat, lng, a))77        if compte % 2 == 1:78            return r["dauid"]79    return None808182# ---------------------------------------------------------------------------83# Assemblage pour la fiche84# ---------------------------------------------------------------------------8586def _cle_ville(city: str) -> str:87    import unicodedata88    s = "".join(c for c in unicodedata.normalize("NFD", city or "")89                if unicodedata.category(c) != "Mn")90    return s.strip().lower()919293def _crime_mtl(qcon: sqlite3.Connection, lat: float, lng: float) -> dict | None:94    """Comptage des actes criminels SPVM à < 500 m : 12 mois vs 12 précédents."""95    dlat = 500 / 111000.096    dlng = 500 / (111000.0 * max(0.2, math.cos(math.radians(lat))))97    now = time.time()98    rows = qcon.execute(99        "SELECT lat, lng, ts FROM crime_mtl WHERE lat BETWEEN ? AND ?"100        " AND lng BETWEEN ? AND ? AND ts >= ?",101        (lat - dlat, lat + dlat, lng - dlng, lng + dlng, now - 730 * 86400)).fetchall()102    recent = avant = 0103    for r in rows:104        # distance exacte (le bbox est un carré)105        d = math.hypot((r["lat"] - lat) * 111000.0,106                       (r["lng"] - lng) * 111000.0 * math.cos(math.radians(lat)))107        if d > 500:108            continue109        if r["ts"] >= now - 365 * 86400:110            recent += 1111        else:112            avant += 1113    if recent == 0 and avant == 0:114        return None115    return {"type": "points", "rayon_m": 500, "douze_mois": recent,116            "douze_mois_precedents": avant}117118119def _crime_igc(qcon: sqlite3.Connection, city: str) -> dict | None:120    service = _IGC_SERVICE.get(_cle_ville(city))121    if not service:122        return None123    row = qcon.execute(124        "SELECT annee, indice FROM igc WHERE service LIKE '%' || ? || '%'"125        " ORDER BY annee DESC LIMIT 1", (service,)).fetchone()126    if row is None or row["indice"] is None:127        return None128    ref = qcon.execute(129        "SELECT indice FROM igc WHERE service LIKE '%canada%' AND annee=?",130        (row["annee"],)).fetchone()131    return {"type": "igc", "ville": city, "annee": row["annee"],132            "indice": round(row["indice"], 1),133            "indice_canada": round(ref["indice"], 1) if ref and ref["indice"] else None}134135136def fiche_quartier(lat: float | None, lng: float | None, city: str,137                   dauid: str | None = None) -> dict | None:138    """Bloc « Le quartier » d'une fiche. None si données indisponibles."""139    if not disponible() or lat is None or lng is None:140        return None141    qcon = _connect()142    try:143        if not dauid:144            dauid = dauid_for(qcon, lat, lng)145        out: dict = {"dauid": dauid}146147        if dauid:148            r = qcon.execute("SELECT * FROM da_stats WHERE dauid=?", (dauid,)).fetchone()149            if r:150                out["demographie"] = {k: r[k] for k in151                                      ("population", "densite", "age_median",152                                       "revenu_median", "pct_locataires",153                                       "loyer_moyen", "pct_francais", "pct_univ")}154            # rangs centiles québécois (0-100) — voir scripts/merge_quartier.py155            r = qcon.execute("SELECT * FROM da_pmd_pct WHERE dauid=?", (dauid,)).fetchone()156            if r:157                out["proximite"] = {k: r[k] / 100.0 for k in r.keys()158                                    if k != "dauid" and r[k] is not None}159            r = qcon.execute("SELECT quintile_materiel, quintile_social FROM da_defav"160                             " WHERE dauid=?", (dauid,)).fetchone()161            if r:162                out["defavorisation"] = dict(r)163164        # îlot de chaleur : coordonnée exacte, sinon la plus proche (~120 m)165        key = f"{round(lat, 4)},{round(lng, 4)}"166        r = qcon.execute("SELECT classe, ecart FROM heat WHERE coord_key=?",167                         (key,)).fetchone()168        if r is None:169            r = qcon.execute(170                "SELECT classe, ecart FROM heat WHERE coord_key LIKE ?"171                " AND classe IS NOT NULL LIMIT 1",172                (f"{round(lat, 3)}%",)).fetchone()173        if r and r["classe"] is not None:174            out["chaleur"] = {"classe": r["classe"], "ecart": r["ecart"]}175176        # criminalité : points SPVM sur l'île, indice IGC ailleurs177        crime = None178        if _cle_ville(city) in _VILLES_SPVM:179            crime = _crime_mtl(qcon, lat, lng)180        if crime is None:181            crime = _crime_igc(qcon, city)182        if crime:183            out["crime"] = crime184185        return out if len(out) > 1 else None186    except sqlite3.Error:187        return None188    finally:189        qcon.close()190191192# ---------------------------------------------------------------------------193# Enrichissement : mémoriser le DAUID de chaque annonce (boucle watch)194# ---------------------------------------------------------------------------195196def enrich(limit: int | None = None) -> dict:197    """Remplit listings.dauid pour les annonces géolocalisées qui ne l'ont pas."""198    if not disponible():199        print("[lou-ka] quartier: data/quartier.db absent — étape sautée")200        return {"enriched": 0, "missing_db": True}201    con = db.connect()202    qcon = _connect()203    rows = con.execute(204        "SELECT uid, lat, lng FROM listings WHERE active=1 AND lat IS NOT NULL"205        " AND (dauid IS NULL OR dauid='')").fetchall()206    if limit is not None:207        rows = rows[:limit]208    done = introuvable = 0209    for r in rows:210        d = dauid_for(qcon, r["lat"], r["lng"])211        con.execute("UPDATE listings SET dauid=? WHERE uid=?",212                    (d or "hors-zone", r["uid"]))213        if d:214            done += 1215        else:216            introuvable += 1217    con.commit()218    qcon.close()219    con.close()220    stats = {"enriched": done, "hors_zone": introuvable, "candidats": len(rows)}221    print(f"[lou-ka] quartier {stats}")222    return stats223