krz/omaha-metro-blotter

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