# routing

This module contains functions that perform routing and path calculations natively in BigQuery without the need of calling external location data services. In order to run the functions of this module the user needs to have access to CARTO's pre-generated [road network](https://carto.com/spatial-data-catalog/browser/geography/cdb_road_networ_81badfc2/) (based on OSM segments) that is available as a public subscription via the Data Observatory. Please check [this guide](https://docs.carto.com/data-and-analysis/data-observatory/guides/subscribing-to-public-and-premium-datasets) to learn how to subscribe to a dataset from the Data Observatory.

### ROUTING\_MATRIX <a href="#routing_matrix" id="routing_matrix"></a>

```sql
ROUTING_MATRIX(start_point_array, dest_point_array, area_of_interest, transportation_mode, do_network_table, do_source, output_table, options)
```

**Description**

This procedure calculates the shortest paths in terms of travel times or distances for all routes between all of a given set of locations. It requires a Data Observatory road network subscription to perform the calculations.

For every given origin, this procedure calculates the minimum cost of travel from that origin to every given destination on the road network specified.

**Input parameters**

* `start_point_array`: `ARRAY<GEOGRAPHY>` Source points array. the node of the network nearest to this point will be used as the source point to compute the shortest path.
* `dest_point_array`: `ARRAY<GEOGRAPHY>` destination points array. the node of the network nearest to this point will be used as the destination point to compute the shortest path.
* `area_of_interest`: `GEOGRAPHY` area of interest over where the analysis takes place.
* `transportation_mode`: `STRING` type of transportation mode to be used for the calculation of routes. Available options: `car`, `car_motorway_only`, `car_major_road_only`, `bicycle` or `foot`.
* `do_network_table`: `STRING` identifier (slug) of the Data Observatory Network table.
* `do_source`: `STRING` name of the location where the Data Observatory subscriptions of the user are stored, in `<my-dataobs-project>.<my-dataobs-dataset>` format. If only the `<my-dataobs-dataset>` is included, it uses the project `carto-data` by default. It can be set to NULL or ''.
* `output_table`: `STRING` qualified name of the output table, e.g. `<my-project>.<my-dataset>.<my-output-table>`. The process will fail if the table already exists.
* `options`: `STRING` containing a valid JSON with the different options. Valid options are described the table below. If `options` is set to `NULL` the all options are set to default.

| Option                           | Type      | Default | Description                                                                                                                                                                                    |
| -------------------------------- | --------- | ------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `TYPE`                           | `STRING`  | `time`  | Defines the kind of optimisation for pathfinding. Possible values: "time", "distance".                                                                                                         |
| `MAX_COST`                       | `FLOAT64` | `NULL`  | Defines maximum cost for pathfinding. If NULL all snapping distance are allowed.                                                                                                               |
| `WITH_PATH`                      | `BOOL`    | `TRUE`  | If True returns paths and detailed geography. If not returns only the cost.                                                                                                                    |
| `UNCOMPACT_GEOMETRY`             | `BOOL`    | `TRUE`  | If True the full non-compacted geometry between each link in the path. If not return only start and destination nodes of each link in the path.                                                |
| `FALLBACK_WRONG_WAY_SPEED_RATIO` | `FLOAT64` | `NULL`  | Defines the penalty ratio that should be applied to take a one-way road backwards. Value should be between 1 and 0 (excluded). If NULL, one-way roads cannot be used backwards.                |
| `MAX_SNAPPING_DISTANCE`          | `FLOAT64` | `NULL`  | Defines the maximum allowed snapping distance (in meters) between start and destination points and network nodes. If exceeded no route is returned. If NULL all snapping distance are allowed. |

**Return type**

The output table includes the following columns:

