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

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

warning

This function consumes LDS quota. Each call consumes as many units of quota as the number of rows your input table has. Before running, we recommend checking the size of the data to be geocoded and your available quota using the GET_LDS_QUOTA_INFO function.

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. If the input table already contains a geometry column with the name geom_column, only those rows with NULL values in it will be geocoded.

The table is geocoded sequentially, one batch of rows at a time. The default batch size depends on the geocoding provider configured for your account: 200 rows with TomTom, which is processed through the provider's asynchronous batch API, and 75 rows with any other provider. Use the carto_batch_size option to override it.

Redshift batches are much smaller than the ones used on other warehouses because the provider request is assembled inside a VARCHAR value, and its 64 KB ceiling bounds how many addresses fit into a single call.

Input parameters

• input_table: VARCHAR(MAX) 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. It must have the form [schema_name].[table_name]. Notice that a table name expressed as [database_name].[schema_name].[table_name] is not valid, and the function will not handle it correctly.

• address_column: VARCHAR(MAX) name of the column from the input table that contains the addresses to be geocoded.

• geom_column (optional): VARCHAR(MAX) column name for the output geometry column. Defaults to 'geom'.

• country (optional): VARCHAR(MAX) name of the country in ISO 3166-1 alpha-2. There is no default: when it is omitted or empty, an empty country is sent to the provider. See Always set the country below.

• options (optional): VARCHAR(MAX) containing a valid JSON with the different options. In addition to the geocoding service options described in the table below, the following CARTO options are accepted:

  CARTO option	Description
  carto_force_geocode	a BOOL (false by default) that requests every row again, including rows that already have a non-null value in geom_column. It replaces values the caller already had, so it is the one option here that spends quota on rows that were already resolved.
  carto_force_rebuild	a BOOL (false by default) that discards the partial table described in Resuming an interrupted job below and starts the job again. It never replaces a value already in the table, so only the rows still pending are requested.
  carto_batch_size	an integer that overrides the number of rows sent to the provider in each batch. It must be 1 or greater — lower values fail with Option "carto_batch_size" must be a positive number. It defaults to the per-provider value described above (200 with TomTom, 75 otherwise). Lower it when a batch fails because the request is too large, which happens with unusually long addresses; a value above it is reduced to it and reported in the summary rather than rejected, which is also what keeps a batch inside the 64 KB VARCHAR ceiling mentioned above.
  carto_max_status_attempts	an integer (120 by default) that bounds how many status checks each TomTom async batch gets before the run fails with did not complete. Each healthy check long-polls, so the default allows hours per batch; raise it only for extremely large batches on a slow provider tier.

  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(MAX) url of the API where the customer account is stored.

• api_access_token (optional): VARCHAR(MAX) an API Access Token that is allowed to use the LDS API.

Always set the country

warning

Pass country whenever your addresses belong to a single country. There is no country default anywhere in the chain: an empty value is forwarded to the provider as an empty country, which turns every address into a worldwide search. In our measurements that makes the same job around 60% slower, and it also lowers match quality — a street name that is unambiguous within one country rarely is worldwide.

If your table mixes countries, geocode it in subsets, one CALL per country, each with its own country value.

Geocoding large tables

Geocoding millions of rows is a long-running operation, and a single CALL can be cut short before it finishes — by your cluster's statement_timeout, by a WLM query monitoring rule, or by cancelling the query. A large table is therefore expected to need several sequential runs.

This is normal operation, not a failure: calling it again with exactly the same arguments continues where the previous call stopped, paying only for what is still pending. The contract is to re-run the same CALL until no rows remain to geocode:

SELECT COUNT(*) FROM <my-schema>.<my-table>
WHERE my_geom_column IS NULL AND my_address_column IS NOT NULL;

That count is what remains once a run has finished. The resolved addresses are merged into the table only when the run completes, so to watch a job that is still running count the rows of its partial table instead — see Resuming an interrupted job below.

Re-run until that count stops going down. What remains at that point are addresses the provider could not resolve, and further runs will not recover them.

warning

Do not use carto_force_geocode: true to resume an interrupted run. It requests every row again and spends quota on rows that were already resolved; a plain re-run resumes and pays only for what is pending.

Resuming an interrupted job

