← Back to work

Spatial analytics · 2026

Toronto Mobility Intelligence

A reproducible PostgreSQL + PostGIS platform that turns 20 years of Toronto's official collision data into findings a city team could defend — from Open Data to a Power BI dashboard.

passing tests
91
spatial match radius, derived from data
20 m
curated analytical SQL queries
19

Architecture

Select a stage to explore it.

01 · Toronto Open Data

KSI collisions, traffic volumes, traffic signals, and neighbourhood boundaries, downloaded through the CKAN API with schema-drift detection before anything is loaded.

Docker + Makefile — one reproducible buildKPI-to-SQL reconciliation testsseven limitations documented with the findings

Visuals

Toronto Mobility Intelligence dashboard overview with KSI totals, annual trend, and road-user involvement charts
Dashboard prototype — Overview page
Toronto Mobility Intelligence dashboard page comparing raw collision counts with land-area density by neighbourhood
Where — raw count and density ranked side by side
Toronto Mobility Intelligence dashboard overview with KSI totals, annual trend, and road-user involvement charts
Dashboard prototype — Overview page
Toronto Mobility Intelligence dashboard page comparing raw collision counts with land-area density by neighbourhood
Where — raw count and density ranked side by side

01 · Problem

Collision counts show where severe crashes happened, but not where risk is concentrated: a large, busy neighbourhood accumulates more collisions almost by construction. The goal was a platform that answers what, where, when, and who honestly — normalizing where a fair denominator exists and saying so where it doesn't.

02 · Findings

The fatal share of KSI collisions rose from under 12% (2006–2012) into a 13–22% range from 2013 onward, even as raw counts fell. Pedestrians and cyclists were involved in the majority of KSI collisions in every year. West Humber-Clairville ranks first by raw count but 120th of 158 once normalized by land area.

03 · Engineering decisions

No layer skips the one below it: the dashboard never touches raw data, and no business logic is defined in DAX that isn't already in SQL. The spatial match radius was derived from the observed distance distribution and cross-validated against the source data's own location fields, and the 44.7% match rate is reported as expected rather than hidden — most severe collisions happen midblock.

04 · Limitations

KSI captures only collisions where someone was killed or seriously injured. Traffic counts are not temporally aligned with collision history, so the exposure metric compares intersections cross-sectionally, never as a trend.