krz/omaha-metro-blotter

  1┌──────────────────────────────────────────────────────────────┐
  2│  O M A H A   M E T R O   B L O T T E R     [ KRZ ]   krz.sh  │
  3└──────────────────────────────────────────────────────────────┘
  4
  5WHAT
  6  an archive of police activity and alpr surveillance across the
  7  omaha metro, pulled from the agencies' own feeds. sarpy county,
  8  council bluffs and the flock portals all serve rolling windows and
  9  delete what falls outside them; this keeps it.
 10
 11COVERAGE
 12  omaha pd                dcgis arcgis view, 2022-01-01 onward,
 13                          nibrs offence records, updated daily.
 14                          no stop or disposition data.
 15  bellevue pd             sarpy county publiccrimemap, cad calls
 16  papillion pd            for service with stop type, disposition
 17  la vista pd             and category. rolling 12-month window:
 18  sarpy county so         records age out of the feed, so the local
 19                          archive is the only long-term copy. gretna
 20                          and springfield appear as fire only; their
 21                          police departments do not report to it.
 22  council bluffs pd       cbpd public cfs feed, refreshed every ten
 23                          minutes, rolling 12-month window. stop
 24                          type, disposition, priority, response time.
 25                          street addresses withheld; points exact.
 26  ralston pd              no machine-readable feed. absent.
 27
 28  alpr cameras            openstreetmap via overpass, the same data
 29                          deflock renders. 169 nodes in the metro
 30                          bbox. odbl, attribution required.
 31
 32  alpr searches           flock transparency portals. council bluffs
 33                          publishes a downloadable 30-day search
 34                          audit; sarpy county and douglas county
 35                          publish counts but no export. omaha pd has
 36                          no portal.
 37
 38SETUP
 39      uv venv .venv
 40      uv pip install --python .venv/bin/python -r requirements.txt
 41
 42USE
 43      .venv/bin/python ingest.py --full        # first run, backfill
 44      .venv/bin/python ingest.py               # daily, last 30 days
 45      .venv/bin/python ingest.py cbpd sarpy    # one source at a time
 46      .venv/bin/python ingest.py opd_archive   # omaha 2015-2021 backfill
 47      .venv/bin/python app.py
 48
 49SITE
 50  build_site.py precomputes every figure into one self-contained
 51  site/index.html -- no server, no fetch, no dependencies, 38 kb of
 52  data. the workflow rebuilds it after each pull and deploys it to
 53  github pages. app.py stays as the exploration tool; it needs a live
 54  process and refilters 300k rows per interaction, which is fine for
 55  one person and wrong for the public.
 56
 57      .venv/bin/python build_site.py && open site/index.html
 58
 59  the map's outlines come from raw_data/boundaries.geojson: douglas
 60  county city limits and boundary, plus sarpy county municipal
 61  boundaries. static reference geometry, so refreshing it is a rare
 62  manual step rather than part of the daily pull.
 63
 64      .venv/bin/python fetch_boundaries.py
 65
 66  pottawattamie county, iowa publishes neither, so council bluffs is
 67  unoutlined. douglas county sheriff publishes no incident or calls
 68  feed at all -- only a flock portal with counts -- so the area
 69  inside omaha's limits carries cameras and no stops.
 70
 71ARCHIVE
 72  .github/workflows/daily-pull.yml runs at 11:17 and 23:17 utc and
 73  keeps the database as metro.db.gz on the "archive" release, so the
 74  archive does not depend on any one machine. each run restores that
 75  asset, pulls, refuses to publish if any source came back with fewer
 76  rows than it started with, then uploads and fails loudly if a feed
 77  has not moved in seven days.
 78
 79  sundays it sweeps every feed in full instead of the last 30 days,
 80  because a 30-day window cannot see an agency amending a record it
 81  filed months ago, and omaha does that.
 82
 83  first run: trigger it manually with bootstrap enabled, which pulls
 84  every feed in full and creates the release. after that the restore
 85  step is mandatory -- a bootstrap over a live archive throws away
 86  whatever has already aged out of the sarpy and council bluffs
 87  feeds.
 88
 89  github disables scheduled workflows after 60 days without repo
 90  activity, and emails first. that is the most likely way this stops
 91  quietly.
 92
 93  to run the pull locally instead:
 94
 95      0 6 * * *  cd /path/to/omaha-metro-blotter && .venv/bin/python ingest.py
 96
 97RAW
 98  raw_records keeps the feed's own json for every version of every
 99  record, keyed the same way amendments are. a parse that turns out
