1. Crashes by Day of Week
Friday highest (67,446) vs Sunday (45,785) → 47% differential guiding enforcement.
Queensland Road Crash Analysis
Temporal, spatial & severity dynamics of reported crashes across Queensland (2001–2024) highlighting peak risk periods & environmental drivers.
Python
PostgreSQL
EDA
Seaborn
Folium
Technology Stack
Python 3
Pandas
NumPy
PostgreSQL
SQL
Matplotlib
Seaborn
Folium
Power BI
Jupyter
Git/GitHub
Interactive Power BI Dashboard
Login to Power BI may be required for full interactivity.
Interactive road crash analysis dashboard (Power BI)
2. Top Suburbs by Crash Count
Southport & Brisbane City dominate → focus mitigations in top decile suburbs.
3. Crashes by Month
March peak vs January trough (~20% lower) → seasonal campaign timing.
4. Yearly Crash Trends
2011 peak (23,525) → 2024 (6,943) ~70% decline; monitor plateau risk.
5. Hourly & Daily Crash Hotspots
Peak 15:00–18:00 weekdays; Friday 17:00 highest → fatigue + congestion.
6. Daily Crash Patterns in Top Suburbs
Commercial districts show sharper Friday spikes → local signal/patrol tuning.
7. Factors Influencing Fatal Casualties
Speed & dark-unlighted conditions elevate severity → prioritize lighting & speed management.
8. Geospatial Mapping of Recent Crashes
Arterial corridor clustering (Brisbane City >3k) → infrastructure risk audits.
9. Crashes by Atmospheric Condition
Clear weather dominates counts (exposure) → need rate normalization.
10. Crashes by Lighting Condition
Daylight majority; dark-unlighted highest severity ratio → illumination upgrades.
Limitations
- Reported crashes only (minor under‑reporting)
- Exploratory – correlation ≠ causation
- No exposure denominator (traffic volume)
Future Work
- Crash rate normalization (traffic counts)
- Severity prediction modeling
- Time series forecasting
Appendix: Full Narrative Detail
Extended explanatory commentary (hypotheses, findings & insights) for each analysis without re‑embedding images. Refer to preceding sections for visual references.
1. Day of Week
Hypothesis: Mid/late week elevated. Finding: Friday highest; Sunday lowest. Insight: 47% differential → targeted Friday enforcement.
2. Top Suburbs
Southport & Brisbane City dominate; concentrating engineering & patrol resources in top decile suburbs yields outsized reduction potential.
3. Seasonal (Month)
March peak vs January trough (~20%) indicates seasonal exposure; schedule campaigns in late Q1.
4. Long-Term Trend
~70% decline 2011→2024 signals systemic safety gains; monitor for plateau to avoid complacency.
5. Hourly Peaks
15:00–18:00 weekday concentration; Friday 17:00 extreme peak → combine fatigue messaging + traffic flow optimization.
6. Suburb Daily Patterns
Steeper late‑week surges in commercial districts; tailored signal timing & variable speed signage recommended.
7. Severity Factors
Higher posted speed & dark–unlighted environments elevate fatal expectation (3–4×) → prioritize lighting + speed management corridors.
8. Geospatial Clustering
Arterial & CBD lattice clusters justify infrastructure risk audits & corridor-level interventions.
9. Atmospheric Conditions
Clear weather dominates absolute counts (exposure). Need traffic volume integration to interpret true relative risk under adverse conditions.
10. Lighting Conditions
Dark–unlighted share smaller in volume yet disproportionately severe → LED retrofits & reflective surfacing high ROI.
Expanded Limitations
- Reported crashes only (minor under‑reporting of low severity).
- No exposure denominators (vehicle‑kilometres / AADT).
- Exploratory correlations ≠ causation.
- Temporal aggregation may mask holiday anomalies.
Future Enhancements
- Integrate traffic sensor & AADT data.
- Severity prediction (gradient boosting / ensembles).
- Forecasting (SARIMAX / Prophet).
- Network graph & spatial autocorrelation analysis.
Explore the Code
Python ETL scripts, feature engineering & visualization notebooks available in the repository.
View on GitHub