LogoLogo
HomeAcademyLoginTry for free
  • Welcome
  • What's new
    • Q2 2025
    • Q1 2025
    • Q4 2024
    • Q3 2024
    • Q2 2024
    • Q1 2024
    • Q4 2023
    • Q3 2023
    • Q2 2023
    • Q1 2023
    • Q4 2022
    • Q3 2022
  • FAQs
    • Accounts
    • Migration to the new platform
    • User & organization setup
    • General
    • Builder
    • Workflows
    • Data Observatory
    • Analytics Toolbox
    • Development Tools
    • Deployment Options
    • CARTO Basemaps
    • CARTO for Education
    • Support Packages
    • Security and Compliance
  • Getting started
    • What is CARTO?
    • Quickstart guides
      • Connecting to your data
      • Creating your first map
      • Creating your first workflow
      • Developing your first application
    • CARTO Academy
  • CARTO User Manual
    • Overview
      • Creating your CARTO organization
      • CARTO Cloud Regions
      • CARTO Workspace overview
    • Maps
      • Data sources
        • Simple features
        • Spatial Indexes
        • Pre-generated tilesets
        • Rasters
        • Defining source spatial data
        • Managing data freshness
        • Changing data source location
      • Layers
        • Point
          • Grid point aggregation
          • H3 point aggregation
          • Heatmap point aggregation
          • Cluster point aggregation
        • Polygon
        • Line
        • Grid
        • H3
        • Raster
        • Zoom to layer
      • Widgets
        • Formula widget
        • Category widget
        • Pie widget
        • Histogram widget
        • Range widget
        • Time Series widget
        • Table widget
      • SQL Parameters
        • Date parameter
        • Text parameter
        • Numeric parameter
        • Publishing SQL parameters
      • Interactions
      • Legend
      • Basemaps
        • Basemap selector
      • AI Agents
      • SQL analyses
      • Map view modes
      • Map description
      • Feature selection tool
      • Search locations
      • Measure distances
      • Exporting data
      • Download PDF reports
      • Managing maps
      • Sharing and collaboration
        • Editor collaboration
        • Map preview for editors
        • Map settings for viewers
        • Comments
        • Embedding maps
        • URL parameters
      • Performance considerations
    • Workflows
      • Workflow canvas
      • Results panel
      • Components
        • Aggregation
        • Custom
        • Data Enrichment
        • Data Preparation
        • Generative AI
        • Input / Output
        • Joins
        • Parsers
        • Raster Operations
        • Spatial Accessors
        • Spatial Analysis
        • Spatial Constructors
        • Spatial Indexes
        • Spatial Operations
        • Statistics
        • Tileset Creation
        • BigQuery ML
        • Snowflake ML
        • Google Earth Engine
        • Google Environment APIs
        • Telco Signal Propagation Models
      • Data Sources
      • Scheduling workflows
      • Sharing workflows
      • Using variables in workflows
      • Executing workflows via API
      • Temporary data in Workflows
      • Extension Packages
      • Managing workflows
      • Workflows best practices
    • Data Explorer
      • Creating a map from your data
      • Importing data
        • Importing rasters
      • Geocoding data
      • Optimizing your data
    • Data Observatory
      • Terminology
      • Browsing the Spatial Data Catalog
      • Subscribing to public and premium datasets
      • Accessing free data samples
      • Managing your subscriptions
      • Accessing your subscriptions from your data warehouse
        • Access data in BigQuery
        • Access data in Snowflake
        • Access data in Databricks
        • Access data in Redshift
        • Access data in PostgreSQL
    • Connections
      • Google BigQuery
      • Snowflake
      • Databricks
      • Amazon Redshift
      • PostgreSQL
      • CARTO Data Warehouse
      • Sharing connections
      • Deleting a connection
      • Required permissions
      • IP whitelisting
      • Customer data responsibilities
    • Applications
    • Settings
      • Understanding your organization quotas
      • Activity Data
        • Activity Data Reference
        • Activity Data Examples
        • Activity Data Changelog
      • Users and Groups
        • Inviting users to your organization
        • Managing user roles
        • Deleting users
        • SSO
        • Groups
        • Mapping groups to user roles
      • CARTO Support Access
      • Customizations
        • Customizing appearance and branding
        • Configuring custom color palettes
        • Configuring your organization basemaps
        • Enabling AI Agents
      • Advanced Settings
        • Managing applications
        • Configuring S3 Bucket for Redshift Imports
        • Configuring OAuth connections to Snowflake
        • Configuring OAuth U2M connections to Databricks
        • Configuring S3 Bucket integration for RDS for PostgreSQL Exports in Builder
        • Configuring Workload Identity Federation for BigQuery
      • Data Observatory
      • Deleting your organization
    • Developers
      • Managing Credentials
        • API Base URL
        • API Access Tokens
        • SPA OAuth Clients
        • M2M OAuth Clients
      • Named Sources
  • Data and Analysis
    • Analytics Toolbox Overview
    • Analytics Toolbox for BigQuery
      • Getting access
        • Projects maintained by CARTO in different BigQuery regions
        • Manual installation in your own project
        • Installation in a Google Cloud VPC
        • Core module
      • Key concepts
        • Tilesets
        • Spatial indexes
      • SQL Reference
        • accessors
        • clustering
        • constructors
        • cpg
        • data
        • http_request
        • import
        • geohash
        • h3
        • lds
        • measurements
        • placekey
        • processing
        • quadbin
        • random
        • raster
        • retail
        • routing
        • s2
        • statistics
        • telco
        • tiler
        • transformations
      • Guides
        • Running queries from Builder
        • Working with Raster data
      • Release notes
      • About Analytics Toolbox regions
    • Analytics Toolbox for Snowflake
      • Getting access
        • Native App from Snowflake's Marketplace
        • Manual installation
      • Key concepts
        • Spatial indexes
        • Tilesets
      • SQL Reference
        • accessors
        • clustering
        • constructors
        • data
        • http_request
        • import
        • h3
        • lds
        • measurements
        • placekey
        • processing
        • quadbin
        • random
        • raster
        • retail
        • s2
        • statistics
        • tiler
        • transformations
      • Guides
        • Running queries from Builder
        • Working with Raster data
      • Release Notes
    • Analytics Toolbox for Databricks
      • Getting access
        • Personal (former Single User) cluster
        • Standard (former Shared) cluster
      • Reference
        • lds
        • tiler
      • Guides
      • Release Notes
    • Analytics Toolbox for Redshift
      • Getting access
        • Manual installation in your database
        • Installation in an Amazon Web Services VPC
        • Core version
      • Key concepts
        • Tilesets
        • Spatial indexes
      • SQL Reference
        • clustering
        • constructors
        • data
        • http_request
        • import
        • lds
        • placekey
        • processing
        • quadbin
        • random
        • s2
        • statistics
        • tiler
        • transformations
      • Guides
        • Running queries from Builder
      • Release Notes
    • Analytics Toolbox for PostgreSQL
      • Getting access
        • Manual installation
        • Core version
      • Key concepts
        • Tilesets
        • Spatial Indexes
      • SQL Reference
        • h3
        • quadbin
        • tiler
      • Guides
        • Creating spatial index tilesets
        • Running queries from Builder
      • Release Notes
    • CARTO + Python
      • Installation
      • Authentication Methods
      • Visualizing Data
      • Working with Data
        • How to work with your data in the CARTO Data Warehouse
        • How to access your Data Observatory subscriptions
        • How to access CARTO's Analytics Toolbox for BigQuery and create visualizations via Python notebooks
        • How to access CARTO’s Analytics Toolbox for Snowflake and create visualizations via Python notebooks
        • How to visualize data from Databricks
      • Reference
    • CARTO QGIS Plugin
  • CARTO for Developers
    • Overview
    • Key concepts
      • Architecture
      • Libraries and APIs
      • Authentication methods
        • API Access Tokens
        • OAuth Access Tokens
        • OAuth Clients
      • Connections
      • Data sources
      • Visualization with deck.gl
        • Basemaps
          • CARTO Basemap
          • Google Maps
            • Examples
              • Gallery
              • Getting Started
              • Basic Examples
                • Hello World
                • BigQuery Tileset Layer
                • Data Observatory Tileset Layer
              • Advanced Examples
                • Arc Layer
                • Extrusion
                • Trips Layer
            • What's New
          • Amazon Location
            • Examples
              • Hello World
              • CartoLayer
            • What's New
        • Rapid Map Prototyping
      • Charts and widgets
      • Filtering and interactivity
      • Summary
    • Quickstart
      • Make your first API call
      • Visualize your first dataset
      • Create your first widget
    • Guides
      • Build a public application
      • Build a private application
      • Build a private application using SSO
      • Visualize massive datasets
      • Integrate CARTO in your existing application
      • Use Boundaries in your application
      • Avoid exposing SQL queries with Named Sources
      • Managing cache in your CARTO applications
    • Reference
      • Deck (@deck.gl reference)
      • Data Sources
        • vectorTableSource
        • vectorQuerySource
        • vectorTilesetSource
        • h3TableSource
        • h3QuerySource
        • h3TilesetSource
        • quadbinTableSource
        • quadbinQuerySource
        • quadbinTilesetSource
        • rasterSource
        • boundaryTableSource
        • boundaryQuerySource
      • Layers (@deck.gl/carto)
      • Widgets
        • Data Sources
        • Server-side vs. client-side
        • Models
          • getFormula
          • getCategories
          • getHistogram
          • getRange
          • getScatter
          • getTimeSeries
          • getTable
      • Filters
        • Column filters
        • Spatial filters
      • CARTO APIs Reference
    • Release Notes
    • Examples
    • CARTO for React
      • Guides
        • Getting Started
        • Views
        • Data Sources
        • Layers
        • Widgets
        • Authentication and Authorization
        • Basemaps
        • Look and Feel
        • Query Parameters
        • Code Generator
        • Sample Applications
        • Deployment
        • Upgrade Guide
      • Examples
      • Library Reference
        • Introduction
        • API
        • Auth
        • Basemaps
        • Core
        • Redux
        • UI
        • Widgets
      • Release Notes
  • CARTO Self-Hosted
    • Overview
    • Key concepts
      • Architecture
      • Deployment requirements
    • Quickstarts
      • Single VM deployment (Kots)
      • Orchestrated container deployment (Kots)
      • Advanced Orchestrated container deployment (Helm)
    • Guides
      • Guides (Kots)
        • Configure your own buckets
        • Configure an external in-memory cache
        • Enable Google Basemaps
        • Enable the CARTO Data Warehouse
        • Configure an external proxy
        • Enable BigQuery OAuth connections
        • Configure Single Sign-On (SSO)
        • Use Workload Identity in GCP
        • High availability configuration for CARTO Self-hosted
        • Configure your custom service account
      • Guides (Helm)
        • Configure your own buckets (Helm)
        • Configure an external in-memory cache (Helm)
        • Enable Google Basemaps (Helm)
        • Enable the CARTO Data Warehouse (Helm)
        • Configure an external proxy (Helm)
        • Enable BigQuery OAuth connections (Helm)
        • Configure Single Sign-On (SSO) (Helm)
        • Use Workload Identity in GCP (Helm)
        • Use EKS Pod Identity in AWS (Helm)
        • Enable Redshift imports (Helm)
        • Migrating CARTO Self-hosted installation to an external database (Helm)
        • Advanced customizations (Helm)
        • Configure your custom service account (Helm)
    • Maintenance
      • Maintenance (Kots)
        • Updates
        • Backups
        • Uninstall
        • Rotating keys
        • Monitoring
        • Change the Admin Console password
      • Maintenance (Helm)
        • Monitoring (Helm)
        • Rotating keys (Helm)
        • Uninstall (Helm)
        • Backups (Helm)
        • Updates (Helm)
    • Support
      • Get debug information for Support (Kots)
      • Get debug information for Support (Helm)
    • CARTO Self-hosted Legacy
      • Key concepts
        • Architecture
        • Deployment requirements
      • Quickstarts
        • Single VM deployment (docker-compose)
      • Guides
        • Configure your own buckets
        • Configure an external in-memory cache
        • Enable Google Basemaps
        • Enable the CARTO Data Warehouse
        • Configure an external proxy
        • Enable BigQuery OAuth connections
        • Configure Single Sign-On (SSO)
        • Enable Redshift imports
        • Configure your custom service account
        • Advanced customizations
        • Migrating CARTO Self-Hosted installation to an external database
      • Maintenance
        • Updates
        • Backups
        • Uninstall
        • Rotating keys
        • Monitoring
      • Support
    • Release Notes
  • CARTO Native App for Snowflake Containers
    • Deploying CARTO using Snowflake Container Services
  • Get Help
    • Legal & Compliance
    • Previous libraries and components
    • Migrating your content to the new CARTO platform
