RouteMatching

Writing Matched Trajectories to GeoParquet with Polars

Once a partition ages out of the PostGIS hot store, it becomes an analytical asset rather than an operational one. Nothing updates it, queries read whole columns rather than individual rows, and the access pattern is a scan with a predicate.

That is precisely what Parquet is for, and the size difference is not marginal — a well-written GeoParquet export of a fleet month typically lands at a fifth of the database’s on-disk footprint and scans several times faster. Most of that advantage comes from three decisions made at write time, and the rest of this page is about getting them right.

Compatibility and Configuration Requirements

Requirement Value Notes
Polars ≥ 1.0 write_parquet with row_group_size and statistics control
GeoParquet 1.0 or 1.1 1.1 adds native geometry types; 1.0 with WKB is the safe interoperable choice
CRS Recorded in file metadata A GeoParquet file without CRS metadata is a liability
Compression zstd level 3 Materially better than snappy for this data at similar decode speed
Object store Any, with prefix listing Directory-style partitioning depends on it

The Export

from __future__ import annotations

import json
import polars as pl

GEO_META = {
    "version": "1.0.0",
    "primary_column": "geom",
    "columns": {
        "geom": {
            "encoding": "WKB",
            "geometry_types": ["LineString"],
            "crs": "EPSG:32631",
        }
    },
}


def export_month(df: pl.DataFrame, path: str, row_group_mb: int = 128) -> None:
    """Write one month of matched segments as GeoParquet, sorted for pruning."""
    ordered = df.sort(["vehicle_id", "enter_utc"])
    ordered.write_parquet(
        path,
        compression="zstd",
        compression_level=3,
        statistics=True,
        row_group_size=row_group_mb * 1024 * 1024 // ordered.estimated_size("b") * len(ordered) or None,
        metadata={"geo": json.dumps(GEO_META)},
    )

Three things are doing the work here.

The sort. Parquet prunes row groups using per-column minima and maxima. Sorted by (vehicle_id, enter_utc), a query for one vehicle touches a handful of row groups; unsorted, every group spans every vehicle and none can be skipped. This single line is usually worth more than every other tuning decision combined.

statistics=True. Without statistics there is nothing to prune on, and the reader scans everything regardless of how well the data is sorted.

The geo metadata. A GeoParquet file that records its CRS can be read correctly by anything; one that does not is a file whose coordinates mean whatever the reader assumes. It costs a few hundred bytes.

What sorting buys at read time A month of matched segments in 96 row groups. Querying one vehicle against an unsorted file touches all 96, because every group's vehicle range spans the whole fleet. Against a file sorted by vehicle then time, the same query touches 3. One vehicle, one month, 96 row groups unsorted all 96 read — 11.4 s sorted 3 read — 0.4 s Same data, same file size, same compression. The only difference is the order rows were written in. Sort on the column queries filter hardest on; for fleet work that is almost always vehicle, then time. A second sort key is nearly free at write time and useless at read time unless the first one is selective. Where queries filter mostly by area instead, sort on a spatial key such as a Hilbert index.

Layout in Object Storage

matched/
  operating_month=2026-06/part-0000.parquet
  operating_month=2026-07/part-0000.parquet
  operating_month=2026-08/part-0000.parquet

Directory-style partitioning at month granularity, one to a few files per month. A query bounded in time skips whole prefixes without opening a file, which is the cheapest pruning available.

Resist finer partitioning. A vehicle_id= level under the month multiplies the object count by the fleet size, and object stores charge per request and list slowly over large prefixes. Within-file row-group statistics already handle vehicle selectivity once the data is sorted, and they do it without a million small objects.

File size wants to land between 256 MB and 1 GB. Smaller files mean more requests and more metadata parsing; larger files reduce the parallelism a query engine can extract. Splitting a large month into a few parts, each internally sorted, is better than one enormous file.

Encodings Worth Setting Deliberately

Column Encoding Why
vehicle_id dictionary A few hundred distinct values across millions of rows
graph_version dictionary A few dozen distinct values, fixed width
osm_way_id dictionary or delta Dictionary if the operating area is small; delta if not
enter_utc, exit_utc delta Monotonic within a sorted file, compresses to almost nothing
geom WKB, zstd The dominant column by size; see the compression guide

Dictionary encoding on vehicle_id alone typically removes ten to fifteen percent of the file. Delta encoding on the sorted timestamps removes nearly all of their cost, because consecutive values differ by a few seconds and the deltas fit in a handful of bits.

The geometry column will still dominate — usually sixty to seventy-five percent of the file — which is why compressing fleet trajectory archives treats it separately.

Where the bytes are A month of matched segments as GeoParquet. With default encodings the file is 4.2 gigabytes, of which geometry is 2.6. With dictionary encoding on the identifier columns and delta encoding on the timestamps the file is 3.1 gigabytes, and geometry is now four fifths of it. 42 M matched segments, column sizes default encodings — 4.2 GB tuned encodings — 3.1 GB geometry (WKB) identifiers timestamps metrics Encoding removes a quarter of the file and leaves geometry as four fifths of what remains. Any further saving has to come from the geometry column, which needs a different technique.

