Analytics Toolbox for Snowflake

Analytics Toolbox for Snowflake

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

Return type

GEOGRAPHY

Constraints

This function performs requests to the CARTO Location Data Services API. Snowflake 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. Unexpected server errors will force Snowflake to retry the requests. 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');
-- { "coordinates": [ -3.69196, 40.41956 ], "type": "Point" }
1
2
SELECT carto.GEOCODE('Madrid', 'es');
-- { "coordinates": [ -3.69196, 40.41956 ], "type": "Point" }
1
2
3
CREATE TABLE 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: GEOGRAPHY input point to obtain the address.

Return type

VARCHAR

Constraints

This function performs requests to the CARTO Location Data Services API. Snowflake 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. Unexpected server errors will force Snowflake to retry the requests. 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 500.

  • input_table: VARCHAR 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 name of the column from the input table that contains the addresses to be geocoded.
  • geom_column (optional): VARCHAR column name for the geometry column. Defaults to 'geom'.
  • country (optional): VARCHAR 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.

Troubleshooting

For the GEOCODE_TABLE procedure to work, the input table should be owned by the same role as the procedure. The procedure may not have access to update the table, for example, if the table was created with the ACCOUNTADMIN role, but the procedure is owned by the SYSADMIN role. In such scenario, the following error will be raised:

SQL access control error: Insufficient privileges to operate on table 'my-table'

You can check the OWNERSHIP of the table with the following query:

1
SHOW GRANTS ON TABLE "my-schema"."my-table";

To change the OWNERSHIP of to table to the SYSADMIN role, execute:

1
GRANT OWNERSHIP ON TABLE "my-schema"."my-table" TO ROLE SYSADMIN COPY CURRENT GRANTS;

After performing this operation, you will be able to run GEOCODE_TABLE without running into privilege issues.

ISOLINE

Description

Creates an isoline from the provided origin.

  • origin: GEOGRAPHY of the origin of the isoline.
  • mode: VARCHAR 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 of the range type. Supported: ‘time’ (for isochrones), ‘distance’ (for isodistances).

Return type

GEOGRAPHY

Constraints

This function performs requests to the CARTO Location Data Services API. Snowflake 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. Unexpected server errors will force Snowflake to retry the requests. 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.ISOLINE(ST_MAKEPOINT(-3,40), 'car', 10, 'time');
-- { "coordinates": [ [ [ -2.999868, 40.001907 ], [ -2.999439, 40.001736 ], [ -2.999096, 40.000706 ], [ -2.998066, 40.000362 ], [ ...

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

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}]