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 and gateway functions. It also optionally sets default credentials.

Credentials

Your CARTO account has annual quotas assigned for each LDS service that are used up by every call to the LDS functions and procedures in the Analytics Toolbox.

Accordingly, the use of these functions and procedures requires providing authorization credentials to prevent fraudulent usage. Two parameters are needed:

api_base_url The API base url is simply the address through which you can access all the services of your CARTO account, and it depends on the region or premises where your account is located. Usually it will be this one: https://gcp-us-east1.api.carto.com.
api_access_token This token is an API Access Token that is allowed to use the LDS API. You must keep this secret! Anyone that has access to this token can use up the LDS quota assigned to your account.

Both the API base url and your API access token can be accessed through the developers section of the CARTO user interface. The API base url is displayed inside this section while for the API access token you will have to create a new API access token allowing the LDS API.

For more information about CARTO for developers, please check our documentation for Developers.

If default credentials are provided to the SETUP procedure, you can simply omit the credential parameters when calling the LDS functions (they default to NULL), and the credentials configured during SETUP will be used.

tip

To check that everything works correctly, without spending any credits, make a call to the GET_LDS_QUOTA_INFO function. You can enter the following in your Oracle SQL client:

SELECT carto.GET_LDS_QUOTA_INFO() FROM DUAL;

Note that if you haven't set up default LDS credentials with SETUP you'll need to provide them when calling the LDS functions:

SELECT carto.GET_LDS_QUOTA_INFO(
  '<my-api-base-url>',
  '<my-api-access-token>'
) FROM DUAL;

You should get a JSON response like this, with the available services and the quotas:

[
  {
    "used_quota": 10,
    "annual_quota": 100000,
    "providers": {
        "geocoding": "tomtom",
        "isolines": "here",
        "routing":"tomtom"
    }
  }
]

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.

This also will allow you to verify that you have the right credentials, the AT is installed correctly and the service is working.

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. It geocodes sequentially the table in chunks of 100 rows. 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.

Input parameters

input_table: VARCHAR2 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: VARCHAR2 name of the column from the input table that contains the addresses to be geocoded.
geom_column (optional): VARCHAR2 column name for the output geometry column. Defaults to 'geom'.
country (optional): VARCHAR2 name of the country in ISO 3166-1 alpha-2. Defaults to ''.
options (optional): VARCHAR2 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 VARCHAR2 that specifies the language of the geocoding in RFC 4647 format.
api_base_url (optional): VARCHAR2 url of the API where the customer account is stored.
api_access_token (optional): VARCHAR2 an API Access Token that is allowed to use the LDS API.

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.

GEOCODE_REVERSE_TABLE

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

warning

Description

Reverse-geocodes an input table by adding an user defined column address_column with the addresses for a given point location column. It geocodes sequentially the table in chunks of 10 rows. 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.

Input parameters

input_table: VARCHAR2 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 column to it to store the reverse geocoding result.
geom_column (optional): SDO_GEOMETRY column name for the geometry column that contains the points to be reverse-geocoded. Defaults to 'geom'.
address_column: VARCHAR2 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): VARCHAR2 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): VARCHAR2 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): VARCHAR2 url of the API where the customer account is stored.
api_access_token (optional): VARCHAR2 an API Access Token that is allowed to use the LDS API.

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_table, output_table, geom_column, travel_mode, range_value, 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 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).

Note that the parameters are named travel_mode and range_value instead of mode and range because both mode and range are reserved words in Oracle.

Input parameters

