krz/omaha-metro-blotter

  1"""Precompute the public site's figures into a self-contained site/index.html.
  2
  3The dashboard in app.py needs a live Python process and refilters 300k rows on
  4every interaction. This does the aggregation once, at ingest time, and emits one
  5static file: no server, no dependencies, no per-visit cost."""
  6
  7import json
  8import sqlite3
  9from datetime import datetime, timezone
 10from pathlib import Path
 11
 12import numpy as np
 13
 14import analysis
 15
 16ROOT = Path(__file__).parent
 17OUT = ROOT / "site"
 18# Grid cells for the map. ~0.004 deg is roughly 300m of latitude here, fine
 19# enough to show which corridors stops sit on without shipping 40k points.
 20CELL = 0.004
 21
 22
 23def summary(conn):
 24    rows = conn.execute(
 25        """SELECT agency, COUNT(*), SUM(is_stop),
 26                  MIN(occurred_at), MAX(occurred_at)
 27             FROM incidents_current GROUP BY agency ORDER BY COUNT(*) DESC"""
 28    ).fetchall()
 29    return [{"agency": a, "incidents": n, "stops": s or 0,
 30             "first": lo[:10], "last": hi[:10]} for a, n, s, lo, hi in rows]
 31
 32
 33def totals(conn):
 34    q = lambda sql: conn.execute(sql).fetchone()[0]
 35    return {
 36        "incidents": q("SELECT COUNT(*) FROM incidents_current"),
 37        "stops": q("SELECT COUNT(*) FROM incidents_current WHERE is_stop=1"),
 38        "cameras": q("SELECT COUNT(*) FROM alpr_cameras"),
 39        "searches": q("SELECT COUNT(*) FROM alpr_searches"),
 40        "amendments": q("SELECT COUNT(*) FROM incident_amendments"),
 41        "raw": q("SELECT COUNT(*) FROM raw_records"),
 42        "built": datetime.now(timezone.utc).strftime("%Y-%m-%d %H:%M UTC"),
 43    }
 44
 45
 46def outcomes(conn):
 47    df = analysis.stop_outcomes(analysis.load_incidents(conn))
 48    if df.empty:
 49        return []
 50    wide = df.pivot(index="agency", columns="outcome", values="rate")
 51    counts = df.groupby("agency")["stops"].first()
 52    return [{"agency": a, "stops": int(counts[a]),
 53             "cited": round(float(wide.loc[a, "Cited"]), 4),
 54             "arrested": round(float(wide.loc[a, "Arrested"]), 4)}
 55            for a in wide.sort_values("Cited", ascending=False).index]
 56
 57
 58def proximity(conn):
 59    df = analysis.load_incidents(conn)
 60    cams = analysis.load_cameras(conn)
 61    prox = analysis.camera_proximity(df, cams)
 62    if prox.empty:
 63        return []
 64    wide = prox.pivot(index="distance_m", columns="kind", values="share")
 65    return [{"m": int(m),
 66             "stops": round(float(r.get("Vehicle stops", 0)), 5),
 67             "other": round(float(r.get("All other incidents", 0)), 5)}
 68            for m, r in wide.iterrows()]
 69
 70
 71def weekly(conn):
 72    df = analysis.load_incidents(conn)
 73    if df.empty:
 74        return {}
 75    w = (df.assign(week=df["occurred_at"].dt.to_period("W").dt.start_time)
 76           .groupby(["agency", "week"]).size().reset_index(name="n"))
 77    out = {}
 78    for agency, g in w.groupby("agency"):
 79        g = g.sort_values("week")
 80        # drop the trailing partial week so the last point is not a false dip
 81        g = g.iloc[:-1] if len(g) > 1 else g
 82        out[agency] = {"weeks": [d.strftime("%Y-%m-%d") for d in g["week"]],
 83                       "counts": [int(x) for x in g["n"]]}
 84    return out
 85
 86
 87def boundaries():
 88    """City limits and the county line, rounded to ~11 m for the wire."""
 89    src = ROOT / "raw_data" / "boundaries.geojson"
 90    if not src.exists():
 91        return []
 92    gj = json.loads(src.read_text())
 93    out = []
 94    for f in gj["features"]:
 95        g = f["geometry"]
 96        polys = ([g["coordinates"]] if g["type"] == "Polygon"
 97                 else g["coordinates"])
 98        rings = [[[round(x, 4), round(y, 4)] for x, y in ring]
 99                 for poly in polys for ring in poly]
