# Snowflake OpenRouteService This extension package brings [OpenRouteService](https://openrouteservice.org/) (ORS) routing capabilities into CARTO Workflows for Snowflake. It includes four components — directions, isochrones, travel-time matrix and vehicle-routing optimization (powered by [VROOM](https://github.com/VROOM-Project/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](https://www.snowflake.com/en/developers/guides/oss-install-openrouteservice-native-app/), which deploys OpenRouteService on Snowpark Container Services. All routing computations stay within your Snowflake account. ## Prerequisites {% hint style="warning" %} 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](https://www.snowflake.com/en/developers/guides/oss-install-openrouteservice-native-app/). The native app must be installed under the database name `OPENROUTESERVICE_APP`. {% endhint %} {% hint style="success" %} **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. {% endhint %} You can list the regions currently available in your Snowflake account by running the following query in the Snowflake worksheet: ```sql 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](https://github.com/CartoDB/workflows-extension-snowflake-ors). ## 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](https://openrouteservice.org/dev/#/api-docs/v2/directions) 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](#common-settings). * **Region**: the ORS region whose routing graph should be used. See [Common settings](#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](https://openrouteservice.org/dev/#/api-docs/v2/isochrones) 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](#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](#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](https://openrouteservice.org/dev/#/api-docs/v2/matrix) documentation. {% hint style="info" %} Output cardinality is `|origins| × |destinations|`, so keep both tables reasonably sized — large matrices grow fast. {% endhint %} **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](#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](#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](https://github.com/VROOM-Project/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](https://github.com/VROOM-Project/vroom/blob/master/docs/API.md). **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](#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. --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://docs.carto.com/carto-user-manual/workflows/components/snowflake-ors.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.