input_table: VARCHAR2 name of the input table or query.
output_table: VARCHAR2 qualified name of the output table, e.g. <my-schema>.<my-output-table>. The process will fail if the table already exists.
geom_column: VARCHAR2 column name for the origin geometry column.
travel_mode: VARCHAR2 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_value: NUMBER range of the isoline in seconds (for range_type 'time') or meters (for range_type 'distance').
range_type: VARCHAR2 type of range. Supported: 'time' (for isochrones), 'distance' (for isodistances).
options (optional): VARCHAR2 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 VARCHAR2 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 VARCHAR2 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 VARCHAR2 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 VARCHAR2 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 VARCHAR2 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 TravelTime docs for more info.
TravelTime
departure_time
Default: "now". A VARCHAR2 that specifies the time of departure. Supported: "now" and date-time as "<YYYY-MM-DD>T<hh:mm:ss>Z".
api_base_url (optional): VARCHAR2 url of the API where the customer account is stored.
api_access_token (optional): VARCHAR2 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.

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.

CREATE_H3_ISOLINES

CREATE_H3_ISOLINES(input_table, output_table, geom_column, travel_mode, range_value, range_type, resolution [, options] [, api_base_url, api_access_token])

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.

Note that the parameters are named travel_mode and range_value instead of mode and range because both mode and range are reserved words in Oracle.

Input parameters

input_table: VARCHAR2 name of the input table or query.
output_table: VARCHAR2 qualified name of the output table, e.g. <my-schema>.<my-output-table>. The process will fail if the table already exists.
geom_column: VARCHAR2 column name for the origin geometry column.
travel_mode: VARCHAR2 type of transport. Supported modes for TravelTime: 'walk', 'car', 'bike', 'public_transport', 'coach', 'bus', 'train', 'ferry'.
range_value: NUMBER range of the isoline in seconds. Valid range for time: 60-36000 (1 minute to 10 hours).
range_type: VARCHAR2 type of range. Currently only 'time' is supported for H3 isolines.
resolution: NUMBER H3 resolution level for the output cells. Valid range: 6-12.
options (optional): VARCHAR2 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 VARCHAR2 that specifies the time of departure. Supported: "now" and date-time as "<YYYY-MM-DD>T<hh:mm:ss>Z".
api_base_url (optional): VARCHAR2 url of the API where the customer account is stored.
api_access_token (optional): VARCHAR2 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

VARCHAR2

H3 cell index.

h3_min

NUMBER

Minimum travel time in seconds for this H3 cell.

h3_max

NUMBER

Maximum travel time in seconds for this H3 cell.

h3_mean

NUMBER

Average travel time in seconds for this H3 cell.

carto_isoline_metadata

VARCHAR2

Error information, or NULL if successful.

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_ROUTES

CREATE_ROUTES(input_table, output_table, geom_column, travel_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. It calculates routes sequentially in chunks of 100 rows.

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

Note that the parameter is named travel_mode instead of mode because mode is a reserved word in Oracle.

Input parameters

input_table: VARCHAR2 name of the input table or query, which must have columns named origin and destination of type SDO_GEOMETRY and containing points. If a column named waypoints is also present, it should contain a VARCHAR2 with the coordinates of the desired intermediate points with the format "lon1,lat1:lon2,lat2...".
output_table: VARCHAR2 qualified name of the output table, e.g. <my-schema>.<my-output-table>. The process will fail if the table already exists.
geom_column: VARCHAR2 column name for generated routes geometry column.
travel_mode: VARCHAR2 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): VARCHAR2 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:
- 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.
api_base_url (optional): VARCHAR2 url of the API where the customer account is stored.
api_access_token (optional): VARCHAR2 an API Access Token that is allowed to use the LDS API.

warning

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.

CREATE_ROUTING_MATRIX