100  wrong, or a field a feed adds later, can only be applied to history
101  if the bytes were kept, and the rolling feeds mean there is no
102  second chance to fetch them. the payloads already carry fields
103  ingest does not map: council bluffs response times and priority,
104  sarpy case status.
105
106  it costs about 0.7 mb gzipped a day and roughly triples the
107  database: 20 mb published without it, 52 mb with. 319 records
108  predate it and their raw is gone; the feeds no longer serve them.
109
110FLOCK SEARCH AUDIT
111  transparency.flocksafety.com/<slug> publishes camera counts, plate
112  reads, search counts and each agency's sharing network. council
113  bluffs also offers the search audit itself as a csv: one row per
114  search, with a timestamp, how many camera networks it reached and
115  a free-text reason.
116
117  cloudflare serves a challenge to every non-browser client, so
118  ingest.py cannot fetch it. collecting is manual: open the portal,
119  click "download csv", then
120
121      .venv/bin/python ingest.py --import-flock ~/Downloads/public_search_audit.csv
122
123  which checks the columns, files it under the right slug and loads
124  it. commit what it writes. every export is named
125  public_search_audit.csv with the agency nowhere inside, so pass
126  --agency for any portal other than council bluffs.
127
128  loading is not manual -- the flock source runs in the daily job and
129  picks up whatever is committed. search ids are stable uuids, so
130  overlapping exports dedupe and re-importing the same window is a
131  no-op.
132
133  the portals keep 30 days. miss a month and that month is gone, so
134  the workflow warns at 21 days since the last export and fails the
135  run at 27, while there is still time to act.
136
137  as of the first export, 100 of 442 council bluffs searches carried
138  any reason at all, against an access policy stating that all access
139  requires one. userid is redacted upstream, so no search can be
140  attributed to a person. the median search reached 466 camera
141  networks; the largest reached 6072.
142
143  all three portals list traffic enforcement under prohibited uses,
144  which is what the camera-proximity panel is measuring against.
145
146AMENDMENTS
147  agencies edit records after publishing them: a disposition changes,
148  a case reopens, a record is withdrawn. nothing in the incidents
149  table is ever updated, so what an agency published first stays
150  readable. every later version the feed serves lands in
151  incident_amendments, and incidents_current is the newest version of
152  each record. analysis.changed_stop_outcomes() lists stops whose
153  disposition changed after filing.
154
155  a version is keyed on the hash of its payload, so a record that
156  reverts to a payload already on file is not recorded again. this
157  holds the set of distinct states observed, not a strict timeline.
158
159  the hash has to survive a round trip through sqlite. a lon of -96
160  arrives from the feed as a json int and comes back out of a REAL
161  column as -96.0, so lat, lon and is_stop are coerced before
162  hashing. get this wrong and every affected record is filed as
163  amended on every run, forever. the workflow fails if any amendment
164  is byte-identical to its original.
165
166NOTES
167  all three arcgis services return utc epochs; their where-clause
168  literals do not agree (opd and council bluffs utc, sarpy central).
169  ingest.py stores occurred_at in local time.
170
171  each feed has its own taxonomy and none of them are comparable, so
172  ingest.py derives one cross-agency flag, is_stop, per source. stop
173  outcomes compare citation and arrest rate by substring, which is
174  all the two disposition vocabularies support: an agency that
175  records warnings less thoroughly shows a higher citation rate for
176  that reason alone.
177
178  colour scheme follows prefers-color-scheme. plotly writes colours
179  into the figure, so assets/theme.js reports the media query into a
180  store and app.py builds each figure from it. restyling after the
181  fact does not work: swapping a maplibre basemap at runtime leaves
182  it rebuilding with no data layers.
183
184  the camera-proximity panel compares stops against other calls from
185  the same agencies. the baseline has to be restricted that way: run
186  against the whole archive it shows stops 2.6x more likely to be
187  within 200m of a camera, but most of the archive is omaha, which
188  reports no stops, so that number measures geography rather than
189  enforcement. like for like it is 1.19x, and median distance is
190  805m for stops against 780m for everything else -- no meaningful
191  separation.
192
193  raw_data/ingress.db is the old 2015-2023 sqlite build. nothing
194  reads it any more.
195
196SCREENSHOTS
197  screenshots/*.png are from the previous 2015-2023 dashboard.
198
199┌──────────────────────────────────────────────────────────────┐
200│  krz.sh                                                      │
201└──────────────────────────────────────────────────────────────┘