* `start_geo`: `GEOGRAPHY` Start point from source points array.
* `dest_geo`: `GEOGRAPHY` Destination point from destination points array.
* `start_geo_snapped`: `GEOGRAPHY` Start point snapped to the nearest start node of links of the network.
* `dest_geo_snapped`: `GEOGRAPHY` Destination point snapped to the nearest destination node of links of the network.
* `start_order`: `INT64` Start point position in the source points array.
* `dest_order`: `INT64` Destination point position in the destination points array.
* `start_s2`: `INT64` Unique identifier of the start point snapped from start point.
* `dest_s2`: `INT64` Unique identifier of the destination node snapped from destination point.
* `cost`: `FLOAT64` Overall cost of the path (travel time or distance depending on the `TYPE` parameter value).
* `distance`: `FLOAT64` Overall driving distance of the path in meters.
* `travel_time`: `FLOAT64` Overall travel time of the path in seconds.
* `path`: `GEOGRAPHY` Overall path.
* `detailed_linestring`: `RECORD`: Array of links that makes up the path.
  * `start_s2` `INT64`: Unique identifier of the start node of the link.
  * `dest_s2` `INT64`: Unique identifier of the destination node of the link.
  * `speed`: `FLOAT64` Speed over the link.
  * `cost`: `FLOAT64` Cost of the link (travel time or distance depending on the `TYPE` parameter value).
  * `distance`: `FLOAT64` Driving distance of the link in meters.
  * `travel_time`: `FLOAT64` Travel time of the path in seconds.
  * `path`: `GEOGRAPHY` Path of the link.
  * `detailed_geography`: `ARRAY<GEOGRAPHY>` Array of points that makes up the link.

**Example**

{% tabs %}
{% tab title="carto-un" %}

```sql
CALL `carto-un`.carto.ROUTING_MATRIX(
     -- start_point_array
    [ST_GEOGPOINT(-73.0, 40.0),ST_GEOGPOINT(-73.0, 41.0)],
    -- dest_point_array
    [ST_GEOGPOINT(-75.0, 41.0),ST_GEOGPOINT(-75.0, 40.0)],
    -- area of interest,
    ST_GEOGFROMTEXT("FULLGLOBE"),
    -- transportation mode
    'car',
    -- do_network_table
    'cdb_road_networ_81badfc2',
     --do_source
    '<my-dataobs-project>.<my-dataobs-dataset>',
     --output_table
    '<my-project>.<my-dataset>.<output_filename>',
    -- options
    """
    {
       "TYPE":"time",
       "MAX_COST":"100000",
       "WITH_PATH":"True"
    }
    """
);
-- {
--   "start_geo": "POINT(-73 40)",
--   "dest_geo": "POINT(-75 41)",
--   "start_geo_snapped": "POINT(-74.013134 40.688339)",
--   "dest_geo_snapped": "POINT(-74.026365 40.685995)",
--   "start_order": "0",
--   "dest_order": "0",
--   "start_s2": "-8520148151882761037",
--   "dest_s2": "-8520148044108704841",
--   "cost": "1082.6350274098224",
--   "distance": "1397.2114843386769",
--   "travel_time": "1082.6350274098224",
--   "path": "LINESTRING(-74.013134 40.688339, ..., -74.026365 40.685995)",
--   "detailed_linestring": [{
--     "start_s2": "-8520148151882761037",
--     "dest_s2": "-8520148151714079533",
--     "speed": "1.9",
--     "cost": "10.694765667341704",
--     "distance": "20.320054767949237",
--     "travel_time": "10.694765667341704",
--     "path": "LINESTRING(-74.013134 40.688339, -74.013375 40.688339)",
--     "detailed_geography": ["POINT(-74.013134 40.688339)", "POINT(-74.013375 40.688339)"]
--   },
--   ...,
--   {
--     "start_s2": "-8520148151867093047",
--     "dest_s2": "-8520148151854368777",
--     "speed": "1.2",
--     "cost": "11.348771839149197",
--     "distance": "13.618526206979036",
--     "travel_time": "11.348771839149197",
--     "path": "LINESTRING(-74.013587 40.688492, -74.013702 40.688578)",
--     "detailed_geography": ["POINT(-74.013587 40.688492)", "POINT(-74.013702 40.688578)"]
--   }]
-- }, {
--   "start_geo": "POINT(-73 40)",
--   "dest_geo": "POINT(-75 40)",
--   "start_geo_snapped": "POINT(-74.013134 40.688339)",
--   "dest_geo_snapped": "POINT(-74.026041 40.684658)",
--   "start_order": "0",
--   "dest_order": "1",
--   "start_s2": "-8520148151882761037",
--   "dest_s2": "-8520148043684863917",
--   "cost": "1124.4760381433846",
--   "distance": "1248.9194302355879",
--   "travel_time": "1124.4760381433846",
--   "path": "LINESTRING(-74.013134 40.688339, ..., -74.026041 40.684658)",
--   "detailed_linestring": [{
--     "start_s2": "-8520148151882761037",
--     "dest_s2": "-8520148151721556507",
--     "speed": "1.5",
--     "cost": "19.218684425894505",
--     "distance": "28.828026638841756",
--     "travel_time": "19.218684425894505",
--     "path": "LINESTRING(-74.013134 40.688339, -74.013221 40.688225, -74.013358 40.688151)",
--     "detailed_geography": ["POINT(-74.013134 40.688339)", "POINT(-74.013221 40.688225)", "POINT(-74.013358 40.688151)"]
--   },
--   ...,
--   {
--     "start_s2": "-8520148055505489297",
--     "dest_s2": "-8520148043684863917",
--     "speed": "1.0",
--     "cost": "305.40490363182045",
--     "distance": "305.40490363182045",
--     "travel_time": "305.40490363182045",
--     "path": "LINESTRING(-74.022772 40.684401, ..., -74.026041 40.684658)",
--     "detailed_geography": ["POINT(-74.022772 40.684401)", ..., "POINT(-74.026041 40.684658)"]
--   }]
-- },
-- ...
```

