A personal data project, built to learn by doing

Hi, I’m Marcel — I have a flair for data.

I like taking messy, real-world data and turning it into something useful and understandable. I built Transit Analysis to explore APIs, geospatial data, graph-like relationships, realtime pipelines, databases and interactive visualisation — and to bring all of it together in one dashboard that keeps itself up to date.

Scroll↓

1 · Why transport data?

Public transport is a useful playground because almost everything has a time, a place and a relationship to something else. Vehicles belong to trips, trips belong to lines, stops form routes, and realtime predictions constantly change the picture.

About the project

Learning by building something real

I’m Marcel, and I have a particular interest in data, digital processes and building practical solutions around them. Transit Analysis started as a personal learning project: I wanted to work with data that is messy, live and interconnected rather than with a finished static dataset.

The project gave me a reason to work hands-on with APIs, geospatial data, graph-like network relationships, realtime collection, data modelling, Python, SQL and interactive visualisation. The goal was not just to experiment with each piece separately, but to combine them into a small application that is always online and continuously becomes more interesting as its history grows.

APIs

Live public data

Static GTFS and realtime feeds are collected continuously instead of downloading one finished dataset for analysis.

Geo

Spatial data

Stops, coordinates, route shapes and moving vehicles are combined on interactive maps and used to understand trip progress.

Graphs

Relationships

A transport network naturally behaves like a graph: stops connect to routes, routes contain ordered stops, trips belong to services and vehicles move through that structure.

Python

Data engineering

Collectors clean, match and transform the feeds, maintain live state and fold short-lived observations into compact historical tables.

Visualisation

Maps + charts

The same data is presented as live maps, route profiles, heatmaps and charts so patterns can be explored rather than only queried.

Operations

Always running

The application runs continuously on a small server. The collectors keep gathering data while the public dashboard reads from the latest database state.

The full source code is available on GitHub. If you are working on something similar, spot a problem, have an idea for the project or simply want to exchange thoughts about data and transport, you can reach me at transitanalysis@protonmail.ch.

Two cities, two data problems

The frontend is shared. The collection logic is not.

Helsinki and Zürich end up in the same dashboard, but the raw data is not equivalent. I deliberately kept the city-specific collection pipelines separate and only converged them at the database and web-API layer.

Helsinki

Reported vehicle positions

HSL provides static GTFS together with realtime feeds that include vehicle positions. The collector can therefore use reported locations and combine them with timetable data to calculate delay and trip progress.

Zürich

Position has to be reconstructed

The Swiss realtime source used here provides TripUpdates but no VehiclePositions. The collector maps the national Swiss trip to the corresponding ZVV trip, reconstructs predicted stop times and estimates the vehicle's location along the ZVV route shape.

Helsinki DB

One SQLite database

Helsinki keeps its own timetable, live snapshot and historical aggregates in helsinki/transit.sqlite.

Zürich DB

Separate SQLite database

Zürich uses the same analytical model where possible but adds mapping tables that connect Swiss identifiers and stop sequences to the ZVV network.

Data quality

Prefer missing data to confident-looking guesses

Zürich realtime data comes from a nationwide Swiss feed and has to be linked to the static ZVV network before it can be used for route and stop analytics.

94.1% In one validation snapshot, 38,092 of 40,467 usable realtime stop updates could be linked to a ZVV stop.
5.9% The remaining updates referred to stop sequences that existed in the Swiss static timetable but were not represented in the corresponding Swiss-to-ZVV stop mapping.
rule Those observations are excluded instead of guessed. A missing value is preferable to assigning a delay to the wrong stop and quietly contaminating the analytics.

The 94.1% figure is a validation snapshot, not a permanent quality guarantee. The exact share changes with the services present in the realtime feed.

The data model

Keep the detailed data briefly, keep the useful history

Both city databases follow the same basic idea: live state is overwritten, raw observations are temporary, and compact aggregates are retained for analytics. Zürich adds its own mapping and prediction tables around that shared core.

live vehicle_now and trip_progress describe the current network and are continuously overwritten.
raw trip_delay contains short-lived poll-level delay observations rather than growing forever.
trip trip_day keeps one compact record per trip and service day: observations, average delay, extremes and cancellations.
stop trip_stop_delay stores one finalized stop-level delay observation per trip and stop before it is folded into permanent aggregates.
profile stop_profile aggregates delay by line, direction and stop sequence so the dashboard can show where along a route delay builds up or is recovered.
heatmap stop_hour aggregates each stop by weekday and hour for recurring spatial patterns.
Zürich zrh_trip_map, zrh_stop_map and the Swiss timetable tables bridge the national feed and the ZVV static network.

Architecture

Different collectors, shared application

The important split happens before the databases: Helsinki can use reported realtime positions, while Zürich needs an additional mapping and estimation layer. Once the observations are normalized, the same Flask application and frontend can serve both cities.

Built with

Small pieces, each doing one job

I deliberately kept the stack small. Python does the collection and API work, SQLite stores the data, and plain JavaScript draws the interactive parts.

Python

Collectors

Fetch feeds, parse GTFS and protobuf data, match trips and stops, calculate delays, estimate Zürich positions and maintain aggregates.

Python

Flask

Serves the pages and the read-only JSON API. The web process reads the databases but does not maintain the collection history itself.

SQL

SQLite

One database per city. Write-ahead logging allows collectors to write while the public application continues to read.

JavaScript

No frontend framework

Maps, charts, filters and even the scroll animation on this page are plain JavaScript.

Apache ECharts

Charts

Used for trends, comparisons and route-level delay profiles drawn from the JSON API.

MapLibre GL

Maps

Used for live vehicles, route geometry and stop-level spatial views rendered interactively with WebGL.

Docker

Services

Separate schedule, realtime and web services run from the same project image and share persistent data.

Caddy

Public edge

Handles HTTPS and reverse proxying in front of the Flask application.

Stop-level analytics

Where does delay actually build up?

A route-level average can tell you that a service is late, but not where the problem develops. The stop profile keeps one value per trip and stop so consecutive stops can be compared.

record A stop observation is attached to a line, direction and ordered stop sequence.
fold Individual observations are accumulated into compact per-stop aggregates so the raw feed does not have to be scanned for every page request.
compare The change from one stop to the next reveals where a vehicle tends to gain delay and where it tends to recover time.

Helsinki and Zürich do not have identical semantics here. Zürich values are based on the latest available realtime timetable prediction around a stop passage, not a GPS-confirmed measured arrival time.

Browser compatibility

Why a corporate browser can behave differently

The interactive maps use MapLibre GL and therefore rely on WebGL / hardware-accelerated browser rendering. Some managed work computers disable WebGL or hardware acceleration through company policy. Security products can also block third-party map or JavaScript resources.

In those environments an interactive map may fail to render even though ordinary HTML pages and the JSON API still work. Reducing external runtime dependencies and providing a graceful fallback for browsers without WebGL are therefore part of the final hardening of the project.

Continuous work

The interesting part happens while nobody is looking

realtime Collect. Fetch current feeds, update the live snapshot, calculate delays and maintain trip progress.
stops Fold. Finalize stop-level observations and fold them into route profiles and weekday/hour aggregates.
periodic Refresh. Reload static timetable and mapping data so realtime identifiers stay aligned with the current schedule.
prune Compact. Remove short-lived raw observations after they have served their purpose while retaining the historical aggregates.

The dashboard becomes more useful over time: one afternoon can show what is happening now, but recurring weekday, hourly and route patterns need weeks of continuously collected history.