Generating trade areas based on drive/walk-time isolines

Requirements

To run this example you'll need:

• An active CARTO organization

• The latest version of the Analytics Toolbox Advanced installed in your Snowflake database

Example

In this example, we will create isolines around some Starbucks locations in order to estimate their trade areas based on drive-time areas around them.

You can use any point table to replicate this analysis, or if you want to replicate this example exactly, you can subscribe to the CARTO Academy data via the Snowflake Data Marketplace. The table we will be using is called SAFEGRAPH_COREPLACES_STARBUCKS_NY.

This process will generate a new table with the columns of the input table (except the column with the point geometry) plus a new column with the isoline polygon (geom column).

WARNING

This function consumes isolines 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.

Creating isolines from the Snowflake console

As a module within CARTO’s Analytics Toolbox, the location data services (lds) capabilities are available as SQL procedures that can be executed directly from your Snowflake console or client of choice after connecting your Snowflake project with your CARTO account. To check whether your Snowflake account or Service Account has access to the LDS module, please execute this query:

SELECT CARTO.CARTO.VERSION_ADVANCED()

The lds module is generally available in the Analytics Toolbox since the “July 26, 2022” version. Please check the Getting Access section if you run into any errors when running the query above.

For this example we will use a table with the Starbucks addresses geocoded MYDB.MYSCHEMA.SAFEGRAPH_COREPLACES_STARBUCKS_NY . The table contains information about Starbucks stores and a column called “geom” with the geographic coordinates (latitude and longitude) of each location. Around these locations we will create isolines based on 5 minutes walking.

In order to create the isolines, we will execute the CREATE_ISOLINES() procedure, which has the following structure:

CALL CARTO.CARTO.CREATE_ISOLINES(
    'my-schema.my-table',
    'my-schema.my-output-table',
    'my_geom_column',
    mode, range, range type
);

You can find more information on this in our documentation. To create 5-minute isolines for our Starbucks locations, we can adapt this in the following way:

SELECT 
    CARTO.CARTO.ISOLINE(geom, 'walk', 300, 'time') AS geom_isoline, *
FROM MYDB.MYSCHEMA.SAFEGRAPH_COREPLACES_STARBUCKS_NY

CALL CARTO.CARTO.CREATE_ISOLINES(
    'MYDB.MYSCHEMA.SAFEGRAPH_COREPLACES_STARBUCKS_NY',
    'MYDB.MYSCHEMA.OUTPUT',
    'geom',
    'walk', 300, 'time'
);

In the query, we call the ISOLINE() function specifying:

• geom_isoline as column name for the origin geometry column

• "mode” parameter = “walk”

• “range_value” parameter = 300 seconds (5 min).

• Last but not least, we need to use FROM call to specify the input table for the geocoding.

This output query has the same schema as the input one, but adding the “GEOM_ISOLINE” column with the geometry of the polygon of the isoline that we have calculated.

And your results should look something like this!

You can also run this type of analysis the no-code way in CARTO Workflows! Check out the tutorial below to get started.

Analyzing multiple drive-time catchment areas dynamically | Academy

This project has received funding from the European Union’s Horizon 2020 research and innovation programme under grant agreement No 960401.