CREATE_ROUTING_MATRIX(origins_table, origins_geom_column, destinations_table, destinations_geom_column, output_table, travel_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.

Note that the parameter is named travel_mode instead of mode because mode is a reserved word in Oracle.

Input parameters

origins_table: VARCHAR2 name of the origins input table or query.
origins_geom_column: VARCHAR2 column name for the origin geometry column.
destinations_table: VARCHAR2 name of the destinations input table or query.
destinations_geom_column: VARCHAR2 column name for the destination geometry column.
output_table: VARCHAR2 qualified name of the output table, e.g. <my-schema>.<my-output-table>. The process will fail if the table already exists.
travel_mode: VARCHAR2 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): VARCHAR2 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 VARCHAR2 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 VARCHAR2 that specifies the type of optimization used when calculating routes. Supported: "fastest", "shortest".
TomTom
traffic
Default: historical. A VARCHAR2 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 VARCHAR2 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): VARCHAR2 url of the API where the customer account is stored.
api_access_token (optional): VARCHAR2 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:
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.
carto_destinations_batch_size
Default: TomTom: 20, TravelTime: 400. A NUMBER that specifies the number of destinations rows to process in each batch.
carto_batch_size
A NUMBER used as a fallback applied to both origins and destinations when carto_origins_batch_size and/or carto_destinations_batch_size are not set.

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

Return type

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

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

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

Examples

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

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 an SDO_GEOMETRY point with its geographic coordinates (latitude and longitude).

Input parameters

address: VARCHAR2 input address to geocode.
country (optional): VARCHAR2 name of the country in ISO 3166-1 alpha-2. Defaults to ''.
options (optional): VARCHAR2 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 VARCHAR2 that specifies the language of the geocoding in RFC 4647 format.
api_base_url (optional): VARCHAR2 url of the API where the customer account is stored.
api_access_token (optional): VARCHAR2 an API Access Token that is allowed to use the LDS API.

Return type

SDO_GEOMETRY

Examples

SELECT carto.GEOCODE('Madrid') FROM DUAL;
-- SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-3.7038, 40.4168, NULL), NULL, NULL)

SELECT carto.GEOCODE('Madrid', 'es') FROM DUAL;
-- SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-3.7038, 40.4168, NULL), NULL, NULL)

SELECT carto.GEOCODE('Madrid', 'es', '{"language":"es-ES"}') FROM DUAL;
-- SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-3.7038, 40.4168, NULL), NULL, NULL)

SELECT carto.GEOCODE('Madrid', 'es', '{"language":"es-ES"}', 'my_api_base_url', 'my_api_access_token') FROM DUAL;
-- SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-3.7038, 40.4168, NULL), NULL, NULL)

CREATE TABLE my_geocoded_table AS
SELECT address, carto.GEOCODE(address) AS geom FROM my_table;
-- The table 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: SDO_GEOMETRY input point for which to obtain the address.
language (optional): VARCHAR2 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): VARCHAR2 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): VARCHAR2 url of the API where the customer account is stored.
api_access_token (optional): VARCHAR2 an API Access Token that is allowed to use the LDS API.

Return type

VARCHAR2

Examples

SELECT carto.GEOCODE_REVERSE(
    SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-74.0060, 40.7128, NULL), NULL, NULL)
) FROM DUAL;
-- 254 Broadway, New York, NY 10007, USA

SELECT carto.GEOCODE_REVERSE(
    SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-74.0060, 40.7128, NULL), NULL, NULL),
    'en-US'
) FROM DUAL;
-- 254 Broadway, New York, NY 10007, USA

ISOLINE

ISOLINE(origin, travel_mode, range_value, 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.

Note that the parameters are named travel_mode and range_value instead of mode and range because both mode and range are reserved words in Oracle.

Input parameters

origin: SDO_GEOMETRY origin point of the isoline.
travel_mode: VARCHAR2 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_value: NUMBER range of the isoline in seconds (for range_type 'time') or meters (for range_type 'distance').
range_type: VARCHAR2 type of range. Supported: 'time' (for isochrones), 'distance' (for isodistances).
options (optional): VARCHAR2 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 VARCHAR2 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 VARCHAR2 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 VARCHAR2 that specifies how isoline calculation is optimized. Supported: "quality", "performance" and "balanced".
HERE
routing_mode
Default: "fast". A VARCHAR2 that specifies which optimization is applied during isoline calculation. Supported: "fast" and "short".
TomTom
departure_time
Default: "now". A VARCHAR2 that specifies the time of departure. 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.
TravelTime
level_of_detail
A JSON string. In the most typical case, use { scale_type: 'simple_numeric', level: -N }, with N being the detail level (-8 by default). Check the TravelTime docs for more info.
TravelTime
departure_time
Default: "now". A VARCHAR2 that specifies the time of departure. Supported: "now" and date-time as "<YYYY-MM-DD>T<hh:mm:ss>Z".
api_base_url (optional): VARCHAR2 url of the API where the customer account is stored.
api_access_token (optional): VARCHAR2 an API Access Token that is allowed to use the LDS API.

warning

Return type

SDO_GEOMETRY

Examples

SELECT carto.ISOLINE(
    SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-3, 40, NULL), NULL, NULL),
    'car', 300, 'time'
) FROM DUAL;
-- SDO_GEOMETRY polygon representing a 5-minute driving isochrone

