Snowflake OpenRouteService

Extension Package provided by CARTO

This extension package brings OpenRouteService (ORS) routing capabilities into CARTO Workflows for Snowflake. It includes four components — directions, isochrones, travel-time matrix and vehicle-routing optimization (powered by VROOM) — that let you solve common logistics and accessibility problems directly inside your Snowflake account, with no external API calls.

The components are thin wrappers around the OpenRouteService Native App, which deploys OpenRouteService on Snowpark Container Services. All routing computations stay within your Snowflake account.

Prerequisites

Before installing this extension package in CARTO Workflows, the OpenRouteService Native App must already be installed in your Snowflake account, following the official Snowflake guide. The native app must be installed under the database name OPENROUTESERVICE_APP.

A friendly note about regions 🌍

The OpenRouteService Native App is configured for a specific geographic region at installation time. For example, if you install it for New York, the setup process will download and prepare the routing graph for that area, and the components in this extension will then work beautifully for any points within the New York region.

If you also want to route somewhere else (say, San Francisco), good news: you can provision additional regions by re-running the installation guide for each one. Once a region is provisioned, the components in this extension can target it through the Region setting.

What this means in practice: the components will only return routes, isochrones or matrices for points that fall inside one of the regions your administrator has provisioned. Points outside a provisioned region won't produce meaningful results.

You can list the regions currently available in your Snowflake account by running the following query in the Snowflake worksheet:

CALL OPENROUTESERVICE_APP.CORE.LIST_REGIONS();

The role used by your CARTO connection also needs read access to the OPENROUTESERVICE_APP database and execute privileges on its procedures and functions. The full grants list is documented in the extension's README.

Common settings

All components in this package share two settings:

• Region: case-sensitive name of the ORS region to use (e.g. NewYork, SanFrancisco). Must match a region that has been provisioned in OPENROUTESERVICE_APP and whose Snowpark Container Services service is currently running. All input points (origins, destinations, vehicle starts, etc.) must lie inside this region's bounding box.

• Routing profile (where applicable): mode of transport used to compute the route. One of:

  • driving-car: passenger cars; follows car-only restrictions and one-ways.

  • driving-hgv: heavy goods vehicles; avoids weight-restricted streets and low bridges.

  • cycling-electric: e-bike speed model; allows cycle paths.

ORS Directions

Description

Computes the road route between an origin and a destination, one row at a time. Each input row becomes one OpenRouteService request, and the result is appended to the row as road distance, travel time and route geometry. All input columns are preserved so you can join the route back to your business data.

For more details, please refer to the OpenRouteService Directions API documentation.

Inputs

• Input table: a table where each row defines one trip. Must contain an origin GEOGRAPHY POINT column and a destination GEOGRAPHY POINT column. Any additional columns (trip id, customer attributes, time windows…) are passed through unchanged.

Settings

• Origin: column containing the trip's start point. Must be a GEOGRAPHY POINT in WGS84 (EPSG:4326).

• Destination: column containing the trip's end point. Must be a GEOGRAPHY POINT in WGS84.

• Routing profile: mode of transport used to compute the route. See Common settings.

• Region: the ORS region whose routing graph should be used. See Common settings.

Outputs

• Output table: all input columns plus three new columns:

  • distance_m: road distance in metres.

  • duration_s: travel time in seconds, accounting for the selected profile.

  • route_geom: GeoJSON LineString with the computed route. Wrap with TO_GEOGRAPHY(route_geom) for spatial operations or to render on a CARTO map.

ORS Isochrones

Description

Computes a reachability polygon (an isochrone) around each point in an input table — the area you can reach within a given travel time. Ideal for catchment analysis, accessibility studies, store coverage maps and "how far can I get in N minutes" questions.

For more details, please refer to the OpenRouteService Isochrones API documentation.

Inputs

• Input table: a table where each row defines one isochrone center. Must contain a GEOGRAPHY POINT column. All other columns (store id, name, sales attributes…) are preserved in the output so you can join the polygon back to your business data.

Settings

• Center point: column containing the isochrone's origin point. Must be a GEOGRAPHY POINT in WGS84.

• Routing profile: mode of transport used to compute the reachability area. See Common settings.

• Range (minutes): travel-time radius in minutes (maximum 300). Typical values are 5–30 min for urban catchments and 60–120 min for regional studies. Default is 15.

