Analytics Toolbox for Redshift

Analytics Toolbox for Redshift

Github Code

lds

This module contains functions and procedures that make use of location data services, such as geocoding, reverse geocoding and isolines computation.

GEOCODE

Description

Geocodes an address into a point with its geographic coordinates (latitude and longitude).

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

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
2

SELECT carto.GEOCODE('Madrid');
-- POINT(109.590465335923 34.1733770650093)

1
2

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

1
2
3

CREATE TABLE my_schema.my_geocoded_table AS
SELECT address, carto.GEOCODE(address) AS geom FROM my_table
-- Table my_geocoded_table successfully created.

GEOCODE_REVERSE

Description

Performs a reverse geocoding of the point received as input.

  • geom: GEOMETRY input point to obtain the address.

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
2

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

GEOCODE_TABLE

Description

Geocodes an input table by adding a column geom with the geographic coordinates (latitude and longitude) of 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.

  • 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 geometry column. Defaults to 'geom'.
  • country (optional): VARCHAR(MAX) name of the country in ISO 3166-1 alpha-2. Defaults to ''.

If the input table already contains a geometry column with the name geom_column, only those rows with NULL values will be geocoded.

Examples

1
2
3

CALL carto.GEOCODE_TABLE('my-schema.my-table');
-- The table `my-schema.my-table` will be updated
-- adding the columns: geom, carto_geocode_metadata.

1
2
3

CALL carto.GEOCODE_TABLE('my-schema.my-table', 'my_address_column');
-- The table `my-schema.my-table` will be updated
-- adding the columns: geom, carto_geocode_metadata.

1
2
3

CALL carto.GEOCODE_TABLE('my-schema.my-table', 'my_address_column', 'my_geom_column');
-- The table `my-schema.my-table` will be updated
-- adding the columns: my_geom_column, carto_geocode_metadata.

1
2
3

CALL carto.GEOCODE_TABLE('my-schema.my-table', 'my_address_column', 'my_geom_column', 'my_country');
-- The table `my-schema.my-table` will be updated
-- adding the columns: my_geom_column, carto_geocode_metadata.

ISOLINE

Description

Calculates the isoline polygon from a given point.

  • origin: GEOMETRY of the origin of the isoline.
  • mode: VARCHAR(MAX) of the type of transport. Supported: ‘walk’, ‘car’.
  • range: INT range of the isoline in seconds (for range_type ‘time’) or meters (for range_type ‘distance’).
  • range_type: VARCHAR(MAX) of the range type. Supported: ‘time’ (for isochrones), ‘distance’ (for isodistances).

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
2

SELECT carto.ISOLINE(ST_POINT(13.37749, 52.51578), 'car', 10, 'time');
-- 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 usage of Location Data Services for the user account, including the monthly and consumed quota for both geocoding and isolines services and their associated provider.

Return type

VARCHAR(MAX)

Example

1
2

SELECT carto.LDS_QUOTA_INFO();
-- [{"monthly_quota":1000,"provider":"tomtom","service":"geocoding","used_quota":10},{"monthly_quota":100,"provider":"here","service":"isolines","used_quota":10}]