lds

ADVANCED BETA

This module contains functions and procedures that make use of location data services, such as geocoding, reverse geocoding, isolines and routing computation.
GEOCODE_TABLE
GEOCODE_TABLE(input_table, address_column [, geom_column] [, country] [, options] [, api_base_url, lds_token])
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 LDS_QUOTA_INFO function.
Description
Geocodes an input table by adding a column geom 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_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. 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.
lds_token (optional): VARCHAR an API Access Token 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
CALL CARTO.CARTO.GEOCODE_TABLE('my-schema.my-table', 'my_address_column');
-- The table `my-schema.my-table` will be updated
-- adding the columns: geom, carto_geocode_metadata.
CALL CARTO.CARTO.GEOCODE_TABLE('my-schema.my-table', 'my_address_column', 'my_geom_column');
-- The table `my-schema.my-table` will be updated
-- adding the columns: my_geom_column, carto_geocode_metadata.
CALL CARTO.CARTO.GEOCODE_TABLE('my-schema.my-table', 'my_address_column', 'my_geom_column', 'my_country');
-- The table `my-schema.my-table` will be updated
-- adding the columns: my_geom_column, carto_geocode_metadata.
CALL CARTO.CARTO.GEOCODE_TABLE('my-schema.my-table', 'my_address_column', 'my_geom_column', 'my_country', '{"language":"en-US"}');
-- The table `my-schema.my-table` will be updated
-- adding the columns: my_geom_column, carto_geocode_metadata.
CALL CARTO.CARTO.GEOCODE_TABLE('my-schema.my-table', 'my_address_column', 'my_geom_column', 'my_country', {'language':'en-US'});
-- The table `my-schema.my-table` will be updated
-- adding the columns: my_geom_column, carto_geocode_metadata.
CALL CARTO.CARTO.GEOCODE_TABLE('my-schema.my-table', 'my_address_column', 'my_geom_column', 'my_country', '{"language":"en-US"}', 'my_api_base_url', 'my_lds_token');
-- The table `my-schema.my-table` will be updated
-- adding the columns: my_geom_column, carto_geocode_metadata.
CALL CARTO.CARTO.GEOCODE_TABLE('my-schema.my-table', 'my_address_column', 'my_geom_column', 'my_country', {'language':'en-US'}, 'my_api_base_url', 'my_lds_token');
-- The table `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 'my-table'
You can check the OWNERSHIP of the table with the following query:
SHOW GRANTS ON TABLE "my-schema"."my-table";
To change the OWNERSHIP of to table to the SYSADMIN role, execute:
GRANT OWNERSHIP ON TABLE "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.
Additional examples
​Geocoding your address data​
GEOCODE_REVERSE_TABLE
GEOCODE_REVERSE_TABLE(input_table [, geom_column] [, address_column] [, language] [, options] [, api_base_url, lds_token])
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 LDS_QUOTA_INFO function.
Description
Reverse-geocodes an input table by adding a column address with the address coordinates corresponding to a given point location column. It geocodes sequentially the table in chunks of 50 rows.
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.
lds_token (optional): VARCHAR an API Access Token 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
CALL CARTO.CARTO.GEOCODE_REVERSE_TABLE('my-schema.my-table');
-- The table `my-schema.my-table`, which should have a `geom` GEOGRAPHY column, will be updated
-- adding the column: `address`.
CALL CARTO.CARTO.GEOCODE_REVERSE_TABLE('my-schema.my-table', 'my_geom_column');
-- The table `my-schema.my-table`, with a GEOGRAPHY column named `my_geom_column`, will be updated
-- adding the column: `address`.
CALL CARTO.CARTO.GEOCODE_REVERSE_TABLE('my-schema.my-table', 'my_geom_column', 'my_address_column');
-- The table `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.
CALL CARTO.CARTO.GEOCODE_REVERSE_TABLE('my-schema.my-table', 'my_geom_column', 'my_address_column', 'en-US');
-- The table `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.
CALL CARTO.CARTO.GEOCODE_REVERSE_TABLE('my-schema.my-table', 'my_geom_column', 'my_address_column', 'en-US', {}, 'my_api_base_url', 'my_lds_token');
-- The table `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 'my-table'
You can check the OWNERSHIP of the table with the following query:
SHOW GRANTS ON TABLE "my-schema"."my-table";
To change the OWNERSHIP of to table to the SYSADMIN role, execute:
GRANT OWNERSHIP ON TABLE "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
CREATE_ISOLINES(input, output_table, geom_column, mode, range, range_type [, options] [, api_base_url, lds_token])
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 LDS_QUOTA_INFO function.
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 100 rows.
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: VARCHAR name of the input table or query.
output_table: VARCHAR name of the output table. It will raise an error 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' and 'private_bus'.
Mapbox: 'walk', 'car' and 'bike'.
TomTom: 'walk', 'car', 'bike', 'motorbike', 'truck', 'taxi', 'bus' and 'van'.
range: INT 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 "any" is introduced, 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 "any" is introduced, 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.
api_base_url (optional): VARCHAR url of the API where the customer account is stored.
lds_token (optional): VARCHAR an API Access Token that is allowed to use the LDS API.
warning
Before running, we recommend checking your provider using the 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.
Examples
CALL CARTO.CARTO.CREATE_ISOLINES(
    'my-schema.my-table',
    'my-schema.my-output-table',
    'my_geom_column',
    'car', 60, 'time'
);
-- The table `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.
CALL CARTO.CARTO.CREATE_ISOLINES(
    'my-schema.my-table',
    'my-schema.my-output-table',
    'my_geom_column',
    'car', 60, 'time',
    '{"departure_time":"any"}'
);
-- The table `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.
CALL CARTO.CARTO.CREATE_ISOLINES(
    'my-schema.my-table',
    'my-schema.my-output-table',
    'my_geom_column',
    'car', 60, 'time',
    {'departure_time':'any'}
);
-- The table `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.
CALL CARTO.CARTO.CREATE_ISOLINES(
    'my-schema.my-table',
    'my-schema.my-output-table',
    'my_geom_column',
    'car', 60, 'time',
    '{"departure_time":"any"}',
    'my_api_base_url',
    'my_lds_token'
);
-- The table `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.
CALL CARTO.CARTO.CREATE_ISOLINES(
    'my-schema.my-table',
    'my-schema.my-output-table',
    'my_geom_column',
    'car', 60, 'time',
    {'departure_time':'any'},
    'my_api_base_url',
    'my_lds_token'
);
-- The table `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.
Additional examples
​Generating trade areas based on drive/walk-time isolines​
CREATE_ROUTES
CREATE_ROUTES(input, output_table, geom_column, mode[, options] [, api_base_url, lds_token])
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 LDS_QUOTA_INFO function.
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 column GEOM (if the input already contains a 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 and HERE are supported.
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: VARCHAR name of the output table. It will raise an error 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'.
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 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
Avoids
This
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​
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​
For more advanced usage, check the documentation of your provider's routing API.
​TomTom​
​HERE​
api_base_url (optional): VARCHAR url of the API where the customer account is stored.
lds_token (optional): VARCHAR an API Access Token that is allowed to use the LDS API.
warning
Before running, we recommend checking your provider using the 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.
Examples
CALL CARTO.CARTO.CREATE_ROUTES(
    'my-schema.my-table',
    'my-schema.my-output-table',
    'GEOM',
    'car'
);
-- The table `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.
CALL CARTO.CARTO.CREATE_ROUTES(
    'my-schema.my-table',
    'my-schema.my-output-table',
    'GEOM',
    'car',
    '{"arriveAt":"2023-06-11T19:00:00+02:00"}'
);
-- The table `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.
CALL CARTO.CARTO.CREATE_ROUTES(
    'my-schema.my-table',
    'my-schema.my-output-table',
    'GEOM',
    'car',
    {"arriveAt":"2023-06-11T19:00:00+02:00"}
);
-- The table `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.
CALL CARTO.CARTO.CREATE_ROUTES(
    'my-schema.my-table',
    'my-schema.my-output-table',
    'GEOM',
    'car',
    '{"arriveAt":"2023-06-11T19:00:00+02:00"}',
    'my_api_base_url',
    'my_lds_token'
);
-- The table `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.
CALL CARTO.CARTO.CREATE_ROUTES(
    'my-schema.my-table',
    'my-schema.my-output-table',
    'GEOM',
    'car',
    {"arriveAt":"2023-06-11T19:00:00+02:00"},
    'my_api_base_url',
    'my_lds_token'
);
-- The table `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
GEOCODE(address [, country] [, options] [, api_base_url, lds_token])
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 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 procedure. Likewise, in order to materialize the results in a table.
Description
Geocodes an address into a point with its geographic coordinates (latitude and longitude).
address: VARCHAR input address to geocode.
country (optional): VARCHAR name of the country in 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.
lds_token (optional): VARCHAR an API Access Token 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 instead. This procedure manages concurrency and payload size to avoid exceeding this limit.
Examples
SELECT CARTO.CARTO.GEOCODE('Madrid');
-- { "coordinates": [ -3.69196, 40.41956 ], "type": "Point" }
SELECT CARTO.CARTO.GEOCODE('Madrid', 'es');
-- { "coordinates": [ -3.69196, 40.41956 ], "type": "Point" }
SELECT CARTO.CARTO.GEOCODE('Madrid', 'es', '{"language":"es-ES"}');
-- { "coordinates": [ -3.69196, 40.41956 ], "type": "Point" }
SELECT CARTO.CARTO.GEOCODE('Madrid', 'es', {'language':'en-US'});
-- { "coordinates": [ -3.69196, 40.41956 ], "type": "Point" }
SELECT CARTO.CARTO.GEOCODE('Madrid', 'es', '{"language":"es-ES"}', 'my_api_base_url', 'my_lds_token');
-- { "coordinates": [ -3.69196, 40.41956 ], "type": "Point" }
SELECT CARTO.CARTO.GEOCODE('Madrid', 'es', {'language':'en-US'}, 'my_api_base_url', 'my_lds_token');
-- { "coordinates": [ -3.69196, 40.41956 ], "type": "Point" }
CREATE TABLE my_geocoded_table AS
SELECT ADDRESS, CARTO.CARTO.GEOCODE(ADDRESS) AS GEOM FROM my_table
-- Table my_geocoded_table successfully created.
GEOCODE_REVERSE
GEOCODE_REVERSE(geom [, language] [, options] [, api_base_url, lds_token])
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 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 procedure. Likewise, in order to materialize the results in a table.
Description
Performs a reverse geocoding of the point received as input.
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.
lds_token (optional): VARCHAR an API Access Token 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
SELECT CARTO.CARTO.GEOCODE_REVERSE(ST_POINT(-74.0060, 40.7128));
-- 254 Broadway, New York, NY 10007, USA
SELECT CARTO.CARTO.GEOCODE_REVERSE(ST_POINT(-74.0060, 40.7128), 'en-US');
-- 254 Broadway, New York, NY 10007, USA
SELECT CARTO.CARTO.GEOCODE_REVERSE(ST_POINT(-74.0060, 40.7128), 'en-US', {}, 'my_api_base_url', 'my_lds_token');
-- 254 Broadway, New York, NY 10007, USA
ISOLINE
ISOLINE(origin, mode, range, range_type [, options] [, api_base_url, lds_token])
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 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 procedure. Likewise, in order to materialize the results in a table.
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).
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' and 'private_bus'.
Mapbox: 'walk', 'car' and 'bike'.
TomTom: 'walk', 'car', 'bike', 'motorbike', 'truck', 'taxi', 'bus' and 'van'.
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 "any" is introduced, 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 "any" is introduced, 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 "any" is introduced, 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.
api_base_url (optional): VARCHAR url of the API where the customer account is stored.
lds_token (optional): VARCHAR an API Access Token that is allowed to use the LDS API.
warning
Before running, we recommend checking your provider using the 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.
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
SELECT CARTO.CARTO.ISOLINE(ST_MAKEPOINT(-3,40), 'car', 10, 'time', '{"departure_time":"any"}');
-- { "coordinates": [ [ [ -2.999868, 40.001907 ], [ -2.999439, 40.001736 ], [ -2.999096, 40.000706 ], [ -2.998066, 40.000362 ], [ ...
SELECT CARTO.CARTO.ISOLINE(ST_MAKEPOINT(-3,40), 'car', 10, 'time', {'departure_time':'any'});
-- { "coordinates": [ [ [ -2.999868, 40.001907 ], [ -2.999439, 40.001736 ], [ -2.999096, 40.000706 ], [ -2.998066, 40.000362 ], [ ...
SELECT CARTO.CARTO.ISOLINE(ST_MAKEPOINT(-3,40), 'car', 10, 'time', '{"departure_time":"any"}', 'my_api_base_url', 'my_lds_token');
-- { "coordinates": [ [ [ -2.999868, 40.001907 ], [ -2.999439, 40.001736 ], [ -2.999096, 40.000706 ], [ -2.998066, 40.000362 ], [ ...
SELECT CARTO.CARTO.ISOLINE(ST_MAKEPOINT(-3,40), 'car', 10, 'time', {'departure_time':'any'}, 'my_api_base_url', 'my_lds_token');
-- { "coordinates": [ [ [ -2.999868, 40.001907 ], [ -2.999439, 40.001736 ], [ -2.999096, 40.000706 ], [ -2.998066, 40.000362 ], [ ...
LDS_QUOTA_INFO
LDS_QUOTA_INFO([api_base_url, lds_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 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.
api_base_url (optional): VARCHAR url of the API where the customer account is stored.
lds_token (optional): VARCHAR an API Access Token that is allowed to use the LDS API.
Return type
VARCHAR
Examples
SELECT CARTO.CARTO.LDS_QUOTA_INFO();
-- [
--   {
--     "used_quota": 10,
--     "annual_quota": 1000,
--     "providers": {
--         "geocoding": "tomtom",
--         "isolines": "here"
--     }
--   }
-- ]
SELECT CARTO.CARTO.LDS_QUOTA_INFO('my_api_base_url', 'my_lds_token');
-- [
--   {
--     "used_quota": 10,
--     "annual_quota": 1000,
--     "providers": {
--         "geocoding": "tomtom",
--         "isolines": "here"
--     }
--   }
-- ]

Provider	Option	Description
`All`	`language`	A `VARCHAR` that specifies the language of the geocoding in RFC 4647 format.

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 `"any"` is introduced, 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 `"any"` is introduced, 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`.

Provider	Option	Description
`All`	`language`	A `VARCHAR` that specifies the language of the geocoding in RFC 4647 format.

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 `"any"` is introduced, 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 `"any"` is introduced, 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 `"any"` is introduced, 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`.

measurements

Last modified 15d ago