> For the complete documentation index, see [llms.txt](https://docs.carto.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.carto.com/data-and-analysis/analytics-toolbox-for-oracle/sql-reference/lds.md). # 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](https://docs.carto.com/carto-user-manual/developers/api-access-tokens) 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](https://docs.carto.com/carto-user-manual/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. {% hint style="info" %} **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: {% code overflow="wrap" lineNumbers="true" %} ```sql SELECT carto.GET_LDS_QUOTA_INFO() FROM DUAL; ``` {% endcode %} Note that if you haven't set up default LDS credentials with `SETUP` you'll need to provide them when calling the LDS functions: {% code overflow="wrap" lineNumbers="true" %} ```sql SELECT carto.GET_LDS_QUOTA_INFO( '', '' ) FROM DUAL; ``` {% endcode %} You should get a JSON response like this, with the available services and the quotas: ```json [ { "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. {% endhint %} ## GEOCODE\_TABLE ```sql GEOCODE_TABLE(input_table, address_column [, geom_column] [, country] [, options] [, api_base_url, api_access_token]) ``` {% hint style="warning" %} **warning** This function consumes LDS quota. Each call consumes as many units of quota as the number of rows 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`](#get_lds_quota_info) function. {% endhint %} **Description** Geocodes an input table by adding an user defined column `geom_column` with the geographic coordinates (latitude and longitude) corresponding to a given address column. This procedure also adds a `carto_geocode_metadata` column with additional information of the geocoding result in JSON format. 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: **10,000 rows** with TomTom, which is processed through the provider's asynchronous batch API, and **200 rows** with any other provider. Use the `carto_batch_size` option to override it. **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](https://en.wikipedia.org/wiki/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): `VARCHAR2` 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 overwrites the values already there and spends quota on every row with an address. | | `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 (10,000 with TomTom, 200 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. | | `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 `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](https://docs.carto.com/carto-user-manual/developers/api-access-tokens) that is allowed to use the LDS API. **Always set the country** {% hint style="warning" %} **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. {% endhint %} **Geocoding large tables** Geocoding millions of rows is a long-running operation, and a single `CALL` can be cut short before it finishes — by a resource-manager or profile limit, by a session being killed, or by cancelling the statement. 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**: {% code overflow="wrap" lineNumbers="true" %} ```sql SELECT COUNT(*) FROM . WHERE my_geom_column IS NULL AND my_address_column IS NOT NULL; ``` {% endcode %} That count is what remains, and it tracks a run in progress too: this procedure commits into the input table after every batch, so there is no separate table to watch. 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. {% hint style="info" %} Results are committed into the input table after every batch, with every provider. An interrupted run therefore keeps every row it had already geocoded, and the next run only spends quota on what is left. Do not run the same call twice at the same time: concurrent runs work through the same internal tables and will collide. Failures are equally loud: if a provider job keeps failing its status checks (3 consecutive failures, or the `carto_max_status_attempts` budget), the run stops with an error naming the job and batch instead of silently leaving rows ungeocoded. Re-running the same call afterwards geocodes only the rows still without a geometry. {% endhint %} {% hint style="warning" %} **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. {% endhint %} **Examples** {% code overflow="wrap" lineNumbers="true" %} ```sql CALL carto.GEOCODE_TABLE('.', 'my_address_column'); -- The table `.` will be updated -- adding the columns: geom, carto_geocode_metadata. ``` {% endcode %} {% code overflow="wrap" lineNumbers="true" %} ```sql CALL carto.GEOCODE_TABLE('.', 'my_address_column', 'my_geom_column'); -- The table `.` will be updated -- adding the columns: my_geom_column, carto_geocode_metadata. ``` {% endcode %} {% code overflow="wrap" lineNumbers="true" %} ```sql CALL carto.GEOCODE_TABLE('.', 'my_address_column', 'my_geom_column', 'my_country'); -- The table `.` will be updated -- adding the columns: my_geom_column, carto_geocode_metadata. ``` {% endcode %} {% code overflow="wrap" lineNumbers="true" %} ```sql CALL carto.GEOCODE_TABLE('.', 'my_address_column', 'my_geom_column', 'my_country', '{"language":"en-US"}'); -- The table `.` will be updated -- adding the columns: my_geom_column, carto_geocode_metadata. ``` {% endcode %} {% code overflow="wrap" lineNumbers="true" %} ```sql CALL carto.GEOCODE_TABLE('.', 'my_address_column', 'my_geom_column', 'my_country', '{"language":"en-US"}', 'my_api_base_url', 'my_api_access_token'); -- The table `.` will be updated -- adding the columns: my_geom_column, carto_geocode_metadata. ``` {% endcode %} ## GEOCODE\_REVERSE\_TABLE ```sql GEOCODE_REVERSE_TABLE(input_table [, geom_column] [, address_column] [, language] [, options] [, api_base_url, api_access_token]) ``` {% hint style="warning" %} **warning** This function consumes LDS quota. Each call consumes as many units of quota as the number of rows 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`](#get_lds_quota_info) function. {% endhint %} **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. That makes a re-run resume by construction: an interrupted run keeps every address it had already resolved, and the next one only spends quota on the rows still missing one. Do not run the same call twice at the same time: concurrent runs work through the same internal tables and will collide. While a run is in progress the procedure keeps two helper columns, `CARTO_ORIG_LON_` and `CARTO_ORIG_LAT_`, on the input table and removes them when it completes; a run that is killed leaves them behind, where the next run of the same call reuses them. They are safe to drop if you abandon the job. The table is reverse-geocoded sequentially, one batch of rows at a time, with a default batch size of **200 rows**. Use the `carto_batch_size` option to override it. **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. 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 200. Reverse geocoding requests are fixed-size points, so lowering it is only needed if the provider times out on full batches; a value above it is reduced to it and reported in the summary rather than rejected. | | `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. | * `api_base_url` (optional): `VARCHAR2` url of the API where the customer account is stored. * `api_access_token` (optional): `VARCHAR2` an [API Access Token](https://docs.carto.com/carto-user-manual/developers/api-access-tokens) that is allowed to use the LDS API. **Examples** {% code overflow="wrap" lineNumbers="true" %} ```sql CALL carto.GEOCODE_REVERSE_TABLE('.'); -- The table `.` with a column `geom` will be updated -- adding the column `address`. ``` {% endcode %} {% code overflow="wrap" lineNumbers="true" %} ```sql CALL carto.GEOCODE_REVERSE_TABLE('.', 'my_geom_column'); -- The table `.` with a column `my_geom_column` will be updated -- adding the column `address`. ``` {% endcode %} {% code overflow="wrap" lineNumbers="true" %} ```sql CALL carto.GEOCODE_REVERSE_TABLE('.', 'my_geom_column', 'my_address_column'); -- The table `.` with a column `my_geom_column` will be updated -- adding the column `my_address_column`. ``` {% endcode %} {% code overflow="wrap" lineNumbers="true" %} ```sql CALL carto.GEOCODE_REVERSE_TABLE('.', 'my_geom_column', 'my_address_column', 'en-US'); -- The 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. ``` {% endcode %} {% code overflow="wrap" lineNumbers="true" %} ```sql CALL carto.GEOCODE_REVERSE_TABLE('.', 'my_geom_column', 'my_address_column', 'en-US', '{}', 'my_api_base_url', 'my_api_access_token'); -- The 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. ``` {% endcode %} ## CREATE\_ISOLINES ```sql CREATE_ISOLINES(input_table, output_table, geom_column, travel_mode, range_value, range_type [, options] [, api_base_url, api_access_token]) ``` {% hint style="warning" %} **warning** This function consumes LDS quota. Each call consumes as many units of quota as the number of rows your input table or query has. Before running, we recommend checking the size of the data and your available quota using the [`GET_LDS_QUOTA_INFO`](#get_lds_quota_info) function. {% endhint %} **Description** Calculates the isolines (polygons) from given origins (points) in a table or query. It creates a new table with the columns of the input table or query except the `geom_column` plus the isolines in the column `geom` (if the input already contains a `geom` column, it will be overwritten). It calculates isolines sequentially in chunks of N rows, N being the optimal batch size for this datawarehouse and the specific LDS provider that you are using. Note that the term *isoline* is used here in a general way to refer to the areas that can be reached from a given origin point within the given travel time or distance (depending on the `range_type` parameter). 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. Isolines are requested one origin at a time: requests to the provider are not batched, and the `carto_batch_size` option is ignored. **Input parameters** * `input_table`: `VARCHAR2` name of the input table or query. * `output_table`: `VARCHAR2` qualified name of the output table, e.g. `.`. The process will fail if the table already exists, unless `carto_force_rebuild` is set. * `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. In addition to the provider options described in the table below, the following CARTO options are accepted: | CARTO option | Description | | ---------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `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 `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 `"T"`. | | `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 `"T"`. | | `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 `"T"`. | | `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](https://docs.traveltime.com/api/reference/isochrones#arrival_searches-level_of_detail) for more info. | | `TravelTime` | `departure_time` | Default: `"now"`. A `VARCHAR2` that specifies the time of departure. Supported: `"now"` and date-time as `"TZ"`. | * `api_base_url` (optional): `VARCHAR2` url of the API where the customer account is stored. * `api_access_token` (optional): `VARCHAR2` an [API Access Token](https://docs.carto.com/carto-user-manual/developers/api-access-tokens) that is allowed to use the LDS API. {% hint style="warning" %} **warning** Before running, we recommend checking your provider using the [`GET_LDS_QUOTA_INFO`](#get_lds_quota_info) function. Notice that some of the parameters are provider dependant. Please contact your CARTO representative if you have questions regarding the service provider configured in your organization. {% endhint %} **Resuming an interrupted job** While a job is unfinished its results live in a table named `_partial_` next to your output, where `` 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** {% code overflow="wrap" lineNumbers="true" %} ```sql CALL carto.CREATE_ISOLINES( '.', '.', 'my_geom_column', 'car', 300, 'time' ); -- The table `.` will be created -- with the columns of the input table except `my_geom_column`. -- Isolines will be added in the "geom" column. ``` {% endcode %} {% code overflow="wrap" lineNumbers="true" %} ```sql CALL carto.CREATE_ISOLINES( '.', '.', 'my_geom_column', 'car', 1000, 'distance' ); -- The table `.` will be created -- with the columns of the input table except `my_geom_column`. -- Isolines will be added in the "geom" column. ``` {% endcode %} {% code overflow="wrap" lineNumbers="true" %} ```sql CALL carto.CREATE_ISOLINES( '.', '.', 'my_geom_column', 'car', 300, 'time', '{"polygons_filter": {"limit": 1}}', 'my_api_base_url', 'my_api_access_token' ); -- The table `.` will be created -- with the columns of the input table except `my_geom_column`. -- Isolines will be added in the "geom" column. ``` {% endcode %} ## CREATE\_H3\_ISOLINES ```sql CREATE_H3_ISOLINES(input_table, output_table, geom_column, travel_mode, range_value, range_type, resolution [, options] [, api_base_url, api_access_token]) ``` {% hint style="warning" %} **warning** This function consumes LDS quota. Each call consumes as many units of quota as the number of rows your input table or query has. Before running, we recommend checking the size of the data and your available quota using the [`GET_LDS_QUOTA_INFO`](#get_lds_quota_info) function. {% endhint %} {% hint style="warning" %} **warning** This procedure requires the TravelTime provider. If your organization uses a different isolines provider, the procedure will raise an error. {% endhint %} **Description** Calculates H3 isolines (isochrones represented as H3 cells) from given origins (points) in a table or query. Unlike the standard `CREATE_ISOLINES` procedure which returns polygon geometries, this procedure returns an array of H3 cells with travel time information for each cell. It creates a new table with the columns of the input table or query except the `geom_column`, with each H3 cell unnested into a separate row containing `h3`, `h3_min`, `h3_max`, and `h3_mean` as scalar values. It calculates H3 isolines sequentially in chunks of N rows, N being the optimal batch size for this datawarehouse. The output table will contain a column named `carto_isoline_metadata` with error information for each isoline result. Rows with errors will have NULL values in the H3 columns. 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. Isolines are requested one origin at a time: requests to the provider are not batched, and the `carto_batch_size` option is ignored. **Input parameters** * `input_table`: `VARCHAR2` name of the input table or query. * `output_table`: `VARCHAR2` qualified name of the output table, e.g. `.`. The process will fail if the table already exists, unless `carto_force_rebuild` is set. * `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. In addition to the provider options described in the table below, the following CARTO options are accepted: | CARTO option | Description | | ---------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `carto_keep_orig_geom` | a boolean, `false` by default. When `true` the output keeps the geometry each result was computed from, in a `CARTO_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](https://docs.traveltime.com/api/reference/isochrones#arrival_searches-level_of_detail) for more info. | | `TravelTime` | `departure_time` | Default: `"now"`. A `VARCHAR2` that specifies the time of departure. Supported: `"now"` and date-time as `"TZ"`. | * `api_base_url` (optional): `VARCHAR2` url of the API where the customer account is stored. * `api_access_token` (optional): `VARCHAR2` an [API Access Token](https://docs.carto.com/carto-user-manual/developers/api-access-tokens) that is allowed to use the LDS API. **Output Table Schema** The output table will contain all columns from the input table except `geom_column`, with each H3 cell as a separate row: | Column | Type | Description | | ------------------------ | ---------- | ------------------------------------------------ | | `h3` | `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. | **Resuming an interrupted job** While a job is unfinished its results live in a table named `_partial_` next to your output, where `` 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** {% code overflow="wrap" lineNumbers="true" %} ```sql CALL carto.CREATE_H3_ISOLINES( '.', '.', 'my_geom_column', 'car', 900, 'time', 6 ); -- The 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. ``` {% endcode %} {% code overflow="wrap" lineNumbers="true" %} ```sql CALL carto.CREATE_H3_ISOLINES( '.', '.', 'my_geom_column', 'walk', 600, 'time', 7, '{"departure_time": "2024-01-15T09:00:00Z"}' ); -- The table `.` will be created -- with H3 cells at resolution 7 for 10-minute walking isochrones. ``` {% endcode %} {% code overflow="wrap" lineNumbers="true" %} ```sql CALL carto.CREATE_H3_ISOLINES( '.', '.', 'my_geom_column', 'car', 900, 'time', 6, NULL, 'my_api_base_url', 'my_api_access_token' ); -- The table `.` will be created -- with H3 cells using explicit API credentials. ``` {% endcode %} ## CREATE\_ROUTES ```sql CREATE_ROUTES(input_table, output_table, geom_column, travel_mode [, options] [, api_base_url, api_access_token]) ``` {% hint style="warning" %} **warning** This function consumes LDS quota. Each call consumes as many units of quota as the number of rows your input query has. Before running, we recommend checking the size of the data to be geocoded and your available quota using the [`GET_LDS_QUOTA_INFO`](#get_lds_quota_info) function. {% endhint %} **Description** Calculates the routes (line strings) between given origins and destinations (points) in a query. It creates a new table with the columns of the input query with the resulting route in the user defined column `geom_column` (if the input already contains a column named `geom_column`, it will be overwritten) and a `carto_routing_metadata` column with the response of the service provider except for the route geometry. 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. Routes are calculated sequentially, one batch at a time. The default batch size depends on the routing provider configured for your account: **20 routes** with TomTom and **10 routes** with any other provider. Use the `carto_batch_size` option to override it. **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. `.`. The process will fail if the table already exists, unless `carto_force_rebuild` is set. * `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`](#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 20 with TomTom and 10 with any other provider. Lower it when a batch fails because the request is too large, which happens with long waypoint lists; a value above it 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. | The following are some of the most common parameters for each provider: * `TomTom`: * `avoid`: Specifies something that the route calculation should try to avoid when determining the route. Possible values (several of them can be used at the same time): * `tollRoads` * `motorways` * `ferries` * `unpavedRoads` * `carpools` * `alreadyUsedRoads` * `borderCrossings` * `tunnels` * `carTrains` * `lowEmissionZones` * `routeType`: Specifies the type of optimization used when calculating routes. Possible values: `fastest`, `shortest`, `short`, `eco`, `thrilling` * `traffic`: Set to true `true` to consider all available traffic information during routing. Set to `false` otherwise * `departAt`: The date and time of departure at the departure point. It should be specified in RFC 3339 format with an optional time zone offset. * `arriveAt`: The date and time of arrival at the destination point. It should be specified in RFC 3339 format with an optional time zone offset. * `vehicleMaxspeed`: Maximum speed of the vehicle in kilometers/hour. * `HERE` * `avoid`: Elements or areas to avoid. Information about avoidance can be found [here](https://developer.here.com/documentation/routing-api/dev_guide/topics/use-cases/avoid.html) * `departureTime`: The date and time of departure at the departure point. It should be specified in RFC 3339 format with an optional time zone offset. * `arrivalTime`: The date and time of arrival at the destination point. It should be specified in RFC 3339 format with an optional time zone offset. * `language`: The language to use. Supported language codes can be found [here](https://developer.here.com/documentation/routing-api/dev_guide/topics/languages.html) * `TravelTime` * `snap_penalty`: Controls whether walking time and distance from the departure location to the nearest road and from the nearest road to the arrival location are included in the route. Possible values: `enabled` (walking segments are added to the total travel time and distance), `disabled` (journey effectively starts and ends at the nearest points on the road network). Defaults to `disabled` for driving modes and `enabled` for other modes. For more advanced usage, check the documentation of your provider's routing API. * [`TomTom`](https://developer.tomtom.com/routing-api/documentation/routing/common-routing-parameters) * [`HERE`](https://developer.here.com/documentation/routing-api/dev_guide/index.html) * [`TravelTime`](https://docs.traveltime.com/api/reference/routes) * `api_base_url` (optional): `VARCHAR2` url of the API where the customer account is stored. * `api_access_token` (optional): `VARCHAR2` an [API Access Token](https://docs.carto.com/carto-user-manual/developers/api-access-tokens) that is allowed to use the LDS API. {% hint style="warning" %} **warning** Before running, we recommend checking your provider using the [`GET_LDS_QUOTA_INFO`](#get_lds_quota_info) function. Notice that some of the parameters are provider dependant. Please contact your CARTO representative if you have questions regarding the service provider configured in your organization. {% endhint %} **Resuming an interrupted job** While a job is unfinished its results live in a table named `_partial_` next to your output, where `` 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** {% code overflow="wrap" lineNumbers="true" %} ```sql CALL carto.CREATE_ROUTES( '.', '.', 'my_geom_column', 'car' ); -- The 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. ``` {% endcode %} {% code overflow="wrap" lineNumbers="true" %} ```sql CALL carto.CREATE_ROUTES( '.', '.', 'my_geom_column', 'car', '{"arriveAt":"2023-06-11T19:00:00+02:00"}' ); -- The 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. ``` {% endcode %} {% code overflow="wrap" lineNumbers="true" %} ```sql CALL carto.CREATE_ROUTES( '.', '.', 'my_geom_column', 'car', '{"arriveAt":"2023-06-11T19:00:00+02:00"}', 'my_api_base_url', 'my_api_access_token' ); -- The 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. ``` {% endcode %} ## CREATE\_ROUTING\_MATRIX ```sql CREATE_ROUTING_MATRIX(origins_table, origins_geom_column, destinations_table, destinations_geom_column, output_table, travel_mode [, options] [, api_base_url, api_access_token]) ``` {% hint style="warning" %} **warning** This function consumes LDS quota. Each call consumes as many units of quota as the number of rows of the origins table multiplied by the rows of destinations table. Before running, we recommend checking the size of the data used to create the routes and your available quota using the [`GET_LDS_QUOTA_INFO`](#get_lds_quota_info) function. {% endhint %} **Description** Calculates the routes (line strings) between given origins and destinations (points) in two table. It creates a new table with the cross join of the origin and destination geom columns adding the distance or time between the different routes. A `carto_routing_matrix_metadata` column will also be added containing pottential errors thrown by the service provider for particular origin-destination combinations. Note that routes are calculated using the external LDS provider assigned to your CARTO account. Currently TomTom and TravelTime are supported. Note that the parameter is named `travel_mode` instead of `mode` because `mode` is a reserved word in Oracle. 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. The `carto_batch_size` option applies the same value to both dimensions when the dimension-specific options are not set. **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. `.`. The process will fail if the table already exists, unless `carto_force_rebuild` is set. * `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`](#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 `"T"`. | | `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 `"TZ"`. | | `TravelTime` | `travel_time` | Default: `14400` (4 hours). A `NUMBER` that specifies the maximum travel time in seconds. Supported: a value in the range \[0, 14400]. | For more advanced usage, check the documentation of your provider's matrix routing API. * [`TomTom`](https://developer.tomtom.com/matrix-routing-v2-api/documentation/asynchronous-matrix-submission) * [`TravelTime`](https://docs.traveltime.com/api/reference/travel-time-distance-matrix#departure_searches) * `api_base_url` (optional): `VARCHAR2` url of the API where the customer account is stored. * `api_access_token` (optional): `VARCHAR2` an [API Access Token](https://docs.carto.com/carto-user-manual/developers/api-access-tokens) that is allowed to use the LDS API. The following are options provided by CARTO in order to adjust the procedure performance: | 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_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. | | `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. | {% hint style="warning" %} **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. {% endhint %} {% hint style="warning" %} **warning** Before running, we recommend checking your provider using the [`GET_LDS_QUOTA_INFO`](#get_lds_quota_info) function. Notice that some of the parameters are provider dependant. Please contact your CARTO representative if you have questions regarding the service provider configured in your organization. {% endhint %} **Return type** The results are stored in the table named ``, 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. **Resuming an interrupted job** While a job is unfinished its results live in a table named `_partial_` next to your output, where `` 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** {% code overflow="wrap" lineNumbers="true" %} ```sql CALL carto.CREATE_ROUTING_MATRIX( '.', 'my_origins_geom_column', '.', 'my_destinations_geom_column', '.', 'car' ); -- The table `.` will be created -- with the columns from both input tables, `route_distance`, `route_duration` and `carto_routing_matrix_metadata`. ``` {% endcode %} Examples with TomTom specific parameters: {% code overflow="wrap" lineNumbers="true" %} ```sql CALL carto.CREATE_ROUTING_MATRIX( '.', 'my_origins_geom_column', '.', 'my_destinations_geom_column', '.', 'car', '{"departAt":"now"}' ); -- The table `.` will be created -- with the columns from both input tables, `route_distance`, `route_duration` and `carto_routing_matrix_metadata`. ``` {% endcode %} {% code overflow="wrap" lineNumbers="true" %} ```sql CALL carto.CREATE_ROUTING_MATRIX( '.', 'my_origins_geom_column', '.', 'my_destinations_geom_column', '.', 'car', '{"departAt":"now"}', 'my_api_base_url', 'my_api_access_token' ); -- The table `.` will be created -- with the columns from both input tables, `route_distance`, `route_duration` and `carto_routing_matrix_metadata`. ``` {% endcode %} Example with batch sizes: {% code overflow="wrap" lineNumbers="true" %} ```sql CALL carto.CREATE_ROUTING_MATRIX( '.', 'my_origins_geom_column', '.', 'my_destinations_geom_column', '.', 'car', '{"carto_origins_batch_size": 10, "carto_destinations_batch_size": 1000}' ); -- The table `.` will be created -- with the columns from both input tables, `route_distance`, `route_duration` and `carto_routing_matrix_metadata`. ``` {% endcode %} ## GEOCODE ```sql GEOCODE(address [, country] [, options] [, api_base_url, api_access_token]) ``` {% hint style="warning" %} **warning** This function consumes LDS quota. Each call consumes one unit of quota. Before running, check the size of the data to be geocoded and make sure you store the result in a table to avoid misuse of the quota. To check the information about available and consumed quota use the function [`GET_LDS_QUOTA_INFO`](#get_lds_quota_info). **We recommend using this function only with an input of up to 10 records. In order to geocode larger sets of addresses, we strongly recommend using the** [**`GEOCODE_TABLE`**](#geocode_table) **procedure. Likewise, in order to materialize the results in a table.** {% endhint %} **Description** Geocodes an address into 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](https://en.wikipedia.org/wiki/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](https://docs.carto.com/carto-user-manual/developers/api-access-tokens) that is allowed to use the LDS API. **Return type** `SDO_GEOMETRY` **Examples** {% code overflow="wrap" lineNumbers="true" %} ```sql SELECT carto.GEOCODE('Madrid') FROM DUAL; -- SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-3.7038, 40.4168, NULL), NULL, NULL) ``` {% endcode %} {% code overflow="wrap" lineNumbers="true" %} ```sql SELECT carto.GEOCODE('Madrid', 'es') FROM DUAL; -- SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-3.7038, 40.4168, NULL), NULL, NULL) ``` {% endcode %} {% code overflow="wrap" lineNumbers="true" %} ```sql SELECT carto.GEOCODE('Madrid', 'es', '{"language":"es-ES"}') FROM DUAL; -- SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-3.7038, 40.4168, NULL), NULL, NULL) ``` {% endcode %} {% code overflow="wrap" lineNumbers="true" %} ```sql 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) ``` {% endcode %} {% code overflow="wrap" lineNumbers="true" %} ```sql CREATE TABLE my_geocoded_table AS SELECT address, carto.GEOCODE(address) AS geom FROM my_table; -- The table my_geocoded_table will be created ``` {% endcode %} ## GEOCODE\_REVERSE ```sql GEOCODE_REVERSE(geom [, language] [, options] [, api_base_url, api_access_token]) ``` {% hint style="warning" %} **warning** This function consumes LDS quota. Each call consumes one unit of quota. Before running, check the size of the data to be reverse geocoded and make sure you store the result in a table to avoid misuse of the quota. To check the information about available and consumed quota use the function [`GET_LDS_QUOTA_INFO`](#get_lds_quota_info). **We recommend using this function only with an input of up to 10 records. In order to reverse-geocode larger sets of locations, we strongly recommend using the** [**`GEOCODE_REVERSE_TABLE`**](#geocode_reverse_table) **procedure. Likewise, in order to materialize the results in a table.** {% endhint %} **Description** Performs a reverse geocoding of the point received as input. **Input parameters** * `geom`: `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](https://docs.carto.com/carto-user-manual/developers/api-access-tokens) that is allowed to use the LDS API. **Return type** `VARCHAR2` **Examples** {% code overflow="wrap" lineNumbers="true" %} ```sql 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 ``` {% endcode %} {% code overflow="wrap" lineNumbers="true" %} ```sql 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 ``` {% endcode %} ## ISOLINE ```sql ISOLINE(origin, travel_mode, range_value, range_type [, options] [, api_base_url, api_access_token]) ``` {% hint style="warning" %} **warning** This function consumes LDS quota. Each call consumes one unit quota. Before running, check the size of the data and make sure you store the result in a table to avoid misuse of the quota. To check the information about available and consumed quota use the function [`GET_LDS_QUOTA_INFO`](#get_lds_quota_info). **We recommend using this function only with an input of up to 10 records. In order to calculate isolines for larger sets of locations, we strongly recommend using the** [**`CREATE_ISOLINES`**](#create_isolines) **procedure. Likewise, in order to materialize the results in a table.** {% endhint %} **Description** 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 `"T"`. | | `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 `"T"`. | | `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 `"T"`. | | `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](https://docs.traveltime.com/api/reference/isochrones#arrival_searches-level_of_detail) for more info. | | `TravelTime` | `departure_time` | Default: `"now"`. A `VARCHAR2` that specifies the time of departure. Supported: `"now"` and date-time as `"TZ"`. | * `api_base_url` (optional): `VARCHAR2` url of the API where the customer account is stored. * `api_access_token` (optional): `VARCHAR2` an [API Access Token](https://docs.carto.com/carto-user-manual/developers/api-access-tokens) that is allowed to use the LDS API. {% hint style="warning" %} **warning** Before running, we recommend checking your provider using the [`GET_LDS_QUOTA_INFO`](#get_lds_quota_info) function. Notice that some of the parameters are provider dependant. Please contact your CARTO representative if you have questions regarding the service provider configured in your organization. {% endhint %} **Return type** `SDO_GEOMETRY` **Examples** {% code overflow="wrap" lineNumbers="true" %} ```sql 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 ``` {% endcode %} {% code overflow="wrap" lineNumbers="true" %} ```sql 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 ``` {% endcode %} ## H3\_ISOLINE ```sql H3_ISOLINE(origin, travel_mode, range_value, range_type, resolution [, options] [, api_base_url, api_access_token]) ``` {% hint style="warning" %} **warning** This function consumes LDS quota. Each call consumes one unit quota. Before running, check the size of the data and make sure you store the result in a table to avoid misuse of the quota. To check the information about available and consumed quota use the function [`GET_LDS_QUOTA_INFO`](#get_lds_quota_info). **We recommend using this function only with an input of up to 10 records. In order to calculate H3 isolines for larger sets of locations, we strongly recommend using the** [**`CREATE_H3_ISOLINES`**](#create_h3_isolines) **procedure. Likewise, in order to materialize the results in a table.** {% endhint %} {% hint style="warning" %} **warning** This function requires the TravelTime provider. If your organization uses a different isolines provider, the function will raise an error. {% endhint %} **Description** Calculates H3 isolines (isochrones represented as H3 cells) from a given point. Unlike the standard `ISOLINE` function which returns a polygon geometry, this function returns 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](https://docs.traveltime.com/api/reference/isochrones#arrival_searches-level_of_detail) for more info. | | `TravelTime` | `departure_time` | Default: `"now"`. A `VARCHAR2` that specifies the time of departure. Supported: `"now"` and date-time as `"TZ"`. | * `api_base_url` (optional): `VARCHAR2` url of the API where the customer account is stored. * `api_access_token` (optional): `VARCHAR2` an [API Access Token](https://docs.carto.com/carto-user-manual/developers/api-access-tokens) that is allowed to use the LDS API. **Return type** 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** {% code overflow="wrap" lineNumbers="true" %} ```sql 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 ``` {% endcode %} {% code overflow="wrap" lineNumbers="true" %} ```sql -- 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 )); ``` {% endcode %} ## GET\_LDS\_QUOTA\_INFO ```sql GET_LDS_QUOTA_INFO([api_base_url, api_access_token]) ``` **Description** Returns statistics about the LDS quota. LDS quota is an annual quota that defines how much geocoding and isolines you can compute. Each geocoded row or computed isolines counts as one LDS quota unit. The single element in the result of GET\_LDS\_QUOTA\_INFO will show your LDS quota for the current annual period (annual\_quota), how much you've spent (used\_quota), and which LDS providers are in use. **Input parameters** * `api_base_url` (optional): `VARCHAR2` url of the API where the customer account is stored. * `api_access_token` (optional): `VARCHAR2` an [API Access Token](https://docs.carto.com/carto-user-manual/developers/api-access-tokens) that is allowed to use the LDS API. **Return type** `CLOB` **Examples** {% code overflow="wrap" lineNumbers="true" %} ```sql SELECT carto.GET_LDS_QUOTA_INFO() FROM DUAL; -- [ -- { -- "used_quota": 10, -- "annual_quota": 100000, -- "providers": { -- "geocoding": "tomtom", -- "isolines": "here", -- "routing":"tomtom" -- } -- } -- ] ``` {% endcode %} {% code overflow="wrap" lineNumbers="true" %} ```sql 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. ``` {% endcode %} --- # Agent Instructions This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com. ## Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation by asking a question. Perform an HTTP GET request on the following URL with the `ask` and `goal` query parameters: ``` GET https://docs.carto.com/data-and-analysis/analytics-toolbox-for-oracle/sql-reference/lds.md?ask=&goal= ``` `ask` is the immediate question: it should be specific, self-contained, and written in natural language. `goal` is what the user is ultimately trying to achieve, the reason they need the answer. Sharing it helps GitBook give you a better, more relevant answer. A goal is most helpful when it describes the outcome the user wants rather than restating the question. For example, with `ask=how do I create an API token`, a goal like `automate deployments from our CI pipeline` lets GitBook tailor the answer to that use case. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.