Powered by GitBook
On this page
  • Credentials
  • GEOCODE_TABLE
  • GEOCODE_REVERSE_TABLE
  • CREATE_ISOLINES
  • CREATE_ROUTES
  • GEOCODE
  • GEOCODE_REVERSE
  • ISOLINE
  • GET_LDS_QUOTA_INFO

Was this helpful?

Export as PDF
  1. Data and Analysis
  2. Analytics Toolbox for Databricks
  3. Reference

lds

PreviousReferenceNexttiler

Last updated 19 days ago

Was this helpful?

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

Credentials

Your CARTO account has monthly 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 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 .

tip

To check that everything works correctly, without spending any credits, make a call to the GET_LDS_QUOTA_INFO procedure. You can enter the following in the Databricks notebook having selected the cluster where the Analytics Toolbox is installed:

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

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

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

LDS quota is an annual quota that defines how much geocoding and isolines you can compute. Each geocoded row or computed isolines counts as one LDS quota unit. The single element in the result of LDS_QUOTA_INFO will show your LDS quota for the current annual period (annual_quota), how much you’ve spent (used_quota), and which LDS providers are in use.

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

GEOCODE_TABLE

GEOCODE_TABLE(api_base_url, api_access_token, input_table, address_column, geom_column, country, options)

warning

