Storing and Querying Matched Trajectories
Matching produces more data than it consumes. A raw fix is a timestamp and two floats; a matched fix carries an edge identifier, an offset along that edge, a confidence, a graph version and usually a snapped coordinate as well. Three to five times the input volume is normal, and it arrives every day.
That would be a manageable storage problem if there were one query. There are at least three, and they want different things. “Where was vehicle 14 on Tuesday afternoon?” wants a vehicle-time lookup. “Which vehicles entered this zone last week?” wants a spatial index. “How much of our driving is on roads with a weight limit?” wants to join against the road network by edge. A single table with a single index serves one of them well and the other two badly.
This page sets out a storage design that handles all three, the partitioning that keeps it fast as the archive grows, and the hot/cold split that keeps it affordable. It follows on from the matching work in the trajectory analysis section and assumes output carrying the version stamp described in pinning map versions.
Prerequisites
- PostgreSQL 16+ with PostGIS 3.4+ for the hot store, or an equivalent with declarative partitioning and a spatial index.
- GeoParquet 1.0+ and an object store for the cold tier.
polarsorpyarrowfor writing. - A stable edge identity — OSM way id plus direction — so that stored rows remain joinable when the graph is rebuilt.
- An operating-date column derived in the vehicle’s local timezone, per calculating accurate dwell times across timezone shifts.
- A projected CRS decided up front, because changing it later means rewriting the archive.
Choosing the Grain
Three grains are possible, and most fleets end up materialising two of them.
Row per matched fix. One row for every input observation, carrying its snapped position and the edge it landed on. Largest by far, and the only grain that preserves the vehicle’s own timeline at full resolution. Stop detection, speed profiling and anything replaying a journey need it.
Row per matched segment. One row per contiguous run of fixes on the same directed edge, with entry and exit times. Typically five to ten times smaller, because a vehicle spends many fixes on one road. This is the grain that answers road-network questions directly, and it joins to the road graph without an aggregation step.
Row per trip. One row per journey with a summary geometry and aggregate metrics. Tiny, and the grain most reporting actually consumes.
The mistake is picking one. The segment grain cannot answer “what was the speed profile through this junction”; the fix grain cannot answer “how many vehicle-kilometres on residential roads” without scanning everything. Materialising fix and segment grains, and deriving trip summaries from segments, covers the realistic query set at a storage cost dominated by the fix table.
The Schema
CREATE TABLE matched_segments (
segment_id bigint GENERATED ALWAYS AS IDENTITY,
vehicle_id text NOT NULL,
operating_date date NOT NULL, -- vehicle-local, not UTC
trip_id uuid NOT NULL,
osm_way_id bigint NOT NULL,
direction smallint NOT NULL, -- +1 forward, -1 reverse
enter_utc timestamptz NOT NULL,
exit_utc timestamptz NOT NULL,
length_m double precision NOT NULL,
mean_speed_kmh real,
confidence real,
graph_version char(12) NOT NULL,
geom geometry(LineString, 32631) NOT NULL,
PRIMARY KEY (operating_date, segment_id)
) PARTITION BY RANGE (operating_date);
CREATE INDEX ON matched_segments USING gist (geom);
CREATE INDEX ON matched_segments (vehicle_id, enter_utc);
CREATE INDEX ON matched_segments (osm_way_id, direction);Three indexes because there are three query shapes, and none of them substitutes for another. The GiST index answers “what passed through this polygon”; the composite index answers “what did this vehicle do between these times”; the edge index answers “what happened on this road”. Dropping any one of them turns its query into a partition scan.
PARTITION BY RANGE (operating_date) is the single most consequential line. Fleet queries are almost
always bounded in time, and partition pruning turns a billion-row table into a few million rows before
any index is consulted. Monthly partitions suit most fleets; daily partitions are worth it past roughly
half a billion rows a year.
Note that operating_date is vehicle-local. Partitioning on a UTC date splits a night shift across two
partitions for half the fleet, which quietly doubles the work for every query about a shift.
Query Shapes and What They Cost
| Question | Index used | Typical cost |
|---|---|---|
| Vehicle 14, Tuesday afternoon | partition + (vehicle_id, enter_utc) |
single-digit milliseconds |
| Everything in this depot polygon last week | partition + GiST | tens of milliseconds |
| All driving on this way, last quarter | partition + (osm_way_id, direction) |
tens of milliseconds |
| Vehicle-kilometres by road class, last year | full scan of pruned partitions | seconds to minutes |
The last row is the one that should not run against the hot store. Analytical scans over a year of data are exactly what columnar formats are built for, and running them against an indexed row store competes with the operational queries for buffer cache.
Hot and Cold Tiers
The access pattern for fleet trajectory data is strongly age-dependent. Operational queries touch the last few weeks; analytical queries scan months or years and touch each row once. Serving both from one system means paying database prices for archival data and letting analytical scans evict the operational working set from cache.
The split that works is simple. Keep a rolling window — sixty to ninety days is typical — in the partitioned database. Age older partitions out to GeoParquet on object storage, partitioned by operating month, and query them with a columnar engine. Detach rather than delete, so the aged partition can be reattached if something needs it back.
-- Age a partition out: detach, export, then drop.
ALTER TABLE matched_segments DETACH PARTITION matched_segments_2026_02;
-- COPY ... TO PROGRAM, or read with a client and write GeoParquet
DROP TABLE matched_segments_2026_02;The saving is substantial in both directions. Columnar storage with dictionary encoding on the identifier columns typically lands at a quarter to a fifth of the database’s on-disk size, and analytical scans over it run several times faster because they read only the columns they need.
Modelling the Edge Reference
The column that most often causes trouble later is the one holding the road identity. Three schemes are common, and only one of them survives a graph rebuild.
The engine’s internal edge id. Compact and fast to join within a single graph build, and meaningless across builds. An archive keyed on it becomes unjoinable the first time the graph is rebuilt, which for a fleet on a weekly refresh is within days.
The OSM way id alone. Stable across rebuilds and ambiguous about direction, so a one-way violation report cannot be produced from it and a dual carriageway collapses to a single road.
The OSM way id plus a direction flag. Stable, unambiguous, and two columns. This is the scheme worth standardising on, and the small cost is that engines split a single way at junctions, so a matched segment sometimes spans several engine edges belonging to one way.
-- The edge reference, and an index that serves road-network joins
osm_way_id bigint NOT NULL,
direction smallint NOT NULL CHECK (direction IN (-1, 1)),
CREATE INDEX ON matched_segments (osm_way_id, direction);Where the split point genuinely matters — a long way carrying different speed limits along its
length — carry the entry offset as well and treat (way, direction, offset_bucket) as the key. That
is a refinement worth adding when a question demands it rather than by default, because it makes every
join more expensive for a distinction most queries do not need.
Joining to the road network
The join to road attributes should happen at query time against a versioned copy of the network, not by denormalising road class and speed limit onto every matched row. Denormalising looks attractive — it removes a join from every query — and it freezes the road attributes at match time, so a correction to the map never reaches the archive.
Keep a road_edges table per graph version, joined on (osm_way_id, direction). It is small relative
to the trajectory data, it makes attribute corrections retroactive, and it keeps the trajectory rows
narrow, which matters more than the join once the archive is measured in hundreds of gigabytes.
Frequently Asked Questions
Do I need a purpose-built trajectory database?
Almost certainly not. Specialised trajectory stores earn their keep on similarity search — “find journeys shaped like this one” — which fleet analytics rarely asks. The queries fleets actually run are partition-pruned range scans and a handful of index lookups, and a partitioned PostGIS table plus a columnar archive handles both at a fraction of the operational cost of another system to run.
How do I handle the road network changing under stored rows?
Store the graph version on the row and re-resolve rather than migrate. When a way is split upstream, the stored identifier stops resolving, and that is a state you want to detect rather than paper over. A nightly job that counts unresolvable edge references per partition turns a silent decay into a number, and the count is a useful proxy for how stale the archive’s road references have become.
Should the snapped coordinate be stored, or recomputed from the edge and offset?
Store it. Recomputing means loading the road geometry for every row, which turns a cheap scan into a join against a large table. The snapped position is sixteen bytes and it makes the archive self-contained, which matters more than the storage once the graph that produced it is several versions old.
What retention is reasonable for each grain?
Fix grain is the expensive one and the least often needed at age; two years is generous for most fleets. Segment grain is a fifth of the size and answers most historical questions, so five to seven years is affordable. Trip grain is small enough that keeping it indefinitely costs nothing and saves an argument later.
How do I keep the two grains consistent?
Derive one from the other in a single job rather than producing them independently. Segments computed from the same matched output that produced the fixes cannot disagree; segments computed by a second pipeline reading the same inputs eventually will, and the disagreement will be found by a customer.
Is it worth storing the unmatched fixes as well?
Yes, flagged. A fix the matcher rejected is evidence about coverage, device health and map quality, and discarding it makes the archive look cleaner than the data was. Keep them in the fix-grain table with a null edge reference and a reason code.
Operational Troubleshooting
Spatial queries slow down over months. The GiST index has bloated, usually after bulk deletes. Reindex the affected partitions; with partitioning this is a per-partition operation rather than a maintenance window.
A vehicle-time query scans everything. The query is not bounded on operating_date, so no
partition is pruned. Adding the date predicate — even a generous one — is usually a hundredfold
improvement.
Edge joins return nothing after a graph rebuild. Stored rows reference way identifiers that were split upstream. Re-resolve as described in adding truck restrictions, and keep the graph version on the row so the mismatch is detectable rather than silent.
Storage grows faster than fix volume. Usually a wide fix-grain table carrying columns nobody queries — every CAN signal, both geometries, several timestamp variants. Audit column usage before adding hardware.
Analytical queries stall operational ones. The tiers are not separated. This is the symptom the hot/cold split exists to prevent.
Deployment Checklist
Related
- Storing matched traces in PostGIS for fleet analytics — the hot tier in detail
- Writing matched trajectories to GeoParquet with Polars — the cold tier
- Compressing fleet trajectory archives without losing fidelity — getting the archive smaller without simplifying geometry
- Pinning map versions for reproducible fleet matching — why
graph_versionis on every row - Trajectory Analysis & Map Matching Techniques — parent section