{% endtab %}

{% tab title="carto-un-eu" %}

```sql
CALL `carto-un-eu`.carto.ROUTING_MATRIX(
     -- start_point_array
    [ST_GEOGPOINT(-73.0, 40.0),ST_GEOGPOINT(-73.0, 41.0)],
    -- dest_point_array
    [ST_GEOGPOINT(-75.0, 41.0),ST_GEOGPOINT(-75.0, 40.0)],
    -- area of interest,
    ST_GEOGFROMTEXT("FULLGLOBE"),
    -- transportation mode
    'car',
    -- do_network_table
    'cdb_road_networ_81badfc2',
     --do_source
    '<my-dataobs-project>.<my-dataobs-dataset>',
     --output_table
    '<my-project>.<my-dataset>.<output_filename>',
    -- options
    """
    {
       "TYPE":"time",
       "MAX_COST":"100000",
       "WITH_PATH":"True"
    }
    """
);
-- {
--   "start_geo": "POINT(-73 40)",
--   "dest_geo": "POINT(-75 41)",
--   "start_geo_snapped": "POINT(-74.013134 40.688339)",
--   "dest_geo_snapped": "POINT(-74.026365 40.685995)",
--   "start_order": "0",
--   "dest_order": "0",
--   "start_s2": "-8520148151882761037",
--   "dest_s2": "-8520148044108704841",
--   "cost": "1082.6350274098224",
--   "distance": "1397.2114843386769",
--   "travel_time": "1082.6350274098224",
--   "path": "LINESTRING(-74.013134 40.688339, ..., -74.026365 40.685995)",
--   "detailed_linestring": [{
--     "start_s2": "-8520148151882761037",
--     "dest_s2": "-8520148151714079533",
--     "speed": "1.9",
--     "cost": "10.694765667341704",
--     "distance": "20.320054767949237",
--     "travel_time": "10.694765667341704",
--     "path": "LINESTRING(-74.013134 40.688339, -74.013375 40.688339)",
--     "detailed_geography": ["POINT(-74.013134 40.688339)", "POINT(-74.013375 40.688339)"]
--   },
--   ...,
--   {
--     "start_s2": "-8520148151867093047",
--     "dest_s2": "-8520148151854368777",
--     "speed": "1.2",
--     "cost": "11.348771839149197",
--     "distance": "13.618526206979036",
--     "travel_time": "11.348771839149197",
--     "path": "LINESTRING(-74.013587 40.688492, -74.013702 40.688578)",
--     "detailed_geography": ["POINT(-74.013587 40.688492)", "POINT(-74.013702 40.688578)"]
--   }]
-- }, {
--   "start_geo": "POINT(-73 40)",
--   "dest_geo": "POINT(-75 40)",
--   "start_geo_snapped": "POINT(-74.013134 40.688339)",
--   "dest_geo_snapped": "POINT(-74.026041 40.684658)",
--   "start_order": "0",
--   "dest_order": "1",
--   "start_s2": "-8520148151882761037",
--   "dest_s2": "-8520148043684863917",
--   "cost": "1124.4760381433846",
--   "distance": "1248.9194302355879",
--   "travel_time": "1124.4760381433846",
--   "path": "LINESTRING(-74.013134 40.688339, ..., -74.026041 40.684658)",
--   "detailed_linestring": [{
--     "start_s2": "-8520148151882761037",
--     "dest_s2": "-8520148151721556507",
--     "speed": "1.5",
--     "cost": "19.218684425894505",
--     "distance": "28.828026638841756",
--     "travel_time": "19.218684425894505",
--     "path": "LINESTRING(-74.013134 40.688339, -74.013221 40.688225, -74.013358 40.688151)",
--     "detailed_geography": ["POINT(-74.013134 40.688339)", "POINT(-74.013221 40.688225)", "POINT(-74.013358 40.688151)"]
--   },
--   ...,
--   {
--     "start_s2": "-8520148055505489297",
--     "dest_s2": "-8520148043684863917",
--     "speed": "1.0",
--     "cost": "305.40490363182045",
--     "distance": "305.40490363182045",
--     "travel_time": "305.40490363182045",
--     "path": "LINESTRING(-74.022772 40.684401, ..., -74.026041 40.684658)",
--     "detailed_geography": ["POINT(-74.022772 40.684401)", ..., "POINT(-74.026041 40.684658)"]
--   }]
-- },
-- ...
```

