krz/omaha-metro-blotter

  1"""Queries behind the dashboard: agency activity and distance to the nearest ALPR."""
  2
  3import sqlite3
  4from pathlib import Path
  5
  6import numpy as np
  7import pandas as pd
  8
  9DB = Path(__file__).parent / "raw_data" / "metro.db"
 10
 11# Sarpy and Council Bluffs publish officer-initiated stops; ingest.py flags them
 12# as is_stop. OPD publishes NIBRS offences only and contributes no stops.
 13POLICE_AGENCIES = ("Omaha PD", "Council Bluffs PD", "Bellevue PD", "Papillion PD",
 14                   "La Vista PD", "Sarpy County SO")
 15
 16EARTH_M = 6371000.0
 17
 18
 19def connect():
 20    return sqlite3.connect(DB)
 21
 22
 23def load_incidents(conn, agencies=None, start=None, end=None, categories=None):
 24    where, params = ["lat IS NOT NULL", "lon IS NOT NULL"], []
 25    if agencies:
 26        where.append(f"agency IN ({','.join('?' * len(agencies))})")
 27        params += list(agencies)
 28    if categories:
 29        where.append(f"category IN ({','.join('?' * len(categories))})")
 30        params += list(categories)
 31    if start:
 32        where.append("occurred_at >= ?")
 33        params.append(f"{start}T00:00:00")
 34    if end:
 35        where.append("occurred_at <= ?")
 36        params.append(f"{end}T23:59:59")
 37    df = pd.read_sql_query(
 38        f"""SELECT source, agency, case_id, occurred_at, category, call_type,
 39                   disposition, offense_desc, is_stop, address, lat, lon
 40            FROM incidents WHERE {' AND '.join(where)}""",
 41        conn, params=params)
 42    df["occurred_at"] = pd.to_datetime(df["occurred_at"])
 43    return df
 44
 45
 46def load_cameras(conn):
 47    return pd.read_sql_query(
 48        "SELECT osm_id, lat, lon, manufacturer, operator, direction, first_seen"
 49        " FROM alpr_cameras", conn)
 50
 51
 52def nearest_camera_m(df, cameras):
 53    """Great-circle distance in metres from each incident to the closest camera.
 54
 55    164 cameras against ~250k incidents, so the full distance matrix is computed
 56    in chunks rather than all at once."""
 57    if df.empty or cameras.empty:
 58        return np.full(len(df), np.nan)
 59
 60    lat1 = np.radians(df["lat"].to_numpy(dtype=float))
 61    lon1 = np.radians(df["lon"].to_numpy(dtype=float))
 62    lat2 = np.radians(cameras["lat"].to_numpy(dtype=float))
 63    lon2 = np.radians(cameras["lon"].to_numpy(dtype=float))
 64
 65    out = np.empty(len(df))
 66    cos2 = np.cos(lat2)
 67    for i in range(0, len(df), 20000):
 68        s = slice(i, i + 20000)
 69        dlat = lat2[None, :] - lat1[s, None]
 70        dlon = lon2[None, :] - lon1[s, None]
 71        a = (np.sin(dlat / 2) ** 2
 72             + np.cos(lat1[s, None]) * cos2[None, :] * np.sin(dlon / 2) ** 2)
 73        out[s] = (2 * EARTH_M * np.arcsin(np.sqrt(a))).min(axis=1)
 74    return out
 75
 76
 77def daily_counts(df):
 78    if df.empty:
 79        return pd.DataFrame(columns=["date", "agency", "incidents"])
 80    g = (df.assign(date=df["occurred_at"].dt.floor("D"))
 81           .groupby(["date", "agency"], as_index=False)
 82           .size().rename(columns={"size": "incidents"}))
 83    return g
 84
 85
 86def stop_outcomes(df):
 87    """Citation and arrest rate on vehicle stops, per agency.
 88
 89    The two CAD systems use different disposition vocabularies -- Sarpy writes
 90    WRITTEN WARNING / CITATION, Council Bluffs writes "3 - Citation" and folds
 91    warnings into "7 - Handled by Officer" -- and both allow several outcomes per
 92    stop. Substring matching on citation and arrest is the only comparison the
 93    two vocabularies actually support, and a department that records warnings
 94    less thoroughly will show a higher citation rate for that reason alone."""
 95    stops = df[(df["is_stop"] == 1) & df["disposition"].notna()]
 96    if stops.empty:
 97        return pd.DataFrame(columns=["agency", "outcome", "rate", "stops"])
 98    d = stops["disposition"].str.upper()
 99    stops = stops.assign(cited=d.str.contains("CITATION"),
100                         arrested=d.str.contains("ARREST"))
101    g = stops.groupby("agency").agg(stops=("cited", "size"),
102                                    Cited=("cited", "mean"),
103                                    Arrested=("arrested", "mean")).reset_index()
104    return (g.melt(id_vars=["agency", "stops"], value_vars=["Cited", "Arrested"],
105                   var_name="outcome", value_name="rate")
106             .sort_values("rate", ascending=False))
107
108
109def camera_proximity(df, cameras, bin_m=200, max_m=2000):
110    """Share of stops vs other incidents falling in each distance band.
111
112    Both series are normalised, so a gap between them means stops cluster
113    differently around cameras than the rest of the call volume does. It is not
114    evidence of causation: cameras and stops both concentrate on arterials."""
115    if df.empty or cameras.empty:
116        return pd.DataFrame(columns=["distance_m", "kind", "share"])
117    d = df.assign(dist=nearest_camera_m(df, cameras))
118    d = d[d["dist"] <= max_m]
119    if d.empty:
120        return pd.DataFrame(columns=["distance_m", "kind", "share"])
121    d["kind"] = np.where(d["is_stop"] == 1, "Vehicle stops", "All other incidents")
122    d["distance_m"] = (d["dist"] // bin_m * bin_m).astype(int)
123    g = (d.groupby(["kind", "distance_m"], as_index=False)
124           .size().rename(columns={"size": "n"}))
125    g["share"] = g["n"] / g.groupby("kind")["n"].transform("sum")
126    return g
127
128
129def agency_options(conn):
130    rows = conn.execute(
131        "SELECT agency, COUNT(*) FROM incidents GROUP BY agency ORDER BY 2 DESC"
132    ).fetchall()
133    return [a for a, _ in rows]
134
135
136def category_options(conn):
137    rows = conn.execute(
138        "SELECT category, COUNT(*) FROM incidents WHERE category IS NOT NULL"
139        " GROUP BY category ORDER BY 2 DESC").fetchall()
140    return [c for c, _ in rows]
141
142
143def date_bounds(conn):
144    lo, hi = conn.execute(
145        "SELECT MIN(occurred_at), MAX(occurred_at) FROM incidents").fetchone()
146    return lo[:10], hi[:10]