Sink geometry

Part of Sinks.

Positions and shapes: geometry columns

Think of a bus that reports where it is every few seconds, a delivery van, a phone or a tracker. A sink can feed them into a format-version 3 table with a geometry column, partitioned by day. The maintenance keeps it fast to query by area.

lhbox table create fleet.transit.positions --format-version 3 --partition 'day(ts)' \
     --column vehicle_id:string:required --column ts:timestamptz:required --column geometry:geometry --column speed:double
cat > positions.json <<'JSON'
{"point": {"column": "geometry", "lon": "lon", "lat": "lat"}, "rename": {"id": "vehicle_id"},
 "timestamp": {"column": "ts", "from": "t", "format": "epoch_s", "fallback": "reject"}}
JSON
lhbox sink create fleet --table transit.positions --name positions_in --mapping positions.json --save ~/positions-sink.json
# a producer then posts [{"id": "bus-12", "lon": -3.7038, "lat": 40.4168, "t": 1791244740, "speed": 7.5}, ...]

What a geometry column takes:

  • a GeoJSON geometry object: {"type": "Point", "coordinates": [-3.70, 40.42]}, lines, polygons, their multi forms and collections;
  • WKT text, POINT (-3.70 40.42), with or without SRID=4326;;
  • hex WKB, as PostGIS prints it.

A mapping's point makes the GeoJSON point from two fields, one for the longitude and one for the latitude. A third, z, is a height. They may be numbers or numeric strings. The axes are always named, because swapped axes are the usual producer bug.

The column is plain geometry, in the default CRS: longitude and latitude in degrees, as GPS gives them. These reject the row:

  • a longitude outside [-180, 180], or a latitude outside [-90, 90];
  • a GeoJSON Feature (send its geometry; its properties are other columns);
  • an unclosed ring, M coordinates, or another SRID.

How it is written. DuckDB commits a format-version 3 table; PyIceberg commits format-version 2 tables and cannot write version 3. Each roll is one append, with one file per day partition. The day is computed in UTC, whatever the sender's zone.

Exactly-once still holds:

  • Before the commit, the roll writes an intent naming its batches.
  • The commit sets the table property lakehousebox.sink.roll in the same transaction.
  • A roll that dies is confirmed or abandoned from that property on the next tick.
  • A commit that races another (an engine's, the maintenance's) is refused by the catalog and retried.
  • A commit whose answer was lost is recognised from the property, and not written twice.
  • A commit whose outcome is unknown is never retried at once, because it may still land. The sink waits up to 10 minutes for the property to name it before rolling those batches again.

Writing such a table yourself. With DuckDB, run SET TimeZone = 'UTC'; before the INSERT. DuckDB 1.5.6 computes the day of a timestamptz in the session's time zone, not in UTC as Iceberg defines it. From a session in Los Angeles, rows of 2026-10-05 UTC were filed under 2026-10-04, and a filter on 2026-10-05 found none of them.

What the maintenance does with it. Today stays as it was written, in time order. So "where is every vehicle now" reads only the newest files. Once a day has ended, plus two hours for late batches, the maintenance compacts it in spatial order. It uses a Hilbert curve over the points, with small row groups carrying geometry statistics. Then a query for one area of one past day reads a small part of it. The figures are under "Numbers and dates".

Questions it answers well, with DuckDB through the catalog's recipe:

-- where every vehicle was last seen in the past ten minutes (reads only the newest files)
SELECT vehicle_id, arg_max(geometry, ts) AS geometry, max(ts) AS ts
FROM lake.transit.positions WHERE ts > now() - INTERVAL 10 MINUTE GROUP BY vehicle_id;
-- what passed through one area today (prunes by day, then by row group)
SELECT vehicle_id, ts FROM lake.transit.positions
WHERE ts >= current_date AND geometry && ST_MakeEnvelope(-3.71, 40.41, -3.69, 40.43);

&& is the operator that prunes; ST_Intersects alone reads everything. In a space-ordered layout, one vehicle's whole day reads most of the day's row groups. For many such questions, keep a per-vehicle table of your own.

Not done for you (yet):

  • a latest-position table kept up to date, or a per-vehicle tracks table;
  • deleting old days on a schedule. Run DELETE FROM … WHERE ts < … yourself: positions of people are personal data, so keep them only as long as you need them;
  • GTFS-Realtime feeds (protobuf, polled rather than pushed), and the query-string form of phone tracker apps.