{% endtab %}

{% tab title="manual" %}

```sql
CALL carto.ROUTING_MATRIX(
     -- start_point_array
    [ST_GEOGPOINT(-73.0, 40.0),ST_GEOGPOINT(-73.0, 41.0)],
    -- dest_point_array
    [ST_GEOGPOINT(-75.0, 41.0),ST_GEOGPOINT(-75.0, 40.0)],
    -- area of interest,
    ST_GEOGFROMTEXT("FULLGLOBE"),
    -- transportation mode
    'car',
    -- do_network_table
    'cdb_road_networ_81badfc2',
     --do_source
    '<my-dataobs-project>.<my-dataobs-dataset>',
     --output_table
    '<my-project>.<my-dataset>.<output_filename>',
    -- options
    """
    {
       "TYPE":"time",
       "MAX_COST":"100000",
       "WITH_PATH":"True"
    }
    """
);
-- {
--   "start_geo": "POINT(-73 40)",
--   "dest_geo": "POINT(-75 41)",
--   "start_geo_snapped": "POINT(-74.013134 40.688339)",
--   "dest_geo_snapped": "POINT(-74.026365 40.685995)",
--   "start_order": "0",
--   "dest_order": "0",
--   "start_s2": "-8520148151882761037",
--   "dest_s2": "-8520148044108704841",
--   "cost": "1082.6350274098224",
--   "distance": "1397.2114843386769",
--   "travel_time": "1082.6350274098224",
--   "path": "LINESTRING(-74.013134 40.688339, ..., -74.026365 40.685995)",
--   "detailed_linestring": [{
--     "start_s2": "-8520148151882761037",
--     "dest_s2": "-8520148151714079533",
--     "speed": "1.9",
--     "cost": "10.694765667341704",
--     "distance": "20.320054767949237",
--     "travel_time": "10.694765667341704",
--     "path": "LINESTRING(-74.013134 40.688339, -74.013375 40.688339)",
--     "detailed_geography": ["POINT(-74.013134 40.688339)", "POINT(-74.013375 40.688339)"]
--   },
--   ...,
--   {
--     "start_s2": "-8520148151867093047",
--     "dest_s2": "-8520148151854368777",
--     "speed": "1.2",
--     "cost": "11.348771839149197",
--     "distance": "13.618526206979036",
--     "travel_time": "11.348771839149197",
--     "path": "LINESTRING(-74.013587 40.688492, -74.013702 40.688578)",
--     "detailed_geography": ["POINT(-74.013587 40.688492)", "POINT(-74.013702 40.688578)"]
--   }]
-- }, {
--   "start_geo": "POINT(-73 40)",
--   "dest_geo": "POINT(-75 40)",
--   "start_geo_snapped": "POINT(-74.013134 40.688339)",
--   "dest_geo_snapped": "POINT(-74.026041 40.684658)",
--   "start_order": "0",
--   "dest_order": "1",
--   "start_s2": "-8520148151882761037",
--   "dest_s2": "-8520148043684863917",
--   "cost": "1124.4760381433846",
--   "distance": "1248.9194302355879",
--   "travel_time": "1124.4760381433846",
--   "path": "LINESTRING(-74.013134 40.688339, ..., -74.026041 40.684658)",
--   "detailed_linestring": [{
--     "start_s2": "-8520148151882761037",
--     "dest_s2": "-8520148151721556507",
--     "speed": "1.5",
--     "cost": "19.218684425894505",
--     "distance": "28.828026638841756",
--     "travel_time": "19.218684425894505",
--     "path": "LINESTRING(-74.013134 40.688339, -74.013221 40.688225, -74.013358 40.688151)",
--     "detailed_geography": ["POINT(-74.013134 40.688339)", "POINT(-74.013221 40.688225)", "POINT(-74.013358 40.688151)"]
--   },
--   ...,
--   {
--     "start_s2": "-8520148055505489297",
--     "dest_s2": "-8520148043684863917",
--     "speed": "1.0",
--     "cost": "305.40490363182045",
--     "distance": "305.40490363182045",
--     "travel_time": "305.40490363182045",
--     "path": "LINESTRING(-74.022772 40.684401, ..., -74.026041 40.684658)",
--     "detailed_geography": ["POINT(-74.022772 40.684401)", ..., "POINT(-74.026041 40.684658)"]
--   }]
-- },
-- ...
```

