Create a dashboard with user-defined analysis using SQL Parameters

Context

In this tutorial, we'll explore the power of Builder in creating web map applications that adapt to user-defined inputs. Our focus will be on demonstrating how SQL Parameters can be used to dynamically update analyses based on user input. You'll learn to implement these parameters effectively, allowing for real-time adjustments in your geospatial analysis.

Although our case study revolves around assessing the risk on Bristol's cycle network, the techniques and methodologies you'll learn are broadly applicable. This tutorial will equip you with the skills to apply similar dynamic analysis strategies across various scenarios, be it urban planning, environmental studies, or any field requiring user input for analytical updates.

Step-by-Step Guide:

1. Access the Maps section from your CARTO Workspace using the Navigation menu.

1. Click on "New map". A new Builder map will open in a new tab.

In this tutorial, we will undertake a detailed analysis of accident risks on Bristol's cycle network. Our objective is to identify and assess the safest and riskiest segments of the network.

1. So first, let's add bristol_cycle_network data source following below steps:

   • Click on "Add sources from..." and select "Data Explorer"

   • Navigate to CARTO Data Warehouse > demo_data > demo_tables

   • Select bristol_cycle_network table and click "Add source"

1. A new layer appears once the source is added to the map. Rename the layer to "Cycle Network" and change the title of the map to "Analyzing risk on Bristol cycle routes".

1. Then, we will add bristol_traffic_accidents data source following below steps:

   • Click on "Add sources from..." and select "Data Explorer"

   • Navigate to CARTO Data Warehouse > demo_data > demo_tables

   • Select bristol_traffic_accidents table and click "Add source"

1. A new layer is added. Rename it to 'Traffic Accidents'.

1. Using Traffic Accidents source, we are going to generate an influence area using ST_BUFFER() function whose radius will be updated by users depending on the scenario they are looking to analyse. To do so, we will add again the Traffic Accidents data source, but this time, we will add it as a SQL Query following 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 will be opened.

1. Enter the following query, with the buffer radius distance set to 50 and click on "Run".

SELECT * EXCEPT(geom), ST_BUFFER(geom,50) as geom FROM carto-demo-data.demo_tables.bristol_traffic_accidents

1. Rename the layer to 'Traffic Influence Area', move it just below Traffic Accidents existing layer. Access the Layer panel and within Fill Color section, reduce its opacity to 0.3 and set the color to red. Just below, disable the Stroke Color using the toggle button.

1. Now, we'll transform bristol_cycle_network source table to a query. To do so, you can click on the three dots located in the source card and click on "Query this table".

1. Click "Continue" on the warning modal highlighting that the styling of this layer will be lost.

1. The SQL Editor panel is displayed with a SELECT * statement. Click on "Run" to execute the query.

1. Repeat Step 10, Step 11 and Step 12 to generate a query, this time from bristol_traffic_accidents source table.

1. To easily distinguish each data source, you can rename them using the 'Rename' function. Simply click on the three dots located on the data source card and select 'Rename' to update their names accordingly to match the layer name.

The Traffic Accidents source contains attributes which spans from 2017-01-03 to 2021-12-31. To allow users interact and obtain insights for the desired time period, we will add to the dashboard:

• A Time Series Widget

• A SQL Date Parameter

1. First, we'll incorporate a Time Series Widget into our map. To do this, head over to the 'Widgets' tab and click on 'Add new widget'. In the Data section, use the 'Split by' functionality to add multiple series by selecting the severity_description column. Also, make sure to rename the widget appropriately to "Accidents by Severity". Once you've configured it, the Time Series Widget will appear at the bottom of the interface, displaying essential information relevant to each severity category.

1. Now, let's add a SQL Date Parameter that will allow users to select their desired time period by accessing to a calendar interface. To do so, access "Create a SQL Parameter" functionality located at the top right corner of the data sources panel.

1. Then, select SQL Date Parameter type in the modal and set the configuration as per below. details Once the configuration is filled, click on "Create parameter".

   • Start date: 2017-01-03

   • End date: 2021-12-31

   • Display name: Event Date

   • Start date SQL name: {{event_date_from}}

   • End date SQL name: {{event_date_to}}

1. A parameter control placeholder will appear in the right panel in Builder. Now let's add the parameter in our Traffic Accident SQL Query using the start and end date SQL name as per below. Once executed, a calendar UI will appear where users can select the desired time period.

SELECT * FROM `carto-demo-data.demo_tables.bristol_traffic_accidents`
WHERE date_ >= {{event_date_from}} AND date_ <= {{event_date_to}}