Description

Geocodes an input table by adding an user defined column geom_column 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.

  • api_base_url: STRING url of the API where the customer account is stored.

  • input_table: STRING 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: STRING name of the column from the input table that contains the addresses to be geocoded.

  • geom_column: STRING|'' column name for the geometry column. Defaults to 'geom'. Set to '' to use the default value.

  • options: STRING|'' containing a valid JSON with the different options. In addition to the options targeted at the geocoding service described below, a boolean option carto_force_geocode (false by default) can be used to force geocoding rows that already have a non-null value in geom_column. Valid geocoding service options are described in the table below. If no options are indicated then 'default' values would be applied.

    Provider
    Option
    Description

    All

    language

    A STRING that specifies the language of the geocoding in RFC 4647 format.

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

Examples

import com.carto.analytics.toolbox.ATExecute

ATExecute.sql(
 """
  |CALL_CARTO carto_un.carto.GEOCODE_TABLE(
  |
  | 'my_api_base_url',
  | 'my_api_access_token',
  | 'my-database.my-schema.my-table',
  | 'my_address_column',
  | '', '', ''
  | );
  | """.stripMargin,
  spark
)
// The table `my-database.my-schema.my-table` will be updated
// adding the columns: geom, carto_geocode_metadata.
import com.carto.analytics.toolbox.ATExecute