{% endtab %}
{% endtabs %}

{% hint style="warning" %}
**Limitations**

Since this module runs natively on Bigquery, it relies exclusively on the resources allocated by the data warehouse for the query.

If a request fails due to a resource limit, you can try the following:

* reduce the size of the network (reduce the size of the area of interest)
* reduce or split into different queries the points in `start_point_array`
* set or reduce (if it already exists) the `MAX_COST` parameter
* set `WITH_PATH` parameter to `False`

In some cases road networks contain segments that are not connected to the main network. If any of the destination or origin points are closer to such segments than to other parts of the network it won't be possible to find routes to or from such points and some paths can be missing from the results. We're working on improving the quality of the road networks to avoid such problems. If you find this problem using the `car` transportation mode you can try using `car_major_road_only` or `car_motorway_only` instead, since the major roads network is less prone to this kind of problem.
{% endhint %}

### ROUTING\_ISOLINES <a href="#routing_isolines" id="routing_isolines"></a>

```sql
ROUTING_ISOLINES(start_point_array, cost_limit_array, area_of_interest, transportation_mode, do_network_table, do_source, output_table, options)
```

**Description**

This procedure generates a table containing *isolines* in terms of either travel times (isochrones) or distances (isodistances) for a given set of origin locations and range limits to be computed on the road network table.

**Input parameters**

* `start_point_array`: `ARRAY<GEOGRAPHY>` Source points array. The node of the network nearest to this point will be used as the source point to compute the shortest path.
* `cost_limit_array`: `ARRAY<FLOAT64>` Cost limit array. For each cost limit all the path within this range are returned. For an isochrone the cost is time in seconds, for an isodistance it's distance in meters.
* `area_of_interest`: `GEOGRAPHY` Area of interest over where the analysis takes place.
* `transportation_mode`: `STRING` Type of transportation mode to be used for the calculation of isolines. Available options: `car`, `car_motorway_only`, `car_major_road_only`, `bicycle` or `foot`.
* `do_network_table`: `STRING` Identifier (slug) of the Data Observatory Network table.
* `do_source`: `STRING` Name of the location where the Data Observatory subscriptions of the user are stored, in `<my-dataobs-project>.<my-dataobs-dataset>` format. If only the `<my-dataobs-dataset>` is included, it uses the project `carto-data` by default. It can be set to NULL or ''.
* `output_table`: `STRING` qualified name of the output table, e.g. `<my-project>.<my-dataset>.<my-output-table>`. The process will fail if the table already exists.
* `options`: `STRING` Containing a valid JSON with the different options. Valid options are described the table below. If `options` is set to `NULL` the all options are set to default.

