subway-data

Continuously capture NYC subway realtime data (GTFS-RT) into TimescaleDB, and put it to work: station reliability analytics, a live train map, and a delay radar that predicts service disruptions before the MTA announces them. Powers subway.fyi.

What it captures

Every 30 seconds (configurable), the poller fetches all 8 MTA realtime feeds in parallel plus the service-alerts feed:

feed	routes
numbered	1, 2, 3, 4, 5, 6, 7, S shuttles
ace	A, C, E
bdfm	B, D, F, M
g	G
jz	J, Z
nqrw	N, Q, R, W
l	L
si	Staten Island Railway
alerts	service changes, planned work

For each cycle it:

1. Saves the raw gzipped protobuf to ./raw/<feed>/<date>/<hour>/… (lossless — you can re-parse any time)
2. Decodes into Postgres tables: trip_updates, stop_time_updates, vehicle_positions, alerts, feed_snapshots

NYCT protobuf extensions (train_id, is_assigned, scheduled_track, actual_track, NYCT direction) are captured. Note: the extension must be imported from nyct_gtfs.compiled_gtfs together with its bundled gtfs_realtime_pb2 — mixing it with google.transit's copy raises a duplicate-proto error, and the silent fallback left every extension field NULL for the project's first weeks (see poller/subway/decode.py).

Delay radar

Two independent detection channels evaluate the live feed and write every evaluation to delay_predictions; the site surfaces fired rows as pulsing roundels with an explanation popup.

Gap channel — a logistic-regression model (plain JSON coefficients, no ML runtime in the poller image) over route-level headway-anomaly features: stations running ≥2× their learned baseline headway, spread, growth, network context. Baselines are per (route, station, day-of-week, hour), rebuilt nightly from months of history with alert-active periods excluded. Trained under a strict protocol (time-split, train-side-only threshold pick, single holdout evaluation, episode-level metrics — never per-row AUC): model/train_delay_model.py + model/extract_clusters.sql.

Stall channel — physical evidence: one train anchored at a platform beyond that stop's learned routine-hold time (ml_stop_hold_baselines — a 9-minute hold at Canal St is normal bridge traffic; at 167 St it's an emergency), with more trains pinned behind it. Guards learned from production incidents: lay-up trains parked on non-revenue tracks are rejected when other trains keep flowing through the same platform, and ghost trips (unassigned future trips parked at origins) are filtered via is_assigned.

Honesty rules, all measured rather than asserted:

• Suppression: a route with a currently active MTA alert doesn't fire (active periods, not mere feed presence — planned-work postings sit in the feed 24/7). Stall suppression is location-aware: work in Brooklyn doesn't mute a midtown emergency.
• Displayed confidence is the calibrated empirical probability that an official alert follows within 45 minutes — not the model's raw score (which runs ~3× overconfident).
• A nightly job labels every prediction against the alerts that actually followed, timestamped by our own first observation, never MTA-backdated times. The results — misses included — are public at /accuracy.
• Alert popups cite radar detections that preceded the alert ("⚡ spotted N min earlier"), with one-claim and location-consistency rules so a detection is never credited to an incident it didn't precede.

Model tooling

• model/replay.py — replay any detector minute-by-minute over the captured firehose: episode-level recall/FA-per-day/lead metrics, named benchmark incidents, false-positive lists, surface-gate sweeps. Every parameter change ships only after a replay gate.
• eval/report.py — the standing scorecard over delay_predictions outcomes (per channel, per day, episode-collapsed). --json feeds the public accuracy page (web/app/accuracy.py mirrors it — keep in sync).
• model/IMPROVEMENT_PLAN.md — the campaign playbook and running status.

Schema

Time-series tables (TimescaleDB hypertables, partitioned by day):

• feed_snapshots, trip_updates, stop_time_updates, vehicle_positions, alerts — the realtime firehose
• route_station_5min_stats — the 5-minute analytics panel (headways, dwell, delays, alert flags)
• delay_predictions — every radar evaluation + nightly outcome labels

Derived/reference tables:

• ml_headway_baselines, ml_stop_hold_baselines — learned "normal", rebuilt nightly
• ml_station_adjacency, ml_terminal_stops — network structure from static GTFS
• alerts_unique, station_daily_stats, station_hourly_stats — rollups
• stops, routes, trips, stop_times, shapes — static GTFS

Run

Two docker-compose projects sharing an external Postgres (set DATABASE_URL; there is no bundled DB container):

• capture/ — poller, predictor, daily-stats, plus one-shot loaders (gtfs-static-loader, ridership-loader). Writes as subway.
• site/ — web (FastAPI: live map, badness map, radar, accuracy page) + grafana. Reads as subway_ro; safe to tear down without touching data.

# 1. Point both stacks at your Postgres (TimescaleDB) instance
export DATABASE_URL=postgresql://subway:…@host:5432/subway

# 2. Capture stack
cd capture && docker compose up -d --build
docker compose --profile static run --rm gtfs-static-loader   # one-shot

# 3. Site
cd ../site && docker compose up -d --build
open http://127.0.0.1:8001            # live train map + delay radar
open http://127.0.0.1:8001/badness    # Badness Map
open http://127.0.0.1:8001/accuracy   # radar track record
open http://127.0.0.1:3030            # Grafana

In production this runs on two hosts simultaneously; the poller, predictor, and nightly-stats services each elect a single leader via advisory locks in leader_lock, so either host can fail without losing capture. Pushes to main auto-deploy both hosts.

Nightly jobs

daily-stats (leader-gated, 04:00 ET) chains: daily/hourly station stats → alerts_unique → the 5-minute panel → headway baselines → stop-hold baselines → prediction outcome labeling → retention cleanup (hypertable drop_chunks + raw-archive pruning).

Sample queries

Most recent snapshot per feed:

SELECT feed_name, max(fetched_at) FROM feed_snapshots WHERE status='ok' GROUP BY 1;

The radar's current view:

SELECT detected_at, route_id, score, fired, suppressed, model_version
FROM delay_predictions
WHERE detected_at > now() - interval '30 minutes'
ORDER BY detected_at DESC;

Radar track record, last 7 days (or just open /accuracy):

SELECT date_trunc('day', detected_at) AS day,
       count(*) FILTER (WHERE fired)                            AS fired,
       count(*) FILTER (WHERE fired AND matched_alert_id IS NOT NULL) AS confirmed
FROM delay_predictions
WHERE detected_at > now() - interval '7 days'
GROUP BY 1 ORDER BY 1;

Active alerts right now:

SELECT DISTINCT ON (alert_id) alert_id, effect, header_text
FROM alerts
WHERE fetched_at > now() - interval '5 minutes'
ORDER BY alert_id, fetched_at DESC;

Configuration

Poller (capture/docker-compose.yml):

• POLL_INTERVAL_SECS — default 30
• SAVE_RAW — 1 to keep gzipped protobuf snapshots
• RAW_DIR — default /raw → ./raw on host
• DATABASE_URL — Postgres DSN

Predictor:

• PREDICT_INTERVAL_SECS — default 60
• MODEL_PATH — default: the delay_model.json baked into the image
• STALL_ANCHOR_MIN, STALL_ANCHOR_PAD, STALL_MIN_OTHERS, STALL_SURFACE_MIN, STALL_SUPPRESS_HOPS — stall-channel knobs; defaults in poller/subway/predictor.py carry the replay-derived rationale in comments

Storage estimate

At 30s polling, ~200 KB/cycle/feed × 9 feeds × 2880 cycles/day ≈ ~5 GB/day raw, and roughly 2–3 GB/day in the DB dominated by stop_time_updates. TimescaleDB compression (3-day columnstore policy) plus 60-day retention on the firehose tables keeps steady state around 150 GB. Tune the interval or set SAVE_RAW=0 to shrink it.

License

CC BY-NC 4.0 — free to use, share, and adapt with attribution (Henry Williams / subway.fyi) and not for commercial use. See LICENSE; contact the author for commercial licensing.