100        out.append({"name": f["properties"]["name"],
101                    "kind": f["properties"]["kind"], "rings": rings})
102    return out
103
104
105def map_layers(conn):
106    df = analysis.load_incidents(conn)
107    stops = df[df["is_stop"] == 1].dropna(subset=["lat", "lon"])
108    lat = (np.floor(stops["lat"] / CELL) * CELL).round(4)
109    lon = (np.floor(stops["lon"] / CELL) * CELL).round(4)
110    grid = (stops.assign(clat=lat, clon=lon)
111                 .groupby(["clat", "clon"]).size().reset_index(name="n"))
112    grid = grid[grid["n"] >= 2]          # single stops are noise at this zoom
113    cams = analysis.load_cameras(conn)
114    return {
115        "cell": CELL,
116        "boundaries": boundaries(),
117        "cells": [[round(r.clat, 4), round(r.clon, 4), int(r.n)]
118                  for r in grid.itertuples()],
119        "cameras": [[round(r.lat, 5), round(r.lon, 5)] for r in cams.itertuples()],
120    }
121
122
123def searches(conn):
124    audit = analysis.search_audit(conn)
125    if audit.empty:
126        return None
127    row = audit.iloc[0]
128    counts = [r[0] for r in conn.execute(
129        "SELECT network_count FROM alpr_searches WHERE network_count IS NOT NULL")]
130    reasons = conn.execute(
131        """SELECT LOWER(reason), COUNT(*) FROM alpr_searches
132            WHERE reason IS NOT NULL GROUP BY 1 ORDER BY 2 DESC LIMIT 8""").fetchall()
133    edges = [0, 1, 10, 100, 500, 1000, 2500, 10000]
134    hist = []
135    for lo, hi in zip(edges, edges[1:]):
136        hist.append({"lo": lo, "hi": hi,
137                     "n": sum(1 for c in counts if lo < c <= hi)})
138    return {
139        "agency": row["agency"], "searches": int(row["searches"]),
140        "with_reason": int(row["with_reason"]),
141        "reason_rate": round(float(row["reason_rate"]), 4),
142        "median_networks": int(np.median(counts)) if counts else 0,
143        "max_networks": int(max(counts)) if counts else 0,
144        "first": row["earliest"][:10], "last": row["latest"][:10],
145        "histogram": hist,
146        "reasons": [{"reason": r, "n": n} for r, n in reasons],
147    }
148
149
150def build():
151    conn = sqlite3.connect(analysis.DB)
152    data = {
153        "totals": totals(conn),
154        "agencies": summary(conn),
155        "outcomes": outcomes(conn),
156        "proximity": proximity(conn),
157        "weekly": weekly(conn),
158        "map": map_layers(conn),
159        "searches": searches(conn),
160    }
161    conn.close()
162
163    OUT.mkdir(exist_ok=True)
164    template = (ROOT / "site_template.html").read_text()
165    payload = json.dumps(data, separators=(",", ":"))
166    (OUT / "index.html").write_text(template.replace("/*DATA*/null", payload))
167    return data, len(payload)
168
169
170if __name__ == "__main__":
171    data, size = build()
172    print(f"  site/index.html written, {size/1024:.0f} KiB of data")
173    for k, v in data["totals"].items():
174        print(f"    {k}: {v}")