Analyzing multiple drive-time catchment areas dynamically

Context

In this tutorial, discover how to harness CARTO Builder for analyzing multiple drive time catchment areas at specific times of the day, tailored to various business needs. We'll demonstrate how to create five distinct catchments at 10, 15, 30, 45, and 60 minutes of driving time for a chosen time - 8:00 AM local time, using CARTO Workflows. You'll then learn to craft an interactive dashboard in Builder, employing SQL Parameters to enable users to select and focus on a catchment area that aligns with their specific interests or business objectives.

Step-by-Step Guide:

In this guide, we'll walk you through:

• Generate drive-time catchment areas with Workflows

• Create an interactive map with Builder

Generate drive-time catchment areas with Workflows

• Access Workflows from your CARTO Workspace using the Navigation menu.

• Select the data warehouse where you have the data accessible. We'll be using the CARTO Data Warehouse, which should be available to all users.

• In the Sources section location on the left panel, navigate to demo_data > demo tables within CARTO Data Warehouse. Drag and drop the retail_stores source to the canvas.

• We are going to focus our analysis in two states: Montana and Wyoming. Luckily, retail_stores source contains a column named state with each state's abbreviation. First, add one Simple Filter component to extract stores whose state column is equal to MT. Then click on "Run".

• To filter those stores in Wyoming, repeat Step 4 by adding another Simple Filter to the canvas and setting the node configuration to filter those equal to WY. Then click on "Run".

• Then, add a Union All component to the canvas and add both Simple Filter output to combine both into a single table again.

To do a quick verification, click the Union All component to activate it, expand the results panel at the bottom of the Workflows canvas, click the Data Preview tab, and then on the state field click the "Show column stats" button. After that, the stats should now show the counts for only stores available for MT and WY.

• In the Components tab, search for the Create Isolines component and drag 5 of them into the canvas, connecting each to the Union component from the steps prior. You can edit the component description by double-clicking the text reading "Create Isolines" under each component's icon in the canvas and edit the component name to be more descriptive.

• Now, set up the Create Isolines components, which will create the catchment areas. Using the example given below for 10 minute drive time for a car, add the proper settings to each respective component. We will be adding an Isoline Option for custom departure time, which will allow each component to mimic driving conditions at that date & time. For that, make sure to enter the following JSON structure in the Isoline Options: {"departure_time":"2023-12-27T08:00:00"}. Once the configuration is set, click on "Run".

• Now, we will create a new column to store the drive time category, so we can later use it to filter the different catchment areas using a parameter control in Builder. To do so, drag 5 Create column components into the canvas and connect each of them with a Create isoline output. In the configuration, set the 'Name for new column' value as "drive_time" and set the expression to the appropriate distance given for each component such as 10.

• Add a Union all component and connect all 5 of the Create Column components to it to merge all of these into one single table.

• Finally, let's save our output as a table by using Save as table component. Add the component to the canvas and connect it to the Union All component. Set the destination to CARTO Data Warehouse > organization > private and save the table as catchment_regions. Then, click "Run" to execute the last part of the Workflows.

• Before closing the Workflows, set a suitable name to the Workflows such as "Generating multiple drive time regions" and add Annotations to facilitate readability.

• Before moving to Builder, for the visualization part, we can review the output of the saved table from Map Preview of Workflows itself, when the Save as table component is empty, or we can review it in the Data Explorer. To do so, navigate to Data Explorer section, using the Navigation panel.

• In the Data Explorer section, navigate to CARTO Data Warehouse > organization data > private and look for catchment_regions table. Click and inspect the source using the Data and Map Preview. Then, click on "Copy qualified name" as we will be using in the next Steps of our tutorial.

Create an interactive map with Builder

• In the CARTO Workspace, access the "Maps" sections from the navigation panel.

• Click on "New map". A new Builder map is opened in a new tab.

• Name your Builder map to "Analyzing multiple drive-time catchment areas"

• Now, we will add our source as a SQL Query. To do so, follow these steps:

  • Click on "Add sources from..." and select "Custom Query (SQL)"

  • Click on the CARTO Data Warehouse connection.

  • Select Type your own query.

  • Click on the "Add Source button".

The SQL Editor panel appears.

• Add the resulted table to your map. To do so, the following SQL query in the Editor replacing the qualified table name by yours in Step 13. and click on "Run".

SELECT * FROM carto-dw-ac-dp1glsh.private_atena_onboardingdemomaps_ca2c4d8c.catchment_regions

Once successfully executed, a map layer is added to the map.

• Rename the layer to "Catchment regions". Then, access the layer panel and within Fill Color section, color based on travel_time column. Just below, disable the Stroke Color using the toggle button.

• Now, let's add a SQL Text Parameter that will allow users to select their desired drive time to analyse the catchment areas around the store locations. To do so, access "Create a SQL Parameter" functionality located at the top right corner of the data sources panel.

• Once the SQL Parameter modal is opened, select Text Parameter type and fill the configuration as per below. Please note you should enter the values manually to provide users with a friendly name to pick the drive time of their choice.

• Once the parameter is configured, click on "Create parameter". After that, a parameter control is added to the right panel. Copy the SQL name so you can add it to the SQL query source.

• Now, let's open the SQL Editor of our catchment_regions source. As the travel_time column is a numeric one, we will be using a regex to select the correct drive time value to filter by the SQL parameter. Update your SQL Query using the below and click on "Run".

SELECT * FROM carto-dw-ac-dp1glsh.private_atena_onboardingdemomaps_ca2c4d8c.catchment_regions
WHERE travel_time IN (SELECT CAST(REGEXP_EXTRACT(t, r'\d*') AS NUMERIC) FROM {{drive_time}} AS t)

Once successfully executed, the layer will be reinstantiated and the parameter control will displayed the selectable values. Now, users can dynamically filter their interested drive time according to their needs.

• We are ready to publish and share our map. To do so, click on the Share button located at the top right corner and set the permission to Public. In the 'Shared Map Settings', enable SQL Parameter. Copy the URL link to seamlessly share this interactive web map app with others.

Finally, we can visualize the results!