| Option                           | Type      | Default | Description                                                                                                                                                                                |
| -------------------------------- | --------- | ------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| `TYPE`                           | `STRING`  | `time`  | Defines the kind of optimisation for pathfinding. Possible values: "time", "distance".                                                                                                     |
| `UNCOMPACT_GEOMETRY`             | `BOOL`    | `TRUE`  | If True the full non-compacted geometry between each link in the path. If not return only start and destination nodes of each link in the path.                                            |
| `FALLBACK_WRONG_WAY_SPEED_RATIO` | `FLOAT64` | `NULL`  | Defines the penalty ratio that should be applied to take a one-way road backward. Value should be between 1 and 0 (excluded). If NULL, one-way road cannot be used backward.               |
| `MAX_SNAPPING_DISTANCE`          | `FLOAT64` | `NULL`  | Defines maximum allowed snapping distance (in meters) between start and destination points and network nodes. If exceeded no route is returned. If NULL all snapping distance are allowed. |

**Return type**

The output table includes the following columns:

* `cost_limit`: `FLOAT64` Cost limit from the `start_point_array` input parameter taken in account for this row.
* `cost_limit_idx`: `INT64` Cost limit position from the `start_point_array` input parameter taken in account for this row.
* `start_order`: `INT64` Start point position from the source points array taken in account for this row.
* `start_geo`: `GEOGRAPHY` The point geometry of the starting node taken in account for this row.
* `start_geo_snapped`: `GEOGRAPHY` Start point snapped to the nearest start node of links of the network.
* `start_s2`: `INT64` Index of the node snapped from start point.
* `start_cost`: `FLOAT64` Cost from snapped start point to the start node of the link.
* `dest_s2`: `INT64` The unique identifier of the destination node of the link.
* `dest_cost`: `FLOAT64` Cost from snapped start point to the destination node of the link.
* `detailed_geography`: `ARRAY<GEOGRAPHY>` Array of points that makes up the link.
* `detailed_geography_chunked_agg`: `GEOGRAPHY` Link geography chunked to cost limit and aggregated in a single geography.

**Example**

{% tabs %}
{% tab title="carto-un" %}

```sql
CALL `carto-un`.carto.ROUTING_ISOLINES(
   -- start_point_array
    [ST_GEOGPOINT(-73.0, 40.0)],
    -- cost_limit_array
    [60., 120.],
    -- area of interest,
    ST_GEOGFROMTEXT("FULLGLOBE"),
    -- transportation mode
    'car',
    -- do_network_table
    'cdb_road_networ_81badfc2',
     --do_source
    '<my-dataobs-project>.<my-dataobs-dataset>',
    --output_table
    '<my-project>.<my-dataset>.<output_filename>',
   -- options
   """
   {
      "TYPE":"time",
      "MAX_COST":"100000",
      "WITH_PATH":"False"
   }
   """
);

-- {
--   "start_geo": "POINT(-122.3296557 47.582691)",
--   "dest_geo": "POINT(-122.3290612 47.5807722)",
--   "start_geo_snapped": "POINT(-122.3296557 47.582691)",
--   "dest_geo_snapped": "POINT(-122.3290612 47.5807722)",
--   "start_order": "0",
--   "dest_order": "35164",
--   "start_s2": "6093487519859207645",
--   "dest_s2": "6093440836742041863",
--   "cost": "27.095695089725034",
--   "distance": "309.31565973502575",
--   "travel_time": "27.095695089725034",
--   "path": "LINESTRING(-122.3296557 47.582691, -122.3298669 47.5827281, ..., -122.3290612 47.5807722)",
--   "detailed_linestring": [{
--     "start_s2": "6093487519859207645",
--     "dest_s2": "6093440836214032467",
--     "speed": "4.166666666666667",
--     "cost": "5.8301084779070846",
--     "distance": "24.292118657946187",
--     "travel_time": "5.8301084779070846",
--     "path": "LINESTRING(-122.3296557 47.582691, -122.3298669 47.5827281, -122.3299725 47.5827299)",
--     "detailed_geography": ["POINT(-122.3296557 47.582691)", "POINT(-122.3298669 47.5827281)", "POINT(-122.3299725 47.5827299)"]
--   },
--   ...,
--   {
--     "start_s2": "6093440836753434943",
--     "dest_s2": "6093440836742041863",
--     "speed": "18.055555555555554",
--     "cost": "0.658359285678671",
--     "distance": "11.887042658087113",
--     "travel_time": "0.658359285678671",
--     "path": "LINESTRING(-122.3290623 47.5808791, -122.3290612 47.5807722)",
--     "detailed_geography": ["POINT(-122.3290623 47.5808791)", "POINT(-122.3290612 47.5807722)"]
--   }]
-- }, ...
```

