This project analyses structural inequality in Melbourne’s public transport network using suburb-level accessibility metrics derived from 2021 GTFS data.
Rather than relying solely on stop counts, the analysis integrates:
- Stop Density (infrastructure provision)
- Route Coverage (network penetration)
- Service Intensity (weekday service supply)
The objective is to quantify structural access gaps, identify under-served suburbs, and translate findings into decision-ready reporting outputs.
Public transport investment decisions are often made without a standardised, suburb-level accessibility framework.
This results in:
- Uneven service distribution across growth corridors
- Infrastructure-heavy but network-light configurations
- Suburbs with high population or land area but structurally limited connectivity
Stakeholders require data-backed evidence to prioritise intervention zones rather than relying on anecdotal infrastructure counts.
- Quantify suburb-level public transport accessibility
- Identify bottom-quartile structural deficit zones
- Compare stop density vs route coverage as drivers of service intensity
- Translate technical findings into executive-ready visual reporting
Which suburbs fall into the bottom quartile for:
- Stop Density
- Route Coverage
- Service Intensity
Are deficits isolated or geographically concentrated?
Is higher physical stop density associated with higher weekday service provision?
Does infrastructure presence equate to effective accessibility?
Between:
- Stop Density
- Route Coverage
Which metric better explains variation in weekday service intensity?
-
Stop Density
Number of stops per km² within each SA2 suburb. Captures infrastructure concentration.
-
Service Intensity (Weekday Average)
Average weekday stop-time events per km². Captures frequency and temporal supply.
-
Route Coverage
Number of distinct routes serving each SA2 per km². Captures network penetration and diversity.
- PostgreSQL database environment setup
- GTFS dastaset ingestion and relational schema design
- ERD-based table normalisation
- Fact table creation for suburb-level metrics
- Spatial joins (GTFS stops <-> SA2 boundaries)
- Aggregation queries using SQL
- Bottom-quartile thresholding
- Correlation and regression diagnostics
- Structured analytical reporting (poster format)
All metric calculatinos were implemented in SQL (PostgreSQL).
Two distinct structural deficit patterns were identified:
Infrastructure Deficit Zones
- Extremely low stop density per km²
- Geographically concentrated in outer-growth suburbs
Netowrk Coverage Gaps
- Limited route penetration despite available infrastructure
- Network design limitations rather than stop scarcity
These suburbs represent priority intervention zones where baseline accessibility is structurally constrained.
Correlation analysis shows:
- Route Coverage explains ~88% of variation in service intensity
- Stop Density explains ~57%
Key Insight:
Network structure is a stronger determinant of service intensity than physical stop abundance.
High stop density alone does not guarantee high service levels. Connectivity and route penetration drive effective accessibility.
Spatial mapping reveals:
- Service intensity deficits cluster in outer-growth corridors
- Route coverage gaps appear along northern and eastern expansion fronts
- Stop density shortages are concentrated in low-density fringe municipalities
Transport inequality is geographically structured rather than randomly distributed.
Outer suburbs experience compounded structural disadvantages across multiple indicators.
- Infrastructure expansion alone is insufficient
- Network redesign and route penetration strategies may yield stronger impact
- Investment prioritisation should target structurally constrained growth corridors
- Accessibility policy should shift from asset-count metrics to network-efficiency metrics
This analysis supports evidence-based transport equity planning.
- SQL-based metric calculation scripts
- PostgreSQL relational schema & ERD
- Analytical poster series (Executive reporting format)
- Tableau dashboards & geospatial visualisations
- GitHub repository for reproducibility
- PostgreSQL (data processing & aggregation)
- SQL (analytics & feature engineering)
- Tableau (executive dashboards)
- R (geospatial visualisation)
- GitHub (documentation & reproducibility)
- Public Transport Victoria (GTFS, 2021)
- ABS boundary data (SA2 / suburb level)
Historical data used to demonstrate methodology and analytical capability.
This porject demonstrates:
- End-to-end analytica workflow design
- SQL-based metric enginerring at scale
- Structural inequality analysis
- Business-oriented rerporting & visual storytelling
- Translation of spatial analytics into policy-ready insight