Scaling Mobility Flow Visualization: Origin-Destination Data with DuckDB, Flowmap.gl, and SQLRooms

<p>Visualizing origin-destination (OD) mobility data—commuter flows, transit ridership, freight traffic—is essential for transport planning, but datasets can contain millions of flows that overwhelm traditional mapping approaches. In this talk, I'll present open-source tools for preparing and visualizing large-scale OD data interactively in the browser.</p> <p>I'll introduce <a href="https://flowmap.gl">flowmap.gl</a>, a WebGL-based flow map layer for <a href="https://deck.gl">deck.gl</a> that renders geographic movements with adaptive clustering and filtering. To handle large datasets, I'll demonstrate <a href="https://pypi.org/project/sqlrooms-flowmap/">sqlrooms-flowmap</a>, a Python tool that uses <a href="https://duckdb.org">DuckDB</a> with spatial extensions to prepare OD data for tiled serving:</p> <ul> <li><strong>Hierarchical clustering</strong>: locations are grouped at each zoom level using pixel-radius clustering, creating a hierarchy where clusters merge as users zoom out</li> <li><strong>Nested Hilbert indexing</strong>: OD pairs are indexed using a space-filling curve that preserves locality, enabling efficient range queries for tile-based serving</li> <li><strong>Spatio-temporal aggregation</strong>: flows are aggregated to match zoom-based clustering, with optional temporal bucketing by hour/day/week</li> </ul> <p>The prepared data can be visualized using a demo app built with <a href="https://sqlrooms.org">SQLRooms</a>, a browser-based analytics framework powered by DuckDB, where users can query and explore flows using SQL alongside interactive maps.</p> <p>I'll show a live demo using Switzerland's <a href="https://www.are.admin.ch/are/en/home/transport-and-infrastructure/data/npvm.html">National Passenger Transport Model (NPVM)</a>—an open dataset of passenger flows across the Swiss transport network—demonstrating the full pipeline from raw data to interactive visualization, all using open-source tools that can run locally without cloud dependencies.</p>