While a job is unfinished its results live in a table named <input_table>_partial_<id> next to your input, for example my_table_partial_1a2b3c4d, where <id> stands for the parameters of the run. The resolved geometries are merged into your table only when the run completes, so a running job is watched by counting the rows of that partial table. If a run is interrupted — cancelled, disconnected or stopped by an error — run the exact same call again: it continues from that partial table and geocodes only the work still pending, paying again for at most one in-flight batch. With TomTom, a provider job that keeps failing its status checks stops the run with an error naming the job and batch, and the next run asks only for the rows still without a geometry.

Every set of parameters keeps its own partial table. Do not run the same call twice at the same time: concurrent runs work through the same internal tables and will collide. Drop the partial table of a job you abandon — it holds results you have already paid for. A table named without its schema keeps its partial table in the session as a temporary table: it is never resumed, and nothing is left once the session ends. Name the schema to resume a job.

Changing any parameter — a different country, other provider options — starts a new job with a partial table of its own. carto_batch_size is not part of that identity, which is what makes an oversized batch recoverable: re-run the same call with a smaller one and every address already paid for is kept.

Examples

CALL 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.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.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.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.GEOCODE_TABLE('<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-schema>.<my-table>` will be updated
-- adding the columns: my_geom_column, carto_geocode_metadata.

Additional examples

• Geocoding your address data

GEOCODE_REVERSE_TABLE

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

warning

This function consumes LDS quota. Each call consumes as many units of quota as the number of rows your input table has. Before running, we recommend checking the size of the data to be geocoded and your available quota using the GET_LDS_QUOTA_INFO function.

Description

Reverse-geocodes an input table by adding an user defined column address_column with the addresses for a given point location column. If the input table already contains a column with the name address_column, only those rows with NULL values in it will be reverse-geocoded. An interrupted run keeps every address it had already resolved, and the next one only spends quota on the geometries still missing one — see Resuming an interrupted job below. Do not run the same call twice at the same time: concurrent runs work through the same internal tables and will collide.

The table is reverse-geocoded sequentially, one batch of rows at a time, with a default batch size of 100 rows, which is also the maximum. Use the carto_batch_size option to lower it.

Input parameters

• input_table: VARCHAR(MAX) name of the table to be reverse-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.

• geom_column (optional): GEOMETRY column name for the geometry column that contains the points to be reverse-geocoded. Defaults to 'geom'.

• address_column: VARCHAR(MAX) 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(MAX) 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(MAX) containing a valid JSON with the different options. The following CARTO options are accepted:

  CARTO option	Description
  carto_batch_size	an integer that overrides the number of points sent to the provider in each batch. It must be 1 or greater — lower values fail with Option "carto_batch_size" must be a positive number. It defaults to 100. Reverse geocoding requests are fixed-size points, so lowering it is only needed if the provider times out on full batches.
  carto_force_geocode	a BOOL (false by default) that requests an address for every geometry again, including rows that already have one. It replaces the addresses already in the address column, and spends quota on all of them.
  carto_force_rebuild	a BOOL (false by default) that discards the partial table described in Resuming an interrupted job below and starts the job again. It never replaces a value already in the table, so only the rows still pending are requested.

• api_base_url (optional): VARCHAR(MAX) url of the API where the customer account is stored.

• api_access_token (optional): VARCHAR(MAX) an API Access Token that is allowed to use the LDS API.

Reverse geocoding large tables

Reverse-geocoding millions of rows is a long-running operation, and a single CALL can be cut short before it finishes — by your cluster's statement_timeout, by a WLM query monitoring rule, or by cancelling the query. A large table is therefore expected to need several sequential runs.

This is normal operation, not a failure: calling it again with exactly the same arguments continues where the previous call stopped, paying only for what is still pending. The contract is to re-run the same CALL until no rows remain to reverse-geocode:

SELECT COUNT(*) FROM <my-schema>.<my-table>
WHERE my_geom_column IS NOT NULL
  AND (my_address_column IS NULL OR TRIM(my_address_column) = '');

That count is what remains once a run has finished. The resolved addresses are merged into the table only when the run completes, so to watch a job that is still running count the rows of its partial table instead — see Resuming an interrupted job below.

Re-run until that count stops going down. What remains at that point are geometries the provider could not resolve, and further runs will not recover them.

warning

Do not use carto_force_geocode: true to resume an interrupted run. It requests an address for every geometry again and spends quota again on the ones that were already resolved.

Resuming an interrupted job

While a job is unfinished its results live in a table named <input_table>_partial_<id> next to your input, for example my_table_partial_1a2b3c4d, where <id> stands for the parameters of the run. The resolved addresses are merged into your table only when the run completes, so a running job is watched by counting the rows of that partial table. If a run is interrupted — cancelled, disconnected or stopped by an error — run the exact same call again: it continues from that partial table and reverse-geocodes only the work still pending, paying again for at most one in-flight batch.

Every set of parameters keeps its own partial table. Do not run the same call twice at the same time: concurrent runs work through the same internal tables and will collide. Drop the partial table of a job you abandon — it holds results you have already paid for. A table named without its schema keeps its partial table in the session as a temporary table: it is never resumed, and nothing is left once the session ends. Name the schema to resume a job. Only distinct geometries are sent to the provider, so a table where the same point repeats costs one request for all of them.

Changing any parameter — a different language, other provider options — starts a new job with a partial table of its own. carto_batch_size is not part of that identity, which is what makes an oversized batch recoverable: re-run the same call with a smaller one and every geometry already paid for is kept.

Examples

CALL carto.GEOCODE_REVERSE_TABLE('<my-schema>.<my-table>');
-- The table `<my-schema>.<my-table>` with a column `geom` will be updated
-- adding the column `address`.

CALL carto.GEOCODE_REVERSE_TABLE('<my-schema>.<my-table>', 'my_geom_column');
-- The table `<my-schema>.<my-table>` with a column `my_geom_column` will be updated
-- adding the column `address`.

CALL carto.GEOCODE_REVERSE_TABLE('<my-schema>.<my-table>', 'my_geom_column', 'my_address_column');
-- The table `<my-schema>.<my-table>` with a column `my_geom_column` will be updated
-- adding the column `my_address_column`.

CALL carto.GEOCODE_REVERSE_TABLE('<my-schema>.<my-table>', 'my_geom_column', 'my_address_column', 'en-US');
-- The table `<my-schema>.<my-table>` with a column `my_geom_column` will be updated
-- adding the column `my_address_column`.
-- The addresses will be in the (US) english language, if supported by the account LDS provider.

CALL carto.GEOCODE_REVERSE_TABLE('<my-schema>.<my-table>', 'my_geom_column', 'my_address_column', 'en-US', '{}', 'my_api_base_url', 'my_api_access_token');
-- The table `<my-schema>.<my-table>` with a column `my_geom_column` will be updated
-- adding the column `my_address_column`.
-- The addresses will be in the (US) english language, if supported by the account LDS provider.

CREATE_ISOLINES

CREATE_ISOLINES(input, output_table, geom_column, mode, range, range_type [, options] [, api_base_url, api_access_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 GET_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 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).

Isolines are requested sequentially, one batch of origins at a time. The default batch size depends on the isolines provider configured for your account: 10 origins with TravelTime, 100 with TomTom, 50 with HERE and 20 with Mapbox. Use the carto_batch_size option to override it.

Input parameters

• input: VARCHAR(MAX) name of the input table or query.

• output_table: VARCHAR(MAX) qualified name of the output table, e.g. <my-schema>.<my-output-table>. The process will fail if the table already exists, unless carto_force_rebuild is set.

• geom_column: VARCHAR(MAX) column name for the origin geometry column.

• mode: VARCHAR(MAX) 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(MAX) type of range. Supported: 'time' (for isochrones), 'distance' (for isodistances).

• options (optional): VARCHAR(MAX) containing a valid JSON with the different options. In addition to the provider options described in the table below, the following CARTO options are accepted:

  CARTO option	Description
  carto_batch_size	an integer that overrides the number of origins sent to the provider in each batch. It must be 1 or greater — lower values fail with Option "carto_batch_size" must be a positive number It defaults to 10 with TravelTime, 100 with TomTom, 50 with HERE and 20 with Mapbox. Isoline responses carry full polygon geometries, so the response size is the practical limit: lower it if large batches fail or time out; a value above it is reduced to it and reported in the summary rather than rejected.
  carto_keep_orig_geom	a boolean, false by default. When true the output keeps the geometry each result was computed from, in a orig_geom column. When false that column is absent.
  carto_force_rebuild	a boolean, false by default. When true the job starts from scratch: the output table and the partial table holding any unfinished work for these parameters are dropped, and every result is requested and paid for again. A table under the output name that this procedure did not produce is never dropped — the call fails instead.

  Valid provider 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(MAX) url of the API where the customer account is stored.

• api_access_token (optional): VARCHAR(MAX) an API Access Token that is allowed to use the LDS API.

warning

Before running, we recommend checking your provider using the 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.

Resuming an interrupted job

While a job is unfinished its results live in a table named <output_table>_partial_<id> next to your output, where <id> stands for the parameters of the run. The output table appears only when the run completes, so a table under the output name is always a finished result. If a run is interrupted — cancelled, disconnected or stopped by an error — run the exact same call again: it continues from that partial table and requests, and pays for, only the work still pending. The error names the batch the run stopped on, so no work is ever dropped silently.

Every set of parameters keeps its own partial table. Do not run the same call twice at the same time: concurrent runs work through the same internal tables and will collide. Drop the partial table of a job you abandon — it holds results you have already paid for.

Once the job has finished the same call fails rather than write over the result; carto_force_rebuild runs it again from scratch. When the input is a query its text is part of the parameters, so editing it — even cosmetically — starts a new job: pass it unchanged, or point at a table or view. carto_batch_size is not part of that identity, which is what makes an oversized batch recoverable: re-run the same call with a smaller one and everything already paid for is kept.

Examples

CALL carto.CREATE_ISOLINES(
    '<my-schema>.<my-table>',
    '<my-schema>.<my-output-table>',
    'my_geom_column',
    'car', 300, '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.CREATE_ISOLINES(
    '<my-schema>.<my-table>',
    '<my-schema>.<my-output-table>',
    'my_geom_column',
    'car', 1000, 'distance'
);
-- 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.CREATE_ISOLINES(
    '<my-schema>.<my-table>',
    '<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-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, api_access_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 to be geocoded and your available quota using the GET_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 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.

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

Routes are calculated sequentially, one batch at a time, with a default batch size of 10 routes, which is also the maximum. Use the carto_batch_size option to lower it.

Input parameters

• input: VARCHAR(MAX) name of the input query, which must have columns named origin and destination of type GEOMETRY 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(MAX) qualified name of the output table, e.g. <my-schema>.<my-output-table>. The process will fail if the table already exists, unless carto_force_rebuild is set.

• geom_column: VARCHAR(MAX) column name for generated routes geometry column.

• mode: VARCHAR(MAX) 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(MAX) 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 function. In addition to the provider parameters described in the table below, the following CARTO options are accepted:

  CARTO option	Description
  carto_batch_size	an integer that overrides the number of routes sent to the provider in each batch. It must be 1 or greater — lower values fail with Option "carto_batch_size" must be a positive number. It defaults to 10. Lower it when a batch fails because the request is too large, which happens with long waypoint lists.
  carto_force_rebuild	a boolean, false by default. When true the job starts from scratch: the output table and the partial table holding any unfinished work for these parameters are dropped, and every result is requested and paid for again. A table under the output name that this procedure did not produce is never dropped — the call fails instead.

  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

    • 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

  • 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

  • HERE

  • TravelTime

• api_base_url (optional): VARCHAR(MAX) url of the API where the customer account is stored.

• api_access_token (optional): VARCHAR(MAX) an API Access Token that is allowed to use the LDS API.

warning

Before running, we recommend checking your provider using the 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.

warning

Due to current limitation in the size of the character types of Redshift, if the resulting route is too long or complex an error similiar to the next one may occur:

SQL Error [XX000]: ERROR: Value too long for character type
  Detail:
  -----------------------------------------------
  error:  Value too long for character type
  code:      8001
  context:   Value too long for type character varying(65535)
  query:     0
  location:  string.cpp:218
  process:   padbmaster [pid=29900]
  -----------------------------------------------

Resuming an interrupted job

While a job is unfinished its results live in a table named <output_table>_partial_<id> next to your output, where <id> stands for the parameters of the run. The output table appears only when the run completes, so a table under the output name is always a finished result. If a run is interrupted — cancelled, disconnected or stopped by an error — run the exact same call again: it continues from that partial table and requests, and pays for, only the work still pending. The error names the provider job and the batch the run stopped on, so no work is ever dropped silently.

Every set of parameters keeps its own partial table. Do not run the same call twice at the same time: concurrent runs work through the same internal tables and will collide. Drop the partial table of a job you abandon — it holds results you have already paid for.

Once the job has finished the same call fails rather than write over the result; carto_force_rebuild runs it again from scratch. When the input is a query its text is part of the parameters, so editing it — even cosmetically — starts a new job: pass it unchanged, or point at a table or view. carto_batch_size is not part of that identity, which is what makes an oversized batch recoverable: re-run the same call with a smaller one and everything already paid for is kept.

Examples

CALL carto.CREATE_ROUTES(
    '<my-schema>.<my-table>',
    '<my-schema>.<my-output-table>',
    'my_geom_column',
    'car'
);
-- The table `<my-schema>.<my-output-table>` will be created
-- with the columns of the input table and 'geom' and 'carto_routing_metadata' columns.
-- Routes will be added in the "geom" column.

CALL carto.CREATE_ROUTES(
    '<my-schema>.<my-table>',
    '<my-schema>.<my-output-table>',
    'my_geom_column',
    '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` columns.
-- Routes will be added in the "geom" column.

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

GEOCODE

GEOCODE(address [, country] [, options] [, api_base_url, api_access_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 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 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).

Input parameters

• address: VARCHAR(MAX) input address to geocode.

• country (optional): VARCHAR name of the country in ISO 3166-1 alpha-2. Defaults to ''.

• options (optional): VARCHAR(MAX) 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(MAX) url of the API where the customer account is stored.

• api_access_token (optional): VARCHAR(MAX) an API Access Token that is allowed to use the LDS API.

Return type

GEOMETRY

Constraints

This function performs requests to the CARTO Location Data Services API. Redshift 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. 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.GEOCODE('Madrid');
-- POINT(109.590465335923 34.1733770650093)

SELECT carto.GEOCODE('Madrid', 'es');
-- POINT(51.405967078794 20.3365500266832)

SELECT carto.GEOCODE('Madrid', 'es', '{"language":"es-ES"}');
-- POINT(51.405967078794 20.3365500266832)

SELECT carto.GEOCODE('Madrid', 'es', '{"language":"es-ES"}', 'my_api_base_url', 'my_api_access_token');
-- POINT(51.405967078794 20.3365500266832)

CREATE TABLE <my-schema>.<my-geocoded-table> AS
SELECT address, carto.GEOCODE(address) AS geom FROM <my-schema>.<my-table>;
-- The table `<my-schema>.<my-geocoded-table>` will be created

GEOCODE_REVERSE

GEOCODE_REVERSE(geom [, language] [, options] [, api_base_url, api_access_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 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 procedure. Likewise, in order to materialize the results in a table.

Description

Performs a reverse geocoding of the point received as input.

Input parameters

• geom: GEOMETRY input point for which to obtain the address.

• language (optional): VARCHAR(MAX) language in which results should be returned.

• options (optional): VARCHAR(MAX) 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(MAX) url of the API where the customer account is stored.

• api_access_token (optional): VARCHAR(MAX) an API Access Token that is allowed to use the LDS API.

Return type

VARCHAR(MAX)

Constraints

This function performs requests to the CARTO Location Data Services API. Redshift 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. 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.GEOCODE_REVERSE(ST_POINT(-74.0060, 40.7128));
-- 254 Broadway, New York, NY 10007, USA

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

SELECT 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

ISOLINE(origin, mode, range, range_type [, options] [, api_base_url, api_access_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 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 procedure. Likewise, in order to materialize the results in a table.

Description

Calculates the isoline polygon from a given point.

Input parameters

• origin: GEOMETRY origin point of the isoline.

• mode: VARCHAR(MAX) 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(MAX) type of range. Supported: 'time' (for isochrones), 'distance' (for isodistances).

• options (optional): VARCHAR(MAX) 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(MAX) url of the API where the customer account is stored.

• api_access_token (optional): VARCHAR(MAX) an API Access Token that is allowed to use the LDS API.

warning

Before running, we recommend checking your provider using the 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.

Return type

GEOMETRY

Constraints

This function performs requests to the CARTO Location Data Services API. Redshift 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. 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.ISOLINE(ST_POINT(-3, 40), 'car', 300, 'time');
-- POLYGON ((-3.0081322 40.00005, -3.0081322 40.0006, -3.007061 40.001423, -3.0042043 40.001423, ...

SELECT carto.ISOLINE(ST_POINT(-3, 40), 'car', 1000, 'distance');
-- POLYGON ((-3.0960834 39.984512, -3.094504 39.984512, -3.092925 39.983295, -3.0913458 39.983295, ...

SELECT carto.ISOLINE(ST_POINT(-3, 40), 'car', 300, 'time', '{"departure_time":"any"}', 'my_api_base_url', 'my_api_access_token');
-- POLYGON ((-3.0081322 40.00005, -3.0081322 40.0006, -3.007061 40.001423, -3.0042043 40.001423, ...

GET_LDS_QUOTA_INFO

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(MAX) url of the API where the customer account is stored.

• api_access_token (optional): VARCHAR(MAX) an API Access Token that is allowed to use the LDS API.

Return type

VARCHAR(MAX)

Examples

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

SELECT 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

CREATE_H3_ISOLINES(input, output_table, geom_column, mode, range, range_type, resolution [, options] [, api_base_url, api_access_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 and your available quota using the GET_LDS_QUOTA_INFO function.

warning

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

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.

Isolines are requested sequentially, one batch of origins at a time, with a default batch size of 10 origins. Use the carto_batch_size option to override it.

Input parameters

• input: VARCHAR(MAX) name of the input table or query.

• output_table: VARCHAR(MAX) qualified name of the output table, e.g. <my-schema>.<my-output-table>. The process will fail if the table already exists, unless carto_force_rebuild is set.

• geom_column: VARCHAR(MAX) column name for the origin geometry column.

• mode: VARCHAR(MAX) 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(MAX) 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(MAX) containing a valid JSON with the different options. In addition to the provider options described in the table below, the following CARTO options are accepted:

  CARTO option	Description
  carto_batch_size	an integer that overrides the number of origins sent to the provider in each batch. It must be 1 or greater — lower values fail with Option "carto_batch_size" must be a positive number It defaults to 10. Lower it if large batches fail or time out; a value above the largest batch CARTO sends to the provider is reduced to it and reported in the summary rather than rejected.
  carto_keep_orig_geom	a boolean, false by default. When true the output keeps the geometry each result was computed from, in a orig_geom column. When false that column is absent.
  carto_force_rebuild	a boolean, false by default. When true the job starts from scratch: the output table and the partial table holding any unfinished work for these parameters are dropped, and every result is requested and paid for again. A table under the output name that this procedure did not produce is never dropped — the call fails instead.

  Valid provider 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 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(MAX) url of the API where the customer account is stored.

• api_access_token (optional): VARCHAR(MAX) an API Access Token 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(MAX)	Error information, or NULL if successful.

Resuming an interrupted job

While a job is unfinished its results live in a table named <output_table>_partial_<id> next to your output, where <id> stands for the parameters of the run. The output table appears only when the run completes, so a table under the output name is always a finished result. If a run is interrupted — cancelled, disconnected or stopped by an error — run the exact same call again: it continues from that partial table and requests, and pays for, only the work still pending. The error names the batch the run stopped on, so no work is ever dropped silently.

Every set of parameters keeps its own partial table. Do not run the same call twice at the same time: concurrent runs work through the same internal tables and will collide. Drop the partial table of a job you abandon — it holds results you have already paid for.

Once the job has finished the same call fails rather than write over the result; carto_force_rebuild runs it again from scratch. When the input is a query its text is part of the parameters, so editing it — even cosmetically — starts a new job: pass it unchanged, or point at a table or view. carto_batch_size is not part of that identity, which is what makes an oversized batch recoverable: re-run the same call with a smaller one and everything already paid for is kept.

Examples

CALL carto.CREATE_H3_ISOLINES(
    '<my-schema>.<my-table>',
    '<my-schema>.<my-output-table>',
    'my_geom_column',
    'car', 900, 'time', 6
);
-- The table `<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.

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

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

CREATE_ROUTING_MATRIX

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

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 function.

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.

The matrix is requested in batches of origins x destinations pairs. The default batch dimensions depend on the routing provider configured for your account: 20 x 20 with TomTom and 1 x 400 with TravelTime. Use the carto_origins_batch_size and carto_destinations_batch_size options described in the table below to override them.

Input parameters

• origins_table: VARCHAR name of the origins input table, or a query whose result is used as origins.

• origins_geom_column: VARCHAR column name for the origin geometry column.

• destinations_table: VARCHAR name of the destinations input table, or a query whose result is used as destinations.

• destinations_geom_column: VARCHAR column name for the destination geometry column.

• output_table: VARCHAR(MAX) qualified name of the output table, e.g. <my-schema>.<my-output-table>. The process will fail if the table already exists, unless carto_force_rebuild is set.

• 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 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 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

  • TravelTime

• api_base_url (optional): VARCHAR url of the API where the customer account is stored.

• api_access_token (optional): VARCHAR an API Access Token that is allowed to use the LDS API.

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

  CARTO option	Description
  carto_origins_batch_size	Default: TomTom: 20, TravelTime: 1. 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. A value above the default is reduced to it and reported in the summary rather than rejected.
  carto_destinations_batch_size	Default: TomTom: 20, TravelTime: 400. A NUMBER that specifies the number of destinations rows to process in each batch. A value above the default is reduced to it and reported in the summary rather than rejected.
  carto_max_status_attempts	an integer, 120 by default, bounding how many status checks a provider job may take before the run stops. A job is also abandoned after 3 consecutive failed checks.
  carto_force_rebuild	a boolean, false by default. When true the job starts from scratch: the output table and the partial table holding any unfinished work for these parameters are dropped, and every result is requested and paid for again. A table under the output name that this procedure did not produce is never dropped — the call fails instead.

  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.

warning

We recommend the product of carto_origins_batch_size x carto_destinations_batch_size to be lower than 400 to ensure that the batches sizes is small enough to be processed.

warning

Before running, we recommend checking your provider using the 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.

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.

Resuming an interrupted job

While a job is unfinished its results live in a table named <output_table>_partial_<id> next to your output, where <id> stands for the parameters of the run. The output table appears only when the run completes, so a table under the output name is always a finished result. If a run is interrupted — cancelled, disconnected or stopped by an error — run the exact same call again: it continues from that partial table and requests, and pays for, only the work still pending. The error names the provider job and the batch the run stopped on, so no work is ever dropped silently.

Every set of parameters keeps its own partial table. Do not run the same call twice at the same time: concurrent runs work through the same internal tables and will collide. Drop the partial table of a job you abandon — it holds results you have already paid for.

Once the job has finished the same call fails rather than write over the result; carto_force_rebuild runs it again from scratch. When the input is a query its text is part of the parameters, so editing it — even cosmetically — starts a new job: pass it unchanged, or point at a table or view. The two batch sizes are not part of that identity, but they are not a way to shrink the batches of a job already under way either: a resumed run follows the grid the interrupted run laid out, and carto_origins_batch_size and carto_destinations_batch_size passed to it only change the quota estimate. Only carto_force_rebuild lays the grid out again, requesting every result once more, so a job that fails on an oversized batch has to be rebuilt with smaller sizes rather than resumed.

Examples

CALL carto.CREATE_ROUTING_MATRIX(
    '<my-schema>.<my-origins-table>',
    'my_origins_geom_column',
    '<my-schema>.<my-destinations-table>',
    'my_destinations_geom_column',
    '<my-schema>.<my-output-table>',
    'car'
);
-- The table `<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:

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

CALL carto.CREATE_ROUTING_MATRIX(
    '<my-schema>.<my-origins-table>',
    'my_origins_geom_column',
    '<my-schema>.<my-destinations-table>',
    'my_destinations_geom_column',
    '<my-schema>.<my-output-table>',
    'car',
    '{"departAt":"now"}',
    'my_api_base_url',
    'my_api_access_token'
);
-- The table `<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:

CALL carto.CREATE_ROUTING_MATRIX(
    '<my-schema>.<my-origins-table>',
    'my_origins_geom_column',
    '<my-schema>.<my-destinations-table>',
    'my_destinations_geom_column',
    '<my-schema>.<my-output-table>',
    'car',
    '{"carto_origins_batch_size": 10, "carto_destinations_batch_size": 1000}'
);
-- The table `<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

H3_ISOLINE(origin, mode, range, range_type, resolution [, options] [, api_base_url, api_access_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 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 procedure. Likewise, in order to materialize the results in a table.

warning

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

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: GEOMETRY origin point of the isoline.

• mode: VARCHAR(MAX) 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(MAX) 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(MAX) 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 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(MAX) url of the API where the customer account is stored.

• api_access_token (optional): VARCHAR(MAX) an API Access Token that is allowed to use the LDS API.

Return type

SUPER

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. Redshift 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

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

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

SELECT
    cell.h3::VARCHAR AS h3,
    cell.h3_min::INT AS h3_min,
    cell.h3_max::INT AS h3_max,
    cell.h3_mean::INT AS h3_mean
FROM carto.H3_ISOLINE(ST_POINT(-3.7038, 40.4168), 'car', 900, 'time', 6) AS cells, cells AS cell;
-- Unnests the array to get individual H3 cells as rows