Build a dashboard to understand historic weather events

Context

In this tutorial, you'll learn how to create an interactive dashboard to navigate through America's severe weather history, focusing on hail, tornadoes, and wind.

Our goal is to create an interactive map that transitions through different layers of data, from state boundaries to the specific paths of severe weather events, using NOAA's datasets.

Get ready to dive deep into visualizing the intensity and patterns of severe weather across the U.S., uncovering insights into historical events and their impacts on various regions.

Steps To Reproduce

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

• Click on "New map" button to create a new Builder map.

• Let's add USA severe weather paths as your main data sources to the map. To do so:

  • Select the Add source from button at the bottom left on the page.

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

Now, run the below query to add USA severe weather paths source:

SELECT * FROM `carto-demo-data.demo_tables.usa_severe_weather_paths`

• Change the layer name to "Weather Events" and the map title to "USA - Historic Severe Weather Events".

• Access the Layer Panel and configure the Stroke Color to "Light Blue" . Then, go back to the main Layers section and set the Blending option to "Additive".

• Now, let's modify the Basemap option to "Dark Matter" so the weather event paths are properly highlighted. Zoom in to inspect the weather paths.

Zoom

Widgets empower users to dynamically explore data, leading to rich visualizations. They also serve to filter data based on the map viewport and interconnected widgets. Let's add some widget to provide insights to our end-users.

• Firstly, we will add a Formula Widget to display the estimated property loss. To do so, navigate to the Widgets tab, select Formula Widget and set the configuration as follows:

  • Operation: SUM

  • Source Category: Loss

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

• Then, add another Formula Widget, this time to display the estimated crop loss. To add it, navigate to the Widgets tab, select Formula Widget and set the configuration as follows:

  • Operation: SUM

  • Source Category: Closs

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

• Add two additional Formula Widgets, both usingCOUNT operation but one using fat property to indicate the total fatalities and the other using inj property, indicating the total injuries caused by severe weather event.

• Time to include a different type of widget. We'll include a Pie Widget displaying the estimated property loss by weather event type. Navigate to the Widgets tab, select Pie Widget and set the configuration as follows:

  • Operation: SUM

  • Source Category: event_Type

  • Aggregation Column: Loss

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

• Time Series Widget allows users to temporarily analyze weather events. Navigate to the Widgets tab, select Time Series Widget and set the configuration as follows:

  • Time: Date

  • Operation: COUNT

  • Split by: event_Type

  • Display Interval: 1 year

SQL parameters are placeholders that you can add in your SQL Query source and can be replaced by input values set by users. In this tutorial, we will learn how you can use them to dynamically update the weights of normalized variables.

• The first step in this section is to create a SQL Text Parameter. You can access this by clicking on the top right icon in the Sources Panel.

How to add a SQL parameter

• Set the SQL Text Parameter configuration as follows and click on "Create parameter" once completed:

  • Values - Add data from a source:

    • Source: usa_severe_weather_paths

    • Property: event_type

  • Naming:

    • Display name: Event Type

    • SQL name: {{event_type}}

• Once you create a parameter, a parameter control is added to the right panel. From there, you can copy the parameter SQL name to add it to your query as below:

SELECT * FROM `carto-demo-data.demo_tables.usa_severe_weather_paths`
WHERE event_Type in {{event_type}}

• We will add another SQL Text Parameter, this time retrieving the state names using name property so we can filter the weather events by state.

  • Values - Add data from a source:

    • Source: usa_severe_weather_paths

    • Property: nameNaming:

  • Naming:

    • Display name: State

    • SQL name: {{state}}

• Once the parameter is created, a parameter control is added to Builder. Use the parameter in your query by adding an additional statement as per below query:

SELECT * FROM `carto-demo-data.demo_tables.usa_severe_weather_paths`
WHERE event_Type in {{event_type}}
AND name in {{state}}

• Finally, we'll add a SQL Date Parameter to filter the severe weather events for the specified time frame.

  • Values

    • Start date: 1950-01-03

    • End date: 2022-01-03

  • Naming:

    • Display name: Event Date

    • Start date SQL name: event_date_from

    • End date SQL name: event_date_to

• Once the parameter is created and the parameter control is added to the map, you can use it in your query as shown below:

SELECT * FROM `carto-demo-data.demo_tables.usa_severe_weather_paths`
WHERE event_Type in {{event_type}}
AND name in {{state}}
AND date >= {{event_date_from}} AND date <= {{event_date_to}}

