Routing and Origin-Destination Analysis

Builds CARTO Workflows that compute routes, travel time/distance matrices, and isoline catchment areas. Supports driving and walking modes. Also covers OD flow pattern analysis using spatial indexing.

Prerequisites: Load carto-create-workflow for the development process, JSON structure, and validation commands.

Instructions

Three main workflow patterns exist. Choose based on the use case:

Pattern A: Isoline/Isochrone Generation

Pipeline:

Source Points -> (Filter) -> Isolines -> (Polyfill / Enrich) -> Save

Step A1: Load Source Points

Use native.gettablebyname to load locations (stores, stations, facilities).

Success: Table with a geometry column and a unique location identifier.

Step A2: Generate Isolines

Use native.isolines with:

Success: Each input point has an associated polygon geometry representing the reachable area.

Step A3: Post-Processing (optional)

Common follow-ups after isoline generation:

• Polyfill + Enrich: Convert isoline polygons to H3 with native.h3polyfill, then enrich with demographics or POI data (see trade-area-analysis skill).
• Overlap analysis: Use native.spatialjoin to find which isolines overlap, identifying areas served by multiple locations.
• Coverage union: Use native.dissolve to merge all isoline polygons into a single coverage footprint.

Step A4: Save

Use native.saveastable to persist isoline polygons or enriched results.

Success: Validated workflow uploadable via carto workflows create.

Pattern B: OD Matrix (Travel Time/Distance)

Pipeline:

Origins Table -> ┐
                 ├-> OD Matrix -> (Filter/Aggregate) -> Save
Destinations Table -> ┘

Step B1: Load Origins and Destinations

Use two native.gettablebyname nodes – one for origins, one for destinations. Both need geometry columns.

Success: Two tables, each with point geometries and unique identifiers.

Step B2: Compute OD Matrix

Use native.routesodmatrix with:

Output columns: origin_id, destination_id, duration_s, distance_m.

Success: One row per origin-destination pair with travel time and distance.

Step B3: Filter or Aggregate (optional)

Common post-processing:

• Nearest destination: Use native.groupby to find the minimum duration_s per origin, then join back to get the nearest destination.
• Threshold filter: Use native.where to keep only pairs within a time/distance limit (e.g. duration_s < 1800 for 30-min threshold).
• Accessibility score: Count destinations reachable within a threshold per origin using native.groupby.

Step B4: Save

Use native.saveastable.

Success: Validated workflow uploadable via carto workflows create.

Pattern C: Route Geometries

Pipeline:

Origins Table -> ┐
                 ├-> Routes -> Save
Destinations Table -> ┘

Step C1: Load Origins and Destinations

Same as Pattern B – two native.gettablebyname nodes with point geometries.

Step C2: Compute Routes

Use native.routes with:

Output: Route line geometries with duration_s and distance_m attributes.

Success: One route geometry per OD pair, visualizable on a map.

Step C3: Save

Use native.saveastable.

Success: Validated workflow uploadable via carto workflows create.

Pattern D: OD Flow Analysis (Grid-Based)

For analyzing trip/movement patterns at scale (e.g. taxi trips, bike rides, commute flows) without calling routing APIs.

Pipeline:

Trip Data -> H3 (origin) + H3 (destination) -> Group By (origin_h3, dest_h3) -> Save

Step D1: Load Trip Data

Use native.gettablebyname. The table should have both origin and destination coordinates (e.g. pickup_lon/lat, dropoff_lon/lat).

Step D2: Index Origins and Destinations to H3

Use native.selectexpression to compute H3 cells for both origin and destination points:

• Origin H3: derive from pickup coordinates
• Destination H3: derive from dropoff coordinates

Alternatively, if the data has separate geometry columns, use native.h3frompoint for each.

Step D3: Aggregate Flows

Use native.groupby to count trips per (origin_h3, destination_h3) pair:

• Group by: origin_h3, destination_h3
• Aggregation: origin_h3,count (trip count per OD pair)

Success: One row per unique OD cell pair with trip count – ready for flow visualization.

Step D4: Save

Use native.saveastable.

Gotchas

• Provider casing & SQL dialect. This skill uses lowercase column names (origin_id, destination_id, duration_s, distance_m, origin_h3, etc.) — BigQuery / Databricks / Postgres / Redshift convention. On Snowflake, reference these UPPERCASE (ORIGIN_ID, DURATION_S, …). See carto-create-workflow/references/providers/<provider>.md for casing rules and SQL dialect equivalents.
• Isolines and routing components consume LDS (Location Data Services) quota. Check available quota with LDS_QUOTA_INFO before bulk operations. Buffers (native.buffer) do not consume LDS quota and are a free alternative for simple circular catchments.
• OD matrices grow quadratically: N origins x M destinations = N*M rows. Filter or sample inputs to keep the matrix manageable. For 1000 origins x 1000 destinations, you get 1 million rows.
• Walking mode has a much shorter practical range than driving. Walking isolines beyond 20-30 minutes or OD matrices beyond a few kilometers produce unreliable or empty results.
• Route geometries can be large. For pure analytics (time/distance only), prefer the OD matrix (Pattern B) over full routes (Pattern C) to reduce data volume.
• Time-of-day affects driving results due to congestion. Specify departure_time if the component supports it; otherwise results reflect typical/average conditions.
• Isoline polygons may overlap for nearby locations. If enriching afterwards, polyfill to a spatial index and deduplicate cells to avoid double-counting.
• For OD flow visualization (Pattern D), use H3 cell center points rather than raw coordinates for cleaner aggregation and visualization. A coarser resolution (e.g. H3 res 7-8) produces more meaningful flow patterns than fine resolutions.
• The LDS routing components require a connection with LDS API access enabled. Validation may fail if the connection lacks this permission.

Reference Templates

Common Variations