# lds

This module contains functions and procedures that make use of location data services, such as geocoding, reverse geocoding, isolines and routing computation.

For manual installations of the CARTO Analytics Toolbox, after installing it for the first time, and before using any LDS function you need to call the `SETUP` procedure to configure the LDS functions. It also optionally sets default credentials.

## GEOCODE\_TABLE <a href="#geocode_table" id="geocode_table"></a>

```sql
GEOCODE_TABLE(input_table, address_column [, geom_column] [, country] [, options] [, api_base_url, api_access_token])
```

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

This function consumes LDS quota. Each call consumes as many units of quota as the number of rows of your input table. Before running, we recommend checking the size of the data to be geocoded and your available quota using the [`GET_LDS_QUOTA_INFO`](#get_lds_quota_info) function.
{% endhint %}

**Description**

Geocodes an input table by adding an user defined column `geom_column` with the geographic coordinates (latitude and longitude) corresponding to a given address column. This procedure also adds a `carto_geocode_metadata` column with additional information of the geocoding result in JSON format. It geocodes sequentially the table in chunks of 500.

**Input parameters**

* `input_table`: `VARCHAR` name of the table to be geocoded. Please make sure you have enough permissions to alter this table, as this procedure will add two columns to it to store the geocoding result.
* `address_column`: `VARCHAR` name of the column from the input table that contains the addresses to be geocoded.
* `geom_column` (optional): `VARCHAR` column name for the geometry column. Defaults to `'geom'`.
* `country` (optional): `VARCHAR` name of the country in [ISO 3166-1 alpha-2](https://en.wikipedia.org/wiki/ISO_3166-1_alpha-2). Defaults to `''`.
* `options` (optional): `VARCHAR|OBJECT` containing a valid JSON with the different options. In addition to the options targeted at the geocoding service described below, a boolean option `carto_force_geocode` (false by default) can be used to force geocoding rows that already have a non-null value in `geom_column`. Valid options are described in the table below. If no options are indicated then 'default' values would be applied.

  | Provider | Option     | Description                                                                  |
  | -------- | ---------- | ---------------------------------------------------------------------------- |
  | `All`    | `language` | A `VARCHAR` that specifies the language of the geocoding in RFC 4647 format. |
* `api_base_url` (optional): `VARCHAR` url of the API where the customer account is stored.
* `api_access_token` (optional): `VARCHAR` an [API Access Token](https://docs.carto.com/carto-user-manual/developers/api-access-tokens) that is allowed to use the LDS API.

If the input table already contains a geometry column with the name `geom_column`, only those rows with NULL values will be geocoded.

**Examples**

```sql
CALL CARTO.CARTO.GEOCODE_TABLE('<my-database>.<my-schema>.<my-table>', 'my_address_column');
-- The table `<my-database>.<my-schema>.<my-table>` will be updated
-- adding the columns: geom, carto_geocode_metadata.
```

```sql
CALL CARTO.CARTO.GEOCODE_TABLE('<my-database>.<my-schema>.<my-table>', 'my_address_column', 'my_geom_column');
-- The table `<my-database>.<my-schema>.<my-table>` will be updated
-- adding the columns: my_geom_column, carto_geocode_metadata.
```

```sql
CALL CARTO.CARTO.GEOCODE_TABLE('<my-database>.<my-schema>.<my-table>', 'my_address_column', 'my_geom_column', 'my_country');
-- The table `<my-database>.<my-schema>.<my-table>` will be updated
-- adding the columns: my_geom_column, carto_geocode_metadata.
```

```sql
CALL CARTO.CARTO.GEOCODE_TABLE('<my-database>.<my-schema>.<my-table>', 'my_address_column', 'my_geom_column', 'my_country', '{"language":"en-US"}');
-- The table `<my-database>.<my-schema>.<my-table>` will be updated
-- adding the columns: my_geom_column, carto_geocode_metadata.
```

```sql
CALL CARTO.CARTO.GEOCODE_TABLE('<my-database>.<my-schema>.<my-table>', 'my_address_column', 'my_geom_column', 'my_country', {'language':'en-US'});
-- The table `<my-database>.<my-schema>.<my-table>` will be updated
-- adding the columns: my_geom_column, carto_geocode_metadata.
```

```sql
CALL CARTO.CARTO.GEOCODE_TABLE('<my-database>.<my-schema>.<my-table>', 'my_address_column', 'my_geom_column', 'my_country', '{"language":"en-US"}', 'my_api_base_url', 'my_api_access_token');
-- The table `<my-database>.<my-schema>.<my-table>` will be updated
-- adding the columns: my_geom_column, carto_geocode_metadata.
```

```sql
CALL CARTO.CARTO.GEOCODE_TABLE('<my-database>.<my-schema>.<my-table>', 'my_address_column', 'my_geom_column', 'my_country', {'language':'en-US'}, 'my_api_base_url', 'my_api_access_token');
-- The table `<my-database>.<my-schema>.<my-table>` will be updated
-- adding the columns: my_geom_column, carto_geocode_metadata.
```

**Troubleshooting**

For the `GEOCODE_TABLE` procedure to work, the input table should be owned by the same role as the procedure. The procedure may not have access to update the table, for example, if the table was created with the `ACCOUNTADMIN role`, but the procedure is owned by the `SYSADMIN` role. In such scenario, the following error will be raised:

```
SQL access control error: Insufficient privileges to operate on table 'mytable'
```

You can check the `OWNERSHIP` of the table with the following query:

```sql
SHOW GRANTS ON TABLE <my-database>.<my-schema>.<my-table>;
```

To change the `OWNERSHIP` of to table to the `SYSADMIN role`, execute:

```sql
GRANT OWNERSHIP ON TABLE <my-database>.<my-schema>.<my-table> TO ROLE SYSADMIN COPY CURRENT GRANTS;
```

After performing this operation, you will be able to run `GEOCODE_TABLE` without running into privilege issues.

{% hint style="info" %}
**Additional examples**

* [Geocoding your address data](https://academy.carto.com/advanced-spatial-analytics/spatial-analytics-for-snowflake/step-by-step-tutorials/geocoding-your-address-data)
  {% endhint %}

## GEOCODE\_REVERSE\_TABLE <a href="#geocode_reverse_table" id="geocode_reverse_table"></a>

```sql
GEOCODE_REVERSE_TABLE(input_table [, geom_column] [, address_column] [, language] [, options] [, api_base_url, api_access_token])
```

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

This function consumes LDS quota. Each call consumes as many units of quota as the number of rows of your input table. Before running, we recommend checking the size of the data to be geocoded and your available quota using the [`GET_LDS_QUOTA_INFO`](#get_lds_quota_info) function.
{% endhint %}

**Description**

Reverse-geocodes an input table by adding an user defined column `address_column` with the address coordinates corresponding to a given point location column. It geocodes sequentially the table in chunks of 50 rows.

**Input parameters**

* `input_table`: `VARCHAR` name of the table to be reverse-geocoded. Please make sure you have enough permissions to alter this table, as this procedure will add a columns to it to store the geocoding result.
* `geom_column` (optional): `GEOGRAPHY` column name from the input table that contains the points to be reverse-geocoded. Defaults to `'geom'`.
* `address_column` (optional): `VARCHAR` name of the column where the computed addresses will be stored. It defaults to `'address'`, and it is created on the input table if it doesn't exist.
* `language` (optional): `VARCHAR` language in which results should be returned. Defaults to `''`. The effect and interpretation of this parameter depends on the LDS provider assigned to your account.
* `options` (optional): `VARCHAR|OBJECT` containing a valid JSON with the different options. No options are allowed currently, so this value will not be taken into account.
* `api_base_url` (optional): `VARCHAR` url of the API where the customer account is stored.
* `api_access_token` (optional): `VARCHAR` an [API Access Token](https://docs.carto.com/carto-user-manual/developers/api-access-tokens) that is allowed to use the LDS API.

If the input table already contains an address column with the name specified by the `address_column` parameter, only those rows with NULL values in it will be reverse-geocoded.

**Examples**

```sql
CALL CARTO.CARTO.GEOCODE_REVERSE_TABLE('<my-database>.<my-schema>.<my-table>');
-- The table `<my-database>.<my-schema>.<my-table>`, which should have a `geom` GEOGRAPHY column, will be updated
-- adding the column: `address`.
```

```sql
CALL CARTO.CARTO.GEOCODE_REVERSE_TABLE('<my-database>.<my-schema>.<my-table>', 'my_geom_column');
-- The table `<my-database>.<my-schema>.<my-table>`, with a GEOGRAPHY column named `my_geom_column`, will be updated
-- adding the column: `address`.
```

```sql
CALL CARTO.CARTO.GEOCODE_REVERSE_TABLE('<my-database>.<my-schema>.<my-table>', 'my_geom_column', 'my_address_column');
-- The table `<my-database>.<my-schema>.<my-table>`, with a GEOGRAPHY column named `my_geom_column`, will be updated
-- adding the column: `my_address_column` if it doesn't previously exist.
```

```sql
CALL CARTO.CARTO.GEOCODE_REVERSE_TABLE('<my-database>.<my-schema>.<my-table>', 'my_geom_column', 'my_address_column', 'en-US');
-- The table `<my-database>.<my-schema>.<my-table>`, with a GEOGRAPHY column named `my_geom_column`, will be updated
-- adding the column: `my_address_column` if it doesn't previously exist.
-- The addresses will be in the (US) english language.
```

```sql
CALL CARTO.CARTO.GEOCODE_REVERSE_TABLE('<my-database>.<my-schema>.<my-table>', 'my_geom_column', 'my_address_column', 'en-US', {}, 'my_api_base_url', 'my_api_access_token');
-- The table `<my-database>.<my-schema>.<my-table>`, with a GEOGRAPHY column named `my_geom_column`, will be updated
-- adding the column: `my_address_column` if it doesn't previously exist.
-- The addresses will be in the (US) english language.
```

**Troubleshooting**

For the `GEOCODE_REVERSE_TABLE` procedure to work, the input table should be owned by the same role as the procedure. The procedure may not have access to update the table, for example, if the table was created with the `ACCOUNTADMIN role`, but the procedure is owned by the `SYSADMIN` role. In such scenario, the following error will be raised:

```
SQL access control error: Insufficient privileges to operate on table 'mytable'
```

You can check the `OWNERSHIP` of the table with the following query:

```sql
SHOW GRANTS ON TABLE <my-database>.<my-schema>.<my-table>;
```

To change the `OWNERSHIP` of to table to the `SYSADMIN role`, execute:

```sql
GRANT OWNERSHIP ON TABLE <my-database>.<my-schema>.<my-table> TO ROLE SYSADMIN COPY CURRENT GRANTS;
```

After performing this operation, you will be able to run `GEOCODE_REVERSE_TABLE` without running into privilege issues.

## CREATE\_ISOLINES <a href="#create_isolines" id="create_isolines"></a>

```sql
CREATE_ISOLINES(input, output_table, geom_column, mode, range, range_type [, options] [, api_base_url, api_access_token])
```

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

This function consumes LDS quota. Each call consumes as many units of quota as the number of rows your input table or query has. Before running, we recommend checking the size of the data to be geocoded and your available quota using the [`GET_LDS_QUOTA_INFO`](#get_lds_quota_info) function.
{% endhint %}

**Description**

Calculates the isolines (polygons) from given origins (points) in a table or query. It creates a new table with the columns of the input table or query except the `geom_column` plus the isolines in the column `geom` (if the input already contains a `geom` column, it will be overwritten). It calculates isolines sequentially in chunks of N rows, N being the optimal batch size for this datawarehouse and the specific LDS provider that you are using.

Note that The term *isoline* is used here in a general way to refer to the areas that can be reached from a given origin point within the given travel time or distance (depending on the `range_type` parameter).

**Input parameters**

* `input`: `VARCHAR` name of the input table or query.
* `output_table`: `STRING` qualified name of the output table, e.g. `<my-database>.<my-schema>.<my-output-table>`. The process will fail if the table already exists.
* `geom_column`: `VARCHAR` column name for the origin geography column.
* `mode`: `VARCHAR` type of transport. The supported modes depend on the provider:
  * `HERE`: 'walk', 'car', 'truck', 'taxi', 'bus', 'private\_bus'.
  * `TomTom`: 'walk', 'car', 'bike', 'motorbike', 'truck', 'taxi', 'bus', 'van'.
  * `TravelTime`: 'walk', 'car', 'bike', 'public\_transport', 'coach', 'bus', 'train', 'ferry'.
  * `Mapbox`: 'walk', 'car', 'bike'.
* `range`: `FLOAT` range of the isoline in seconds (for `range_type` 'time') or meters (for `range_type` 'distance').
* `range_type`: `VARCHAR` type of range. Supported: 'time' (for isochrones), 'distance' (for isodistances).
* `options` (optional): `VARCHAR|OBJECT` containing a valid JSON with the different options. Valid options are described in the table below. If no options are indicated then 'default' values would be applied.

  | Provider     | Option            | Description                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
  | ------------ | ----------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
  | `HERE`       | `arrival_time`    | A `VARCHAR` that specifies the time of arrival. If the value is set, a reverse isoline is calculated. If set to `"any"`, then time-dependent effects will not be taken into account. It cannot be used in combination with `departure_time`. Supported: `"any"`, `"now"` and date-time as `"<YYYY-MM-DD>T<hh:mm:ss>"`.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
  | `HERE`       | `departure_time`  | Default: `"any"`. A `VARCHAR` that specifies the time of departure. If set to `"any"`, then time-dependent effects will not be taken into account. It cannot be used in combination with `arrival_time`. Supported: `"any"`, `"now"` and date-time as `"<YYYY-MM-DD>T<hh:mm:ss>"`.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
  | `HERE`       | `optimize_for`    | Default: `"balanced"`. A `VARCHAR` that specifies how isoline calculation is optimized. Supported: `"quality"` (calculation of isoline focuses on quality, that is, the graph used for isoline calculation has higher granularity generating an isoline that is more precise), `"performance"` (calculation of isoline is performance-centric, quality of isoline is reduced to provide better performance) and `"balanced"` (calculation of isoline takes a balanced approach averaging between quality and performance).                                                                                                                                                                                                                                                                                                                       |
  | `HERE`       | `routing_mode`    | Default: `"fast"`. A `VARCHAR` that specifies which optimization is applied during isoline calculation. Supported: `"fast"` (route calculation from start to destination optimized by travel time. In many cases, the route returned by the fast mode may not be the route with the fastest possible travel time. For example, the routing service may favor a route that remains on a highway, even if a faster travel time can be achieved by taking a detour or shortcut through an inconvenient side road) and `"short"` (route calculation from start to destination disregarding any speed information. In this mode, the distance of the route is minimized, while keeping the route sensible. This includes, for example, penalizing turns. Because of that, the resulting route will not necessarily be the one with minimal distance). |
  | `TomTom`     | `departure_time`  | Default: `"now"`. A `VARCHAR` that specifies the time of departure. Supported: `"now"` and date-time as `"<YYYY-MM-DD>T<hh:mm:ss>"`.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
  | `TomTom`     | `traffic`         | Default: `false`. A `BOOLEAN` that specifies if all available traffic information will be taken into consideration. Supported: `true` and `false`.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
  | `TravelTime` | `level_of_detail` | A JSON string. In the most typical case, you will want to use a string in the form `{ scale_type: 'simple_numeric', level: -N }`, with `N` being the detail level (-8 by default). Higher Ns (more negative levels) will simplify the polygons more but will reduce performance. There are other ways of setting the level of detail. Check the \[<https://docs.traveltime.com/api/reference/isochrones#arrival\\_searches-level\\_of\\_detail]\\(TravelTime> docs) for more info.                                                                                                                                                                                                                                                                                                                                                               |
  | `TravelTime` | `departure_time`  | Default: `"now"`. A `STRING` that specifies the time of departure. Supported: `"now"` and date-time as `"<YYYY-MM-DD>T<hh:mm:ss>Z"`.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
* `api_base_url` (optional): `VARCHAR` url of the API where the customer account is stored.
* `api_access_token` (optional): `VARCHAR` an [API Access Token](https://docs.carto.com/carto-user-manual/developers/api-access-tokens) that is allowed to use the LDS API.

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

Before running, we recommend checking your provider using the [`GET_LDS_QUOTA_INFO`](#get:get_lds_quota_info) function. Notice that some of the parameters are provider dependant. Please contact your CARTO representative if you have questions regarding the service provider configured in your organization.
{% endhint %}

**Examples**

```sql
CALL CARTO.CARTO.CREATE_ISOLINES(
    '<my-database>.<my-schema>.<my-table>',
    '<my-database>.<my-schema>.<my-output-table>',
    'my_geom_column',
    'car', 300, 'time'
);
-- The table `<my-database>.<my-schema>.<my-output-table>` will be created
-- with the columns of the input table except `my_geom_column`.
-- Isolines will be added in the "geom" column.
```

```sql
CALL CARTO.CARTO.CREATE_ISOLINES(
    '<my-database>.<my-schema>.<my-table>',
    '<my-database>.<my-schema>.<my-output-table>',
    'my_geom_column',
    'car', 1000, 'distance'
);
-- The table `<my-database>.<my-schema>.<my-output-table>` will be created
-- with the columns of the input table except `my_geom_column`.
-- Isolines will be added in the "geom" column.
```

```sql
CALL CARTO.CARTO.CREATE_ISOLINES(
    '<my-database>.<my-schema>.<my-table>',
    '<my-database>.<my-schema>.<my-output-table>',
    'my_geom_column',
    'car', 300, 'time',
    '{"polygons_filter": {"limit": 1}}',
    'my_api_base_url',
    'my_api_access_token'
);
-- The table `<my-database>.<my-schema>.<my-output-table>` will be created
-- with the columns of the input table except `my_geom_column`.
-- Isolines will be added in the "geom" column.
```

{% hint style="info" %}
**Additional examples**

* [Generating trade areas based on drive/walk-time isolines](https://academy.carto.com/advanced-spatial-analytics/spatial-analytics-for-snowflake/step-by-step-tutorials/generating-trade-areas-based-on-drive-walk-time-isolines)
  {% endhint %}

## CREATE\_ROUTES <a href="#create_routes" id="create_routes"></a>

```sql
CREATE_ROUTES(input, output_table, geom_column, mode[, options] [, api_base_url, api_access_token])
```

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

This function consumes LDS quota. Each call consumes as many units of quota as the number of rows your input query has. Before running, we recommend checking the size of the data used to create the routes and your available quota using the [`GET_LDS_QUOTA_INFO`](#get_lds_quota_info) function.
{% endhint %}

**Description**

Calculates the routes (line strings) between given origins and destinations (points) in a query. It creates a new table with the columns of the input query with the resulting route in the user defined column `geom_column` (if the input already contains a column named `geom_column`, it will be overwritten) and a `carto_routing_metadata` column with the response of the service provider except for the route geometry. It calculates routes sequentially in chunks of 100 rows.

Note that routes are calculated using the external LDS provider assigned to your CARTO account. Currently TomTom, HERE, and TravelTime are supported.

**Input parameters**

* `input`: `VARCHAR` name of the input query, which must have columns named `ORIGIN` and `DESTINATION` of type `GEOGRAPHY` and containing points. If a column named `WAYPOINTS` is also present, it should contain a VARCHAR with the coordinates of the desired intermediate points with the format `"lon1,lat1:lon2,lat2..."`.
* `output_table`: `STRING` qualified name of the output table, e.g. `<my-database>.<my-schema>.<my-output-table>`. The process will fail if the table already exists.
* `geom_column`: `VARCHAR` column name for the generated routes geography column.
* `mode`: `VARCHAR` type of transport. The supported modes depend on the provider:
  * `TomTom`: 'car', 'pedestrian', 'bicycle', 'motorcycle', 'truck', 'taxi', 'bus', 'van'.
  * `HERE`: 'car', 'truck', 'pedestrian', 'bicycle', 'scooter', 'taxi', 'bus', 'privateBus'.
  * `TravelTime`: 'cycling', 'driving', 'walking', 'public\_transport', 'coach', 'bus', 'train', 'ferry', 'driving+train', 'driving+ferry', 'cycling+ferry', 'cycling+public\_transport'.
* `options` (optional): `VARCHAR|OBJECT` containing a valid JSON with optional parameters. This is intended for advanced use: additional parameters can be passed directly to the Routing provider by placing them in this JSON string. To find out what your provider is, check the [`GET_LDS_QUOTA_INFO`](#get_lds_quota_info) function. The following are some of the most common parameters for each provider:

  * `TomTom`:
    * `avoid`: Specifies something that the route calculation should try to avoid when determining the route. Possible values (several of them can be used at the same time):
      * `tollRoads`
      * `motorways`
      * `ferries`
      * `unpavedRoads`
      * `carpools`
      * `alreadyUsedRoads`
      * `borderCrossings`
      * `tunnels`
      * `carTrains`
      * `lowEmissionZones`
    * `routeType`: Specifies the type of optimization used when calculating routes. Possible values: `fastest`, `shortest`, `short` `eco`, `thrilling`
    * `traffic`: Set to true `true` to consider all available traffic information during routing. Set to `false` otherwise
    * `departAt`: The date and time of departure at the departure point. It should be specified in RFC 3339 format with an optional time zone offset.
    * `arriveAt`: The date and time of arrival at the destination point. It should be specified in RFC 3339 format with an optional time zone offset.
    * `vehicleMaxspeed`: Maximum speed of the vehicle in kilometers/hour.
  * `HERE`
    * `avoid`: Elements or areas to avoid. Information about avoidance can be found [here](https://developer.here.com/documentation/routing-api/dev_guide/topics/use-cases/avoid.html)
    * `departureTime`: The date and time of departure at the departure point. It should be specified in RFC 3339 format with an optional time zone offset.
    * `arrivalTime`: The date and time of arrival at the destination point. It should be specified in RFC 3339 format with an optional time zone offset.
    * `language`: The language to use. Supported language codes can be found [here](https://developer.here.com/documentation/routing-api/dev_guide/topics/languages.html)
  * `TravelTime`
    * `snap_penalty`: Controls whether walking time and distance from the departure location to the nearest road and from the nearest road to the arrival location are included in the route. Possible values: `enabled` (walking segments are added to the total travel time and distance), `disabled` (journey effectively starts and ends at the nearest points on the road network). Defaults to `disabled` for driving modes and `enabled` for other modes.

  For more advanced usage, check the documentation of your provider's routing API.

  * [`TomTom`](https://developer.tomtom.com/routing-api/documentation/routing/common-routing-parameters)
  * [`HERE`](https://developer.here.com/documentation/routing-api/dev_guide/index.html)
  * [`TravelTime`](https://docs.traveltime.com/api/reference/routes)
* `api_base_url` (optional): `VARCHAR` url of the API where the customer account is stored.
* `api_access_token` (optional): `VARCHAR` an [API Access Token](https://docs.carto.com/carto-user-manual/developers/api-access-tokens) that is allowed to use the LDS API.

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

Before running, we recommend checking your provider using the [`GET_LDS_QUOTA_INFO`](#get_lds_quota_info) function. Notice that some of the parameters are provider dependant. Please contact your CARTO representative if you have questions regarding the service provider configured in your organization.
{% endhint %}

**Examples**

```sql
CALL CARTO.CARTO.CREATE_ROUTES(
    '<my-database>.<my-schema>.<my-table>',
    '<my-database>.<my-schema>.<my-output-table>',
    'GEOM',
    'car'
);
-- The table `<my-database>.<my-schema>.<my-output-table>` will be created
-- with the columns of the input table and 'GEOM' and 'CARTO_ROUTING_METADATA'.
-- Routes will be added in the "GEOM" column.
```

```sql
CALL CARTO.CARTO.CREATE_ROUTES(
    '<my-database>.<my-schema>.<my-table>',
    '<my-database>.<my-schema>.<my-output-table>',
    'GEOM',
    'car',
    '{"arriveAt":"2023-06-11T19:00:00+02:00"}'
);
-- The table `<my-database>.<my-schema>.<my-output-table>` will be created
-- with the columns of the input table except and 'GEOM' and 'CARTO_ROUTING_METADATA'.
-- Routes will be added in the "GEOM" column.
```

```sql
CALL CARTO.CARTO.CREATE_ROUTES(
    '<my-database>.<my-schema>.<my-table>',
    '<my-database>.<my-schema>.<my-output-table>',
    'GEOM',
    'car',
    {"arriveAt":"2023-06-11T19:00:00+02:00"}
);
-- The table `<my-database>.<my-schema>.<my-output-table>` will be created
-- with the columns of the input table and 'GEOM' and 'CARTO_ROUTING_METADATA'.
-- Routes will be added in the "GEOM" column.
```

```sql
CALL CARTO.CARTO.CREATE_ROUTES(
    '<my-database>.<my-schema>.<my-table>',
    '<my-database>.<my-schema>.<my-output-table>',
    'GEOM',
    'car',
    '{"arriveAt":"2023-06-11T19:00:00+02:00"}',
    'my_api_base_url',
    'my_api_access_token'
);
-- The table `<my-database>.<my-schema>.<my-output-table>` will be created
-- with the columns of the input table except and 'GEOM' and 'CARTO_ROUTING_METADATA'.
-- Routes will be added in the "GEOM" column.
```

```sql
CALL CARTO.CARTO.CREATE_ROUTES(
    '<my-database>.<my-schema>.<my-table>',
    '<my-database>.<my-schema>.<my-output-table>',
    'GEOM',
    'car',
    {"arriveAt":"2023-06-11T19:00:00+02:00"},
    'my_api_base_url',
    'my_api_access_token'
);
-- The table `<my-database>.<my-schema>.<my-output-table>` will be created
-- with the columns of the input table and 'GEOM' and 'CARTO_ROUTING_METADATA'.
-- Routes will be added in the "GEOM" column.
```

## GEOCODE <a href="#geocode" id="geocode"></a>

```sql
GEOCODE(address [, country] [, options] [, api_base_url, api_access_token])
```

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

This function consumes LDS quota. Each call consumes one unit of quota. Before running, check the size of the data to be geocoded and make sure you store the result in a table to avoid misuse of the quota. To check the information about available and consumed quota use the function [`GET_LDS_QUOTA_INFO`](#get_lds_quota_info).

**We recommend using this function only with an input of up to 10 records. In order to geocode larger sets of addresses, we strongly recommend using the** [**`GEOCODE_TABLE`**](#geocode_table) **procedure. Likewise, in order to materialize the results in a table.**
{% endhint %}

**Description**

Geocodes an address into a point with its geographic coordinates (latitude and longitude).

**Input parameters**

* `address`: `VARCHAR` input address to geocode.
* `country` (optional): `VARCHAR` name of the country in [ISO 3166-1 alpha-2](https://en.wikipedia.org/wiki/ISO_3166-1_alpha-2). Defaults to `''`.
* `options` (optional): `VARCHAR|OBJECT` containing a valid JSON with the different options. Valid options are described in the table below. If no options are indicated then 'default' values would be applied.

  | Provider | Option     | Description                                                                  |
  | -------- | ---------- | ---------------------------------------------------------------------------- |
  | `All`    | `language` | A `VARCHAR` that specifies the language of the geocoding in RFC 4647 format. |
* `api_base_url` (optional): `VARCHAR` url of the API where the customer account is stored.
* `api_access_token` (optional): `VARCHAR` an [API Access Token](https://docs.carto.com/carto-user-manual/developers/api-access-tokens) that is allowed to use the LDS API.

**Return type**

`GEOGRAPHY`

**Constraints**

This function performs requests to the CARTO Location Data Services API. Snowflake makes parallel requests depending on the number of records you are processing, potentially hitting the limit of the number of requests per seconds allowed for your account. The payload size of these requests depends on the number of records and could cause a timeout in the external function, with the error message `External function timeout`. Unexpected server errors will force Snowflake to retry the requests. The limit is around 500 records but could vary with the provider. To avoid this error, please try geocoding smaller volumes of data or using the procedure [`GEOCODE_TABLE`](#geocode_table) instead. This procedure manages concurrency and payload size to avoid exceeding this limit.

**Examples**

```sql
SELECT CARTO.CARTO.GEOCODE('Madrid');
-- { "coordinates": [ -3.69196, 40.41956 ], "type": "Point" }
```

```sql
SELECT CARTO.CARTO.GEOCODE('Madrid', 'es');
-- { "coordinates": [ -3.69196, 40.41956 ], "type": "Point" }
```

```sql
SELECT CARTO.CARTO.GEOCODE('Madrid', 'es', '{"language":"es-ES"}');
-- { "coordinates": [ -3.69196, 40.41956 ], "type": "Point" }
```

```sql
SELECT CARTO.CARTO.GEOCODE('Madrid', 'es', {'language':'en-US'});
-- { "coordinates": [ -3.69196, 40.41956 ], "type": "Point" }
```

```sql
SELECT CARTO.CARTO.GEOCODE('Madrid', 'es', '{"language":"es-ES"}', 'my_api_base_url', 'my_api_access_token');
-- { "coordinates": [ -3.69196, 40.41956 ], "type": "Point" }
```

```sql
SELECT CARTO.CARTO.GEOCODE('Madrid', 'es', {'language':'en-US'}, 'my_api_base_url', 'my_api_access_token');
-- { "coordinates": [ -3.69196, 40.41956 ], "type": "Point" }
```

```sql
CREATE TABLE my_geocoded_table AS
SELECT ADDRESS, CARTO.CARTO.GEOCODE(ADDRESS) AS GEOM FROM my_table
-- The table `<my-database>.<my-schema>.<my-geocoded-table>` will be created
```

## GEOCODE\_REVERSE <a href="#geocode_reverse" id="geocode_reverse"></a>

```sql
GEOCODE_REVERSE(geom [, language] [, options] [, api_base_url, api_access_token])
```

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

This function consumes LDS quota. Each call consumes one unit of quota. Before running, check the size of the data to be reverse geocoded and make sure you store the result in a table to avoid misuse of the quota. To check the information about available and consumed quota use the function [`GET_LDS_QUOTA_INFO`](#get_lds_quota_info).

**We recommend using this function only with an input of up to 10 records. In order to reverse-geocode larger sets of locations, we strongly recommend using the** [**`GEOCODE_REVERSE_TABLE`**](#geocode_reverse_table) **procedure. Likewise, in order to materialize the results in a table.**
{% endhint %}

**Description**

Performs a reverse geocoding of the point received as input.

**Input parameters**

* `geom`: `GEOGRAPHY` input point to obtain the address.
* `language` (optional): `VARCHAR` language in which results should be returned.
* `options` (optional): `VARCHAR|OBJECT` containing a valid JSON with the different options. No options are allowed currently, so this value will not be taken into account.
* `api_base_url` (optional): `VARCHAR` url of the API where the customer account is stored.
* `api_access_token` (optional): `VARCHAR` an [API Access Token](https://docs.carto.com/carto-user-manual/developers/api-access-tokens) that is allowed to use the LDS API.

**Return type**

`VARCHAR`

**Constraints**

This function performs requests to the CARTO Location Data Services API. Snowflake makes parallel requests depending on the number of records you are processing, potentially hitting the limit of the number of requests per seconds allowed for your account. The payload size of these requests depends on the number of records and could cause a timeout in the external function, with the error message `External function timeout`. Unexpected server errors will force Snowflake to retry the requests. The limit is around 500 records but could vary with the provider. To avoid this error, please try processing smaller volumes of data.

**Examples**

```sql
SELECT CARTO.CARTO.GEOCODE_REVERSE(ST_POINT(-74.0060, 40.7128));
-- 254 Broadway, New York, NY 10007, USA
```

```sql
SELECT CARTO.CARTO.GEOCODE_REVERSE(ST_POINT(-74.0060, 40.7128), 'en-US');
-- 254 Broadway, New York, NY 10007, USA
```

```sql
SELECT CARTO.CARTO.GEOCODE_REVERSE(ST_POINT(-74.0060, 40.7128), 'en-US', {}, 'my_api_base_url', 'my_api_access_token');
-- 254 Broadway, New York, NY 10007, USA
```

## ISOLINE <a href="#isoline" id="isoline"></a>

```sql
ISOLINE(origin, mode, range, range_type [, options] [, api_base_url, api_access_token])
```

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

This function consumes LDS quota. Each call consumes one unit quota. Before running, check the size of the data and make sure you store the result in a table to avoid misuse of the quota. To check the information about available and consumed quota use the function [`GET_LDS_QUOTA_INFO`](#get_lds_quota_info).

**We recommend using this function only with an input of up to 10 records. In order to calculate isolines for larger sets of locations, we strongly recommend using the** [**`CREATE_ISOLINES`**](#create_isolines) **procedure. Likewise, in order to materialize the results in a table.**
{% endhint %}

**Description**

Creates an isoline from the provided origin.

Note that The term *isoline* is used here in a general way to refer to the areas that can be reached from a given origin point within the given travel time or distance (depending on the `range_type` parameter).

**Input parameters**

* `origin`: `GEOGRAPHY` of the origin of the isoline.
* `mode`: `VARCHAR` type of transport. The supported modes depend on the provider:
  * `HERE`: 'walk', 'car', 'truck', 'taxi', 'bus', 'private\_bus'.
  * `TomTom`: 'walk', 'car', 'bike', 'motorbike', 'truck', 'taxi', 'bus', 'van'.
  * `TravelTime`: 'walk', 'car', 'bike', 'public\_transport', 'coach', 'bus', 'train', 'ferry'.
  * `Mapbox`: 'walk', 'car', 'bike'.
* `range`: `INT` range of the isoline in seconds (for `range_type` 'time') or meters (for `range_type` 'distance').
* `range_type`: `VARCHAR` of the range type. Supported: ‘time’ (for isochrones), ‘distance’ (for isodistances).
* `options` (optional): `VARCHAR|OBJECT` containing a valid JSON with the different options. Valid options are described in the table below. If no options are indicated then 'default' values would be applied.

  | Provider     | Option            | Description                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
  | ------------ | ----------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
  | `HERE`       | `arrival_time`    | A `VARCHAR` that specifies the time of arrival. If the value is set, a reverse isoline is calculated. If set to `"any"`, then time-dependent effects will not be taken into account. It cannot be used in combination with `departure_time`. Supported: `"any"`, `"now"` and date-time as `"<YYYY-MM-DD>T<hh:mm:ss>"`.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
  | `HERE`       | `departure_time`  | Default: `"now"`. A `VARCHAR` that specifies the time of departure. If set to `"any"`, then time-dependent effects will not be taken into account. It cannot be used in combination with `arrival_time`. Supported: `"any"`, `"now"` and date-time as `"<YYYY-MM-DD>T<hh:mm:ss>"`.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
  | `HERE`       | `optimize_for`    | Default: `"balanced"`. A `VARCHAR` that specifies how isoline calculation is optimized. Supported: `"quality"` (calculation of isoline focuses on quality, that is, the graph used for isoline calculation has higher granularity generating an isoline that is more precise), `"performance"` (calculation of isoline is performance-centric, quality of isoline is reduced to provide better performance) and `"balanced"` (calculation of isoline takes a balanced approach averaging between quality and performance).                                                                                                                                                                                                                                                                                                                       |
  | `HERE`       | `routing_mode`    | Default: `"fast"`. A `VARCHAR` that specifies which optimization is applied during isoline calculation. Supported: `"fast"` (route calculation from start to destination optimized by travel time. In many cases, the route returned by the fast mode may not be the route with the fastest possible travel time. For example, the routing service may favor a route that remains on a highway, even if a faster travel time can be achieved by taking a detour or shortcut through an inconvenient side road) and `"short"` (route calculation from start to destination disregarding any speed information. In this mode, the distance of the route is minimized, while keeping the route sensible. This includes, for example, penalizing turns. Because of that, the resulting route will not necessarily be the one with minimal distance). |
  | `TomTom`     | `departure_time`  | Default: `"now"`. A `VARCHAR` that specifies the time of departure. If set to `"any"`, then time-dependent effects will not be taken into account. Supported: `"any"`, `"now"` and date-time as `"<YYYY-MM-DD>T<hh:mm:ss>"`.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
  | `TomTom`     | `traffic`         | Default: `true`. A `BOOLEAN` that specifies if all available traffic information will be taken into consideration. Supported: `true` and `false`.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
  | `TravelTime` | `level_of_detail` | A JSON string. In the most typical case, you will want to use a string in the form `{ scale_type: 'simple_numeric', level: -N }`, with `N` being the detail level (-8 by default). Higher Ns (more negative levels) will simplify the polygons more but will reduce performance. There are other ways of setting the level of detail. Check the \[<https://docs.traveltime.com/api/reference/isochrones#arrival\\_searches-level\\_of\\_detail]\\(TravelTime> docs) for more info.                                                                                                                                                                                                                                                                                                                                                               |
  | `TravelTime` | `departure_time`  | Default: `"now"`. A `STRING` that specifies the time of departure. Supported: `"now"` and date-time as `"<YYYY-MM-DD>T<hh:mm:ss>Z"`.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
* `api_base_url` (optional): `VARCHAR` url of the API where the customer account is stored.
* `api_access_token` (optional): `VARCHAR` an [API Access Token](https://docs.carto.com/carto-user-manual/developers/api-access-tokens) that is allowed to use the LDS API.

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

Before running, we recommend checking your provider using the [`GET_LDS_QUOTA_INFO`](#get_lds_quota_info) function. Notice that some of the parameters are provider dependant. Please contact your CARTO representative if you have questions regarding the service provider configured in your organization.
{% endhint %}

**Return type**

`GEOGRAPHY`

**Constraints**

This function performs requests to the CARTO Location Data Services API. Snowflake makes parallel requests depending on the number of records you are processing, potentially hitting the limit of the number of requests per seconds allowed for your account. The payload size of these requests depends on the number of records and could cause a timeout in the external function, with the error message `External function timeout`. Unexpected server errors will force Snowflake to retry the requests. The limit is around 500 records but could vary with the provider. To avoid this error, please try processing smaller volumes of data.

**Examples**

```sql
SELECT CARTO.CARTO.ISOLINE(ST_MAKEPOINT(-3, 40), 'car', 300, 'time');
-- { "coordinates": [ [ [ -3.0127745, 40.00472 ], [ -3.0131316, 40.004993 ], [ -3.0131316, 40.006092 ], [ -3.0134888, 40.006367 ], [ ...
```

```sql
SELECT CARTO.CARTO.ISOLINE(ST_MAKEPOINT(-3, 40), 'car', 1000, 'distance');
-- { "coordinates": [ [ [ -3.002268, 39.99645 ], [ -3.002268, 39.995907 ], [ -3.001917, 39.995636 ], [ -3.0012143, 39.995636 ], [ ...
```

```sql
SELECT CARTO.CARTO.ISOLINE(ST_MAKEPOINT(-3, 40), 'car', 300, 'time', {'departure_time':'any'}, 'my_api_base_url', 'my_api_access_token');
-- { "coordinates": [ [ [ -3.0127745, 40.00472 ], [ -3.0131316, 40.004993 ], [ -3.0131316, 40.006092 ], [ -3.0134888, 40.006367 ], [ ...
```

## GET\_LDS\_QUOTA\_INFO <a href="#get_lds_quota_info" id="get_lds_quota_info"></a>

```sql
GET_LDS_QUOTA_INFO([api_base_url, api_access_token])
```

**Description**

Returns statistics about the LDS quota. LDS quota is an annual quota that defines how much geocoding and isolines you can compute. Each geocoded row or computed isolines counts as one LDS quota unit. The single element in the result of GET\_LDS\_QUOTA\_INFO will show your LDS quota for the current annual period (annual\_quota), how much you’ve spent (used\_quota), and which LDS providers are in use.

**Input parameters**

* `api_base_url` (optional): `VARCHAR` url of the API where the customer account is stored.
* `api_access_token` (optional): `VARCHAR` an [API Access Token](https://docs.carto.com/carto-user-manual/developers/api-access-tokens) that is allowed to use the LDS API.

**Return type**

`VARCHAR`

**Examples**

```sql
SELECT CARTO.CARTO.GET_LDS_QUOTA_INFO();
-- [
--   {
--     "used_quota": 10,
--     "annual_quota": 100000,
--     "providers": {
--         "geocoding": "tomtom",
--         "isolines": "here",
--         "routing":"tomtom"
--     }
--   }
-- ]
```

```sql
SELECT CARTO.CARTO.GET_LDS_QUOTA_INFO('my_api_base_url', 'my_api_access_token');
-- [
--   {
--     "used_quota": 10,
--     "annual_quota": 100000,
--     "providers": {
--         "geocoding": "tomtom",
--         "isolines": "here",
--         "routing":"tomtom"
--     }
--   }
-- ]
```

## CREATE\_H3\_ISOLINES <a href="#create_h3_isolines" id="create_h3_isolines"></a>

```sql
CREATE_H3_ISOLINES(input, output_table, geom_column, mode, range, range_type, resolution [, options] [, api_base_url, api_access_token])
```

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

This function consumes LDS quota. Each call consumes as many units of quota as the number of rows your input table or query has. Before running, we recommend checking the size of the data and your available quota using the [`GET_LDS_QUOTA_INFO`](#get_lds_quota_info) function.
{% endhint %}

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

This procedure requires the TravelTime provider. If your organization uses a different isolines provider, the procedure will raise an error.
{% endhint %}

**Description**

Calculates H3 isolines (isochrones represented as H3 cells) from given origins (points) in a table or query. Unlike the standard `CREATE_ISOLINES` procedure which returns polygon geometries, this procedure returns an array of H3 cells with travel time information for each cell.

It creates a new table with the columns of the input table or query except the `geom_column`, with each H3 cell unnested into a separate row containing `h3`, `h3_min`, `h3_max`, and `h3_mean` as scalar values. It calculates H3 isolines sequentially in chunks of N rows, N being the optimal batch size for this datawarehouse.

The output table will contain a column named `carto_isoline_metadata` with error information for each isoline result. Rows with errors will have NULL values in the H3 columns.

**Input parameters**

* `input`: `VARCHAR` name of the input table or query.
* `output_table`: `STRING` qualified name of the output table, e.g. `<my-database>.<my-schema>.<my-output-table>`. The process will fail if the table already exists.
* `geom_column`: `VARCHAR` column name for the origin geography column.
* `mode`: `VARCHAR` type of transport. Supported modes for TravelTime: 'walk', 'car', 'bike', 'public\_transport', 'coach', 'bus', 'train', 'ferry'.
* `range`: `FLOAT` range of the isoline in seconds (for `range_type` 'time') or meters (for `range_type` 'distance'). Valid range for time: 60-36000 (1 minute to 10 hours).
* `range_type`: `VARCHAR` type of range. Currently only 'time' is supported for H3 isolines.
* `resolution`: `FLOAT` H3 resolution level for the output cells. Valid range: 6-12.
* `options` (optional): `VARCHAR|OBJECT` containing a valid JSON with the different options. Valid options are described in the table below. If no options are indicated then 'default' values would be applied.

  | Provider     | Option            | Description                                                                                                                                                                                                                                                                                                                                                                                                       |
  | ------------ | ----------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
  | `TravelTime` | `level_of_detail` | A JSON string. In the most typical case, you will want to use a string in the form `{ scale_type: 'simple_numeric', level: -N }`, with `N` being the detail level (-8 by default). Higher Ns (more negative levels) will simplify the results more but will reduce performance. Check the [TravelTime docs](https://docs.traveltime.com/api/reference/isochrones#arrival_searches-level_of_detail) for more info. |
  | `TravelTime` | `departure_time`  | Default: `"now"`. A `STRING` that specifies the time of departure. Supported: `"now"` and date-time as `"<YYYY-MM-DD>T<hh:mm:ss>Z"`.                                                                                                                                                                                                                                                                              |
* `api_base_url` (optional): `VARCHAR` url of the API where the customer account is stored.
* `api_access_token` (optional): `VARCHAR` an [API Access Token](https://docs.carto.com/carto-user-manual/developers/api-access-tokens) that is allowed to use the LDS API.

**Output Table Schema**

The output table will contain all columns from the input table except `geom_column`, with each H3 cell as a separate row:

| Column                   | Type      | Description                                      |
| ------------------------ | --------- | ------------------------------------------------ |
| `h3`                     | `VARCHAR` | H3 cell index.                                   |
| `h3_min`                 | `INT`     | Minimum travel time in seconds for this H3 cell. |
| `h3_max`                 | `INT`     | Maximum travel time in seconds for this H3 cell. |
| `h3_mean`                | `INT`     | Average travel time in seconds for this H3 cell. |
| `carto_isoline_metadata` | `VARCHAR` | Error information, or NULL if successful.        |

**Examples**

```sql
CALL CARTO.CARTO.CREATE_H3_ISOLINES(
    '<my-database>.<my-schema>.<my-table>',
    '<my-database>.<my-schema>.<my-output-table>',
    'my_geom_column',
    'car', 900, 'time', 6
);
-- The table `<my-database>.<my-schema>.<my-output-table>` will be created
-- with the columns of the input table except `my_geom_column`.
-- H3 cell data will be added in the "h3", "h3_min", "h3_max", and "h3_mean" columns.
```

```sql
CALL CARTO.CARTO.CREATE_H3_ISOLINES(
    '<my-database>.<my-schema>.<my-table>',
    '<my-database>.<my-schema>.<my-output-table>',
    'my_geom_column',
    'walk', 600, 'time', 7,
    '{"departure_time": "2024-01-15T09:00:00Z"}'
);
-- The table `<my-database>.<my-schema>.<my-output-table>` will be created
-- with H3 cells at resolution 7 for 10-minute walking isochrones.
```

```sql
CALL CARTO.CARTO.CREATE_H3_ISOLINES(
    '<my-database>.<my-schema>.<my-table>',
    '<my-database>.<my-schema>.<my-output-table>',
    'my_geom_column',
    'car', 900, 'time', 6,
    NULL,
    'my_api_base_url',
    'my_api_access_token'
);
-- The table `<my-database>.<my-schema>.<my-output-table>` will be created
-- with H3 cells using explicit API credentials.
```

## CREATE\_ROUTING\_MATRIX <a href="#create_routing_matrix" id="create_routing_matrix"></a>

```sql
CREATE_ROUTING_MATRIX(origins_table, origins_geom_column, destinations_table, destinations_geom_column, output_table, mode [, options] [, api_base_url, api_access_token])
```

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

This function consumes LDS quota. Each call consumes as many units of quota as the number of rows of the origins table multiplied by the rows of destinations table. Before running, we recommend checking the size of the data used to create the routes and your available quota using the [`GET_LDS_QUOTA_INFO`](#get_lds_quota_info) function.
{% endhint %}

**Description**

Calculates the routes (line strings) between given origins and destinations (points) in two table. It creates a new table with the cross join of the origin and destination geom columns adding the distance or time between the different routes. A `carto_routing_matrix_metadata` column will also be added containing pottential errors thrown by the service provider for particular origin-destination combinations.

Note that routes are calculated using the external LDS provider assigned to your CARTO account. Currently TomTom and TravelTime are supported.

**Input parameters**

* `origins_table`: `VARCHAR` name of the origins input table.
* `origins_geom_column`: `VARCHAR` column name for the origin geometry column.
* `destinations_table`: `VARCHAR` name of the destinations input table.
* `destinations_geom_column`: `VARCHAR` column name for the destination geometry column.
* `output_table`: `STRING` qualified name of the output table, e.g. `<my-database>.<my-schema>.<my-output-table>`. The process will fail if the table already exists.
* `geom_column`: `VARCHAR` column name for the generated geography column that will contain the resulting routes.
* `mode`: `VARCHAR` type of transport. The supported modes depend on the provider:
  * `TomTom`: 'car', 'truck', 'pedestrian'.
  * `TravelTime`: 'cycling', 'driving', 'driving+train', 'driving+public\_transport', 'public\_transport', 'walking', 'coach', 'bus', 'train', 'ferry', 'driving+ferry', 'cycling+ferry', 'cycling+public\_transport'.
* `options` (optional): `VARCHAR|OBJECT` containing a valid JSON with optional parameters. This is intended for advanced use: additional parameters can be passed directly to the Routing provider by placing them in this JSON string. To find out what your provider is, check the [`GET_LDS_QUOTA_INFO`](#get_lds_quota_info) function. The following are some of the most common parameters for each provider:

  | Provider     | Option            | Description                                                                                                                                                                                                                                                  |
  | ------------ | ----------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
  | `TomTom`     | `avoid`           | Default: `[]`. An `ARRAY` that specifies something that the route calculation should try to avoid when determining the route. Supported: `["tollRoads"]`, `["unpavedRoads"]`.                                                                                |
  | `TomTom`     | `departAt`        | Default: `"any"`. A `STRING` that specifies the time of departure. Supported: `"now"`, `"any"` and date-time as `"<YYYY-MM-DD>T<hh:mm:ss>"`.                                                                                                                 |
  | `TomTom`     | `routeType`       | Default: `"fastest"`. A `STRING` that specifies the type of optimization used when calculating routes. Supported: `"fastest"`, `"shortest"`.                                                                                                                 |
  | `TomTom`     | `traffic`         | Default: `historical`. A `STRING` that decides how traffic is considered for computing routes. Supported: `historical` and `live`. `live` may not be used in conjunction with `departAt=any`.                                                                |
  | `TomTom`     | `vehicleMaxSpeed` | Default: `0`. A `NUMBER` that specifies the maximum speed of the vehicle in kilometers/hour. Supported: a value in the range \[0, 250]. A value of `0` means that an appropriate value for the vehicle will be determined and applied during route planning. |
  | `TravelTime` | `departure_time`  | Default: `"now"`. A `STRING` that specifies the time of departure. Supported: `"now"` and date-time as `"<YYYY-MM-DD>T<hh:mm:ss>Z"`.                                                                                                                         |
  | `TravelTime` | `travel_time`     | Default: `14400` (4 hours). A `NUMBER` that specifies the maximum travel time in seconds. Supported: a value in the range \[0, 14400].                                                                                                                       |

  For more advanced usage, check the documentation of your provider's matrix routing API.

  * [`TomTom`](https://developer.tomtom.com/matrix-routing-v2-api/documentation/asynchronous-matrix-submission)
  * [`TravelTime`](https://docs.traveltime.com/api/reference/travel-time-distance-matrix#departure_searches)
* `api_base_url` (optional): `VARCHAR` url of the API where the customer account is stored.
* `api_access_token` (optional): `VARCHAR` an [API Access Token](https://docs.carto.com/carto-user-manual/developers/api-access-tokens) that is allowed to use the LDS API.

  The following are options provided by CARTO in order to adjust the procedure performance:

  | Option                          | Description                                                                                                                                                                                                                                                                                                  |
  | ------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
  | `carto_origins_batch_size`      | Default: `TomTom: 100`, `TravelTime: 1`. Max: `TomTom: 10000`, `TravelTime: 10`. A `NUMBER` that specifies the number of origins rows to process in each batch. Increasing this value in the case of Traveltime can cause some origins-destinations combinations to be skipped when the origin is not found. |
  | `carto_destinations_batch_size` | Default: `TomTom: 100`, `TravelTime: 2000`. Max: `TomTom: 10000`, `TravelTime: 2000`. A `NUMBER` that specifies the number of destinations rows to process in each batch.                                                                                                                                    |

  In the case of `TomTom` the next requirements must be met:

  * If `departAt=now` or `departAT=dateTime` then:
    * The size of the matrix should not be larger than 2500.
    * The number of origins should not be larger than 1000.
    * The number of destinations should not be larger than 1000.
    * The bounding box should not be larger than 400km X 400km. Example: 2X1000, 5X500, 100X25, etc.
  * If `departAt=any` then:
    * The size of the matrix can be as large as 100M.
    * The number of origins should not be larger than 10000.
    * The number of destinations should not be larger than 10000.

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

We recommend the product of `carto_origins_batch_size x carto_destinations_batch_size` to be lower than 10000 to ensure that the batches sizes is small enough to be processed.
{% endhint %}

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

Before running, we recommend checking your provider using the [`GET_LDS_QUOTA_INFO`](#get_lds_quota_info) function. Notice that some of the parameters are provider dependant. Please contact your CARTO representative if you have questions regarding the service provider configured in your organization.
{% endhint %}

**Return type**

The results are stored in the table named `<output-table>`, which contains the following columns:

* `origin_geom`: `VARCHAR` the origin geometry.
* `destination_geom`: `VARCHAR` the destination geometry.
* `route_distance`: `INT` the distance of the route in meters.
* `route_duration`: `INT` the duration of the route in seconds.
* `carto_routing_matrix_metadata`: `VARCHAR` possible errors thrown by the service provider for particular origin-destination combinations.

When generating the output table origin-destination combinations, duplicated or null geometries will not be processed.

**Examples**

```sql
CALL CARTO.CARTO.CREATE_ROUTING_MATRIX(
    '<my-database>.<my-schema>.<my-origins-table>',
    'my_origins_geom_column',
    '<my-database>.<my-schema>.<my-destinations-table>',
    'my_destinations_geom_column',
    '<my-database>.<my-schema>.<my-output-table>',
    'car'
);
-- The table `<my-database>.<my-schema>.<my-output-table>` will be created
-- with the columns from both input tables, `route_distance`, `route_duration` and `carto_routing_matrix_metadata`.
```

Examples with TomTom specific parameters:

```sql
CALL CARTO.CARTO.CREATE_ROUTING_MATRIX(
    '<my-database>.<my-schema>.<my-origins-table>',
    'my_origins_geom_column',
    '<my-database>.<my-schema>.<my-destinations-table>',
    'my_destinations_geom_column',
    '<my-database>.<my-schema>.<my-output-table>',
    'car',
    '{"departAt":"now"}'
);
-- The table `<my-database>.<my-schema>.<my-output-table>` will be created
-- with the columns from both input tables, `route_distance`, `route_duration` and `carto_routing_matrix_metadata`.
```

```sql
CALL CARTO.CARTO.CREATE_ROUTING_MATRIX(
    '<my-database>.<my-schema>.<my-origins-table>',
    'my_origins_geom_column',
    '<my-database>.<my-schema>.<my-destinations-table>',
    'my_destinations_geom_column',
    '<my-database>.<my-schema>.<my-output-table>',
    'car',
    {"departAt":"now"}
);
-- The table `<my-database>.<my-schema>.<my-output-table>` will be created
-- with the columns from both input tables, `route_distance`, `route_duration` and `carto_routing_matrix_metadata`.
```

```sql
CALL CARTO.CARTO.CREATE_ROUTING_MATRIX(
    '<my-database>.<my-schema>.<my-origins-table>',
    'my_origins_geom_column',
    '<my-database>.<my-schema>.<my-destinations-table>',
    'my_destinations_geom_column',
    '<my-database>.<my-schema>.<my-output-table>',
    'car',
    '{"departAt":"now"}',
    'my_api_base_url',
    'my_api_access_token'
);
-- The table `<my-database>.<my-schema>.<my-output-table>` will be created
-- with the columns from both input tables, `route_distance`, `route_duration` and `carto_routing_matrix_metadata`.
```

```sql
CALL CARTO.CARTO.CREATE_ROUTING_MATRIX(
    '<my-database>.<my-schema>.<my-origins-table>',
    'my_origins_geom_column',
    '<my-database>.<my-schema>.<my-destinations-table>',
    'my_destinations_geom_column',
    '<my-database>.<my-schema>.<my-output-table>',
    'car',
    {"departAt":"now"},
    'my_api_base_url',
    'my_api_access_token'
);
-- The table `<my-database>.<my-schema>.<my-output-table>` will be created
-- with the columns from both input tables, `route_distance`, `route_duration` and `carto_routing_matrix_metadata`.
```

Example with batch sizes:

```sql
CALL CARTO.CARTO.CREATE_ROUTING_MATRIX(
    '<my-database>.<my-schema>.<my-origins-table>',
    'my_origins_geom_column',
    '<my-database>.<my-schema>.<my-destinations-table>',
    'my_destinations_geom_column',
    '<my-database>.<my-schema>.<my-output-table>',
    'car',
    '{"carto_origins_batch_size": 10, "carto_destinations_batch_size": 1000}'
);
-- The table `<my-database>.<my-schema>.<my-output-table>` will be created
-- with the columns from both input tables, `route_distance`, `route_duration` and `carto_routing_matrix_metadata`.
```

## H3\_ISOLINE <a href="#h3_isoline" id="h3_isoline"></a>

```sql
H3_ISOLINE(origin, mode, range, range_type, resolution [, options] [, api_base_url, api_access_token])
```

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

This function consumes LDS quota. Each call consumes one unit quota. Before running, check the size of the data and make sure you store the result in a table to avoid misuse of the quota. To check the information about available and consumed quota use the function [`GET_LDS_QUOTA_INFO`](#get_lds_quota_info).

**We recommend using this function only with an input of up to 10 records. In order to calculate H3 isolines for larger sets of locations, we strongly recommend using the** [**`CREATE_H3_ISOLINES`**](#create_h3_isolines) **procedure. Likewise, in order to materialize the results in a table.**
{% endhint %}

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

This function requires the TravelTime provider. If your organization uses a different isolines provider, the function will raise an error.
{% endhint %}

**Description**

Calculates H3 isolines (isochrones represented as H3 cells) from a given point. Unlike the standard `ISOLINE` function which returns a polygon geometry, this function returns an array of H3 cells with travel time information for each cell.

**Input parameters**

* `origin`: `GEOGRAPHY` origin point of the isoline.
* `mode`: `VARCHAR` type of transport. Supported modes for TravelTime: 'walk', 'car', 'bike', 'public\_transport', 'coach', 'bus', 'train', 'ferry'.
* `range`: `INT` range of the isoline in seconds (for `range_type` 'time') or meters (for `range_type` 'distance'). Valid range for time: 60-36000 (1 minute to 10 hours).
* `range_type`: `VARCHAR` type of range. Currently only 'time' is supported for H3 isolines.
* `resolution`: `INT` H3 resolution level for the output cells. Valid range: 6-12.
* `options` (optional): `VARCHAR|OBJECT` containing a valid JSON with the different options. Valid options are described in the table below. If no options are indicated then 'default' values would be applied.

  | Provider     | Option            | Description                                                                                                                                                                                                                                                                                                                                                                                                       |
  | ------------ | ----------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
  | `TravelTime` | `level_of_detail` | A JSON string. In the most typical case, you will want to use a string in the form `{ scale_type: 'simple_numeric', level: -N }`, with `N` being the detail level (-8 by default). Higher Ns (more negative levels) will simplify the results more but will reduce performance. Check the [TravelTime docs](https://docs.traveltime.com/api/reference/isochrones#arrival_searches-level_of_detail) for more info. |
  | `TravelTime` | `departure_time`  | Default: `"now"`. A `STRING` that specifies the time of departure. Supported: `"now"` and date-time as `"<YYYY-MM-DD>T<hh:mm:ss>Z"`.                                                                                                                                                                                                                                                                              |
* `api_base_url` (optional): `VARCHAR` url of the API where the customer account is stored.
* `api_access_token` (optional): `VARCHAR` an [API Access Token](https://docs.carto.com/carto-user-manual/developers/api-access-tokens) that is allowed to use the LDS API.

**Return type**

`ARRAY`

Each element in the array is an object representing an H3 cell within the isochrone:

| Field     | Type      | Description                                           |
| --------- | --------- | ----------------------------------------------------- |
| `h3`      | `VARCHAR` | H3 cell index.                                        |
| `h3_min`  | `INT`     | Minimum travel time in seconds to reach this H3 cell. |
| `h3_max`  | `INT`     | Maximum travel time in seconds to reach this H3 cell. |
| `h3_mean` | `INT`     | Average travel time in seconds to reach this H3 cell. |

**Constraints**

This function performs requests to the CARTO Location Data Services API. Snowflake makes parallel requests depending on the number of records you are processing, potentially hitting the limit of the number of requests per seconds allowed for your account. To avoid this error, please try processing smaller volumes of data or use the `CREATE_H3_ISOLINES` procedure instead.

**Examples**

```sql
SELECT CARTO.CARTO.H3_ISOLINE(ST_POINT(-3.7038, 40.4168), 'car', 900, 'time', 6, NULL, NULL, NULL);
-- Returns an array of H3 cells at resolution 6 for a 15-minute drive from Madrid
```

```sql
SELECT CARTO.CARTO.H3_ISOLINE(ST_POINT(-3.7038, 40.4168), 'walk', 600, 'time', 7, '{"departure_time": "2024-01-15T09:00:00Z"}', NULL, NULL);
-- Returns an array of H3 cells at resolution 7 for a 10-minute walk
-- with specific departure time
```

```sql
SELECT
    cell.VALUE:h3::VARCHAR AS h3,
    cell.VALUE:h3_min::INT AS h3_min,
    cell.VALUE:h3_max::INT AS h3_max,
    cell.VALUE:h3_mean::INT AS h3_mean
FROM TABLE(FLATTEN(INPUT => CARTO.CARTO.H3_ISOLINE(ST_POINT(-3.7038, 40.4168), 'car', 900, 'time', 6, NULL, NULL, NULL))) AS cell;
-- Unnests the array to get individual H3 cells as rows
```