Module

Lightning Proximity

Turn the vendor's per-minute lightning-strike feed into a proximity-and-status service keyed on City facilities, so a supervisor can decide — for the pool, the ballfield, the crew's worksite — whether to hold, resume, or evacuate. Strikes already flow in through the Mailbox Watcher and land in data.LightningStrike; this module is the read/analysis layer (LightningStrikesController) plus the City Viewer and MyGov surfaces that render it. A separate workflow track replaces the legacy notification email.

Status Draft — design
Last updated —
Related Mailbox Watcher · Workflow Engine · Location / Geo Integration · Facility View / City Viewer · Distribution

Module Definition

A weather vendor (AWIS) emails the city once a minute; if any lightning struck in or near Auburn in that minute, the message carries the strike(s) as coordinates. The operational problem: personnel spread across the city — Public Works construction crews, Public Safety at special events, Parks & Rec running a program or deciding whether an outdoor pool stays open — need near-real-time proximity data to make their own hold/resume/evacuate calls.

The legacy system parsed each email, stored the strikes, computed a distance from every City facility to the strikes, appended that as a table to the vendor's email, and forwarded it to a staff distribution list. This module keeps the same facility-anchored framing — and that framing is deliberate, not a simplification:

Design principle — the anchor is the facility, not the person. Because of the city's command structure, it is usually a supervisor making the call and relaying it to crews, often for several sites at once — and that supervisor is rarely standing at the pool or the worksite. So the primary surface is a list/dashboard of facilities each carrying its own proximity + status, not a "where am I" locator. Arbitrary-point proximity (a dragged pin, a live GPS) is a cheap wrapper over the same calculation and stays available, but it does not drive the design.
Design principle — two independent tracks off one dataset. The controller (this doc's focus) serves the MyGov + City Viewer visual surfaces and is entirely web-facing. Replacing the legacy email is a separate track that lives inside the workflow engine — a stored procedure seeded into the workflow context by MSSqlHandler, rendered by an email template, forwarded to the list (§7). The email's needs never shape the controller, and the two can ship independently. The legacy job keeps running until the email replacement is in place.

1 · Data — data.LightningStrike

The strike table already exists and is live-synced from the vendor feed via the Mailbox Watcher pipeline (TextExtract → MsSql.Merge). One column definition matters for everything below.

ColumnTypeRole
Idint PKIdentity.
Namevarchar(50)Vendor alert class (ALERT / WARNING).
Timestampdatetime2When the strike occurred. Leading column of the natural-key index — every query filters on it first.
Typevarchar(50)Cloud Stroke Lightning / Ground Stroke Lightning.
Latitude / Longitudedecimal(9,6)Decimal degrees. The exact-match half of the Merge natural key.
Locationgeography(4326)Computed + persisted from Latitude/Longitude. This is what proximity queries measure against — Location.STDistance(@anchor) returns real ellipsoidal metres on WGS84.

The unique key is (Timestamp, Latitude, Longitude, Name, Type) — all five, because the vendor legitimately reports both an ALERT and a WARNING, and both a cloud and a ground stroke, at the same instant and point. No spatial index is needed at city volumes: a storm is a few hundred rows in 30 minutes, and the Timestamp filter shrinks any query to that small residual before STDistance runs.

2 · The Proximity Calculation

One calculation underlies every endpoint: given an anchor point (a facility's location), a danger radius, a danger window, and a history window, derive the block below. The endpoints are thin wrappers that supply the anchor from different sources.

Derived valueDefinitionAnswers
lastStrike Distance from the facility to the single most-recent strike system-wide, plus its age. "Where is the newest activity relative to this facility?" (the web analog of the legacy email's Last Strike column — see the note below).
closest Minimum distance to any strike within the history window, plus that strike's timestamp. "How close did it get, and when?" (the legacy Closest (8 hr) column).
status Active (a strike inside the danger radius within the danger window) / Clearing (none now, but the last in-radius strike was < all-clear ago → countdown) / Clear. "Do I hold, resume, or evacuate — right now?"
countInWindow Count of strikes within the danger radius during the danger window. Storm intensity near this facility.
Two independent windows. The danger window (~30 min) + danger radius (~8–10 mi) drive status and the all-clear countdown — the lightning-safety threshold. The history window (~8 hr default) drives the closest context column; the larger range exists to cover a whole storm passing through, per the legacy tool. All three are plain endpoint-parameter defaults, overridable per request; not per-facility (that earns its place only if a specific site genuinely needs a different radius — a v2 question, not v1) and deliberately not appsettings — nothing today needs them tunable without a deploy (the per-request override already gives the real flexibility), and a staff-editable version later belongs in a database-backed config (this codebase's DataList pattern), not a deploy-owned file. See §3 and §6.
Why lastStrike differs subtly from the legacy email. In the email, "Last Strike" meant the closest strike in this email's batch to each facility — a per-minute construct, because sequential processing means the message being handled holds the newest strikes. The controller has no "this email"; it reads a continuous stream, so its analog is distance to the single most-recent strike (which the live map also shows as the newest dot). The exact per-batch column belongs to the email track (§7), computed in T-SQL there — not replicated here.
Deferred — storm bearing / approach vector. A crude "moving toward you / away" vector from the centroid shift of the last few strikes is the one analysis the legacy email couldn't show and the strongest pull toward a live map. It is explicitly not a threshold driver (the legacy attempt to build on it over-complicated the tool). Revisit as a visual-only overlay after v1.

3 · API — LightningStrikesController

Route prefix /lightning-strikes. Every endpoint accepts an optional asOf (default: now) so the same code answers both "right now" (operations) and "replay last night's storm" (review) — nearly free, since it only shifts the window anchor.

RouteMethodReturns / does
/lightning-strikes?since=&bbox=&type=&asOf=GETRaw strikes for the map — time-windowed, optionally clipped to a viewport bbox and/or type. The "plot the dots" feed; the client time-decays them (recent bright → older faded).
/lightning-strikes/status?asOf=GETCity-wide rollup: is any facility Active? highest-threat facility, total strikes in the last N min. Cheap ~30–60 s poll for a banner / nav pill (matches the vendor's per-minute cadence).
/lightning-strikes/facilities?radiusMiles=&dangerMinutes=&historyHours=&asOf=GETThe primary endpoint. Every facility with its full proximity block (§2), sorted by threat. Drives the supervisor dashboard and is the direct successor to the emailed table.
/lightning-strikes/facilities/{id}/proximity?…&asOf=GETThe same block scoped to one facility — for a facility detail panel and the reusable per-facility component (§6).
/lightning-strikes/proximity?lat=&lng=&radiusMiles=&dangerMinutes=&historyHours=&asOf=GETOptional The same calculation for an arbitrary point (a dragged pin, a crew's GPS). Trivial once the facility path exists; not the driver.

Decided (2026-07-15): all five endpoints are [AllowAnonymous]. City Viewer is a public, mostly-unauthenticated PWA — its apiGet() helper is anonymous-only, so a staff-gated controller would have been unreachable from it. Facility names/locations are already public (the same facilities City Viewer's Places tab shows), and the point of this feature is reaching personnel in the field who may not be signed into anything, so there's no split between "safe" strike/status data and "sensitive" facility data — everything is open.

4 · Projection — Repairing Facility.LatLongPoint

The one real snag: strikes are geography(4326) (WGS84 degrees), but Facility.Point is geometry(102629) (Alabama State Plane, feet). STDistance can't cross coordinate frames, so the anchor must be a WGS84 point. The decision: repair and repurpose the existing (currently [Obsolete]) Facility.LatLongPoint as the canonical WGS84 anchor, stored — not transformed per request. This is the higher-leverage answer to the geo layer's open "where does 102629→4326 conversion happen" question (see Location): it happens once, on write, and every consumer reads a ready point.

Honesty on cost — this is write-path work, not a computed column. SQL Server can't reproject 102629→4326 natively, so LatLongPoint can't be a computed column the way the strike's Location is. It must be stamped app-side on facility save — the same shape as the IAuditable stamping — using GeometryHelper / ProjNet (the multi-CRS→WGS84 path City Requests already uses). The work: (1) a one-time backfill script populating LatLongPoint from Point for existing facilities; (2) guard every facility write path (create / update / patch) so a coordinate edit recomputes it and it can't drift; (3) drop the [Obsolete] marker. The downside the team accepted: those write-path guards must be kept correct, or the stored point silently goes stale.

5 · Calculation Mechanics

  • Distance: strike.Location.STDistance(@facilityPoint) → metres → miles (/ 1609.344). Both sides WGS84 geography after §4.
  • Query shape: filter Timestamp >= @windowStart first (indexed), then STDistance over the small residual. No spatial index required at transition volumes; add one only if a facilities-batch query over a severe multi-hour storm ever measures slow.
  • Status derivation is pure C# over the windowed strike set per facility — no extra round-trips. Active if any in-radius strike within the danger window; else Clearing with allClearInMinutes = allClearWindow − minutesSinceLastInRadius while that's positive; else Clear.
  • Batch efficiency: the /facilities endpoint loads the windowed strike set once and evaluates all ~50–150 facilities against it in memory — one strike read, N cheap distance passes — rather than a query per facility.

6 · Surfaces

  • MyGov admin — the supervisor dashboard: facilities sorted by threat (status pill · all-clear countdown · lastStrike · closest), a storm-visualization map (strike dots + facility markers + danger-radius rings), and the config for thresholds and the legacy distribution list during transition. Consumes /facilities and /lightning-strikes.
  • City Viewer PWA — a storm view: the map, a facility-threat list, a status pill + all-clear countdown. Polls /status on the vendor's ~1 min cadence (poll, not push, for v1).
  • Reusable per-facility component — a <facility-lightning-status> element that takes a facility id and renders current status + lastStrike + closest + a mini radius ring, consuming /facilities/{id}/proximity. Droppable into both the PWA facility view and the MyGov facility detail page, so lightning context appears wherever a facility already appears — not only on the dedicated dashboard.
Push is the fast-follow, not v1. City Viewer already has VAPID push. The clean path when it lands: a workflow action after the Merge step evaluates threat and fires the push — reusing the workflow engine rather than adding a bespoke notifier. Poll first; push when the surfaces are proven.

6a · Storm Map Base Layer — the Aerial Tile System In progress

The storm map (strike dots + facility markers on something visual) needs a base layer, and neither app has a map library vendored (City Viewer has none at all; Leaflet+Esri was planned-but-never-built for City Requests' location step too). Rather than vendor one, the answer landed on is "cheat the base layer": pre-render static aerial-photo tiles from the city's own GIS imagery and place points on them with plain affine math — the same pan/zoom-over-a-static-image pattern fv-map.js already uses for facility floor plans, just with a photo instead of a floor plan. This is a shared, general-purpose capability (not lightning-specific) — City Requests' location picker is the other real consumer. It was built here because this is where the need surfaced first; its own design doc under geo-integration.html is still owed once the viewer side exists to describe.

PieceWhereStatus
Data modelapi/Models/organization/AerialTile.cs — schema mirrors FacilityFloorUnderlay (State Plane feet, Y-up placement rect + native pixel dims); Slug drives /media/aerial/{slug}-{year}.jpg.Built
APIapi/Controllers/AerialTilesController.cs — GET /aerial-tiles, /{id}, /covering?lat=&lng= (or x=&y=), sorted finest-first by feet-per-pixel, and /viewport?x=&y=&widthFt=&heightFt=&px= — the level-selection endpoint: picks the coarsest pyramid level that stays sharp at the given screen density and returns that level's tiles intersecting the rect (capped, stepping coarser rather than fanning out). Level choice lives server-side because the full tile list (~2,200 rows) is too heavy for clients. [AllowAnonymous], matching LightningStrikesController. Image URLs: rows store media/aerial/… relative to the static root while MediaSettings:BaseUrl also ends in /media — the DTO collapses the overlap (the naive join 404'd).Built
MigrationAddAerialTile — generated, not yet applied.Pending apply
Tile generatorscripts/aerial-tiles/generate_aerial_tiles.py — pure Python + Pillow (no GDAL/ArcGIS). Reads GIS's raw GeoTIFF + world-file tiles read-only off the network share; writes images + a MERGE .sql script.Built
Generation runL07 down through L01 for the full "City of Auburn" bbox (~2,200 tiles: single city-wide images at L05–L07, grids below — L01 = 0.75 ft/px), images on the webserver and rows in organization.AerialTile.Done
Pan/zoom viewerfacility-view/js/components/cv-storm-map.js — the SVG's user space IS State Plane feet (Y negated), so placement is (x, −y) and pan/zoom is viewBox arithmetic (fv-map's gesture model at city scale). Coarse city image as permanent backdrop, /viewport swaps sharper tiles on settle (/covering center-tile fallback on older APIs), strike dots age-fade, facility markers status-colored, tap-select shows name + danger ring. Client-side WGS84→State-Plane projection in js/geo.js (closed-form TM from GeometryHelper's exact WKT constants; validated against the server at six city points). Embedded in cv-lightning between banner and list; MyGov dashboard embed still open.Built
Why tiles, not one image at increasing resolution. The first pass built 3 whole-city images at different downsample amounts — that's mip levels of one image, not tiles, and tops out around 20 ft/px city-wide (useless for picking out a structure). Real tiling means many small images at consistent resolution, so panning loads a different piece rather than one image just getting blurrier. The generator now auto-decides per level: if the whole area fits under 2048px at that level's native resolution, it's one image (true of the coarse levels, L04–L07); above that, it's cut into a real grid of 2048px-capped cells at native resolution, no downsampling within a cell (L01–L03, and optionally the raw un-reduced source below L01).
The source data's own levels are not a universal standard. GIS's overview pyramid is labeled L01–L07 (confirmed exact 3x pixel-size reduction per step via live .tfw reads: 0.75 / 2.25 / 6.75 / 20.25 / 60.75 / 182.25 / 546.75 ft/px) — that numbering is specific to this ArcGIS mosaic dataset, not an "Esri standard L01–L12." There is also a genuinely finer source below L01: the raw flight capture (TilesforReview, 0.25 ft/px — 3x finer than L01, which is already a reduced overview, not native resolution) — wired into the generator as a real, working second source, not just a TODO, but not run by default (~446MB per source file; opt in with --levels raw once a smaller target area is picked, not the whole city). For planning in familiar terms, our levels approximate (not exactly, since our ladder is 3x/level and Esri's is 2x/level) these Web Mercator zoom levels at Auburn's latitude: raw≈Z21, L01≈Z19, L02≈Z18, L03≈Z16, L04≈Z14, L05≈Z13, L06≈Z11, L07≈Z10.

7 · Email Replacement — A Separate Workflow Track

Retiring the legacy forward-the-vendor-email job is not controller work. It lives entirely in the workflow engine, on the same per-minute pipeline that already ingests the strikes:

  • A stored procedure, called by MSSqlHandler (Execute or Query), computes the per-facility columns for the strikes in this batch — including the email's true per-batch Last Strike (closest strike in this message to each facility) — and seeds them into the workflow context.
  • An email template renders that context as a table and forwards to the distribution list, exactly as today. Metrics need not match the web surfaces; the status value can be added to the proc as a low-effort extra column and trialled in the template (easy to remove if the list doesn't want it).
  • Keeping it in the workflow means no controller dependency and independent shipping. The legacy job runs untouched until this is in place.
Decided against — an embedded status image in the email. A generated status/map image would give a "semi-updated" glance, but email image caching is unreliable and a stale image is more confusing than none. The replacement email carries the calculated table plus links to the City Viewer / MyGov surfaces for the live, visual, detailed view — the links are the upgrade path, not a baked-in picture.

8 · Build Sequence

  1. Built Projection — repair LatLongPoint: write-path guards (Put/Patch/Post) + POST /facility/sync-latlong/all catch-up (a backfill endpoint, not a script — see §4) + drop [Obsolete]. Gated every distance calc; done first.
  2. Built Calc + core endpoints — LightningProximityCalculator (unit-tested) + all five LightningStrikesController endpoints, asOf throughout.
  3. In progress Surfaces — MyGov dashboard Built and City Viewer storm view Built (both: facility list sorted by threat, live-polling + asOf history replay). Storm map base layer (§6a, the aerial tile system) has its data/API/generator Built and a generation run in progress, but the actual pan/zoom map viewer and the <facility-lightning-status> per-facility component are not started.
  4. Email replacement — the stored proc + template + list forward (§7); retire the legacy job once verified. Not started; independent of step 3.
  5. Fast-follows — push-on-threshold via a post-Merge workflow action; storm-bearing overlay. Not started. (The arbitrary-point endpoint shipped with step 2, not deferred.)

9 · Dev Task List

Living checklist — remove items as they land (don't mark them done). Tags: api app my data/config

  • my Embed the storm map in the MyGov admin dashboard's lightning view — cv-storm-map is app-local today; either vendor it the calendar-event-row way or lift it into static/components when the second consumer lands.
  • data/config Decide whether/where the L01–L03 grid or the raw source needs targeted regeneration for specific areas (City Requests' precision need is the driver — see §6a).
  • api data/config The tile system now has two live consumers (this storm map + City Requests' cv-location-picker): write up AerialTile/AerialTilesController/the generator script as their own design doc under geo-integration.html, and extract the shared tile/gesture core the two components currently duplicate knowingly. Documented here only because this is where the need first surfaced.
  • app my <facility-lightning-status> component (consumes /facilities/{id}/proximity); embed in the PWA + MyGov facility views.
  • api data/config Email-replacement stored proc (per-batch per-facility columns incl. the true per-batch Last Strike, optional status); wire via MSSqlHandler into the strike-ingest workflow; author the email template + list forward; retire the legacy job. Independent of everything above — not blocked by it.
  • api app Fast-follow: push-on-threshold workflow action (post-Merge step). City Viewer has no push/VAPID code yet — the VAPID keys in api/appsettings.json are unrelated infra. This fast-follow needs push wired into City Viewer first (service-worker push handler + subscription flow), not just the workflow-side send. After the dashboard/storm view are proven, not before.
  • api Fast-follow: storm-bearing/approach-vector overlay (visual only, never a threshold input — see §2).

Landed (removed from the list): Facility.LatLongPoint backfill + write-path guard (§4); LightningProximityCalculator with boundary-case unit tests (§2); LightningStrikesController — all five endpoints (/facilities, /facilities/{id}/proximity, /lightning-strikes, /status, /proximity), asOf on all, thresholds as plain endpoint-parameter defaults (8 mi / 30 min / 30 min / 8 hr) — deliberately not appsettings (see the note-box above). [AllowAnonymous] on the whole controller (see §3). MyGov facility-list dashboard (my/js/modules/lightning-proximity/, route #/admin/lightning-proximity, "IT Health" group on the IT Admin hub) and the City Viewer storm view (cv-lightning, ?view=lightning, a Services sub-view) — both live, polling every 60s to match the vendor cadence, with a "View as of" control that replays any past moment via asOf and pauses polling while active. The aerial tile system (§6a) — data model, API, and generator script all built.

10 · Open Questions

  • Exact default thresholds — confirm the danger radius (8 vs 10 mi), danger/all-clear window (30 min), and history window (8 hr) against what staff currently trust from the legacy tool.
  • Facility inclusion — all facilities, or only those with outdoor/public-safety relevance? A severe storm's facilities-batch is fine either way; this is a UI-noise question, not a performance one.
  • Aerial tile coverage scope (§6a) — is the full-city L01 grid actually needed everywhere, or should fine-grained coverage concentrate on denser/higher-request-volume areas, with coarser levels elsewhere? Real usage from the storm map + City Requests will inform this better than guessing now.