{% endtab %}

{% tab title="carto-un-eu" %}

```sql
CALL `carto-un-eu`.carto.ROUTING_ISOLINES(
   -- start_point_array
    [ST_GEOGPOINT(-73.0, 40.0)],
    -- cost_limit_array
    [60., 120.],
    -- area of interest,
    ST_GEOGFROMTEXT("FULLGLOBE"),
    -- transportation mode
    'car',
    -- do_network_table
    'cdb_road_networ_81badfc2',
     --do_source
    '<my-dataobs-project>.<my-dataobs-dataset>',
    --output_table
    '<my-project>.<my-dataset>.<output_filename>',
   -- options
   """
   {
      "TYPE":"time",
      "MAX_COST":"100000",
      "WITH_PATH":"False"
   }
   """
);

-- {
--   "start_geo": "POINT(-122.3296557 47.582691)",
--   "dest_geo": "POINT(-122.3290612 47.5807722)",
--   "start_geo_snapped": "POINT(-122.3296557 47.582691)",
--   "dest_geo_snapped": "POINT(-122.3290612 47.5807722)",
--   "start_order": "0",
--   "dest_order": "35164",
--   "start_s2": "6093487519859207645",
--   "dest_s2": "6093440836742041863",
--   "cost": "27.095695089725034",
--   "distance": "309.31565973502575",
--   "travel_time": "27.095695089725034",
--   "path": "LINESTRING(-122.3296557 47.582691, -122.3298669 47.5827281, ..., -122.3290612 47.5807722)",
--   "detailed_linestring": [{
--     "start_s2": "6093487519859207645",
--     "dest_s2": "6093440836214032467",
--     "speed": "4.166666666666667",
--     "cost": "5.8301084779070846",
--     "distance": "24.292118657946187",
--     "travel_time": "5.8301084779070846",
--     "path": "LINESTRING(-122.3296557 47.582691, -122.3298669 47.5827281, -122.3299725 47.5827299)",
--     "detailed_geography": ["POINT(-122.3296557 47.582691)", "POINT(-122.3298669 47.5827281)", "POINT(-122.3299725 47.5827299)"]
--   },
--   ...,
--   {
--     "start_s2": "6093440836753434943",
--     "dest_s2": "6093440836742041863",
--     "speed": "18.055555555555554",
--     "cost": "0.658359285678671",
--     "distance": "11.887042658087113",
--     "travel_time": "0.658359285678671",
--     "path": "LINESTRING(-122.3290623 47.5808791, -122.3290612 47.5807722)",
--     "detailed_geography": ["POINT(-122.3290623 47.5808791)", "POINT(-122.3290612 47.5807722)"]
--   }]
-- }, ...
```

{% endtab %}

{% tab title="manual" %}

