Prepare origin-destination datasets for tiling using DuckDB and spatial extensions.

pip install sqlrooms-flowmap

sqlrooms-flowmap \
  --locations locations.csv \
  --flows flows.csv \
  --output-dir ./output

This generates:

• ./output/locations-clusters.parquet - hierarchical clusters
• ./output/locations-flows.parquet - tiled flows with nested Hilbert indexing

sqlrooms-flowmap \
  --locations locations.csv \
  --flows flows.csv \
  --output-dir ./output \
  --skip-flows

sqlrooms-flowmap \
  --locations locations.csv \
  --flows flows.csv \
  --output-dir ./output \
  --radius 50 \
  --min-zoom 0 \
  --max-zoom 15

sqlrooms-flowmap \
  --locations locations.csv \
  --flows flows.csv \
  --output-dir ./output \
  --time-bucket day \
  --time-zone America/New_York

• --locations - Path to locations CSV/Parquet file
• --flows - Path to flows CSV/Parquet file
• --output-dir - Output directory (files named from locations file)
• --radius - Cluster radius in pixels (default: 40). Scales with zoom level
• --min-zoom - Minimum zoom level for clustering (default: 0)
• --max-zoom - Maximum zoom level to start from (default: 20)
• --time-bucket - (Optional) Temporal aggregation: hour, day, week, month, year
• --time-zone - Time zone for temporal aggregation (default: UTC)
• --skip-flows - Skip flow tiling, only generate clusters

Locations table:

• id - unique identifier
• name (optional) - location name
• lat - WGS84 latitude
• lon - WGS84 longitude

Flows table:

• origin - origin location ID
• dest - destination location ID
• count - flow magnitude
• time (optional) - temporal dimension

Parquet file with hierarchical clusters ordered by zoom level and Hilbert index:

• z - zoom level
• h_index - Hilbert index for spatial ordering
• id - cluster/location ID
• name - cluster/location name (format: "Location + N")
• parent_id - parent cluster ID (self-reference for top-level items)
• lat - WGS84 latitude
• lon - WGS84 longitude
• x - Web Mercator X coordinate
• y - Web Mercator Y coordinate
• weight - cluster weight (max of in/out flows)
• size - total number of original locations in this cluster
• top_id - ID of the highest-weighted original location in this cluster
• total_in - total incoming flows from non-members
• total_out - total outgoing flows to non-members
• total_self - self-loops plus flows between cluster members

The cluster hierarchy is built by creating separate rows for each location/cluster at each zoom level. Locations appear at multiple zoom levels until they merge:

z=11 (detailed):
  Geneva (parent_id=Geneva, size=1)      ← top-level, self-reference
  Lausanne (parent_id=Lausanne, size=1)  ← top-level, self-reference
  Zurich (parent_id=Zurich, size=1)      ← top-level, self-reference

z=10 (slightly zoomed out, Geneva+Lausanne start merging):
  Geneva (parent_id=cluster_z9_0, size=1)    ← will merge at z=9
  Lausanne (parent_id=cluster_z9_0, size=1)  ← will merge at z=9
  Zurich (parent_id=Zurich, size=1)          ← still separate, top-level

z=9 (merged):
  cluster_z9_0 "Geneva + 1" (parent_id=cluster_z8_0, size=2)  ← Geneva+Lausanne merged
  Zurich (parent_id=cluster_z8_0, size=1)                     ← will merge at z=8

z=8 (all merge):
  cluster_z8_0 "Zurich + 2" (parent_id=cluster_z8_0, size=3)  ← all 3 cities merged

z=0 (fully zoomed out):
  cluster_z0_0 (parent_id=cluster_z0_0, size=26) ← single cluster containing everything

Key points:

• Each location appears at multiple zoom levels as separate rows
• parent_id = id means top-level (rendered at this zoom)
• parent_id != id means merged into parent at the next lower zoom level
• To find all leaf members of a cluster, recursively follow parent_id links

Parquet file with tiled OD flows ordered by z, flow_h, [time]:

• z - zoom level
• flow_h - nested Hilbert index for origin-destination pairs (enables efficient spatial queries)
• origin - origin cluster/location ID
• dest - destination cluster/location ID
• count - aggregated flow count
• time or time_bucket - temporal dimension (if present in input and/or bucketed)

The flows are spatially indexed using a nested Hilbert transform that preserves locality in OD-space, making range queries efficient for tile-based serving.

A Flask-based vector tile server is included in examples/server.py. To run it:

# Install dev dependencies
uv sync --group dev

# Run the server
python examples/server.py --clusters <clusters.parquet> --flows <flows.parquet>

See examples/README.md for details.

MIT