As you might know, SQL Parameters can be used with multiple sources at the same time. This is perfect for our approach as we are looking to filter and dynamically update an analysis that affect to different sources.

1. For instance, we will now add the same WHERE statement to filter also the Accident Influence Area source to make sure that both sources and layers are on sync. To do so, open the SQL Query of Accident Influence Area source and update it as per below query:

SELECT * EXCEPT(geom), ST_BUFFER(geom,50) as geom FROM carto-demo-data.demo_tables.bristol_traffic_accidents
WHERE date_ >= {{event_date_from}} AND date_ <= {{event_date_to}}

Then click run to execute it.

Now when using Event Date parameter, both sources, Traffic Accidents and Accident Influence Area are filtered to the specified time period.

1. Now, we are going to add a new SQL Parameter that will allow users to define their desired radius to calculate the Accident Influence Area. This parameter will be added as a placeholder to our ST_BUFFER() function already added to our Accident Influence Area SQL query. First, create a SQL Numeric Parameter and configure it as per below:

   • Slider Type: Simple

   • Min Value: 0

   • Default Value: 30

   • Max Value: 100

   • Scale type: Discrete

   • Step increment: 10

   • Parameter Name: Accident Influence Radius

   • Parameter SQL Name: {{accident_influence_radius}}

1. Once the parameter is added as a control placeholder, you can use the SQL name in your Accident Influence Area SQL Query. You just need to replace the 50 value in the ST_BUFFER() function by {{accident_influence_radius}}.

The output query should look as per below:

SELECT * EXCEPT(geom), ST_BUFFER(geom,{{accident_influence_radius}}) as geom FROM carto-demo-data.demo_tables.bristol_traffic_accidents
WHERE date_ >= {{event_date_from}} AND date_ <= {{event_date_to}}

Now, users can leverage Accident Influence Radius parameter control to dynamically update the accident influence area.

1. Now we can update Cycle Network source to count the number of accident regions that intersect with each segment to understand its risk. As you can see, the query takes into account the SQL parameters to calculate the risk according to the user-defined parameters.

-- Extract the accident influence area
WITH accident_area AS (
  SELECT 
    ST_BUFFER(geom, {{accident_influence_radius}}) as buffered_geom,
    *
  FROM 
    `carto-demo-data.demo_tables.bristol_traffic_accidents`
  WHERE date_ >= {{event_date_from}} AND date_ <= {{event_date_to}}
),
-- Count the accident areas that intersect with a cycle network
network_with_risk AS (
  SELECT 
    h.geoid,
    ANY_VALUE(h.geom) AS geom,
    COUNT(a.buffered_geom) AS accident_count
  FROM 
    `carto-demo-data.demo_tables.bristol_cycle_network` h
  LEFT JOIN 
    accident_area a 
  ON 
    ST_INTERSECTS(h.geom, a.buffered_geom)
  GROUP BY h.geoid
)
-- Join the risk network with those were no accidents occurred
SELECT 
  IFNULL(a.accident_count,0) as accident_count, b.* 
 FROM `carto-demo-data.demo_tables.bristol_cycle_network` b 
 LEFT JOIN network_with_risk a
 ON a.geoid = b.geoid
 

1. Access Cycle Network layer panel and in the Stroke Color section select accident_count as the 'Color based on' column. In the Palette, set the Step Number to 4, select 'Custom' as the palette type and assign the following colors:

   • Color 1: #40B560

   • Color 2: #FFB011

   • Color 3: #DA5838

   • Color 4: #83170C

Then, set the Data Classification Method to Quantize and set the Stroke Width to 2.

Now, the Cycle Network layer displays cycle network by accident count, so users can easily extract risk insights on it.

1. Now we will add some Widgets linked to Cycle Network source. First, we will add a Pie Widget that displays accidents by route type. Navigate to the Widgets tab, select Pie Widget and set the configuration as follows:

   • Operation: SUM

   • Source Category: Newroutety

   • Aggregation Column: Accident_count

Once the configuration is set, the widget is displayed in the right panel.

1. Then, we'll add a Histogram widget to display the network accident risk. Go back and click on the icon to add a new widget and select Cycle Network source. Afterwards, select Histogram as the widget type. In the configuration, select Accident_count in the Data section and set the number of buckets in the Display options to 5.

1. Finally, we will add a Category widget displaying the number of accidents by route status. To do so, add a new Category widget and set the configuration as below:

   • Operation: SUM

   • Source category: R_status

   • Aggregation column: Accident_count

