spb/lou-ka

1# -----------------------------------------------------------------------------2# Lou-Ka — Agrégateur de logements à louer (province de Québec)3# Auteur : Simon-Pierre Boucher — contact@spboucher.ai4# air.py : qualité de l'air à l'adresse — RSQAQ (MELCCFP, Données Québec)5#6#   Sources ouvertes officielles du Réseau de surveillance de la qualité de7#   l'air du Québec :8#     · stations (rsqaq-stations) : coordonnées, ouverture/fermeture ;9#     · données horaires continues (PM2.5, NO2, O3, SO2, CO) 2024-2025 ;10#     · données séquentielles (Concentration PST / PM10 + métaux) 2007-2026 —11#       les particules en suspension totales demandées (étiquette PST).12#   `refresh()` télécharge les CSV et réduit tout en moyennes annuelles par13#   station dans data/air.db (minuscule) ; `lookup(lat, lng)` remonte la14#   station la plus proche (≤ 60 km) avec ses mesures les plus récentes,15#   comparées aux repères annuels de l'OMS (2021).16# -----------------------------------------------------------------------------17from __future__ import annotations1819import csv20import io21import math22import sqlite323import time24import urllib.request25from pathlib import Path2627DB_PATH = Path(__file__).resolve().parent.parent / "data" / "air.db"28UA = "LouKaBot/1.0 (+https://www.lou-ka.com; contact@spboucher.ai)"2930URL_STATIONS = ("https://www.donneesquebec.ca/recherche/dataset/"31                "8656ad05-c174-41c5-9ed7-8c69d308beb9/resource/"32                "cebea532-a9e0-4a39-8c2d-54f33d937c73/download/"33                "rsqaq_stations_de_la_qualite_de_lair.csv")34URL_HORAIRE = {35    2025: ("https://www.donneesquebec.ca/recherche/dataset/"36           "a80757bd-d442-4d3d-9269-11628330b727/resource/"37           "370a6be4-1530-4c2b-92f3-0a308224f284/download/"38           "rsqaq_continues_horaires_2025.csv"),39    2024: ("https://www.donneesquebec.ca/recherche/dataset/"40           "a80757bd-d442-4d3d-9269-11628330b727/resource/"41           "135146b7-fd4a-4564-b10b-b1ae94257889/download/"42           "rsqaq_continues_horaires_2024.csv"),43}44URL_SEQ = ("https://www.donneesquebec.ca/recherche/dataset/"45           "bff56fad-22c3-450b-aafa-4ccdad6c91f2/resource/"46           "b83cac3b-6199-4cfd-a903-cdec2369630c/download/"47           "rsqaq_sequentielles_2007-2026.csv")4849# colonnes horaires retenues -> polluant canonique50_HOURLY = {"PM2.5-T640": "PM2.5", "PM2.5-BAM": "PM2.5", "NO2": "NO2",51           "O3": "O3", "SO2": "SO2", "CO": "CO"}52# contaminants séquentiels retenus (concentrations de particules)53_SEQ = {"Concentration PST": "PST", "Concentration PM10": "PM10"}5455# repères ANNUELS (moyenne) : OMS 2021 pour PM2.5/PM10/NO2 ; PST : norme56# annuelle du Règlement sur l'assainissement de l'atmosphère (Québec)57REFS = {"PM2.5": ("OMS 2021", 5.0), "PM10": ("OMS 2021", 15.0),58        "NO2": ("OMS 2021", 10.0), "PST": ("RAA Québec", 60.0)}59UNITES = {"PM2.5": "µg/m³", "PM10": "µg/m³", "PST": "µg/m³",60          "NO2": "ppb", "O3": "ppb", "SO2": "ppb", "CO": "ppm"}616263def _dl(url: str) -> io.StringIO:64    req = urllib.request.Request(url, headers={"User-Agent": UA})65    with urllib.request.urlopen(req, timeout=300) as r:66        return io.StringIO(r.read().decode("utf-8-sig"))676869def refresh() -> None:70    stations: dict[str, dict] = {}71    for row in csv.DictReader(_dl(URL_STATIONS)):72        sid = row["ID_STATION"].strip().lstrip("0")73        stations[sid] = {"nom": row["NOM_STATION"], "ville": row["MUNICIPALITE"],74                         "lat": float(row["LATITUDE"] or 0),75                         "lng": float(row["LONGITUDE"] or 0)}76    print(f"[air] {len(stations)} stations")7778    # (station, annee, polluant) -> [somme, n]79    acc: dict[tuple, list[float]] = {}8081    def _sid(label: str) -> str:82        return label.split(" - ")[0].strip().lstrip("0")8384    for annee, url in URL_HORAIRE.items():85        n = 086        rd = csv.DictReader(_dl(url))87        cols = [c for c in (rd.fieldnames or []) if c in _HOURLY]88        for row in rd:89            sid = _sid(row["Station"])90            for c in cols:91                v = row.get(c)92                if not v:93                    continue94                try:95                    x = float(v)96                except ValueError:97                    continue98                if x < 0:99                    continue100                a = acc.setdefault((sid, annee, _HOURLY[c]), [0.0, 0])101                a[0] += x102                a[1] += 1103                n += 1104        print(f"[air] horaires {annee} : {n} mesures")105106    n = 0107    for row in csv.DictReader(_dl(URL_SEQ)):108        pol = _SEQ.get(row["Contaminant"])109        if not pol:110            continue111        annee = int(row["Date"][:4])112        if annee < 2022:113            continue114        v = row.get("Resultat")115        if not v:116            ld = row.get("LD")117            if not ld:118                continue119            try:120                x = float(ld) / 2.0            # convention < LD -> LD/2121            except ValueError:122                continue123        else:124            try:125                x = float(v)126            except ValueError:127                continue128        a = acc.setdefault((_sid(row["Station"]), annee, pol), [0.0, 0])129        a[0] += x130        a[1] += 1131        n += 1132    print(f"[air] séquentielles PST/PM10 : {n} mesures (≥ 2022)")133134    con = sqlite3.connect(DB_PATH)135    con.executescript("""136        DROP TABLE IF EXISTS air_stats;137        CREATE TABLE air_stats (station TEXT, nom TEXT, ville TEXT,138            lat REAL, lng REAL, annee INTEGER, polluant TEXT,139            moyenne REAL, n INTEGER);140        CREATE INDEX idx_air_latlng ON air_stats (lat, lng);141    """)142    now = time.strftime("%Y-%m-%d")143    rows = []144    for (sid, annee, pol), (somme, cnt) in acc.items():145        st = stations.get(sid)146        # minimum de couverture : 30 jours d'heures ou 15 échantillons147        if not st or cnt < (720 if pol in ("PM2.5", "NO2", "O3", "SO2", "CO")148                            else 15):149            continue150        rows.append((sid, st["nom"], st["ville"], st["lat"], st["lng"],151                     annee, pol, round(somme / cnt, 2), cnt))152    with con:153        con.executemany("INSERT INTO air_stats VALUES (?,?,?,?,?,?,?,?,?)",154                        rows)155        con.execute("CREATE TABLE IF NOT EXISTS meta (k TEXT PRIMARY KEY, v)")156        con.execute("INSERT OR REPLACE INTO meta VALUES ('maj', ?)", (now,))157    print(f"[air] {len(rows)} agrégats station-année-polluant -> {DB_PATH}")158    con.close()159160161def _dist_km(lat1, lng1, lat2, lng2) -> float:162    dlat = math.radians(lat2 - lat1)163    dlng = math.radians(lng2 - lng1)164    a = (math.sin(dlat / 2) ** 2 + math.cos(math.radians(lat1))165         * math.cos(math.radians(lat2)) * math.sin(dlng / 2) ** 2)166    return 6371 * 2 * math.asin(math.sqrt(a))167168169def lookup(lat: float, lng: float, max_km: float = 60.0) -> dict | None:170    """Mesures de la station RSQAQ la plus proche (année la plus récente)."""171    if not DB_PATH.exists():172        return None173    con = sqlite3.connect(f"file:{DB_PATH}?mode=ro", uri=True)174    con.row_factory = sqlite3.Row175    d = max_km / 111.0176    rows = con.execute(177        "SELECT * FROM air_stats WHERE lat BETWEEN ? AND ? AND lng BETWEEN "178        "? AND ?", (lat - d, lat + d, lng - d / max(0.2, math.cos(179            math.radians(lat))), lng + d / max(0.2, math.cos(180            math.radians(lat))))).fetchall()181    con.close()182    if not rows:183        return {"station": None}184    # station la plus proche disposant de données récentes185    best: dict[str, dict] = {}186    for r in rows:187        km = _dist_km(lat, lng, r["lat"], r["lng"])188        if km > max_km:189            continue190        b = best.setdefault(r["station"], {"km": km, "rows": [], "r": r})191        b["rows"].append(r)192    if not best:193        return {"station": None}194    sid, b = min(best.items(), key=lambda kv: kv[1]["km"])195    # par polluant : l'année la plus récente196    mesures: dict[str, dict] = {}197    for r in sorted(b["rows"], key=lambda r: -r["annee"]):198        if r["polluant"] in mesures:199            continue200        ref = REFS.get(r["polluant"])201        mesures[r["polluant"]] = {202            "moyenne": r["moyenne"], "annee": r["annee"], "n": r["n"],203            "unite": UNITES.get(r["polluant"], ""),204            "ref": ref[1] if ref else None,205            "ref_nom": ref[0] if ref else None}206    st = b["r"]207    return {"station": st["nom"], "ville": st["ville"],208            "distance_km": round(b["km"], 1), "mesures": mesures}209