carto-routing-od-analysis

Routing and Origin-Destination Analysis

Computes routes, travel time/distance matrices, and isoline catchment areas (driving and walking modes), plus OD flow pattern analysis via spatial indexing. Two access paths, by scale:

• Single / ad-hoc (MCP-first): for one route, one location's isolines, or a small OD matrix, the CARTO MCP tools route, calculate_isolines, and calculate_od_matrix answer directly (each also has a capabilities operation to list supported modes/range types). No workflow needed. These run on any MCP host, including sandboxed chat hosts where the CLI can't. Prefer them for interactive requests.
• Table-scale / repeatable: for computing over a whole table or a reusable pipeline, use the Workflow patterns below. Load carto-create-workflow for the development process, JSON structure, and validation — it covers both paths (MCP create_workflow / validate_workflow / run_workflow when attached; the carto workflows CLI otherwise; routing signals in carto-basics/references/access-paths.md).

Over an API-token MCP session the ad-hoc/authoring tools are hidden (read/discovery only) — fall back to the CLI or reconnect over OAuth.

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, then validate and upload the workflow (MCP create_workflow / validate_workflow, or 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, then validate and upload (MCP create_workflow or 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: One route line geometry per OD pair (with duration_s, distance_m), visualizable on a map.

Step C3: Save

Use native.saveastable, then validate and upload (MCP create_workflow or 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. pickuplon/lat, dropofflon/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 (originh3, destinationh3) 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