SELECT carto.ISOLINE(
    SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-3, 40, NULL), NULL, NULL),
    'car', 1000, 'distance'
) FROM DUAL;
-- SDO_GEOMETRY polygon representing a 1km driving isodistance

H3_ISOLINE

H3_ISOLINE(origin, travel_mode, range_value, range_type, resolution [, options] [, api_base_url, api_access_token])

warning

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 a pipelined table of H3 cells with travel time information for each cell. Consume it via TABLE(H3_ISOLINE(...)).

Input parameters

origin: SDO_GEOMETRY origin point of the isoline.
travel_mode: VARCHAR2 type of transport. Supported modes for TravelTime: 'walk', 'car', 'bike', 'public_transport', 'coach', 'bus', 'train', 'ferry'.
range_value: NUMBER range of the isoline in seconds. Valid range for time: 60-36000 (1 minute to 10 hours).
range_type: VARCHAR2 type of range. Currently only 'time' is supported for H3 isolines.
resolution: NUMBER H3 resolution level for the output cells. Valid range: 6-12.
options (optional): VARCHAR2 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, use { scale_type: 'simple_numeric', level: -N }, with N being the detail level (-8 by default). Check the TravelTime docs for more info.
TravelTime
departure_time
Default: "now". A VARCHAR2 that specifies the time of departure. Supported: "now" and date-time as "<YYYY-MM-DD>T<hh:mm:ss>Z".
api_base_url (optional): VARCHAR2 url of the API where the customer account is stored.
api_access_token (optional): VARCHAR2 an API Access Token that is allowed to use the LDS API.

Return type

Pipelined table of LDS_H3_ISOLINE_CELL rows. Each row has:

Field

Type

Description

h3

VARCHAR2(16)

H3 cell index.

h3_min

NUMBER

Minimum travel time in seconds to reach this H3 cell.

h3_max

NUMBER

Maximum travel time in seconds to reach this H3 cell.

h3_mean

NUMBER

Average travel time in seconds to reach this H3 cell.

Examples

SELECT h3, h3_min, h3_max, h3_mean
FROM TABLE(carto.H3_ISOLINE(
    SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-3.7038, 40.4168, NULL), NULL, NULL),
    'car', 900, 'time', 6
));
-- Returns one row per H3 cell reachable within 15 minutes by car from Madrid

-- Count cells and aggregate travel-time stats
SELECT COUNT(*) AS num_cells,
       MIN(h3_min) AS min_sec,
       MAX(h3_max) AS max_sec
FROM TABLE(carto.H3_ISOLINE(
    SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-3.7038, 40.4168, NULL), NULL, NULL),
    'car', 900, 'time', 6
));

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): VARCHAR2 url of the API where the customer account is stored.
api_access_token (optional): VARCHAR2 an API Access Token that is allowed to use the LDS API.

Return type

CLOB

Examples

SELECT carto.GET_LDS_QUOTA_INFO() FROM DUAL;
-- [
--   {
--     "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') FROM DUAL;
-- Same as above, but using explicit credentials instead of the defaults configured with SETUP.

Previoush3 Nextquadbin

Last updated 17 days ago

Was this helpful?