ATExecute.sql(
 """
  |CALL_CARTO carto_un.carto.GEOCODE_TABLE(
  |
  | 'my_api_base_url',
  | 'my_api_access_token',
  | 'my-database.my-schema.my-table',
  | 'my_address_column',
  | 'my_geom_column',
  | '', ''
  | );
  | """.stripMargin,
  spark
)
// The table `my-database.my-schema.my-table` will be updated
// adding the columns: my_geom_column, carto_geocode_metadata.
import com.carto.analytics.toolbox.ATExecute

ATExecute.sql(
 """
  |CALL_CARTO carto_un.carto.GEOCODE_TABLE(
  |
  | 'my_api_base_url',
  | 'my_api_access_token',
  | 'my-database.my-schema.my-table',
  | 'my_address_column',
  | 'my_geom_column',
  | 'my_country',
  | ''
  | );
  | """.stripMargin,
  spark
)
// The table `my-database.my-schema.my-table` will be updated
// adding the columns: my_geom_column, carto_geocode_metadata.
import com.carto.analytics.toolbox.ATExecute

ATExecute.sql(
 """
  |CALL_CARTO carto_un.carto.GEOCODE_TABLE(
  | 'my_api_base_url',
  | 'my_api_access_token',
  | 'my-database.my-schema.my-table',
  | 'my_address_column',
  | 'my_geom_column',
  | 'my_country',
  | '{"language":"en-US"}'
  | );
  | """.stripMargin,
  spark
)
// The table `my-database.my-schema.my-table` will be updated
// adding the columns: my_geom_column, carto_geocode_metadata.
import com.carto.analytics.toolbox.ATExecute