• Region: the ORS region whose routing graph should be used. See Common settings.

Outputs

• Output table: all input columns plus one new column:

  • isochrone_geom: GeoJSON Polygon with the reachable area within the specified range. Wrap with TO_GEOGRAPHY(isochrone_geom) for spatial joins or to render on a CARTO map.

ORS Travel-Time Matrix

Description

Computes a many-to-many travel-time and distance matrix from a table of origins to a table of destinations. Returns one row per (origin, destination) pair — perfect for hub-and-spoke logistics, supply-chain analysis, store assignment, or as preprocessing for an external optimizer.

For more details, please refer to the OpenRouteService Matrix API documentation.

Output cardinality is |origins| × |destinations|, so keep both tables reasonably sized — large matrices grow fast.

Inputs

• Origins table: a table of origin points (one row per origin). Must contain a unique id column and a GEOGRAPHY POINT column.

• Destinations table: a table of destination points (one row per destination). Must contain a unique id column and a GEOGRAPHY POINT column.

Settings

• Origin id: column uniquely identifying each origin. Carried through to the output so you can join durations back to the origin entity.

• Origin point: column with the origin location. Must be a GEOGRAPHY POINT in WGS84.

• Destination id: column uniquely identifying each destination. Carried through to the output.

• Destination point: column with the destination location. Must be a GEOGRAPHY POINT in WGS84.

• Routing profile: mode of transport applied to every pair in the matrix. See Common settings. If you need different profiles per pair, run the component multiple times and union the results.

• Region: the ORS region whose routing graph should be used. See Common settings.

Outputs

• Output table: one row per origin × destination pair, with four columns:

  • origin_id: echoed from the origins table.

  • destination_id: echoed from the destinations table.

  • duration_s: travel time in seconds.

  • distance_m: road distance in metres.

  Pairs that ORS cannot route (e.g. unreachable due to islands or restrictions) appear with NULL duration_s and distance_m so you can filter them out downstream.

ORS Vehicle Routing Optimization

Description

Solves a Vehicle Routing Problem (VRP): assigns a set of jobs to a set of vehicles and produces optimized routes that minimize total travel time. Powered by OpenRouteService + VROOM.

Returns one row per vehicle with the full optimized route as a GEOGRAPHY LineString plus per-step details as ordered arrays — ready to render on a map or to flatten into a step-level table.

For more details, please refer to the VROOM API documentation.

Inputs

• Jobs table: a table of jobs (deliveries, pickups, service stops). One row per job. Must contain a unique numeric id column and a GEOGRAPHY POINT location column.

• Vehicles table: a table of available vehicles. One row per vehicle. Must contain a unique numeric id and a GEOGRAPHY POINT for the vehicle's start (depot) location. The solver assumes vehicles return to their start at end-of-day.

Settings

• Job id: column with a unique numeric job id. Must be a 64-bit integer. If your source ids are strings or UUIDs, generate sequential integers with ROW_NUMBER() OVER (ORDER BY ...).

• Job location: column with the job's location. Must be a GEOGRAPHY POINT in WGS84.

• Vehicle id: column with a unique numeric vehicle id. Must be a 64-bit integer.

• Vehicle start (depot): column with the vehicle's start point. Must be a GEOGRAPHY POINT. Used as the depot at both the start and the end of the route. Multiple vehicles can share the same start point.

• Region: the ORS region whose routing graph should be used. All jobs and all vehicle start points must lie inside this region's bounding box. See Common settings.

Outputs

• Output table: one row per vehicle, with the following columns:

  • vehicle_id: echoed from the vehicles table.

  • duration_s: total tour duration in seconds.

  • num_jobs: count of jobs assigned to this vehicle (may be 0 if VROOM left it idle).

  • route_geom: GEOGRAPHY LineString with the full optimized route, ready to render on a CARTO map.

  • step_types: ARRAY of STRING with the steps in visit order, e.g. ['start','job','job','job','end'].

  • visited_job_ids: ARRAY of NUMBER with the visited job ids in visit order (excludes start/end).

  • arrivals_s: ARRAY of FLOAT with cumulative seconds at each step, parallel to step_types.

  To get one row per step, LATERAL FLATTEN the arrays downstream.