spb/lou-ka

1# -----------------------------------------------------------------------------2# Lou-Ka — Agrégateur de logements à louer (province de Québec)3# Auteur : Simon-Pierre Boucher — contact@spboucher.ai4# rdl.py : Registre des loyers (registre-des-loyers.ca) — extraction + requêtes5#6#   Le Registre des loyers est une initiative citoyenne (Vivre en ville) où7#   les locataires déclarent volontairement leur loyer. La carte publique8#   expose une API JSON ouverte :9#       GET /api/v1/housings/{NElat},{NElng},{SWlat},{SWlng}10#   On balaie la province par grandes boîtes (aucune pagination côté serveur ;11#   la boîte « tout le Québec » dépasse ses capacités -> découpage, avec12#   subdivision récursive en cas d'erreur 500), puis on stocke le tout dans13#   data/rdl.db (base séparée : jamais de verrou sur louka.db).14#15#   Usage :  python run.py rdl            # rafraîchît la base locale16#   Lecture : nearby(lat, lng, radius_m)  # loyers déclarés autour d'un point17#             (bloc « Registre des loyers » de la fiche, /api/rdl)18# -----------------------------------------------------------------------------19from __future__ import annotations2021import json22import math23import sqlite324import time25import urllib.request26from pathlib import Path27from statistics import median2829DB_PATH = Path(__file__).resolve().parent.parent / "data" / "rdl.db"30API = "https://registre-des-loyers.ca/api/v1/housings/{},{},{},{}"31UA = "LouKaBot/1.0 (+https://www.lou-ka.com; contact@spboucher.ai)"32DELAY = 2.0                      # politesse entre deux boîtes3334# Boîtes (NE_lat, NE_lng, SW_lat, SW_lng) couvrant le pays entier (le registre35# est pancanadien ; Lou-Ka n'affiche que ce qui tombe près d'une annonce).36# Elles se recoupent légèrement, la déduplication se fait par id.37BOXES: list[tuple[float, float, float, float]] = [38    (46.0, -70.0, 45.0, -75.0),      # Grand Montréal + Montérégie + Estrie39    (47.2, -70.0, 46.0, -76.0),      # Québec, Mauricie, Centre-du-Québec40    (46.2, -74.8, 45.2, -77.5),      # Outaouais41    (49.5, -63.5, 47.2, -80.0),      # Abitibi, Saguenay, Gaspésie42    (47.2, -63.5, 45.0, -70.0),      # Bas-Saint-Laurent, Beauce est43    (63.0, -55.0, 49.5, -80.0),      # Côte-Nord, Nord-du-Québec44    (45.05, -70.0, 44.5, -80.0),     # frange frontalière sud45    (57.0, -74.5, 41.6, -96.0),      # Ontario46    (60.0, -96.0, 48.9, -110.0),     # Prairies (MB, SK)47    (60.0, -110.0, 48.0, -140.0),    # Alberta, Colombie-Britannique48    (70.0, -60.0, 60.0, -142.0),     # territoires + Nunavik49    (49.0, -52.0, 43.0, -70.0),      # Atlantique (NB, NÉ, ÎPÉ, TNL sud)50    (61.0, -52.0, 49.0, -57.0),      # Terre-Neuve nord + Labrador est51]5253# au-delà de ce volume on subdivise par prudence (plafond serveur inconnu)54SUSPECT = 400005556_SCHEMA = """57CREATE TABLE IF NOT EXISTS rdl_housings (58    id INTEGER PRIMARY KEY,59    full_address TEXT, street_number TEXT, apartment_number TEXT,60    street_name TEXT, city TEXT, zip TEXT,61    lat REAL, lng REAL,62    price REAL, rooms INTEGER, year INTEGER, start_date TEXT,63    type_of_accomodation TEXT,64    heating_included INTEGER, electricity_included INTEGER,65    furnishing_included INTEGER, parking_included INTEGER,66    animal_allowed INTEGER,67    address_slug TEXT, updated_at TEXT, fetched_at TEXT68);69CREATE INDEX IF NOT EXISTS idx_rdl_latlng ON rdl_housings (lat, lng);70"""717273def _connect(ro: bool = False) -> sqlite3.Connection:74    if ro:75        con = sqlite3.connect(f"file:{DB_PATH}?mode=ro", uri=True)76    else:77        DB_PATH.parent.mkdir(parents=True, exist_ok=True)78        con = sqlite3.connect(DB_PATH)79    con.row_factory = sqlite3.Row80    return con818283def _fetch_box(ne_lat: float, ne_lng: float, sw_lat: float, sw_lng: float,84               depth: int = 0) -> list[dict]:85    """Une boîte ; en cas d'erreur serveur (boîte trop lourde), découpe en 4."""86    url = API.format(ne_lat, ne_lng, sw_lat, sw_lng)87    req = urllib.request.Request(url, headers={"User-Agent": UA})88    try:89        with urllib.request.urlopen(req, timeout=120) as resp:90            data = json.load(resp)91        rows = data.get("data", {}).get("housings") or []92        if len(rows) < SUSPECT or depth >= 3:93            return rows94        raise RuntimeError(f"{len(rows)} résultats — subdivision de prudence")95    except Exception as exc:96        if depth >= 3:97            print(f"  ! abandon boîte {url}: {exc}")98            return []99        mid_lat = (ne_lat + sw_lat) / 2100        mid_lng = (ne_lng + sw_lng) / 2101        out: list[dict] = []102        for quad in ((ne_lat, ne_lng, mid_lat, mid_lng),103                     (ne_lat, mid_lng, mid_lat, sw_lng),104                     (mid_lat, ne_lng, sw_lat, mid_lng),105                     (mid_lat, mid_lng, sw_lat, sw_lng)):106            time.sleep(DELAY)107            out.extend(_fetch_box(*quad, depth=depth + 1))108        return out109110111def _num(v, cast=float):112    try:113        return cast(v)114    except (TypeError, ValueError):115        return None116117118def refresh() -> None:119    """Balaie la province et remplace le contenu de data/rdl.db."""120    now = time.strftime("%Y-%m-%d %H:%M:%S")121    seen: dict[int, dict] = {}122    for i, box in enumerate(BOXES, 1):123        rows = _fetch_box(*box)124        fresh = 0125        for h in rows:126            hid = _num(h.get("id"), int)127            if hid is None or hid in seen:128                continue129            seen[hid] = h130            fresh += 1131        print(f"[rdl] boîte {i}/{len(BOXES)} : {len(rows)} reçus, "132              f"{fresh} nouveaux ({len(seen)} au total)")133        time.sleep(DELAY)134135    con = _connect()136    con.executescript(_SCHEMA)137    with con:138        con.execute("DELETE FROM rdl_housings")139        con.executemany(140            "INSERT OR REPLACE INTO rdl_housings VALUES "141            "(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",142            [(hid,143              h.get("full_address"), h.get("street_number"),144              h.get("apartment_number"), h.get("street_name"),145              h.get("city"), h.get("zip"),146              _num(h.get("latitude")), _num(h.get("longitude")),147              _num(h.get("price")), _num(h.get("number_of_closed_room"), int),148              _num(h.get("year"), int),149              (h.get("start_date") or "")[:10] or None,150              h.get("type_of_accomodation"),151              1 if h.get("heating_included") else 0,152              1 if h.get("electricity_included") else 0,153              1 if h.get("furnishing_included") else 0,154              1 if h.get("parking_included") else 0,155              1 if h.get("animal_allowed") else 0,156              h.get("address_slug"), h.get("updated_at"), now)157             for hid, h in seen.items()])158    n, cities = con.execute(159        "SELECT COUNT(*), COUNT(DISTINCT city) FROM rdl_housings").fetchone()160    print(f"[rdl] terminé : {n} loyers déclarés, {cities} villes -> {DB_PATH}")161    con.close()162163164def _dist_m(lat1: float, lng1: float, lat2: float, lng2: float) -> float:165    dlat = math.radians(lat2 - lat1)166    dlng = math.radians(lng2 - lng1)167    a = (math.sin(dlat / 2) ** 2 + math.cos(math.radians(lat1))168         * math.cos(math.radians(lat2)) * math.sin(dlng / 2) ** 2)169    return 6371000 * 2 * math.asin(math.sqrt(a))170171172def nearby(lat: float, lng: float, radius_m: int = 600,173           limit: int = 12) -> dict | None:174    """Loyers déclarés autour d'un point + agrégats (bloc fiche / API)."""175    if not DB_PATH.exists():176        return None177    dlat = radius_m / 111320.0178    dlng = radius_m / (111320.0 * max(0.2, math.cos(math.radians(lat))))179    con = _connect(ro=True)180    rows = con.execute(181        "SELECT * FROM rdl_housings WHERE lat BETWEEN ? AND ? "182        "AND lng BETWEEN ? AND ? AND price > 100 AND price < 20000",183        (lat - dlat, lat + dlat, lng - dlng, lng + dlng)).fetchall()184    con.close()185    hits = []186    for r in rows:187        if r["lat"] is None or r["lng"] is None:188            continue189        d = _dist_m(lat, lng, r["lat"], r["lng"])190        if d <= radius_m:191            hits.append((d, r))192    if not hits:193        return {"n": 0, "radius_m": radius_m, "items": []}194    hits.sort(key=lambda t: t[0])195196    def _q(vals: list[float], q: float) -> int:197        s = sorted(vals)198        i = q * (len(s) - 1)199        lo = int(i)200        hi = min(lo + 1, len(s) - 1)201        return round(s[lo] + (s[hi] - s[lo]) * (i - lo))202203    prices = [r["price"] for _, r in hits]204    recent = [r["price"] for _, r in hits if (r["year"] or 0) >= 2023]205    base = recent if len(recent) >= 8 else prices206    quart = ({"p10": _q(base, 0.10), "p25": _q(base, 0.25),207              "p75": _q(base, 0.75), "p90": _q(base, 0.90)}208             if len(base) >= 5 else None)209    by_rooms: dict[str, dict] = {}210    for _, r in hits:211        if r["rooms"] is None:212            continue213        b = by_rooms.setdefault(str(r["rooms"]), {"n": 0, "prices": []})214        b["n"] += 1215        b["prices"].append(r["price"])216    for b in by_rooms.values():217        b["median"] = round(median(b.pop("prices")))218219    def item(d: float, r: sqlite3.Row) -> dict:220        addr = " ".join(x for x in (r["street_number"], r["street_name"]) if x)221        if r["apartment_number"]:222            addr += f", app. {r['apartment_number']}"223        return {"address": addr or r["full_address"], "city": r["city"],224                "price": r["price"], "rooms": r["rooms"], "year": r["year"],225                "date": r["start_date"], "dist_m": round(d),226                "heating": bool(r["heating_included"]),227                "furnished": bool(r["furnishing_included"])}228229    return {230        "n": len(hits),231        "radius_m": radius_m,232        "median": round(median(prices)),233        "median_recent": round(median(recent)) if recent else None,234        "n_recent": len(recent),235        "quartiles": quart,236        "by_rooms": by_rooms,237        "items": [item(d, r) for d, r in hits[:limit]],238    }239