ATExecute.sql(
 """
  |CALL_CARTO carto_un.carto.GEOCODE_TABLE(
  | 'my_api_base_url',
  | 'my_api_access_token',
  | 'my-database.my-schema.my-table',
  | 'my_address_column',
  | 'my_geom_column',
  | 'my_country',
  | '{"carto_force_geocode":true}'
  | );
  | """.stripMargin,
  spark
)
// The table `my-database.my-schema.my-table` will be updated
// adding the columns: my_geom_column, carto_geocode_metadata.

GEOCODE_REVERSE_TABLE

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

warning

Description

Reverse-geocodes an input table by adding an user defined column address_column with the address coordinates corresponding to a given point location column. It geocodes sequentially the table in chunks of 100 rows.

  • api_base_url: STRING url of the API where the customer account is stored.

  • input_table: STRING 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: STRING|'' column name for the geometry column that contains the points to be reverse-geocoded. Defaults to 'geom'. Set to '' to use the default value.

  • address_column: STRING 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: STRING|'' 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: STRING|'' 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 defined by address_column, only those rows with NULL values will be reverse-geocoded.

Examples

import com.carto.analytics.toolbox.ATExecute

ATExecute.sql(
 """
  |CALL_CARTO carto_un.carto.GEOCODE_REVERSE_TABLE(
  |
  | 'my_api_base_url',
  | 'my_api_access_token',
  | 'my-database.my-schema.my-table',
  | '', '', '', ''
  | );
  | """.stripMargin,
  spark
)
// The table `my-database.my-schema.my-table` with a column `geom` will be updated
// adding the column `address`.
import com.carto.analytics.toolbox.ATExecute

ATExecute.sql(
 """
  |CALL_CARTO carto_un.carto.GEOCODE_REVERSE_TABLE(
  |
  | 'my_api_base_url',
  | 'my_api_access_token',
  | 'my-database.my-schema.my-table',
  | 'my_geom_column',
  | '', '', ''
  | );
  | """.stripMargin,
  spark
)
// The table `my-database.my-schema.my-table` with a column `my_geom_column` will be updated
// adding the column `address`.
import com.carto.analytics.toolbox.ATExecute

ATExecute.sql(
 """
  |CALL_CARTO carto_un.carto.GEOCODE_REVERSE_TABLE(
  |
  | 'my_api_base_url',
  | 'my_api_access_token',
  | 'my-database.my-schema.my-table',
  | 'my_geom_column',
  | 'my_address_column',
  | '', ''
  | );
  | """.stripMargin,
  spark
)
// The table `my-database.my-schema.my-table` with a column `my_geom_column` will be updated
// adding the column `my_address_column`.
import com.carto.analytics.toolbox.ATExecute

ATExecute.sql(
 """
  |CALL_CARTO carto_un.carto.GEOCODE_REVERSE_TABLE(
  |
  | 'my_api_base_url',
  | 'my_api_access_token',
  | 'my-database.my-schema.my-table',
  | 'my_geom_column',
  | 'my_address_column',
  | 'en-US',
  | ''
  | );
  | """.stripMargin,
  spark
)
// The table `my-database.my-schema.my-table` with a column `my_geom_column` will be updated
// adding the column `my_address_column`.
// The addresses will be in the (US) english language, if supported by the account LDS provider.

CREATE_ISOLINES

CREATE_ISOLINES(api_base_url, api_access_token, input, output_table, geom_column, mode, range, range_type, options)

warning

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.

