spb/lou-ka Public
Lou·Ka — tous les logements à louer du Québec, un seul endroit.
HTML 99.7%
1#!/usr/bin/env python32# -*- coding: utf-8 -*-3"""4build_contexte.py — Construit data/staging-contexte.db : tables « proximité et5contexte socio-éducatif » pour l'enrichissement des fiches Lou-Ka.67Tables produites (schéma contractuel) :8 1. da_pmd — mesures de proximité StatCan (PMD 2021, parution 2023) agrégées9 par aire de diffusion (AD, DAUID 24*), valeurs normalisées 0..1.10 2. da_defav — quintiles de défavorisation matérielle/sociale INSPQ 2021 par AD11 (échelle Québec, 1 = favorisé, 5 = défavorisé).12 3. ecoles — écoles primaires/secondaires (public + privé) du Québec avec13 coordonnées MEQ et déciles IMSE/SFR (public seulement).14 4. meta — sources, dates, licences.1516Sources (téléchargées dans /tmp/louka-ctx si absentes, puis nettoyées avec --clean) :17 * PMD : Base de données sur les mesures de proximité, StatCan 17-26-0002,18 édition 2023 (données 2021). CSV national par îlot de diffusion (DBUID).19 Les mesures prox_idx_* sont DÉJÀ normalisées entre 0 et 1 à l'échelle20 nationale par StatCan (transformation min-max des mesures brutes de21 proximité) ; « .. » = valeur indisponible. On agrège par AD (colonne DAUID22 fournie = 8 premiers caractères du DBUID) en moyenne pondérée par la23 population de l'îlot (DBPOP) ; si aucun îlot peuplé n'a de valeur, moyenne24 simple des valeurs non nulles.25 * INSPQ : Indice de défavorisation matérielle et sociale 2021 — tableau26 d'équivalence complet « TableCorrespondancesCompleteQuebec2021_fr.xlsx »27 (le jeu Données Québec ne publie le tableau d'équivalence qu'en XLSX, pas28 en CSV ; on lit le XLSX en streaming avec openpyxl read_only). Colonnes29 QuintMat / QuintSoc = quintiles à l'échelle Québec.30 * MEQ écoles : CSV « Écoles publiques » (pps_public_ecole.csv, 1 ligne par31 immeuble) + « Installations privées » (pps_prive_installation.csv, 1 ligne32 par installation) + « Établissements privés » (noms officiels). Coordonnées33 COORD_X_LL84 (longitude) / COORD_Y_LL84 (latitude), WGS84.34 * MEQ IMSE/SFR : CSV « Défavorisation - Écoles primaires/secondaires35 2025-2026 » — déciles 1 (favorisé) à 10 (défavorisé).3637Choix de jointure écoles ↔ IMSE/SFR (documenté) :38 Les fichiers IMSE/SFR sont publiés par CODE D'ORGANISME (Code_Org, 7 chiffres,39 ex. 711002 = l'école en tant qu'entité administrative), pas par code de40 bâtiment (CD_IMM). On agrège donc pps_public_ecole.csv par CD_ORGNS (= code41 d'organisme de l'école) et on joint sur Code_Org == CD_ORGNS. Une école42 multi-bâtiments donne 1 ligne ; ses coordonnées sont celles du premier43 immeuble géocodé du fichier MEQ. Une école présente dans les deux fichiers44 IMSE (primaire ET secondaire) reçoit en priorité les déciles du fichier45 primaire. Le privé n'a pas d'IMSE/SFR publié → NULL.4647Usage :48 .venv/bin/python scripts/build_contexte.py [--clean]49 (--clean : supprime /tmp/louka-ctx à la fin)50"""5152import csv53import glob54import io55import os56import sqlite357import sys58import urllib.request59import zipfile60from collections import defaultdict61from datetime import date6263# ---------------------------------------------------------------------------64# Chemins et URLs65# ---------------------------------------------------------------------------66REPO = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))67DB_PATH = os.path.join(REPO, "data", "staging-contexte.db")68TMP = "/tmp/louka-ctx"6970URLS = {71 # StatCan PMD 2021 (parution 2023) — zip ~25 Mo contenant PMD-en.csv72 "pmd-eng.zip": "https://www150.statcan.gc.ca/pub/17-26-0002/2023001/csv/pmd-eng.zip",73 # INSPQ IDMS 2021, échelle Québec — zip contenant le tableau d'équivalence XLSX74 "inspq2021.zip": "https://www.inspq.qc.ca/sites/default/files/IDMS/Qu%C3%A9bec2021_042024.zip",75 # MEQ localisation des établissements76 "pps_public_ecole.csv": "https://www.donneesquebec.ca/recherche/dataset/2d3b5cf8-b347-49c7-ad3b-bd6a9c15e443/resource/c6640a54-bc4b-43ec-864e-6c325dce61bc/download/pps_public_ecole.csv",77 "pps_prive_installation.csv": "https://www.donneesquebec.ca/recherche/dataset/2d3b5cf8-b347-49c7-ad3b-bd6a9c15e443/resource/e22aa6f1-c4ff-4896-9534-6fa65133b3e9/download/pps_prive_installation.csv",78 "pps_prive_etablissement.csv": "https://www.donneesquebec.ca/recherche/dataset/2d3b5cf8-b347-49c7-ad3b-bd6a9c15e443/resource/83aae1b5-87b5-4e3d-9074-c0d4778fe812/download/pps_prive_etablissement.csv",79 # MEQ indices de défavorisation scolaire (IMSE/SFR)80 "defav_prim.csv": "https://www.donneesquebec.ca/recherche/dataset/004de02c-19f1-4da0-9af8-33f893e41972/resource/6c5d4a5d-ba3b-40a6-b570-916f43ab622c/download/defav_ecole_prim_public.csv",81 "defav_sec.csv": "https://www.donneesquebec.ca/recherche/dataset/004de02c-19f1-4da0-9af8-33f893e41972/resource/3e9aa43a-c32b-4779-b258-8407db716813/download/defav_ecole_sec_public.csv",82}8384# Correspondance colonne PMD -> colonne du schéma contractuel85PMD_COLS = [86 ("prox_idx_grocery", "prox_epicerie"),87 ("prox_idx_pharma", "prox_pharmacie"),88 ("prox_idx_childcare", "prox_garderie"),89 ("prox_idx_educpri", "prox_ecole_prim"),90 ("prox_idx_educsec", "prox_ecole_sec"),91 ("prox_idx_health", "prox_sante"),92 ("prox_idx_lib", "prox_bibliotheque"),93 ("prox_idx_parks", "prox_parc"),94 ("prox_idx_transit", "prox_transport"),95 ("prox_idx_emp", "prox_emploi"),96]979899def telecharger():100 """Télécharge les fichiers sources manquants dans /tmp/louka-ctx."""101 os.makedirs(TMP, exist_ok=True)102 for nom, url in URLS.items():103 dest = os.path.join(TMP, nom)104 if os.path.exists(dest) and os.path.getsize(dest) > 0:105 continue106 print(f" téléchargement {nom} …")107 req = urllib.request.Request(url, headers={"User-Agent": "lou-ka/1.0"})108 with urllib.request.urlopen(req, timeout=300) as r, open(dest, "wb") as f:109 while True:110 bloc = r.read(1 << 20)111 if not bloc:112 break113 f.write(bloc)114 # dézippage (idempotent)115 if not glob.glob(os.path.join(TMP, "pmd", "**", "PMD-en.csv"), recursive=True):116 with zipfile.ZipFile(os.path.join(TMP, "pmd-eng.zip")) as z:117 z.extractall(os.path.join(TMP, "pmd"))118 if not glob.glob(os.path.join(TMP, "inspq", "**", "*TableCorrespondances*"),119 recursive=True):120 with zipfile.ZipFile(os.path.join(TMP, "inspq2021.zip")) as z:121 z.extractall(os.path.join(TMP, "inspq"))122123124# ---------------------------------------------------------------------------125# 1. da_pmd — agrégation des îlots (DB) vers les aires de diffusion (AD)126# ---------------------------------------------------------------------------127def construire_da_pmd(cx):128 chemin = glob.glob(os.path.join(TMP, "pmd", "**", "PMD-en.csv"),129 recursive=True)[0]130 # Accumulateurs par DAUID : pour chaque mesure, (somme pondérée, somme des131 # poids, somme simple, compte) — le fichier national (~500 k lignes) est lu132 # en streaming, on ne garde en mémoire que les AD du Québec (~13-14 k).133 acc = defaultdict(lambda: [[0.0, 0.0, 0.0, 0] for _ in PMD_COLS])134 # NB : le CSV PMD est encodé en Latin-1 (noms de lieux accentués), pas UTF-8.135 with open(chemin, newline="", encoding="latin-1") as f:136 lecteur = csv.DictReader(f)137 for ligne in lecteur:138 dauid = ligne["DAUID"].strip()139 if not dauid.startswith("24"): # Québec seulement (PRUID 24)140 continue141 # Poids = population de l'îlot de diffusion (DBPOP) ; vide ou 0 =142 # îlot non peuplé, il ne contribue qu'à la moyenne simple de repli.143 try:144 poids = float(ligne["DBPOP"] or 0.0)145 except ValueError:146 poids = 0.0147 a = acc[dauid]148 for i, (col_src, _) in enumerate(PMD_COLS):149 brut = ligne[col_src].strip()150 if brut in ("", "..", "F", "x"): # valeur indisponible151 continue152 try:153 v = float(brut)154 except ValueError:155 continue156 cell = a[i]157 if poids > 0:158 cell[0] += poids * v159 cell[1] += poids160 cell[2] += v161 cell[3] += 1162163 lignes = []164 for dauid, a in acc.items():165 vals = []166 for wsum, wtot, ssum, n in a:167 if wtot > 0:168 v = wsum / wtot # moyenne pondérée par la population169 elif n > 0:170 v = ssum / n # repli : moyenne simple des non-nuls171 else:172 v = None # aucune valeur disponible dans l'AD173 if v is not None:174 v = min(1.0, max(0.0, v)) # garde-fou 0..1175 vals.append(v)176 lignes.append((dauid, *vals))177178 cx.executemany(179 "INSERT INTO da_pmd VALUES (?,?,?,?,?,?,?,?,?,?,?)", lignes)180 return len(lignes)181182183# ---------------------------------------------------------------------------184# 2. da_defav — quintiles INSPQ (échelle Québec) par AD185# ---------------------------------------------------------------------------186def construire_da_defav(cx):187 import openpyxl # lecture streaming du XLSX (le tableau d'équivalence188 # n'est publié qu'en XLSX par l'INSPQ / Données Québec)189 chemin = glob.glob(os.path.join(TMP, "inspq", "**",190 "*TableCorrespondances*Quebec2021*.xlsx"),191 recursive=True)[0]192 wb = openpyxl.load_workbook(chemin, read_only=True)193 ws = wb["Données"]194 it = ws.iter_rows(values_only=True)195 entete = [str(c) for c in next(it)]196 i_ad = entete.index("AD")197 i_qm = entete.index("QuintMat") # quintile matériel, échelle Québec198 i_qs = entete.index("QuintSoc") # quintile social, échelle Québec199 vus = {}200 for ligne in it:201 ad = str(ligne[i_ad]).strip() if ligne[i_ad] is not None else ""202 if len(ad) != 8 or not ad.startswith("24"):203 continue204 qm, qs = ligne[i_qm], ligne[i_qs]205 qm = int(qm) if qm not in (None, "") else None206 qs = int(qs) if qs not in (None, "") else None207 # Une AD scindée entre plusieurs CLSC apparaît sur plusieurs lignes ;208 # les quintiles « échelle Québec » y sont identiques -> première ligne.209 if ad not in vus:210 vus[ad] = (qm, qs)211 wb.close()212 cx.executemany("INSERT INTO da_defav VALUES (?,?,?)",213 [(ad, qm, qs) for ad, (qm, qs) in vus.items()])214 return len(vus)215216217# ---------------------------------------------------------------------------218# 3. ecoles — localisation MEQ + IMSE/SFR219# ---------------------------------------------------------------------------220def _lire_defav_meq():221 """Déciles IMSE/SFR par code d'organisme (public seulement).222 Priorité au fichier primaire si un code figure dans les deux fichiers."""223 deciles = {}224 for nom in ("defav_sec.csv", "defav_prim.csv"): # prim écrase sec225 with open(os.path.join(TMP, nom), newline="", encoding="utf-8-sig") as f:226 for ligne in csv.DictReader(f):227 code = ligne["Code_Org"].strip()228 imse = ligne["Rang_Decile_IMSE"].strip()229 sfr = ligne["Rang_Decile_SFR"].strip()230 imse = int(imse) if imse else None231 sfr = int(sfr) if sfr else None232 if imse is None and sfr is None and code in deciles:233 continue # ne pas écraser une valeur par du vide234 deciles[code] = (imse, sfr)235 return deciles236237238def _coord(ligne, cx_col, cy_col):239 """Extrait (lat, lng) WGS84 ; COORD_X = longitude, COORD_Y = latitude."""240 try:241 lng = float(ligne[cx_col])242 lat = float(ligne[cy_col])243 except (ValueError, TypeError, KeyError):244 return None245 # garde-fou : bornes du Québec246 if not (-80.0 <= lng <= -55.0 and 44.0 <= lat <= 63.0):247 return None248 return (lat, lng)249250251def construire_ecoles(cx):252 deciles = _lire_defav_meq()253254 # --- Public : 1 ligne par immeuble -> agrégation par code d'organisme ---255 publics = {}256 with open(os.path.join(TMP, "pps_public_ecole.csv"), newline="",257 encoding="utf-8-sig") as f:258 for ligne in csv.DictReader(f):259 code = ligne["CD_ORGNS"].strip()260 if not code:261 continue262 e = publics.setdefault(code, {263 "nom": ligne["NOM_OFFCL_ORGNS"].strip() or ligne["NOM_COURT_ORGNS"].strip(),264 "coord": None, "prim": False, "sec": False,265 })266 e["prim"] |= ligne["PRIM"].strip() == "1"267 e["sec"] |= ligne["SEC"].strip() == "1"268 if e["coord"] is None: # premier immeuble géocodé du fichier269 e["coord"] = _coord(ligne, "COORD_X_LL84_IMM", "COORD_Y_LL84_IMM")270271 # --- Privé : installations (flags d'ordre + coords) regroupées par272 # établissement responsable (CD_ORGNS_RESP) ---273 noms_prives = {}274 with open(os.path.join(TMP, "pps_prive_etablissement.csv"), newline="",275 encoding="utf-8-sig") as f:276 for ligne in csv.DictReader(f):277 noms_prives[ligne["CD_ORGNS"].strip()] = (278 ligne["NOM_OFFCL"].strip() or ligne["NOM_COURT"].strip())279280 prives = {}281 with open(os.path.join(TMP, "pps_prive_installation.csv"), newline="",282 encoding="utf-8-sig") as f:283 for ligne in csv.DictReader(f):284 code = ligne["CD_ORGNS_RESP"].strip() or ligne["CD_ORGNS"].strip()285 if not code:286 continue287 e = prives.setdefault(code, {288 "nom": noms_prives.get(code) or ligne["NOM_OFFCL"].strip(),289 "coord": None, "prim": False, "sec": False,290 })291 e["prim"] |= ligne["PRIM"].strip() == "1"292 e["sec"] |= ligne["SEC"].strip() == "1"293 if e["coord"] is None:294 e["coord"] = _coord(ligne, "COORD_X_LL84", "COORD_Y_LL84")295296 lignes = []297 for reseau, ecoles in (("public", publics), ("privé", prives)):298 for code, e in ecoles.items():299 # On ne garde que le primaire/secondaire (exclut préscolaire seul,300 # formation professionnelle et éducation des adultes).301 if not (e["prim"] or e["sec"]):302 continue303 ordre = ("primaire-secondaire" if e["prim"] and e["sec"]304 else "primaire" if e["prim"] else "secondaire")305 lat, lng = e["coord"] if e["coord"] else (None, None)306 imse, sfr = deciles.get(code, (None, None)) if reseau == "public" \307 else (None, None)308 lignes.append((code, e["nom"], lat, lng, ordre, reseau, imse, sfr))309310 cx.executemany("INSERT OR IGNORE INTO ecoles VALUES (?,?,?,?,?,?,?,?)",311 lignes)312 return len(lignes)313314315# ---------------------------------------------------------------------------316# 4. meta317# ---------------------------------------------------------------------------318def construire_meta(cx):319 meta = {320 "date_construction": date.today().isoformat(),321 "script": "scripts/build_contexte.py",322 "source_pmd": ("Statistique Canada, Base de données sur les mesures de "323 "proximité (PMD) 2021, no 17-26-0002, édition 2023. "324 "https://www150.statcan.gc.ca/n1/pub/17-26-0002/172600022023001-eng.htm"),325 "licence_pmd": ("Licence du gouvernement ouvert – Canada "326 "(https://ouvert.canada.ca/fr/licence-du-gouvernement-ouvert-canada). "327 "Contient de l'information visée par la Licence du "328 "gouvernement ouvert – Canada, Statistique Canada."),329 "note_pmd": ("Mesures prox_idx_* déjà normalisées 0..1 par StatCan à "330 "l'échelle nationale (min-max des mesures brutes de "331 "proximité par îlot de diffusion). Agrégées ici par AD "332 "(DAUID 24*) en moyenne pondérée par la population de "333 "l'îlot (DBPOP), repli sur la moyenne simple des valeurs "334 "non nulles si aucun îlot peuplé. NULL = aucune donnée."),335 "source_defav": ("INSPQ, Indice de défavorisation matérielle et sociale "336 "2021, tableau d'équivalence complet (échelle Québec), "337 "version avril 2024. https://www.donneesquebec.ca/recherche/"338 "dataset/indice-de-defavorisation-du-quebec-2021"),339 "licence_defav": "Creative Commons Attribution 4.0 (CC-BY 4.0) — INSPQ.",340 "note_defav": ("QuintMat/QuintSoc, échelle Québec : 1 = plus favorisé, "341 "5 = plus défavorisé. NULL = AD non cotée (pop. faible, "342 "institutionnelle, etc.). Tableau publié en XLSX "343 "seulement (pas de CSV)."),344 "source_ecoles": ("MEQ-MES, Localisation des établissements d'enseignement "345 "du réseau scolaire (CSV pps_public_ecole, "346 "pps_prive_installation, pps_prive_etablissement). "347 "https://www.donneesquebec.ca/recherche/dataset/"348 "localisation-des-etablissements-d-enseignement-du-"349 "reseau-scolaire-au-quebec"),350 "source_imse_sfr": ("MEQ, Indices de défavorisation (IMSE et SFR), déciles "351 "1 (favorisé) à 10 (défavorisé), année scolaire "352 "2025-2026. https://www.donneesquebec.ca/recherche/"353 "dataset/indices-de-defavorisation"),354 "licence_ecoles": "Creative Commons Attribution 4.0 (CC-BY 4.0) — Gouvernement du Québec (MEQ).",355 "note_ecoles": ("code = code d'ORGANISME MEQ (7 chiffres), pas le code "356 "de bâtiment : c'est la clé des fichiers IMSE/SFR "357 "(Code_Org). Coordonnées WGS84 du premier immeuble/"358 "installation géocodé. ordre ∈ {primaire, secondaire, "359 "primaire-secondaire} ; préscolaire seul, FP et adultes "360 "exclus. IMSE/SFR NULL pour le privé (non publié) et "361 "pour les écoles publiques sous seuil de diffusion."),362 }363 cx.executemany("INSERT INTO meta VALUES (?,?)", sorted(meta.items()))364 return len(meta)365366367# ---------------------------------------------------------------------------368# Validations369# ---------------------------------------------------------------------------370def valider(cx):371 q = lambda s: cx.execute(s).fetchone()[0]372 print("\n=== VALIDATIONS ===")373 n_pmd = q("SELECT COUNT(*) FROM da_pmd")374 print(f"da_pmd : {n_pmd} AD (attendu ~10-13 k)")375 assert 10000 <= n_pmd <= 15000, "nb d'AD hors plage attendue"376 for _, col in PMD_COLS:377 mn, mx, nn = cx.execute(378 f"SELECT MIN({col}), MAX({col}), SUM({col} IS NOT NULL) FROM da_pmd"379 ).fetchone()380 assert mn is None or (0.0 <= mn and mx <= 1.0), f"{col} hors 0..1"381 print(f" {col:18s} min={mn:.4f} max={mx:.4f} non-null={nn}")382383 n_def = q("SELECT COUNT(*) FROM da_defav")384 n_cot = q("SELECT COUNT(*) FROM da_defav WHERE quintile_materiel IS NOT NULL")385 # Couverture des AD « urbaines » : approximée par les AD de la PMD ayant386 # une mesure de transport en commun (desserte = milieu urbain).387 couv = q("""SELECT 100.0 * SUM(d.quintile_materiel IS NOT NULL) / COUNT(*)388 FROM da_pmd p LEFT JOIN da_defav d ON d.dauid = p.dauid389 WHERE p.prox_transport IS NOT NULL""")390 print(f"da_defav : {n_def} AD, {n_cot} cotées ; couverture AD urbaines "391 f"(proxy transport) = {couv:.1f} % (exigé >= 90 %)")392 assert couv >= 90.0, "couverture urbaine insuffisante"393394 n_ec = q("SELECT COUNT(*) FROM ecoles")395 n_geo = q("SELECT COUNT(*) FROM ecoles WHERE lat IS NOT NULL")396 n_imse = q("SELECT COUNT(*) FROM ecoles WHERE imse IS NOT NULL")397 print(f"ecoles : {n_ec} (géocodées {n_geo}, avec IMSE {n_imse})")398 assert n_ec >= 2500, "trop peu d'écoles"399400 print("échantillon Limoilou (attendu IMSE élevé) / Sillery (faible) :")401 for motif in ("%Saint-Fidèle%", "%Saint-Paul-Apôtre%", "%Stadacona%",402 "%Saint-Michel de Sillery%", "%Les Sources%", "%Rochebelle%"):403 for row in cx.execute(404 "SELECT code, nom, ordre, reseau, imse, sfr FROM ecoles "405 "WHERE nom LIKE ? LIMIT 3", (motif,)):406 print(" ", row)407408409# ---------------------------------------------------------------------------410def main():411 print("Téléchargement / extraction des sources …")412 telecharger()413414 os.makedirs(os.path.dirname(DB_PATH), exist_ok=True)415 if os.path.exists(DB_PATH):416 os.remove(DB_PATH) # reconstruction complète417 cx = sqlite3.connect(DB_PATH)418 cx.executescript("""419 CREATE TABLE da_pmd(420 dauid TEXT PRIMARY KEY,421 prox_epicerie REAL, prox_pharmacie REAL, prox_garderie REAL,422 prox_ecole_prim REAL, prox_ecole_sec REAL, prox_sante REAL,423 prox_bibliotheque REAL, prox_parc REAL, prox_transport REAL,424 prox_emploi REAL);425 CREATE TABLE da_defav(426 dauid TEXT PRIMARY KEY,427 quintile_materiel INTEGER, quintile_social INTEGER);428 CREATE TABLE ecoles(429 code TEXT PRIMARY KEY, nom TEXT, lat REAL, lng REAL,430 ordre TEXT, reseau TEXT, imse INTEGER, sfr INTEGER);431 CREATE TABLE meta(cle TEXT PRIMARY KEY, valeur TEXT);432 """)433434 print("Construction da_pmd (streaming du CSV national ~500 k lignes) …")435 print(f" {construire_da_pmd(cx)} AD insérées")436 print("Construction da_defav …")437 print(f" {construire_da_defav(cx)} AD insérées")438 print("Construction ecoles …")439 print(f" {construire_ecoles(cx)} écoles insérées")440 construire_meta(cx)441 cx.commit()442443 valider(cx)444 cx.close()445 print(f"\nOK -> {DB_PATH} ({os.path.getsize(DB_PATH)/1e6:.1f} Mo)")446447 if "--clean" in sys.argv:448 import shutil449 shutil.rmtree(TMP, ignore_errors=True)450 print(f"{TMP} supprimé")451452453if __name__ == "__main__":454 main()455