```sql
CALL carto.ROUTING_ISOLINES(
   -- start_point_array
    [ST_GEOGPOINT(-73.0, 40.0)],
    -- cost_limit_array
    [60., 120.],
    -- area of interest,
    ST_GEOGFROMTEXT("FULLGLOBE"),
    -- transportation mode
    'car',
    -- do_network_table
    'cdb_road_networ_81badfc2',
     --do_source
    '<my-dataobs-project>.<my-dataobs-dataset>',
    --output_table
    '<my-project>.<my-dataset>.<output_filename>',
   -- options
   """
   {
      "TYPE":"time",
      "MAX_COST":"100000",
      "WITH_PATH":"False"
   }
   """
);

-- {
--   "start_geo": "POINT(-122.3296557 47.582691)",
--   "dest_geo": "POINT(-122.3290612 47.5807722)",
--   "start_geo_snapped": "POINT(-122.3296557 47.582691)",
--   "dest_geo_snapped": "POINT(-122.3290612 47.5807722)",
--   "start_order": "0",
--   "dest_order": "35164",
--   "start_s2": "6093487519859207645",
--   "dest_s2": "6093440836742041863",
--   "cost": "27.095695089725034",
--   "distance": "309.31565973502575",
--   "travel_time": "27.095695089725034",
--   "path": "LINESTRING(-122.3296557 47.582691, -122.3298669 47.5827281, ..., -122.3290612 47.5807722)",
--   "detailed_linestring": [{
--     "start_s2": "6093487519859207645",
--     "dest_s2": "6093440836214032467",
--     "speed": "4.166666666666667",
--     "cost": "5.8301084779070846",
--     "distance": "24.292118657946187",
--     "travel_time": "5.8301084779070846",
--     "path": "LINESTRING(-122.3296557 47.582691, -122.3298669 47.5827281, -122.3299725 47.5827299)",
--     "detailed_geography": ["POINT(-122.3296557 47.582691)", "POINT(-122.3298669 47.5827281)", "POINT(-122.3299725 47.5827299)"]
--   },
--   ...,
--   {
--     "start_s2": "6093440836753434943",
--     "dest_s2": "6093440836742041863",
--     "speed": "18.055555555555554",
--     "cost": "0.658359285678671",
--     "distance": "11.887042658087113",
--     "travel_time": "0.658359285678671",
--     "path": "LINESTRING(-122.3290623 47.5808791, -122.3290612 47.5807722)",
--     "detailed_geography": ["POINT(-122.3290623 47.5808791)", "POINT(-122.3290612 47.5807722)"]
--   }]
-- }, ...
```

{% endtab %}
{% endtabs %}

The output table returns links that form the isolines indexed by origin location and range limits. To get the full isoline geographies you need to aggregate the table:

{% tabs %}
{% tab title="carto-un" %}

```sql
SELECT ST_UNION_AGG(detailed_geography_chunked_agg) AS full_isoline_geography
FROM `output_table`
GROUP BY cost_limit, start_s2
```

{% endtab %}

{% tab title="carto-un-eu" %}

```sql
SELECT ST_UNION_AGG(detailed_geography_chunked_agg) AS full_isoline_geography
FROM `output_table`
GROUP BY cost_limit, start_s2
```

{% endtab %}

{% tab title="manual" %}

```sql
SELECT ST_UNION_AGG(detailed_geography_chunked_agg) AS full_isoline_geography
FROM `output_table`
GROUP BY cost_limit, start_s2
```

{% endtab %}
{% endtabs %}

{% hint style="warning" %}
**Limitations**

Since this module runs natively on Bigquery, it relies exclusively on the resources allocated by the data warehouse for the query.

If a request fails due to a resource limit, you can try the following:

* reduce the size of the network (reduce the size of the area of interest)
* reduce or split into different queries the points in `start_point_array`
* reduce the maximum the cost limit in `cost_limit_array`

In some cases road networks contain segments that are not connected to the main network. If any of the destination or origin points are closer to such segments than to other parts of the network it won't be possible to find routes to or from such points and some paths can be missing from the results. We're working on improving the quality of the road networks to avoid such problems. If you find this problem using the `car` transportation mode you can try using `car_major_road_only` or `car_motorway_only` instead, since the major roads network is less prone to this kind of problem.
{% endhint %}

<img src="https://3029946802-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FybPdpmLltPkzGFvz7m8A%2Fuploads%2Fgit-blob-1c82685e4e434438152a8e3d867df996413489fe%2Feu-flag-website.png?alt=media&#x26;token=4343f6e5-973a-4e9a-8e14-50366a086f72" alt="EU flag" data-size="line">This project has received funding from the [European Union’s Horizon 2020](https://ec.europa.eu/programmes/horizon2020/en) research and innovation programme under grant agreement No 960401.