Processing 15 Million GPS Pings Daily: ETL Architecture for Transport Analytics
The system processes 15 million GPS coordinates from 1,800 buses across 170 routes in Tashkent. Pings arrive every 10 seconds via the 3TM API. The key challenge is transforming isolated coordinates into structured data for analysis: identifying stops, traffic jams, trips, and route deviations.
A loader using asyncio + aiohttp polls the API via systemd timers. Data is written to PostgreSQL with PostGIS. ETL orchestration via Dagster ensures the sequence: routes → buses → stops → GPS updates.
Tech Stack Selection and Avoiding Over-Engineering
Initially, Kafka and ClickHouse were tested but proved unsuitable:
- Kafka is not ideal for a pull-based API polling model—it adds overhead without benefit.
- ClickHouse is premature given current query volumes.
Final stack:
- Ingestion: Asynchronous Python loader, ~360 inserts/sec on 2 vCPU / 4 GB RAM.
- Storage: PostgreSQL + PostGIS for routes, stops, GPS time series.
- Orchestration: Dagster for dependencies and traceability.
- Spatial Analytics: PySpark for heavy tasks—snapping GPS to routes, calculating load.
The Staging layer stores raw JSON for 1 day, then ODS structures data in memory without re-reading from the database.
Multi-Layered Data Model
Data is organized into three layers for scalability and auditability:
- ODS: Operational data—routes, stops, current bus positions.
- DDS (Data Vault 2.0): Hubs (entities), satellites (attributes), links (relationships). GPS is not stored; focus is on routes, buses, stops.
- CDM: Analytical marts. The
fct_bus_tripstable—PostGIS LineString per trip with metadata: timestamps, mileage, speed, match % to route.
Data Vault allows adding attributes (telemetry) without refactoring the model.
Trip Segmentation Algorithms
Segmentation determines trip start/end based on three signals:
- Speed: Idling at start/end stops.
- Time gap >5 min: Stream interruption.
- Proximity to route endpoint.
— Editorial Team
No comments yet.