Bounding boxes, byte strings, and the “which-files-do-I-need” query.

1 Parcels with WKB — prep/parcels_wkb.py

The parcels module from Lesson 2 returned a GeoPandas GeoDataFrame. That’s great for Python-side work, but DuckDB doesn’t speak GeoPandas; it speaks WKB (Well-Known Binary), the standard byte encoding for geometries. So we produce a derived form designed for DuckDB consumption:

build_parcels_wkb(
    parcels_gdf,        # the GeoDataFrame from prep.parcels
    routes_gdf,         # the route polygons (for assignment)
)
# Returns a plain DataFrame with columns:
#   APN, route_id, geom_wkb (bytes), min_lon, min_lat, max_lon, max_lat

• WKB bytes — the geometry as a bytes column DuckDB can ST_GeomFromWKB on without Python ever touching it.
• Per-parcel route_id assignment — a spatial join (parcels-in-routes) attaches the route each parcel belongs to. Parcels outside any route get NULL; they’re not serviced.
• Bbox columns precomputed — min/max lon/lat directly on the row. Bbox filtering becomes plain SQL WHERE clauses.

Writing the output to a single parquet via write_parcels_wkb() gives you a file DuckDB can query directly: SELECT * FROM 'parcels_wkb.parquet' WHERE route_id = 'A1'.

2 Vehicle-day indexes — cache/gps_indexes.py

Each GPS cache file (one parquet per vehicle per local day from Lesson 6) gets summarized to a single row of metadata:

build_day_index(cache_dir, "2026-01-18")
# -> writes cache/2026-01-18/_vehicle_day_index.parquet
#    columns: vehicle_id, n_rows, min_dt, max_dt,
#             min_lon, min_lat, max_lon, max_lat

The index file lives inside the day directory, so it’s discoverable next to the data it summarizes. The _ prefix sorts it to the top and makes it look like the “table of contents” for that day.

• summarize_vehicle_day_file(path) — do the math for one file. Reads min/max dt and lon/lat in a single pyarrow scan; no full materialization.
• build_day_index(cache_dir, day) — loop one day’s files, write the summary parquet.
• build_all_day_indexes(cache_dir) — rebuild every day’s index. Idempotent.

3 The master index — cache/master_index.py

The headline lookup table. For every (vehicle, day) in the cache and every route_id in your route bbox index, check whether their bounding boxes overlap. If they do, emit a row. If not, don’t.

build_master_index(
    vehicle_day_indexes_df,   # concatenated output of build_day_index
    route_bboxes_df,          # output of build_route_index from Lesson 3
)
# -> DataFrame with columns:
#    vehicle_id, day, route_id, n_rows, min_dt, max_dt

This is STAC-like in spirit: a small lookup of “which assets cover which spatial extents.” The actual implementation is a self-join with bbox-overlap predicates — no heavy geometry library needed, just numeric comparisons.

files_for_route(master_df, route_id, day) is the tiny convenience: takes the master index and a (route, day), returns the list of vehicle-day cache files that need to be opened. The routing engine in Lesson 8 will call this once at the top of each route-day analysis — maybe 5 files instead of 500.

4 Tests with synthetic data

Three test files, all using synthetic geometries and pings (no real data):

• tests/test_parcels_wkb.py — WKB encoding round-trips; spatial-join assignment correct; bbox columns match geometry.
• tests/test_gps_indexes.py — summary stats correct; index file written; build_all idempotent.
• tests/test_master_index.py — bbox-overlap logic; non-overlapping pairs excluded; files_for_route returns the right paths.

pip install -e ".[dev,geotab,postgres]"
ruff check .
pytest -q

5 Commit and push

git add .
git commit -m "Lesson 7: parcels_wkb + GPS indexes + master index"
git push origin main