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# scripts/merge_quartier.py : fusionne les bases de préparation5#   data/staging-{recensement,contexte,environnement}.db -> data/quartier.db6#   (la base statique servie en production ; voir louka/quartier.py)7# Usage : .venv/bin/python scripts/merge_quartier.py8# -----------------------------------------------------------------------------9from __future__ import annotations1011import sqlite312import sys13from pathlib import Path1415DATA = Path(__file__).resolve().parent.parent / "data"16CIBLE = DATA / "quartier.db"1718# staging -> tables attendues19SOURCES = {20    "staging-recensement.db": ["da_poly", "da_stats"],21    "staging-contexte.db": ["da_pmd", "da_defav", "ecoles"],22    "staging-environnement.db": ["heat", "crime_mtl", "igc"],23}242526def main() -> None:27    if CIBLE.exists():28        CIBLE.unlink()29    out = sqlite3.connect(CIBLE)30    out.execute("CREATE TABLE meta (cle TEXT PRIMARY KEY, valeur TEXT)")3132    for fichier, tables in SOURCES.items():33        chemin = DATA / fichier34        if not chemin.exists():35            print(f"⚠ {fichier} absent — tables {tables} sautées", file=sys.stderr)36            continue37        out.execute("ATTACH DATABASE ? AS src", (str(chemin),))38        for t in tables:39            existe = out.execute(40                "SELECT name FROM src.sqlite_master WHERE type='table' AND name=?",41                (t,)).fetchone()42            if not existe:43                print(f"⚠ table {t} absente de {fichier}", file=sys.stderr)44                continue45            schema = out.execute(46                "SELECT sql FROM src.sqlite_master WHERE type='table' AND name=?",47                (t,)).fetchone()[0]48            out.execute(schema)49            out.execute(f"INSERT INTO {t} SELECT * FROM src.{t}")50            n = out.execute(f"SELECT COUNT(*) FROM {t}").fetchone()[0]51            print(f"✓ {t}: {n} lignes (depuis {fichier})")52        # préfixer les métadonnées de provenance53        if out.execute("SELECT name FROM src.sqlite_master WHERE name='meta'").fetchone():54            for cle, val in out.execute("SELECT cle, valeur FROM src.meta"):55                out.execute("INSERT OR REPLACE INTO meta VALUES (?,?)",56                            (f"{fichier.replace('.db', '')}:{cle}", val))57        out.commit()58        out.execute("DETACH DATABASE src")5960    # scores PMD -> rangs centiles parmi les AD du Québec (l'échelle brute est61    # normalisée nationalement et écrase les valeurs urbaines : un Saint-Roch62    # très marchable afficherait 17/100 ; son centile québécois le rétablit)63    import bisect64    cols = [r[1] for r in out.execute("PRAGMA table_info(da_pmd)") if r[1] != "dauid"]65    out.execute("DROP TABLE IF EXISTS da_pmd_pct")66    out.execute("CREATE TABLE da_pmd_pct (dauid TEXT PRIMARY KEY, "67                + ", ".join(f"{c} INTEGER" for c in cols) + ")")68    lignes = out.execute(f"SELECT dauid, {', '.join(cols)} FROM da_pmd").fetchall()69    tries = {c: sorted(v for (v,) in70                       out.execute(f"SELECT {c} FROM da_pmd WHERE {c} IS NOT NULL"))71             for c in cols}72    q = (f"INSERT INTO da_pmd_pct VALUES (?{',?' * len(cols)})")73    for ligne in lignes:74        vals = [ligne[0]]75        for i, c in enumerate(cols):76            v = ligne[i + 1]77            t = tries[c]78            if v is None or len(t) < 2:79                vals.append(None)80            else:   # rang centile parmi les valeurs non nulles du Québec81                vals.append(round(100 * bisect.bisect_left(t, v) / (len(t) - 1)))82        out.execute(q, vals)83    print(f"✓ da_pmd_pct: {len(lignes)} lignes (rangs centiles QC, non-nuls)")8485    # index utiles au runtime86    for idx in [87        "CREATE INDEX IF NOT EXISTS idx_poly_bbox ON da_poly(lat_min, lat_max)",88        "CREATE INDEX IF NOT EXISTS idx_crime_lat2 ON crime_mtl(lat)",89    ]:90        try:91            out.execute(idx)92        except sqlite3.Error as e:93            print(f"⚠ index: {e}", file=sys.stderr)94    out.commit()95    out.execute("VACUUM")96    out.close()97    print(f"→ {CIBLE} : {CIBLE.stat().st_size / 1e6:.1f} Mo")9899100if __name__ == "__main__":101    main()102