krz/omaha-metro-blotter

 1-- Incidents from every ingested feed. occurred_at is local time (America/Chicago);
 2-- the upstream services return UTC epochs and ingest.py converts on the way in.
 3CREATE TABLE IF NOT EXISTS incidents (
 4    source       TEXT NOT NULL,  -- opd | sarpy | cbpd | opd_csv
 5    source_key   TEXT NOT NULL,  -- PK (opd) | IncidentId (sarpy) | cfs_number (cbpd)
 6    agency       TEXT NOT NULL,
 7    case_id      TEXT,
 8    occurred_at  TEXT NOT NULL,  -- ISO 8601, no offset
 9    category     TEXT,           -- each feed's own taxonomy, not comparable
10    call_type    TEXT,           -- CadTypeDesc (sarpy) | incident_code (cbpd)
11    disposition  TEXT,           -- CAD disposition, sarpy and cbpd
12    offense_desc TEXT,           -- StatuteDesc (sarpy) | statute text (opd_csv)
13    is_stop      INTEGER NOT NULL DEFAULT 0,  -- officer-initiated vehicle stop
14    address      TEXT,
15    lat          REAL,
16    lon          REAL,
17    PRIMARY KEY (source, source_key)
18);
19
20CREATE INDEX IF NOT EXISTS incidents_occurred ON incidents (occurred_at);
21CREATE INDEX IF NOT EXISTS incidents_agency   ON incidents (agency, occurred_at);
22CREATE INDEX IF NOT EXISTS incidents_category ON incidents (category);
23CREATE INDEX IF NOT EXISTS incidents_stop     ON incidents (is_stop, occurred_at);
24
25-- ALPR cameras from OpenStreetMap (ODbL). first_seen/last_seen track when a node
26-- entered and was last present in the Overpass result, so cameras that appear or
27-- are removed are visible over time.
28CREATE TABLE IF NOT EXISTS alpr_cameras (
29    osm_id       INTEGER PRIMARY KEY,
30    lat          REAL NOT NULL,
31    lon          REAL NOT NULL,
32    manufacturer TEXT,
33    operator     TEXT,
34    direction    TEXT,
35    first_seen   TEXT NOT NULL,
36    last_seen    TEXT NOT NULL,
37    tags         TEXT NOT NULL  -- full OSM tag dict as JSON
38);