Reading It Back

q = (
    pl.scan_parquet("s3://fleet/matched/operating_month=*/*.parquet")
    .filter(
        (pl.col("operating_month") >= "2026-06")
        & (pl.col("vehicle_id") == "v-0142")
    )
    .select(["vehicle_id", "enter_utc", "osm_way_id", "length_m"])
)
print(q.explain(optimized=True))       # confirm the predicate and projection pushed down
df = q.collect(engine="streaming")

Always check explain before trusting a query’s cost. A predicate that has not pushed into the scan is applied after reading everything, and the symptom is a query that is inexplicably slow on a well-sorted file. The two usual causes are a filter on a computed column and a select placed before the filter in a way that drops the filter column.

Selecting only the columns needed is the other half. Reading four columns from a file whose geometry column is four fifths of the bytes costs a fifth of the I/O — a saving no row store can offer.

Common Pitfalls Specific to This Technique

Writing without statistics. Some writers disable them for speed. The file then cannot be pruned, and every query pays for it forever.

Omitting the geo metadata. The file still reads, and every consumer has to be told its CRS out of band. Six months later somebody will not be told.

Partitioning by day. Twelve times the objects for the same data, slower listing, and no benefit that row-group statistics were not already providing.

Re-sorting on read. If queries consistently sort by something other than the write order, change the write order. Sorting at read time on a multi-gigabyte scan is the most expensive way to fix a write-time decision.

Keeping the Export Job Honest

An export job that silently writes a bad file is worse than one that fails, because the failure is discovered months later by a query that returns too few rows. Four checks make that unlikely, and all of them run in seconds against the file just written.

Row count matches the source. The most basic assertion and the one that catches a truncated write, a filter applied by accident, and a partition boundary off by a day.

Column set and dtypes match the schema. Parquet is self-describing, so a column that changed type upstream will happily write and then fail to concatenate with last month’s file.

Statistics are present and selective. Read the file’s metadata back and assert that the sort column’s row-group ranges do not all span the full domain. This is the check that catches a forgotten sort, which is invisible in every other respect.

A sample round-trips. Read a hundred rows back and compare against the source, geometry included.

import pyarrow.parquet as pq

def verify(path: str, source: pl.DataFrame) -> None:
    md = pq.ParquetFile(path).metadata
    assert md.num_rows == len(source), f"{md.num_rows} rows written for {len(source)}"

    # Row groups must not all span the whole vehicle range, or the sort was skipped.
    col = pq.ParquetFile(path).schema_arrow.get_field_index("vehicle_id")
    spans = {
        (md.row_group(i).column(col).statistics.min, md.row_group(i).column(col).statistics.max)
        for i in range(md.num_row_groups)
    }
    assert len(spans) > 1, "every row group spans the same range — the frame was not sorted"

    back = pl.read_parquet(path, n_rows=100)
    assert back.schema == source.schema, "schema drift between source and file"

The third assertion is the interesting one, because it is the only check here that can fail while everything else looks perfect. Test it by writing an unsorted file deliberately and confirming it raises — a check that has never rejected anything is not evidence of anything.

Idempotency

Exports get re-run: a backfill, a corrected upstream batch, a job that failed after writing half its files. Make the export a pure function of its input partition and write to a temporary prefix that is promoted on success, so a re-run replaces rather than appends.

tmp = f"{dest}/_tmp-{month}"
export_month(df, f"{tmp}/part-0000.parquet")
verify(f"{tmp}/part-0000.parquet", df)
promote(tmp, f"{dest}/operating_month={month}")     # atomic rename or copy-then-delete

Appending is the failure mode to avoid. A partially-written month that is appended to on the next run produces duplicate rows that no downstream query will notice, because trajectory data has no natural uniqueness constraint a reader would check.

Four checks, four distinct export failures Row count catches a truncated write. Schema comparison catches an upstream type change. Row-group statistics catch a forgotten sort. A sample round-trip catches a geometry encoding error. Only the third can fail while every other signal looks healthy. Each check exists because the others miss its failure row count truncated write, stray filter schema upstream type change breaks concatenation row-group stats forgotten sort — invisible otherwise sample round-trip geometry encoding or CRS mistake A file that fails only the third check reads correctly, returns correct answers, and scans ten times slower. That is the failure nobody reports, because nothing is wrong — it is just expensive. Write to a temporary prefix and promote on success, so a re-run replaces rather than appends. Trajectory data has no natural key, so duplicate rows from an append are effectively undetectable later.

Related

Up to section Storing and Querying Matched Trajectories Related Storing Matched Traces in PostGIS for Fleet Analytics Related Compressing Fleet Trajectory Archives Without Losing Fidelity