The output table will contain a column named carto_isoline_metadata with error information for each isoline result. Rows with errors will have a NULL geom.

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

  • api_base_url: STRING url of the API where the customer account is stored.

  • input: STRING name of the input table or query.

  • output_table: STRING name of the output table. It will raise an error if the table already exists.

  • geom_column: STRING column name for the origin geometry column.

  • mode: STRING 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: BIGINT range of the isoline in seconds (for range_type 'time') or meters (for range_type 'distance').

  • range_type: STRING type of range. Supported: 'time' (for isochrones), 'distance' (for isodistances).

  • options: STRING|'' 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 STRING that specifies the time of arrival. If the value is set, a reverse isoline is calculated. If set to "any", then time-dependent effects will not be taken into account. It cannot be used in combination with departure_time. Supported: "any", "now" and date-time as "<YYYY-MM-DD>T<hh:mm:ss>".

    HERE

    departure_time

    Default: "any". A STRING that specifies the time of departure. If set to "any", then time-dependent effects will not be taken into account. It cannot be used in combination with arrival_time. Supported: "any", "now" and date-time as "<YYYY-MM-DD>T<hh:mm:ss>".

    HERE

    optimize_for

    Default: "balanced". A STRING 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 STRING 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 STRING that specifies the time of departure. Supported: "now" and date-time as "<YYYY-MM-DD>T<hh:mm:ss>".

    TomTom

    traffic

    Default: false. A BOOLEAN that specifies if all available traffic information will be taken into consideration. Supported: true and false.

    TravelTime

    single_shape

    Default: false. A BOOLEAN that specifies if the response should contain only the main polygon in case of a result with multiple polygons. 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 [https://docs.traveltime.com/api/reference/isochrones#arrival_searches-level_of_detail](TravelTime docs) for more info.

    TravelTime

    departure_time

    Default: "now". A STRING that specifies the time of departure. Supported: "now" and date-time as "<YYYY-MM-DD>T<hh:mm:ss>".

warning

Examples

import com.carto.analytics.toolbox.ATExecute

ATExecute.sql(
 """
  |CALL_CARTO carto_un.carto.CREATE_ISOLINES(
  |
  | 'my_api_base_url',
  | 'my_api_access_token',
  | 'my-database.my-schema.my-table',
  | 'my-database.my-schema.my-output-table',
  | 'my_geom_column',
  | 'car',
  | 300,
  | 'time',
  | ''
  | );
  | """.stripMargin,
  spark
)
// The table `my-database.my-schema.my-output-table` will be created
// with the columns of the input table except `my_geom_column`.
// Isolines will be added in the "geom" column.
import com.carto.analytics.toolbox.ATExecute

ATExecute.sql(
 """
  |CALL_CARTO carto_un.carto.CREATE_ISOLINES(
  |
  | 'my_api_base_url',
  | 'my_api_access_token',
  | 'my-database.my-schema.my-table',
  | 'my-database.my-schema.my-output-table',
  | 'my_geom_column',
  | 'car',
  | 1000,
  | 'distance',
  | '{"polygons_filter": {"limit": 1}}'
  | );
  | """.stripMargin,
  spark
)
// The table `my-database.my-schema.my-output-table` will be created
// with the columns of the input table except `my_geom_column`.
// Isolines will be added in the "geom" column.

CREATE_ROUTES

CREATE_ROUTES(api_base_url, api_access_token, input, output_table, geom_column, mode, options)

warning

Description

Calculates the routes (line strings) between given origins and destinations (points) in a query. It creates a new table with the columns of the input query with the resulting route in the user defined column geom_column (if the input already contains a column named geom_column, it will be overwritten) and a carto_routing_metadata column with the response of the service provider except for the route geometry. It calculates routes sequentially in chunks of 100 rows.

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

  • api_base_url: STRING url of the API where the customer account is stored.

  • input: STRING name of the input query, which must have columns named origin and destination of type GEOGRAPHY and containing points. If a column named waypoints is also present, it should contain a STRING with the coordinates of the desired intermediate points with the format "lon1,lat1:lon2,lat2...".

  • output_table: STRING name of the output table. It will raise an error if the table already exists.

  • geom_column: STRING column name for the generated geography column that will contain the resulting routes.

  • mode: STRING 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'.

    • 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

        • Avoids

        • This

        • 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

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

    For more advanced usage, check the documentation of your provider's routing API.

warning

Examples

import com.carto.analytics.toolbox.ATExecute

ATExecute.sql(
 """
  |CALL_CARTO carto_un.carto.CREATE_ROUTES(
  |
  | 'my_api_base_url',
  | 'my_api_access_token',
  | 'SELECT id, origin, destination FROM my-database.my-schema.my-table',
  | 'my-database.my-schema.my-output-table',
  | 'my_geom_column',
  | 'car',
  | ''
  | );
  | """.stripMargin,
  spark
)
// The table `my-database.my-schema.my-output-table` will be created
// with the columns `id`, `origin`, `destination`, `geom` and `carto_routing_metadata`.

Example with TomTom specific parameters:

import com.carto.analytics.toolbox.ATExecute

ATExecute.sql(
 """
  |CALL_CARTO carto_un.carto.CREATE_ROUTES(
  |
  | 'my_api_base_url',
  | 'my_api_access_token',
  | 'SELECT id, origin, destination FROM my-database.my-schema.my-table',
  | 'my-database.my-schema.my-output-table',
  | 'my_geom_column',
  | 'car',
  | '{"departAt":"now"}'
  | );
  | """.stripMargin,
  spark
)
// The table `my-database.my-schema.my-output-table` will be created
// with the columns `id`, `origin`, `destination`, `geom` and `carto_routing_metadata`.

GEOCODE

GEOCODE(api_base_url, api_access_token, address , country, options)

warning

Description

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

  • api_base_url: STRING url of the API where the customer account is stored.

  • address: STRING input address to geocode.

  • options: STRING|'' containing a valid JSON with the different options.

    Provider
    Option
    Description

    All

    language

    A STRING that specifies the language of the geocoding in RFC 4647 format.

Return type

GEOGRAPHY

Examples

SELECT GEOCODE('my_api_base_url', 'my_api_access_token', 'Madrid', '', '');
-- AAAAAAHADZpRGc4HX0BENVYZEUj+
SELECT GEOCODE('my_api_base_url', 'my_api_access_token', 'Madrid', 'es', '');
-- AAAAAAHADZpRGc4HX0BENVYZEUj+
SELECT GEOCODE('my_api_base_url', 'my_api_access_token', 'Madrid', 'es', '{"language":"es-ES"}');
-- AAAAAAHADZpRGc4HX0BENVYZEUj+
CREATE TABLE my_dataset.my_geocoded_table AS
SELECT address, GEOCODE('my_api_base_url', 'my_api_access_token', address, '', '') AS geom FROM my_table
-- Table my_geocoded_table successfully created.

GEOCODE_REVERSE

GEOCODE_REVERSE(api_base_url, api_access_token, 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 [GET_LDS_QUOTA_INFO](lds.md#get_ lds_quota_info)

Description

Performs a reverse geocoding of the point received as input.

  • api_base_url: STRING url of the API where the customer account is stored.

  • geom: GEOGRAPHY input point for which to obtain the address.

  • language: STRING 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: STRING|'' containing a valid JSON with the different options. No options are allowed currently, so this value will not be taken into account.

Return type

STRING

Examples

SELECT GEOCODE_REVERSE('my_api_base_url', 'my_api_access_token', ST_ASWKB(ST_POINT(-74.0060, 40.7128)), '', '');
-- 1 Broadway, New York, NY 10007
SELECT GEOCODE_REVERSE('my_api_base_url', 'my_api_access_token', ST_ASWKB(ST_POINT(-74.0060, 40.7128)), 'en-US', '');
-- 1 Broadway, New York, NY 10007

ISOLINE

ISOLINE(api_base_url, api_access_token, origin, mode, range_value, range_type, option)

warning

Description

Calculates the isoline polygon from a given point.

  • api_base_url: STRING url of the API where the customer account is stored.

  • origin: GEOGRAPHY origin point of the isoline.

  • mode: STRING 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: INT range of the isoline in seconds (for range_type 'time') or meters (for range_type 'distance').

  • range_type: STRING type of range. Supported: 'time' (for isochrones), 'distance' (for isodistances).

  • options STRING|'' 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 STRING that specifies the time of arrival. If the value is set, a reverse isoline is calculated. If set to "any", then time-dependent effects will not be taken into account. It cannot be used in combination with departure_time. Supported: "any", "now" and date-time as "<YYYY-MM-DD>T<hh:mm:ss>".

    HERE

    departure_time

    Default: "now". A STRING that specifies the time of departure. If set to "any", then time-dependent effects will not be taken into account. It cannot be used in combination with arrival_time. Supported: "any", "now" and date-time as "<YYYY-MM-DD>T<hh:mm:ss>".

    HERE

    optimize_for

    Default: "balanced". A STRING 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 STRING 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 STRING that specifies the time of departure. If set to "any", 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. 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 [https://docs.traveltime.com/api/reference/isochrones#arrival_searches-level_of_detail](TravelTime docs) for more info.

    TravelTime

    departure_time

    Default: "now". A STRING that specifies the time of departure. Supported: "now" and date-time as "<YYYY-MM-DD>T<hh:mm:ss>".

warning

Return type

GEOGRAPHY

Examples

SELECT ISOLINE('my_api_base_url', 'my_api_access_token', ST_ASWKB(ST_POINT(-3, 40)), 'car', 300, 'time', '');
-- AAAAAAcAAAABAAAAAAYAAAADAAAAAAMAAAABAAAAEcAIEGTs6aLGQEP/baAWi13ACBBk7OmixkBD/4KhL ...
SELECT ISOLINE('my_api_base_url', 'my_api_access_token', ST_ASWKB(ST_POINT(-3, 40)), 'car', 1000, 'distance', '');
-- AAAAAAcAAAABAAAAAAMAAAABAAAAKsAIBKUVzp5eQEP/i6xxDLPACASlFc6eXkBD/3nhbW3CwAgD7Q9ic ...
SELECT ISOLINE('my_api_base_url', 'my_api_access_token', ST_ASWKB(ST_POINT(-3, 40)), 'car', 300, 'time', '{"departure_time":"any"}');
-- AAAAAAcAAAABAAAAAAYAAAADAAAAAAMAAAABAAAAD8AIMZmgT5ZAQEQAxL0z0pXACDJ8v24/eUBEAM+eOGT ...

GET_LDS_QUOTA_INFO

GET_LDS_QUOTA_INFO(api_base_url, api_access_token)

Description

Returns statistics about the LDS quota. LDS quota is an annual quota that defines how much geocoding and isolines you can compute. Each geocoded row or computed isolines counts as one LDS quota unit. The single element in the result of GET_LDS_QUOTA_INFO will show your LDS quota for the current annual period (annual_quota), how much you’ve spent (used_quota), and which LDS providers are in use.

  • api_base_url: STRING url of the API where the customer account is stored.

Return type

STRING

Example

SELECT GET_LDS_QUOTA_INFO('my_api_base_url', 'my_api_access_token');
-- [
--   {
--     "used_quota": 10,
--     "annual_quota": 100000,
--     "providers": {
--         "geocoding": "tomtom",
--         "isolines": "here",
--         "routing":"tomtom"
--     }
--   }
-- ]

This function consumes LDS quota. Each call consumes as many units of quota as the number of rows of your input table. Before running, we recommend checking the size of the data to be geocoded and your available quota using the function.

api_access_token: STRING an that is allowed to use the LDS API.

country: STRING|'' name of the country in .

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

api_access_token: STRING an that is allowed to use the LDS API.

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 data for which isolines will be created and your available quota using the function.

api_access_token: STRING an that is allowed to use the LDS API.

Before running, we recommend checking your provider using the 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.

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 used to create the routes and your available quota using the function.

api_access_token: STRING an that is allowed to use the LDS API.

options: STRING|'' 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 function. The following are some of the most common parameters for each provider:

avoid: Elements or areas to avoid. Information about avoidance can be found

language: The language to use. Supported language codes can be found

Before running, we recommend checking your provider using the 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.

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 .

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 procedure. Likewise, in order to materialize the results in a table.

api_access_token: STRING an that is allowed to use the LDS API.

country: STRING|'' name of the country in .

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 procedure. Likewise, in order to materialize the results in a table.

api_access_token: STRING an that is allowed to use the LDS API.

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 .

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 procedure. Likewise, in order to materialize the results in a table.

api_access_token: STRING an that is allowed to use the LDS API.

Before running, we recommend checking your provider using the 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.

api_access_token: STRING an that is allowed to use the LDS API.

API Access Token
documentation for Developers
API Access Token
ISO 3166-1 alpha-2
API Access Token
API Access Token
API Access Token
here
here
TomTom
HERE
API Access Token
ISO 3166-1 alpha-2
API Access Token
API Access Token
API Access Token
GET_LDS_QUOTA_INFO
GET_LDS_QUOTA_INFO
GET_LDS_QUOTA_INFO
GET_LDS_QUOTA_INFO
GET_LDS_QUOTA_INFO
GET_LDS_QUOTA_INFO
GET_LDS_QUOTA_INFO
GET_LDS_QUOTA_INFO
GEOCODE_TABLE
GEOCODE_REVERSE_TABLE
GET_LDS_QUOTA_INFO
CREATE_ISOLINES
GET_LDS_QUOTA_INFO