Spatial & Operational AnalyticsDec 2025
Where is Halifax’s transit network leaving riders underserved?
Results
GTFS static feed across 11 normalized tables (481,610 stop-time entries).
A connectivity gap: 973 stops (of 2,380) depend on one route only.
Stop-connectivity view optimized via B-tree and GIST spatial indexes.
663,788 GTFS records modelled in PostgreSQL + PostGIS, with a Streamlit dashboard showing where route access, stop connectivity and service thin out across Halifax.
Halifax publishes its transit schedule as an open GTFS feed, but the raw feed is a set of flat files — useful for trip planning, not for asking whether the network serves riders evenly. There was no queryable, spatial view of where coverage thins out.
This project treats the feed as an analytics problem: could a normalized spatial database and dashboard reveal the parts of the network where access, connectivity, and frequency fall short — the kind of evidence a municipal transit planner would need to target improvements?
It is a personal project. The stakeholder is framed, not real, so the recommendations are written as what the analysis suggests rather than a mandate.
PostGIS geometry columns are populated automatically from stop coordinates via triggers, and GIST spatial indexes make distance queries fast enough for interactive use.
Route 1 (Spring Garden) runs about 180 trips per service pattern — far above the next-busiest routes — and the Barrington St hub serves 27 routes.
Why it matters. A handful of trunk routes carry the network. Their reliability disproportionately shapes the rider experience city-wide.
Route frequency, top 10 routes
| Route | Avg trips per service pattern |
|---|---|
| 1 | 180 |
| 7B | 119 |
| 7A | 118 |
| 2 | 116 |
| 5 | 114 |
| 4 | 112 |
| 3 | 102 |
| FerD | 91 |
| 8 | 86 |
| 9A | 76 |
Of 2,380 stops, 973 are served by only one route, and average connectivity is 2.17 routes per stop. Only 152 stops (6%) act as hubs with five or more routes.
Why it matters. Single-route stops leave riders with no alternative when a route is delayed or cut — a structural fragility in the network.
Caveat. Based on the scheduled GTFS feed, not observed ridership; it measures service supply, not demand.
Stop connectivity distribution
| Routes serving a stop | Number of stops |
|---|---|
| 1 | 973 |
| 2 | 875 |
| 3 | 220 |
| 4 | 150 |
| 5 | 52 |
| 6 | 15 |
| 7 | 30 |
| 8 | 18 |
| 9 | 14 |
| 10+ | 33 |
Demand-shaped scheduling produces a clear morning (7–9 AM) and evening (4–6 PM) commuter pattern, with the evening rush the busiest window.
Why it matters. Off-peak and midday coverage is comparatively thin, which shapes access for non-commuter trips.
Departures by hour of day
| Hour of day | Departures |
|---|---|
| 5 | 4,100 |
| 6 | 24,200 |
| 7 | 31,200 |
| 8 | 30,800 |
| 9 | 27,000 |
| 10 | 24,400 |
| 11 | 24,100 |
| 12 | 24,300 |
| 13 | 24,300 |
| 14 | 25,100 |
| 15 | 28,400 |
| 16 | 32,500 |
| 17 | 33,635 |
| 18 | 31,900 |
| 19 | 23,200 |
| 20 | 21,400 |
| 21 | 20,800 |
| 22 | 18,400 |
| 23 | 15,200 |
Weekday scheduled trips (4,195) fall to 2,217 on Sundays — a 47% reduction confirmed directly in the schedule data.
Why it matters. Weekend service reductions disproportionately affect riders who depend on transit for non-standard work hours.
Caveat. Reflects scheduled service; special-event or seasonal adjustments are not modelled.
Scheduled trips by day type
| Day type | Scheduled trips |
|---|---|
| Weekday | 4,195 |
| Saturday | 2,765 |
| Sunday | 2,217 |
A personal project, so these are readings of the data, not delivered advice.
A validation layer in the ETL pipeline queried referenced tables to build valid ID sets and filtered the dataframe before insertion, removing invalid trips and calendar dates.
Results were checked against known ground truth — Route 1 (Spring Garden) as the highest-frequency route and Barrington St as the busiest transfer hub (27 routes).
EXPLAIN ANALYZE identified sequential scans and a disk-based merge sort on the 481,610-row connectivity view; adding B-tree and GIST indexes cut execution from 4 s+ to under 1 s.
Schema, analytical views, and the ETL pipeline are scripted, so the database can be rebuilt from the raw feed end to end.
The stop-connectivity view joined stop_times (481,610 rows) and initially took 4+ seconds, using an external merge sort spilling ~39MB to disk. Adding B-tree indexes on the foreign keys (trip_id, stop_id, route_id, service_id) and GIST indexes on the geometry columns brought it under one second, and Streamlit result caching kept the dashboard responsive.
Four views encapsulate the analysis: route_summary (trips, patterns, unique stops per route), route_efficiency (average trips per service pattern), stop_connectivity (routes per stop, hub identification), and busiest_stops (ranked by daily service volume).
11 normalized tables follow the GTFS specification with foreign-key constraints. PostGIS geometry columns are auto-populated from lat/lon via triggers, and spatial indexes support ST_Distance proximity queries.
Tables
| stops | Stop locations; PostGIS geometry auto-populated from lat/lon. | 2,380 rows |
|---|---|---|
| routes | Route identifiers and metadata. | 80 rows |
| trips | Scheduled trips linking routes to service patterns. | 13,666 rows |
| stop_times | Arrival/departure per stop per trip — the analytical grain. | 481,610 rows |
Figures on this page