krz/omaha-metro-blotter

Convert README.nfo to README.org !1

merged merged by cmc on 2026-08-31 22:41 UTC · krz/omaha-metro-blotter:nfo-to-org into main

5 files changed, +482 −350

Layout: unified · split

.gitbay/ci.yml deleted −147
@@ -1,147 +0,0 @@
1# Twice-daily archive pull, ported from the GitHub workflow. Sarpy and
2# Council Bluffs serve a rolling 12-month window; records that age out of
3# those feeds exist nowhere else. This job is the only thing keeping them,
4# so it refuses to publish an archive smaller than the one it started with.
5# The archive lives as metro.db.gz on the gitbay release tagged "archive";
6# the site deploys to the pages branch.
7jobs:
8 daily-pull:
9 schedule: "17 11,23 * * *"
10 steps:
11 - python3 -m venv .venv && .venv/bin/pip install -q -r requirements-ingest.txt
12 - |
13 set -e
14 export PATH="$PWD/.venv/bin:$PATH" DB=raw_data/metro.db HOST=git@127.0.0.1 R=krz/omaha-metro-blotter
15 mkdir -p raw_data
16
17 # Restore the published archive; a crashed publish leaves metro.db.new.gz.
18 if ssh $HOST release asset get $R archive metro.db.gz > metro.db.gz 2>/dev/null && [ -s metro.db.gz ]; then
19 :
20 elif ssh $HOST release asset get $R archive metro.db.new.gz > metro.db.gz 2>/dev/null && [ -s metro.db.gz ]; then
21 echo "recovered from interrupted publish"
22 else
23 echo "ERROR: no metro.db.gz on release 'archive'; aged-out records cannot be recovered"
24 exit 1
25 fi
26 gunzip -c metro.db.gz > "$DB" && rm metro.db.gz
27 echo "restored $(du -h "$DB" | cut -f1)"
28
29 counts() {
30 sqlite3 -noheader -separator ' ' "$DB" \
31 "SELECT source, COUNT(*) FROM incidents GROUP BY source ORDER BY source"
32 sqlite3 -noheader -separator ' ' "$DB" \
33 "SELECT source || '+amend', COUNT(*) FROM incident_amendments GROUP BY source ORDER BY source" 2>/dev/null || true
34 sqlite3 -noheader -separator ' ' "$DB" \
35 "SELECT source || '+raw', COUNT(*) FROM raw_records GROUP BY source ORDER BY source" 2>/dev/null || true
36 sqlite3 -noheader -separator ' ' "$DB" \
37 "SELECT agency || '+search', COUNT(*) FROM alpr_searches GROUP BY agency ORDER BY agency" 2>/dev/null || true
38 }
39 counts > before.txt
40 cat before.txt
41
42 # OPD amends records filed months ago; sweep the whole feed on Sundays.
43 if [ "$(date -u +%u)" = "7" ]; then
44 echo "full sweep"
45 python ingest.py --full opd sarpy cbpd alpr flock opd_archive
46 else
47 python ingest.py
48 fi
49
50 counts > after.txt
51 cat after.txt
52 test -s after.txt || { echo "ERROR: archive is empty"; exit 1; }
53 awk -v first=before.txt '
54 FILENAME == first { was[$1] = $2; next }
55 { now[$1] = $2 }
56 END {
57 for (s in was)
58 if (now[s] + 0 < was[s] + 0) {
59 printf "ERROR: %s lost rows: %d -> %d\n", s, was[s], now[s]
60 bad = 1
61 }
62 exit bad
63 }' before.txt after.txt
64
65 # An amendment identical to its original means the digest drifted.
66 phantom=$(sqlite3 -noheader "$DB" "
67 SELECT COUNT(*) FROM incident_amendments a
68 JOIN incidents o ON o.source = a.source AND o.source_key = a.source_key
69 WHERE a.agency IS o.agency AND a.case_id IS o.case_id
70 AND a.occurred_at IS o.occurred_at AND a.category IS o.category
71 AND a.call_type IS o.call_type AND a.disposition IS o.disposition
72 AND a.offense_desc IS o.offense_desc AND a.is_stop IS o.is_stop
73 AND a.address IS o.address AND a.lat IS o.lat AND a.lon IS o.lon")
74 if [ "$phantom" -ne 0 ]; then
75 echo "ERROR: $phantom amendments are identical to their original"
76 exit 1
77 fi
78
79 # Publish. The .new asset makes the sequence crash-safe: at every
80 # point at least one asset holds the full archive.
81 sqlite3 "$DB" "VACUUM;"
82 gzip -c "$DB" > metro.db.gz
83 ssh $HOST release asset remove $R archive metro.db.new.gz 2>/dev/null || true
84 ssh $HOST release asset add $R archive metro.db.new.gz < metro.db.gz
85 ssh $HOST release asset remove $R archive metro.db.gz 2>/dev/null || true
86 ssh $HOST release asset add $R archive metro.db.gz < metro.db.gz
87 ssh $HOST release asset remove $R archive metro.db.new.gz 2>/dev/null || true
88 rm metro.db.gz
89 {
90 echo "SQLite archive of Omaha metro police incident feeds, rebuilt twice daily."
91 echo "Sarpy County and Council Bluffs publish a rolling 12-month window,"
92 echo "so this holds records their own feeds no longer serve."
93 echo
94 echo "Updated $(date -u '+%Y-%m-%d %H:%M UTC'). Schema: schema.sql."
95 echo
96 echo "incidents holds each record as first published; every later"
97 echo "version the feed served is a row in incident_amendments"
98 echo "($(sqlite3 -noheader "$DB" 'SELECT COUNT(*) FROM incident_amendments') so far)."
99 echo
100 echo '```'
101 sqlite3 -header -column "$DB" \
102 "SELECT agency, COUNT(*) AS rows, SUM(is_stop) AS stops,
103 MIN(occurred_at) AS earliest, MAX(occurred_at) AS latest
104 FROM incidents GROUP BY agency ORDER BY rows DESC"
105 echo '```'
106 } > notes.md
107 ssh $HOST "release edit $R archive --title 'Incident archive' --file -" < notes.md
108 rm notes.md
109 echo "archive published"
110 - |
111 set -e
112 export PATH="$PWD/.venv/bin:$PATH" DB=raw_data/metro.db
113 .venv/bin/pip install -q -r requirements.txt
114 python build_site.py
115 origin=$(git remote get-url origin)
116 cd site && git init -q -b pages
117 git -c user.name=ci -c user.email=ci@gitbay.org add -A
118 git -c user.name=ci -c user.email=ci@gitbay.org commit -q -m "site $(date -u '+%Y-%m-%d %H:%M')"
119 git push -qf "$origin" pages:pages
120 echo "site deployed"
121 - |
122 set -e
123 export DB=raw_data/metro.db
124 # Alarms last, on purpose: a stale feed should fail the build loudly
125 # without having blocked the archive or the site.
126 sqlite3 -noheader "$DB" \
127 "SELECT source || ' ' || MAX(occurred_at) FROM incidents
128 WHERE source IN ('opd', 'sarpy', 'cbpd') GROUP BY source
129 HAVING MAX(occurred_at) < datetime('now', '-7 days')" > stale.txt
130 if [ -s stale.txt ]; then
131 while read -r line; do echo "ERROR: feed is stale: $line"; done < stale.txt
132 exit 1
133 fi
134 echo "all feeds current"
135 age=$(sqlite3 -noheader "$DB" "
136 SELECT CAST(julianday('now') - julianday(MAX(imported_at)) AS INT)
137 FROM alpr_searches" 2>/dev/null || echo "")
138 if [ -z "$age" ]; then echo "no Flock export on file yet"; exit 0; fi
139 echo "newest Flock export imported $age days ago"
140 if [ "$age" -ge 27 ]; then
141 echo "ERROR: Flock search audit is $age days old and the portal only keeps 30."
142 echo "Download it from https://transparency.flocksafety.com/council-bluffs-ia-pd"
143 echo "and commit it to raw_data/flock/ before the window closes."
144 exit 1
145 elif [ "$age" -ge 21 ]; then
146 echo "WARNING: Flock search audit is $age days old; refresh it soon."
147 fi
.github/workflows/daily-pull.yml added +269
@@ -0,0 +1,269 @@
1# Sarpy and Council Bluffs serve a rolling 12-month window; records that age out
2# of those feeds exist nowhere else. This job is the only thing keeping them, so
3# it refuses to publish an archive smaller than the one it started with.
4name: daily pull
5
6on:
7 schedule:
8 # Twice a day, because the cadence sets how much the rolling feeds drop
9 # before it is captured: a five-hour gap cost 13 Sarpy records once.
10 # Offset from the hour, GitHub drops on-the-hour runs under load.
11 - cron: "17 11 * * *"
12 - cron: "17 23 * * *"
13 workflow_dispatch:
14 inputs:
15 bootstrap:
16 description: "Start a new archive instead of restoring the published one"
17 type: boolean
18 default: false
19 full:
20 description: "Pull each feed in full rather than the last 30 days"
21 type: boolean
22 default: false
23
24permissions:
25 contents: write
26 pages: write
27 id-token: write
28
29concurrency:
30 group: archive
31 cancel-in-progress: false
32
33env:
34 TAG: archive
35 DB: raw_data/metro.db
36 GH_TOKEN: ${{ github.token }}
37
38jobs:
39 pull:
40 runs-on: ubuntu-latest
41 timeout-minutes: 45
42
43 steps:
44 - uses: actions/checkout@v4
45
46 - uses: actions/setup-python@v5
47 with:
48 python-version: "3.13"
49 cache: pip
50 cache-dependency-path: requirements-ingest.txt
51
52 - run: pip install -r requirements-ingest.txt
53
54 - name: Restore archive
55 run: |
56 if gh release download "$TAG" --pattern metro.db.gz --dir .; then
57 gunzip -c metro.db.gz > "$DB"
58 rm metro.db.gz
59 echo "restored $(du -h "$DB" | cut -f1)"
60 elif [ "${{ inputs.bootstrap }}" = "true" ]; then
61 echo "no published archive; starting a new one"
62 else
63 echo "::error::No metro.db.gz on release '$TAG'. Anything that has" \
64 "already aged out of the Sarpy and Council Bluffs feeds cannot" \
65 "be recovered. Re-run with bootstrap only if that is intended."
66 exit 1
67 fi
68
69 - name: Count rows before
70 run: |
71 : > before.txt
72 if [ -f "$DB" ]; then
73 sqlite3 -noheader -separator ' ' "$DB" \
74 "SELECT source, COUNT(*) FROM incidents GROUP BY source ORDER BY source" \
75 > before.txt
76 # absent until the amendment migration has run against this archive
77 sqlite3 -noheader -separator ' ' "$DB" \
78 "SELECT source || '+amend', COUNT(*) FROM incident_amendments
79 GROUP BY source ORDER BY source" >> before.txt 2>/dev/null || true
80 sqlite3 -noheader -separator ' ' "$DB" \
81 "SELECT source || '+raw', COUNT(*) FROM raw_records
82 GROUP BY source ORDER BY source" >> before.txt 2>/dev/null || true
83 sqlite3 -noheader -separator ' ' "$DB" \
84 "SELECT agency || '+search', COUNT(*) FROM alpr_searches
85 GROUP BY agency ORDER BY agency" >> before.txt 2>/dev/null || true
86 sqlite3 -noheader -separator ' ' "$DB" \
87 "SELECT source || '+raw', COUNT(*) FROM raw_records
88 GROUP BY source ORDER BY source" >> before.txt 2>/dev/null || true
89 sqlite3 -noheader -separator ' ' "$DB" \
90 "SELECT agency || '+search', COUNT(*) FROM alpr_searches
91 GROUP BY agency ORDER BY agency" >> before.txt 2>/dev/null || true
92 fi
93 cat before.txt
94
95 - name: Pull feeds
96 run: |
97 # A 30-day window cannot see an agency amending a record filed months
98 # ago, and OPD does exactly that, so sweep the whole feed on Sundays.
99 if [ "${{ inputs.full }}" = "true" ] || [ "${{ inputs.bootstrap }}" = "true" ] \
100 || [ "$(date -u +%u)" = "7" ]; then
101 echo "full sweep"
102 # opd_archive is closed years that never change; the weekly sweep is
103 # often enough to notice if OPD ever restates one.
104 python ingest.py --full opd sarpy cbpd alpr flock opd_archive
105 else
106 python ingest.py
107 fi
108
109 - name: Check nothing was lost
110 run: |
111 sqlite3 -noheader -separator ' ' "$DB" \
112 "SELECT source, COUNT(*) FROM incidents GROUP BY source ORDER BY source" \
113 > after.txt
114 sqlite3 -noheader -separator ' ' "$DB" \
115 "SELECT source || '+amend', COUNT(*) FROM incident_amendments
116 GROUP BY source ORDER BY source" >> after.txt
117 sqlite3 -noheader -separator ' ' "$DB" \
118 "SELECT source || '+raw', COUNT(*) FROM raw_records
119 GROUP BY source ORDER BY source" >> after.txt
120 sqlite3 -noheader -separator ' ' "$DB" \
121 "SELECT agency || '+search', COUNT(*) FROM alpr_searches
122 GROUP BY agency ORDER BY agency" >> after.txt
123 cat after.txt
124 test -s after.txt || { echo "::error::archive is empty"; exit 1; }
125 # Keyed on FILENAME, not NR == FNR: before.txt is empty on a bootstrap
126 # run, and awk never resets FNR for a zero-length file.
127 awk -v first=before.txt '
128 FILENAME == first { was[$1] = $2; next }
129 { now[$1] = $2 }
130 END {
131 for (s in was)
132 if (now[s] + 0 < was[s] + 0) {
133 printf "::error::%s lost rows: %d -> %d\n", s, was[s], now[s]
134 bad = 1
135 }
136 exit bad
137 }' before.txt after.txt
138
139 # An amendment identical to its original means the digest drifted
140 # against what SQLite stores, and every run would file the same
141 # phantom again. Cheap to check, silent and cumulative if it happens.
142 phantom=$(sqlite3 -noheader "$DB" "
143 SELECT COUNT(*) FROM incident_amendments a
144 JOIN incidents o ON o.source = a.source AND o.source_key = a.source_key
145 WHERE a.agency IS o.agency AND a.case_id IS o.case_id
146 AND a.occurred_at IS o.occurred_at AND a.category IS o.category
147 AND a.call_type IS o.call_type AND a.disposition IS o.disposition
148 AND a.offense_desc IS o.offense_desc AND a.is_stop IS o.is_stop
149 AND a.address IS o.address AND a.lat IS o.lat AND a.lon IS o.lon")
150 if [ "$phantom" -ne 0 ]; then
151 echo "::error::$phantom amendments are identical to their original"
152 exit 1
153 fi
154
155 - name: Publish archive
156 run: |
157 sqlite3 "$DB" "VACUUM;"
158 gzip -c "$DB" > metro.db.gz
159 gh release view "$TAG" >/dev/null 2>&1 \
160 || gh release create "$TAG" --title "Incident archive" --notes "building"
161 gh release upload "$TAG" metro.db.gz --clobber
162 {
163 echo "SQLite archive of Omaha metro police incident feeds, rebuilt daily."
164 echo "Sarpy County and Council Bluffs publish a rolling 12-month window,"
165 echo "so this holds records their own feeds no longer serve."
166 echo
167 echo "Updated $(date -u '+%Y-%m-%d %H:%M UTC'). Schema: schema.sql."
168 echo
169 echo "incidents holds each record as first published; every later"
170 echo "version the feed served is a row in incident_amendments"
171 echo "($(sqlite3 -noheader "$DB" 'SELECT COUNT(*) FROM incident_amendments') so far)."
172 echo "incidents_current is the newest version of each, and"
173 echo "raw_records keeps the feed's own JSON for every version"
174 echo "so a parse can be redone against what actually arrived."
175 echo
176 echo '```'
177 sqlite3 -header -column "$DB" \
178 "SELECT agency, COUNT(*) AS rows, SUM(is_stop) AS stops,
179 MIN(occurred_at) AS earliest, MAX(occurred_at) AS latest
180 FROM incidents GROUP BY agency ORDER BY rows DESC"
181 echo '```'
182 } > notes.md
183 gh release edit "$TAG" --notes-file notes.md
184
185 # Runs after the upload on purpose: a feed that stopped updating should
186 # raise the alarm without also blocking the archive from being published.
187 - name: Check the feeds are still moving
188 run: |
189 # An allowlist, not an exclusion: naming the live feeds means adding a
190 # backfill source cannot quietly turn this into a permanent failure,
191 # which is exactly what renaming opd_csv to opd_archive did.
192 sqlite3 -noheader "$DB" \
193 "SELECT source || ' ' || MAX(occurred_at) FROM incidents
194 WHERE source IN ('opd', 'sarpy', 'cbpd') GROUP BY source
195 HAVING MAX(occurred_at) < datetime('now', '-7 days')" > stale.txt
196 if [ -s stale.txt ]; then
197 while read -r line; do echo "::error::feed is stale: $line"; done < stale.txt
198 exit 1
199 fi
200 echo "all feeds current"
201
202 # The Flock export is the one thing here that cannot be automated: the
203 # portal challenges every non-browser client. Its window is 30 days, so
204 # this fails at 27 while there is still time to act, not afterwards.
205 - name: Check the Flock export is current
206 run: |
207 age=$(sqlite3 -noheader "$DB" "
208 SELECT CAST(julianday('now') - julianday(MAX(imported_at)) AS INT)
209 FROM alpr_searches" 2>/dev/null || echo "")
210 if [ -z "$age" ] || [ "$age" = "" ]; then
211 echo "no Flock export on file yet"; exit 0
212 fi
213 echo "newest Flock export imported $age days ago"
214 if [ "$age" -ge 27 ]; then
215 echo "::error::Flock search audit is $age days old and the portal only" \
216 "keeps 30. Download it from" \
217 "https://transparency.flocksafety.com/council-bluffs-ia-pd and commit" \
218 "it to raw_data/flock/ before the window closes."
219 exit 1
220 elif [ "$age" -ge 21 ]; then
221 echo "::warning::Flock search audit is $age days old; refresh it soon."
222 fi
223
224 - name: Summary
225 if: always()
226 run: |
227 {
228 echo "| source | before | after |"
229 echo "|---|---|---|"
230 awk -v first=before.txt '
231 FILENAME == first { was[$1] = $2; next }
232 { printf "| %s | %s | %s |\n", $1, ($1 in was ? was[$1] : 0), $2 }' \
233 before.txt after.txt
234 } >> "$GITHUB_STEP_SUMMARY"
235
236 # Separate job: the site needs pandas and numpy, and a Pages failure must not
237 # put the archive at risk.
238 site:
239 needs: pull
240 runs-on: ubuntu-latest
241 environment:
242 name: github-pages
243 url: ${{ steps.deploy.outputs.page_url }}
244
245 steps:
246 - uses: actions/checkout@v4
247
248 - uses: actions/setup-python@v5
249 with:
250 python-version: "3.13"
251 cache: pip
252 cache-dependency-path: requirements.txt
253
254 - run: pip install -r requirements.txt
255
256 - name: Fetch the published archive
257 run: |
258 gh release download "$TAG" --pattern metro.db.gz --dir .
259 mkdir -p raw_data
260 gunzip -c metro.db.gz > "$DB"
261
262 - run: python build_site.py
263
264 - uses: actions/configure-pages@v5
265 - uses: actions/upload-pages-artifact@v3
266 with:
267 path: site
268 - id: deploy
269 uses: actions/deploy-pages@v4
.mailmap deleted −2
@@ -1,2 +0,0 @@
1Christian Cleberg <hello@cleberg.net> <156287552+ccleberg@users.noreply.github.com>
2Christian Cleberg <hello@cleberg.net> <hello@cmc.pub>
README.nfo deleted −201
@@ -1,201 +0,0 @@
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└──────────────────────────────────────────────────────────────┘
README.org added +213
@@ -0,0 +1,213 @@
1#+title: omaha metro blotter
2
3* what
4an archive of police activity and alpr surveillance across the
5omaha metro, pulled from the agencies' own feeds. sarpy county,
6council bluffs and the flock portals all serve rolling windows and
7delete what falls outside them; this keeps it.
8
9* coverage
10#+begin_example
11omaha pd dcgis arcgis view, 2022-01-01 onward,
12 nibrs offence records, updated daily.
13 no stop or disposition data.
14bellevue pd sarpy county publiccrimemap, cad calls
15papillion pd for service with stop type, disposition
16la vista pd and category. rolling 12-month window:
17sarpy 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.
21council 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.
25ralston pd no machine-readable feed. absent.
26#+end_example
27
28#+begin_example
29alpr cameras openstreetmap via overpass, the same data
30 deflock renders. 169 nodes in the metro
31 bbox. odbl, attribution required.
32#+end_example
33
34#+begin_example
35alpr searches flock transparency portals. council bluffs
36 publishes a downloadable 30-day search
37 audit; sarpy county and douglas county
38 publish counts but no export. omaha pd has
39 no portal.
40#+end_example
41
42* setup
43#+begin_src sh
44uv venv .venv
45uv pip install --python .venv/bin/python -r requirements.txt
46#+end_src
47
48* use
49#+begin_src sh
50.venv/bin/python ingest.py --full # first run, backfill
51.venv/bin/python ingest.py # daily, last 30 days
52.venv/bin/python ingest.py cbpd sarpy # one source at a time
53.venv/bin/python ingest.py opd_archive # omaha 2015-2021 backfill
54.venv/bin/python app.py
55#+end_src
56
57* site
58build_site.py precomputes every figure into one self-contained
59site/index.html -- no server, no fetch, no dependencies, 38 kb of
60data. the workflow rebuilds it after each pull and deploys it to
61github pages. app.py stays as the exploration tool; it needs a live
62process and refilters 300k rows per interaction, which is fine for
63one person and wrong for the public.
64
65#+begin_src sh
66.venv/bin/python build_site.py && open site/index.html
67#+end_src
68
69the map's outlines come from raw_data/boundaries.geojson: douglas
70county city limits and boundary, plus sarpy county municipal
71boundaries. static reference geometry, so refreshing it is a rare
72manual step rather than part of the daily pull.
73
74#+begin_src sh
75.venv/bin/python fetch_boundaries.py
76#+end_src
77
78pottawattamie county, iowa publishes neither, so council bluffs is
79unoutlined. douglas county sheriff publishes no incident or calls
80feed at all -- only a flock portal with counts -- so the area
81inside omaha's limits carries cameras and no stops.
82
83* archive
84.github/workflows/daily-pull.yml runs at 11:17 and 23:17 utc and
85keeps the database as metro.db.gz on the "archive" release, so the
86archive does not depend on any one machine. each run restores that
87asset, pulls, refuses to publish if any source came back with fewer
88rows than it started with, then uploads and fails loudly if a feed
89has not moved in seven days.
90
91sundays it sweeps every feed in full instead of the last 30 days,
92because a 30-day window cannot see an agency amending a record it
93filed months ago, and omaha does that.
94
95first run: trigger it manually with bootstrap enabled, which pulls
96every feed in full and creates the release. after that the restore
97step is mandatory -- a bootstrap over a live archive throws away
98whatever has already aged out of the sarpy and council bluffs
99feeds.
100
101github disables scheduled workflows after 60 days without repo
102activity, and emails first. that is the most likely way this stops
103quietly.
104
105to run the pull locally instead:
106
107#+begin_src sh
1080 6 * * * cd /path/to/omaha-metro-blotter && .venv/bin/python ingest.py
109#+end_src
110
111* raw
112raw_records keeps the feed's own json for every version of every
113record, keyed the same way amendments are. a parse that turns out
114wrong, or a field a feed adds later, can only be applied to history
115if the bytes were kept, and the rolling feeds mean there is no
116second chance to fetch them. the payloads already carry fields
117ingest does not map: council bluffs response times and priority,
118sarpy case status.
119
120it costs about 0.7 mb gzipped a day and roughly triples the
121database: 20 mb published without it, 52 mb with. 319 records
122predate it and their raw is gone; the feeds no longer serve them.
123
124* flock search audit
125transparency.flocksafety.com/<slug> publishes camera counts, plate
126reads, search counts and each agency's sharing network. council
127bluffs also offers the search audit itself as a csv: one row per
128search, with a timestamp, how many camera networks it reached and
129a free-text reason.
130
131cloudflare serves a challenge to every non-browser client, so
132ingest.py cannot fetch it. collecting is manual: open the portal,
133click "download csv", then
134
135#+begin_src sh
136.venv/bin/python ingest.py --import-flock ~/Downloads/public_search_audit.csv
137#+end_src
138
139which checks the columns, files it under the right slug and loads
140it. commit what it writes. every export is named
141public_search_audit.csv with the agency nowhere inside, so pass
142--agency for any portal other than council bluffs.
143
144loading is not manual -- the flock source runs in the daily job and
145picks up whatever is committed. search ids are stable uuids, so
146overlapping exports dedupe and re-importing the same window is a
147no-op.
148
149the portals keep 30 days. miss a month and that month is gone, so
150the workflow warns at 21 days since the last export and fails the
151run at 27, while there is still time to act.
152
153as of the first export, 100 of 442 council bluffs searches carried
154any reason at all, against an access policy stating that all access
155requires one. userid is redacted upstream, so no search can be
156attributed to a person. the median search reached 466 camera
157networks; the largest reached 6072.
158
159all three portals list traffic enforcement under prohibited uses,
160which is what the camera-proximity panel is measuring against.
161
162* amendments
163agencies edit records after publishing them: a disposition changes,
164a case reopens, a record is withdrawn. nothing in the incidents
165table is ever updated, so what an agency published first stays
166readable. every later version the feed serves lands in
167incident_amendments, and incidents_current is the newest version of
168each record. analysis.changed_stop_outcomes() lists stops whose
169disposition changed after filing.
170
171a version is keyed on the hash of its payload, so a record that
172reverts to a payload already on file is not recorded again. this
173holds the set of distinct states observed, not a strict timeline.
174
175the hash has to survive a round trip through sqlite. a lon of -96
176arrives from the feed as a json int and comes back out of a REAL
177column as -96.0, so lat, lon and is_stop are coerced before
178hashing. get this wrong and every affected record is filed as
179amended on every run, forever. the workflow fails if any amendment
180is byte-identical to its original.
181
182* notes
183all three arcgis services return utc epochs; their where-clause
184literals do not agree (opd and council bluffs utc, sarpy central).
185ingest.py stores occurred_at in local time.
186
187each feed has its own taxonomy and none of them are comparable, so
188ingest.py derives one cross-agency flag, is_stop, per source. stop
189outcomes compare citation and arrest rate by substring, which is
190all the two disposition vocabularies support: an agency that
191records warnings less thoroughly shows a higher citation rate for
192that reason alone.
193
194colour scheme follows prefers-color-scheme. plotly writes colours
195into the figure, so assets/theme.js reports the media query into a
196store and app.py builds each figure from it. restyling after the
197fact does not work: swapping a maplibre basemap at runtime leaves
198it rebuilding with no data layers.
199
200the camera-proximity panel compares stops against other calls from
201the same agencies. the baseline has to be restricted that way: run
202against the whole archive it shows stops 2.6x more likely to be
203within 200m of a camera, but most of the archive is omaha, which
204reports no stops, so that number measures geography rather than
205enforcement. like for like it is 1.19x, and median distance is
206805m for stops against 780m for everything else -- no meaningful
207separation.
208
209raw_data/ingress.db is the old 2015-2023 sqlite build. nothing
210reads it any more.
211
212* screenshots
213screenshots/*.png are from the previous 2015-2023 dashboard.