lds
ADVANCED BETA
This module contains functions and procedures that make use of location data services, such as geocoding, reverse geocoding and isolines computation.
GEOCODE_TABLE(input_table, address_column [, geom_column] [, country] [, options])
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
LDS_QUOTA_INFO
function.Description
Geocodes an input table by adding a column
geom
with the geographic coordinates (latitude and longitude) corresponding to a given address column. This procedure also adds a carto_geocode_metadata
column with additional information of the geocoding result in JSON format. It geocodes sequentially the table in chunks of 100 rows.input_table
:VARCHAR(MAX)
name of the table to be geocoded. Please make sure you have enough permissions to alter this table, as this procedure will add two columns to it to store the geocoding result.address_column
:VARCHAR(MAX)
name of the column from the input table that contains the addresses to be geocoded.geom_column
(optional):VARCHAR(MAX)
column name for the output geometry column. Defaults to'geom'
.options
(optional):VARCHAR(MAX)
containing a valid JSON with the different options. Valid options are described in the table below. If no options are indicated then 'default' values would be applied.ProviderOptionDescriptionAll
language
AVARCHAR
that specifies the language of the geocoding in RFC 4647 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.Examples
1
CALL carto.GEOCODE_TABLE('my-schema.my-table', 'my_address_column');
2
-- The table `my-schema.my-table` will be updated
3
-- adding the columns: geom, carto_geocode_metadata.
1
CALL carto.GEOCODE_TABLE('my-schema.my-table', 'my_address_column', 'my_geom_column');
2
-- The table `my-schema.my-table` will be updated
3
-- adding the columns: my_geom_column, carto_geocode_metadata.
1
CALL carto.GEOCODE_TABLE('my-schema.my-table', 'my_address_column', 'my_geom_column', 'my_country');
2
-- The table `my-schema.my-table` will be updated
3
-- adding the columns: my_geom_column, carto_geocode_metadata.
1
CALL carto.GEOCODE_TABLE('my-schema.my-table', 'my_address_column', 'my_geom_column', 'my_country', '{"language":"en-US"}');
2
-- The table `my-schema.my-table` will be updated
3
-- adding the columns: my_geom_column, carto_geocode_metadata.
Additional examples
GEOCODE_REVERSE_TABLE(input_table [, geom_column] [, address_column] [, language] [, options])
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
LDS_QUOTA_INFO
function.Description
Reverse-geocodes an input table by adding a column
address
with the addresses for a given point location column. It geocodes sequentially the table in chunks of 10 rows.input_table
:VARCHAR(MAX)
name of the table to be reverse-geocoded. Please make sure you have enough permissions to alter this table, as this procedure will add two columns to it to store the geocoding result.geom_column
(optional):GEOMETRY
column name for the geometry column that contains the points to be reverse-geocoded. Defaults to'geom'
.address_column
:VARCHAR(MAX)
name of the column where the computed addresses will be stored. It defaults to'address'
, and it is created on the input table if it doesn't exist.language
(optional):VARCHAR(MAX)
language in which results should be returned. Defaults to''
. The effect and interpretation of this parameter depends on the LDS provider assigned to your account.options
(optional):VARCHAR(MAX)
containing a valid JSON with the different options. No options are allowed currently, so this value will not be taken into account.
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.Examples
1
CALL carto.GEOCODE_REVERSE_TABLE('my-schema.my-table');
2
-- The table `my-schema.my-table` with a column `geom` will be updated
3
-- adding the column `address`.
1
CALL carto.GEOCODE_REVERSE_TABLE('my-schema.my-table', 'my_geom_column');
2
-- The table `my-schema.my-table` with a column `my_geom_column` will be updated
3
-- adding the column `address`.
1
CALL carto.GEOCODE_REVERSE_TABLE('my-schema.my-table', 'my_geom_column', 'my_address_column');
2
-- The table `my-schema.my-table` with a column `my_geom_column` will be updated
3
-- adding the column `my_address_column`.
1
CALL carto.GEOCODE_REVERSE_TABLE('my-schema.my-table', 'my_geom_column', 'my_address_column', 'en-US');
2
-- The table `my-schema.my-table` with a column `my_geom_column` will be updated
3
-- adding the column `my_address_column`.
4
-- The addresses will be in the (US) english language, if supported by the account LDS provider.
CREATE_ISOLINES(input, output_table, geom_column, mode, range, range_type [, options])
warning
This function consumes LDS quota. Each call consumes as many units of quota as the number of rows your input table or query has. Before running, we recommend checking the size of the data to be geocoded and your available quota using the
LDS_QUOTA_INFO
function.Description
Calculates the isolines (polygons) from given origins (points) in a table or query. It creates a new table with the columns of the input table or query except the
geom_column
plus the isolines in the column geom
(if the input already contains a geom
column, it will be overwritten). It calculates isolines sequentially in chunks of 100 rows.Note that The term isoline is used here in a general way to refer to the areas that can be reached from a given origin point within the given travel time or distance (depending on the
range_type
parameter).input
:VARCHAR(MAX)
name of the input table or query.output_table
:VARCHAR(MAX)
name of the output table. It will raise an error if the table already exists.geom_column
:VARCHAR(MAX)
column name for the origin geometry column.mode
:VARCHAR(MAX)
type of transport. The supported modes depend on the provider:Here
: 'walk', 'car', 'truck', 'taxi', 'bus' and 'private_bus'.Mapbox
: 'walk', 'car' and 'bike'.TomTom
: 'walk', 'car', 'bike', 'motorbike', 'truck', 'taxi', 'bus' and 'van'.
range
:INT
range of the isoline in seconds (forrange_type
'time') or meters (forrange_type
'distance').range_type
:VARCHAR(MAX)
type of range. Supported: 'time' (for isochrones), 'distance' (for isodistances).options
(optional):VARCHAR(MAX)
containing a valid JSON with the different options. Valid options are described in the table below. If no options are indicated then 'default' values would be applied.ProviderOptionDescriptionHere
arrival_time
AVARCHAR
that specifies the time of arrival. If the value is set, a reverse isoline is calculated. If"any"
is introduced, then time-dependent effects will not be taken into account. It cannot be used in combination withdeparture_time
. Supported:"any"
,"now"
and date-time as"<YYYY-MM-DD>T<hh:mm:ss>"
.Here
departure_time
Default:"now"
. AVARCHAR
that specifies the time of departure. If"any"
is introduced, then time-dependent effects will not be taken into account. It cannot be used in combination witharrival_time
. Supported:"any"
,"now"
and date-time as"<YYYY-MM-DD>T<hh:mm:ss>"
.Here
optimize_for
Default:"balanced"
. AVARCHAR
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"
. AVARCHAR
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"
. AVARCHAR
that specifies the time of departure. If"any"
is introduced, then time-dependent effects will not be taken into account. Supported:"any"
,"now"
and date-time as"<YYYY-MM-DD>T<hh:mm:ss>"
.TomTom
traffic
Default:true
. ABOOLEAN
that specifies if all available traffic information will be taken into consideration. Supported:true
andfalse
.
warning
Before running, we recommend checking your provider using the
LDS_QUOTA_INFO
function. Notice that some of the parameters are provider dependant. Please contact your CARTO representative if you have questions regarding the service provider configured in your organization.Examples
1
CALL carto.CREATE_ISOLINES(
2
'my-schema.my-table',
3
'my-schema.my-output-table',
4
'my_geom_column',
5
'car', 60, 'time'
6
);
7
-- The table `my-schema.my-output-table` will be created
8
-- with the columns of the input table except `my_geom_column`.
9
-- Isolines will be added in the "geom" column.
1
CALL carto.CREATE_ISOLINES(
2
'my-schema.my-table',
3
'my-schema.my-output-table',
4
'my_geom_column',
5
'car', 60, 'time',
6
'{"departure_time":"any"}'
7
);
8
-- The table `my-schema.my-output-table` will be created
9
-- with the columns of the input table except `my_geom_column`.
10
-- Isolines will be added in the "geom" column.
Additional examples
GEOCODE(address [, country] [, options])
warning
This function consumes LDS quota. Each call consumes one unit of quota. Before running, check the size of the data to be geocoded and make sure you store the result in a table to avoid misuse of the quota. To check the information about available and consumed quota use the function
LDS_QUOTA_INFO
.We recommend using this function only with an input of up to 10 records. In order to geocode larger sets of addresses, we strongly recommend using the
GEOCODE_TABLE
procedure. Likewise, in order to materialize the results in a table.Description
Geocodes an address into a point with its geographic coordinates (latitude and longitude).
address
:VARCHAR(MAX)
input address to geocode.options
(optional):VARCHAR(MAX)
containing a valid JSON with the different options. Valid options are described in the table below. If no options are indicated then 'default' values would be applied.ProviderOptionDescriptionAll
language
AVARCHAR
that specifies the language of the geocoding in RFC 4647 format.
Return type
GEOMETRY
Constraints
This function performs requests to the CARTO Location Data Services API. Redshift makes parallel requests depending on the number of records you are processing, potentially hitting the limit of the number of requests per seconds allowed for your account. The payload size of these requests depends on the number of records and could cause a timeout in the external function, with the error message
External function timeout
. The limit is around 500 records but could vary with the provider. To avoid this error, please try geocoding smaller volumes of data or using the procedure GEOCODE_TABLE
instead. This procedure manages concurrency and payload size to avoid exceeding this limit.Examples
1
SELECT carto.GEOCODE('Madrid');
2
-- POINT(109.590465335923 34.1733770650093)
1
SELECT carto.GEOCODE('Madrid', 'es');
2
-- POINT(51.405967078794 20.3365500266832)
1
SELECT carto.GEOCODE('Madrid', 'es', '{"language":"es-ES"}');
2
-- POINT(51.405967078794 20.3365500266832)
1
CREATE TABLE my_schema.my_geocoded_table AS
2
SELECT address, carto.GEOCODE(address) AS geom FROM my_table
3
-- Table my_geocoded_table successfully created.
GEOCODE_REVERSE(geom [, language] [, options])
warning
This function consumes LDS quota. Each call consumes one unit of quota. Before running, check the size of the data to be reverse geocoded and make sure you store the result in a table to avoid misuse of the quota. To check the information about available and consumed quota use the function
LDS_QUOTA_INFO
We recommend using this function only with an input of up to 10 records. In order to reverse-geocode larger sets of locations, we strongly recommend using the
GEOCODE_REVERSE_TABLE
procedure. Likewise, in order to materialize the results in a table.Description
Performs a reverse geocoding of the point received as input.
geom
:GEOMETRY
input point for which to obtain the address.language
(optional):VARCHAR(MAX)
language in which results should be returned.options
(optional):VARCHAR(MAX)
containing a valid JSON with the different options. No options are allowed currently, so this value will not be taken into account.
Return type
VARCHAR(MAX)
Constraints
This function performs requests to the CARTO Location Data Services API. Redshift makes parallel requests depending on the number of records you are processing, potentially hitting the limit of the number of requests per seconds allowed for your account. The payload size of these requests depends on the number of records and could cause a timeout in the external function, with the error message
External function timeout
. The limit is around 500 records but could vary with the provider. To avoid this error, please try processing smaller volumes of data.Example
1
SELECT carto.GEOCODE_REVERSE(ST_POINT(-74.0060, 40.7128));
2
-- 254 Broadway, New York, NY 10007, USA
ISOLINE(origin, mode, range, range_type [, options])
warning
This function consumes LDS quota. Each call consumes one unit quota. Before running, check the size of the data and make sure you store the result in a table to avoid misuse of the quota. To check the information about available and consumed quota use the function
LDS_QUOTA_INFO
.We recommend using this function only with an input of up to 10 records. In order to calculate isolines for larger sets of locations, we strongly recommend using the
CREATE_ISOLINES
procedure. Likewise, in order to materialize the results in a table.Description
Calculates the isoline polygon from a given point.
origin
:GEOMETRY
origin point of the isoline.mode
:VARCHAR(MAX)
type of transport. The supported modes depend on the provider:Here
: 'walk', 'car', 'truck', 'taxi', 'bus' and 'private_bus'.Mapbox
: 'walk', 'car' and 'bike'.TomTom
: 'walk', 'car', 'bike', 'motorbike', 'truck', 'taxi', 'bus' and 'van'.
range
:INT
range of the isoline in seconds (forrange_type
'time') or meters (forrange_type
'distance').range_type
:VARCHAR(MAX)
type of range. Supported: 'time' (for isochrones), 'distance' (for isodistances).options
(optional):VARCHAR(MAX)
containing a valid JSON with the different options. Valid options are described in the table below. If no options are indicated then 'default' values would be applied.ProviderOptionDescriptionHere
arrival_time
AVARCHAR
that specifies the time of arrival. If the value is set, a reverse isoline is calculated. If"any"
is introduced, then time-dependent effects will not be taken into account. It cannot be used in combination withdeparture_time
. Supported:"any"
,"now"
and date-time as"<YYYY-MM-DD>T<hh:mm:ss>"
.Here
departure_time
Default:"now"
. AVARCHAR
that specifies the time of departure. If"any"
is introduced, then time-dependent effects will not be taken into account. It cannot be used in combination witharrival_time
. Supported:"any"
,"now"
and date-time as"<YYYY-MM-DD>T<hh:mm:ss>"
.Here
optimize_for
Default:"balanced"
. AVARCHAR
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"
. AVARCHAR
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"
. AVARCHAR
that specifies the time of departure. If"any"
is introduced, then time-dependent effects will not be taken into account. Supported:"any"
,"now"
and date-time as"<YYYY-MM-DD>T<hh:mm:ss>"
.TomTom
traffic
Default:true
. ABOOLEAN
that specifies if all available traffic information will be taken into consideration. Supported:true
andfalse
.
warning
Before running, we recommend checking your provider using the
LDS_QUOTA_INFO
function. Notice that some of the parameters are provider dependant. Please contact your CARTO representative if you have questions regarding the service provider configured in your organization.Return type
GEOMETRY
Constraints
This function performs requests to the CARTO Location Data Services API. Redshift makes parallel requests depending on the number of records you are processing, potentially hitting the limit of the number of requests per seconds allowed for your account. The payload size of these requests depends on the number of records and could cause a timeout in the external function, with the error message
External function timeout
. The limit is around 500 records but could vary with the provider. To avoid this error, please try processing smaller volumes of data.Examples
1
SELECT carto.ISOLINE(ST_POINT(13.37749, 52.51578), 'car', 10, 'time');
2
-- POLYGON ((13.377142 52.516537, 13.377399 52.516193, 13.377743 52.51585, 13.377914 52.515335, 13.377743 52.51482, 13.377399 52.51482, 13.376713 52.515507, 13.376541 52.516022, 13.376627 52.516537, 13.376884 52.516708, 13.377142 52.516537))
1
SELECT carto.ISOLINE(ST_POINT(13.37749, 52.51578), 'car', 10, 'time', '{"departure_time":"any"}');
2
-- POLYGON ((13.377142 52.516537, 13.377399 52.516193, 13.377743 52.51585, 13.377914 52.515335, 13.377743 52.51482, 13.377399 52.51482, 13.376713 52.515507, 13.376541 52.516022, 13.376627 52.516537, 13.376884 52.516708, 13.377142 52.516537))
LDS_QUOTA_INFO()
Description
Returns statistics about the LDS quota. LDS quota is an annual quota that defines how much geocoding and isolines you can compute. Each geocoded row or computed isolines counts as one LDS quota unit. The single element in the result of LDS_QUOTA_INFO will show your LDS quota for the current annual period (annual_quota), how much you’ve spent (used_quota), and which LDS providers are in use.
Return type
VARCHAR(MAX)
Example
1
SELECT carto.LDS_QUOTA_INFO();
2
-- [
3
-- {
4
-- "used_quota": 10,
5
-- "annual_quota": 1000,
6
-- "providers": {
7
-- "geocoding": "tomtom",
8
-- "isolines": "here"
9
-- }
10
-- }
11
-- ]
Last modified 1mo ago