Your map with the addition of the parameter controls should look similar to the below.

• Let's add more sources to our map. First, we will add a custom query (SQL) source to display USA State boundaries including the state SQL parameter in your query as per below.

SELECT * FROM `carto-demo-data.demo_tables.usa_states_boundaries`
WHERE name in {{state}}

• Once the layer is added to the map, rename it to "State Boundary", disable the Fill Color and set the Stroke Color to white.

Now, when you use the 'State' parameter control to filter, both the weather events and the state boundaries will be seamlessly filtered at the same time.

• Add a pre-generated tileset source displaying OSM point location of buildings at a worldwide scale. To do so:

  • Select the Add source from button at the bottom left on the page.

  • Click on the Data Explorer.

  • Navigate to CARTO Data Warehouse > carto-demo-data > demo_tilesets.

  • Select osm_buildings tileset.

  • Click "Add Source".

• Name the recently added layer "OSM Buildings" and move it to the bottom of the layer order by dragging it down. Set the Fill Color to dark brown and its Opacity to 0.5

• Add a map description to provide further information to end-users consulting the map. You can use the below description using markdown syntax.

#### Historical Severe Weather

This map showcases the paths of hail, tornadoes, and wind across the United States, providing insight into historical severe weather events.

Data sourced from NOAA, accessible at:
[SPC NOAA Data](http://www.spc.noaa.gov/wcm/#data)
____

**Data Insights**

- **State Boundary**: Displays the boundary for USA State.
- **Aggregated Severe Weather Events (H3)**: Employs an H3 spatial index for comprehensive visualization of incidents density.
- **Severe Weather Events Paths**: Visualize severe weather events (wind, hail, tornadoes) paths.
- **Building Locations**: Open Street Map building locations to display potentially affected regions. 

Bonus track

For our bonus section, we're going to add something extra to our map. We'll create a new layer that includes a buffer zone extending 5 meters around the weather event paths. Then, we'll turn these areas into polygons and use H3 spatial indexing to group the weather event info together.

H3 spatial indexes help us get a clearer, aggregated view of the data, which makes it easier to see patterns, especially when you're zoomed out. Ready to dive in? Let's get started!

1. In Workflows page, use the "New workflow" button to start a new Workflow. Select CARTO Data warehouse as the connection you want to work with.

1. From the Sources panel located on the left side, navigate to CARTO Data Warehouse > demo_data > demo_tables and locate usa_severe_weather_paths. Drag and drop the source table into the canvas.

1. Rename the Workflows to Aggregating weather events to H3. In the Components tab, add ST Buffer and set the buffer radius to 5 meters.

1. Now we will proceed to perform a polyfill of the buffered weather paths. For that, we will use H3 Polyfill component setting the H3 resolution level at 8. In the configuration, ensure you are bringing the properties from your input tables. ❗ This analysis may take some time to complete. Consider using a Limit or Simple Filter component to reduce the input data for shorter processing times.

2. To finish this Workflow, add a Save as Table component to save the results as a permanent table.

3. Now let's go back to our Builder map and create a new source. Specifically, we we'll add this layer using a custom SQL query source so we can leverage the existing parameters in the map. Type the following query, updating the qualified table name on Step 5, and execute the query:

SELECT h3, COUNT(*) as weather_path_count, SUM(inj) AS inj FROM `yourproject.yourdataset.severe_weather_h3level8`
WHERE name IN {{state}} AND 
date >= {{event_date_from}} AND date <= {{event_date_to}}
AND event_type IN {{event_type}}
GROUP BY h3

1. Rename the newly added layer to "Aggregated Severe Weather Paths". Open the Layer panel and set the aggregated resolution size of the H3 one level higher, to 5.

1. We will now style the layer based on the number of severe weather paths within each H3 cell. For that, set the color based on within the Fill Color section to use the COUNT() aggregation over a numeric column such as inj. Set the Steps of the color palette to 3 and use the color scheme of your preference.

1. Aggregated data is better visualized at lower zoom levels whereas you want to display the raw data, in this case the weather path lines at higher zoom levels. You can control when layers are visualize using the Visibility by zoom level functionality. Set a specific visibility range for your layers:

   • Aggregated Severe Weather Paths: Zoom 0 - 5

   • State Boundaries: All zoom levels (0-21)

   • Severe Weather Paths: Zoom 6 - 21

   • Buildings: Zoom 7 - 21

Awesome job making it this far and smashing through the bonus track! Your map should now be looking similar to what's shown below.