1. After setting the widgets, we are going to add a new parameter to our dashboard that will allow users filter those networks and accidents by their desired route type(s). To do so, we'll click on 'Create a SQL Parameter' and select Text Parameter. Set the configuration as below, adding the values from Cycle Network source using newroutety column.

1. A parameter control placeholder will be added to the parameter panel. Now, let's update the SQL Query sources to include this WHERE statement WHERE newroutety IN {{route_type}} to filter both accidents and network by the route type. The final SQL queries for the three sources should look as below:

Cycle Network SQL Query:

-- Extract the accident influence area
WITH accident_area AS (
  SELECT 
    ST_BUFFER(geom, {{accident_influence_radius}}) as buffered_geom,
    *
  FROM 
    `carto-demo-data.demo_tables.bristol_traffic_accidents`
  WHERE date_ >= {{event_date_from}} AND date_ <= {{event_date_to}}
),
-- Count the accident areas that intersect with a cycle network
network_with_risk AS (
  SELECT 
    h.geoid,
    ANY_VALUE(h.geom) AS geom,
    COUNT(a.buffered_geom) AS accident_count
  FROM 
    `carto-demo-data.demo_tables.bristol_cycle_network` h
  LEFT JOIN 
    accident_area a 
  ON 
    ST_INTERSECTS(h.geom, a.buffered_geom)
  GROUP BY h.geoid
)
-- Join the risk network with those were no accidents occurred
SELECT 
  IFNULL(a.accident_count,0) as accident_count, b.* 
 FROM `carto-demo-data.demo_tables.bristol_cycle_network` b 
 LEFT JOIN network_with_risk a
 ON a.geoid = b.geoid
 WHERE newroutety IN {{route_type}}

Traffic Accidents SQL Query

WITH buffer AS (
   SELECT 
       ST_BUFFER(geom,{{accident_influence_radius}}) as buffer_geom, 
       * 
   FROM `carto-demo-data.demo_tables.bristol_traffic_accidents`
   WHERE date_ >= {{event_date_from}} AND date_ <= {{event_date_to}})

SELECT 
    a.* EXCEPT(buffer_geom)
FROM buffer a,
`carto-demo-data.demo_tables.bristol_cycle_network` h
WHERE  ST_INTERSECTS(h.geom, a.buffer_geom) 
AND newroutety IN {{route_type}}

Accident Influence Area SQL Query

WITH buffer AS (
SELECT ST_BUFFER(geom,{{accident_influence_radius}}) as geom, 
* EXCEPT(geom) 
FROM `carto-demo-data.demo_tables.bristol_traffic_accidents`
WHERE date_ >= {{event_date_from}} AND date_ <= {{event_date_to}})


  SELECT 
    a.*
  FROM buffer a,
  `carto-demo-data.demo_tables.bristol_cycle_network` h
  WHERE  ST_INTERSECTS(h.geom, a.geom) 
  AND newroutety IN {{route_type}}

Once you execute the updated SQL queries you will be able to filter the accidents and network by the route type.

1. Change the style of Traffic Accidents layer, setting the Fill Color to red and the Radius to 2. Disable the Stroke Color.

1. Interactions allow users to extract insights from specific features by clicking or hoovering over them. Navigate to the Interactions tab and enable Click interaction for Cycle Network layer, setting below attributes and providing a user-friendly name.

1. In the Legend tab, change the text label of the first step of Cycle Network layer to NO ACCIDENTS and rename the title to Accidents Count.

1. Add a map description to your dashboard to provide further context to the viewer users. To do so, access the map description functionality by clicking on the icon located at the top right corner of the header. You can add your own description or copy the below. Remember map description ad widget notes support markdown syntax.

### Cycle Routes Safety Analysis

![Image: Global Populated Places](https://app-gallery.cartocdn.com/builder/cyclist_accident.jpg)

This map is designed to promote safer cycling experiences in Bristol and assist in efficient transport planning.  

#### What You'll Discover: 

- **Historical Insight into Accidents**: Filter accidents by specific date ranges to identify temporal patterns, perhaps finding times where increased safety measures could be beneficial.

- **Adjustable Influence Area**: Adjust the accident influence radius to dynamically identify affected cycle routes based on different scenarios.

- **Cycle Route Analysis**: By analyzing specific route types, we can make data-driven decisions for optimization of cycle route network.

- **Temporal Accident Trends**: Utilize our time series widget to recognize patterns. Are some months riskier than others? These insights can inform seasonal safety campaigns or infrastructure adjustments.

1. 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!