# Build a dashboard to understand historic weather events

<div align="left"><figure><img src="https://3015558743-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FFEElAdsRIl9DzfMhbRlB%2Fuploads%2F8iAlvj1s2Th85m6IvcG5%2Fbeginner%20banner.png?alt=media&#x26;token=10e8f40a-3632-4920-87c3-6b0dca2a6775" alt="Beginner difficulty banner" width="175"><figcaption></figcaption></figure></div>

## **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.&#x20;

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](https://www.spc.noaa.gov/)'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.

<figure><img src="https://3015558743-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FFEElAdsRIl9DzfMhbRlB%2Fuploads%2FovDcctcTXvrZA7Ki9LsW%2Fimage.png?alt=media&#x26;token=5c86cd64-9dce-444d-abb9-b6f00de1fdfc" alt=""><figcaption></figcaption></figure>

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

<figure><img src="https://3015558743-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FFEElAdsRIl9DzfMhbRlB%2Fuploads%2FfB6rcBxYL0nkQr6AYjJ3%2Fimage.png?alt=media&#x26;token=5f36a636-ab31-4d94-b9f4-e267dffc22ba" alt=""><figcaption></figcaption></figure>

* Let's add USA severe weather paths as your main data sources to the map. To do so:&#x20;
  * Select the _Add source from_ button at the bottom left on the page.&#x20;
  * Click on the _CARTO Data Warehouse_ connection.
  * Select _Type your own query_.
  * Click on the _Add Source button_.

<figure><img src="https://3015558743-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FFEElAdsRIl9DzfMhbRlB%2Fuploads%2FESUVFIxegkWhkRYkbCKS%2Fimage.png?alt=media&#x26;token=cf814a66-6e03-467d-8dcd-d37af8791c1e" alt=""><figcaption></figcaption></figure>

The SQL Editor panel will be opened.&#x20;

<figure><img src="https://3015558743-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FFEElAdsRIl9DzfMhbRlB%2Fuploads%2FXoD0YRpHVwT7zQl5gzv2%2Fimage.png?alt=media&#x26;token=f7279922-8964-4d25-89b2-9538516ad8bb" alt=""><figcaption></figcaption></figure>

Now, run the below query to add USA severe weather paths source:&#x20;

<pre class="language-sql"><code class="lang-sql"><strong>SELECT * FROM `carto-demo-data.demo_tables.usa_severe_weather_paths`
</strong></code></pre>

<figure><img src="https://3015558743-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FFEElAdsRIl9DzfMhbRlB%2Fuploads%2FBjluiUXPKlQns2QSEnai%2Fimage.png?alt=media&#x26;token=dc087408-2b35-4d26-a8dd-75cbac2ffb82" alt=""><figcaption></figcaption></figure>

* 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".

<figure><img src="https://3015558743-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FFEElAdsRIl9DzfMhbRlB%2Fuploads%2F5KUYevIpUmFcU1DQ8Zx1%2Fimage.png?alt=media&#x26;token=a0390268-57d3-43c4-9322-a6e3ca163c6b" alt=""><figcaption></figcaption></figure>

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

<figure><img src="https://3015558743-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FFEElAdsRIl9DzfMhbRlB%2Fuploads%2FFJRNFB7npMvDWdR8CG8O%2Fimage.png?alt=media&#x26;token=fcb838c3-6ecb-4b6d-b8bf-216f37c11618" alt=""><figcaption><p>Zoom</p></figcaption></figure>

[Widgets](https://docs.carto.com/carto-user-manual/maps/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.&#x20;

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

<figure><img src="https://3015558743-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FFEElAdsRIl9DzfMhbRlB%2Fuploads%2FQe8xFLWRh3zfQVetmaAT%2Fimage.png?alt=media&#x26;token=f3ea4f54-38c6-49cf-9e26-efd23f4b4546" alt="" width="361"><figcaption></figcaption></figure>

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

<figure><img src="https://3015558743-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FFEElAdsRIl9DzfMhbRlB%2Fuploads%2FUZcEQi8v8u8vs1WLSKJQ%2Fimage.png?alt=media&#x26;token=c7c11fe5-8881-411d-a8a2-66e613cec058" alt="" width="288"><figcaption></figcaption></figure>



* Add two additional **Formula Widgets,** both using`COUNT` 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.&#x20;

<figure><img src="https://3015558743-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FFEElAdsRIl9DzfMhbRlB%2Fuploads%2FsZoiDpxzSqTqnllYiqDF%2Fimage.png?alt=media&#x26;token=ccd8b3cb-594c-4156-bf1e-9c7c294f1d48" alt=""><figcaption></figcaption></figure>

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

<figure><img src="https://3015558743-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FFEElAdsRIl9DzfMhbRlB%2Fuploads%2FBCEMzsF0VmBCQpmTPgTD%2Fimage.png?alt=media&#x26;token=d782b623-544f-489d-b4f5-90fc83f2a6fa" alt="" width="368"><figcaption></figcaption></figure>

* **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`

<figure><img src="https://3015558743-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FFEElAdsRIl9DzfMhbRlB%2Fuploads%2FfRqDbjowYX8Igxv1VSzF%2Fimage.png?alt=media&#x26;token=28e90c3f-b891-4906-a78e-98f7e6edafb1" alt=""><figcaption></figcaption></figure>

[SQL parameters](../../widgets-and-sql-parameters#using-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.&#x20;

<figure><img src="https://3015558743-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FFEElAdsRIl9DzfMhbRlB%2Fuploads%2FBaUyhsE47fST0emYSwl9%2FSQL%20Parameter%20UI.png?alt=media&#x26;token=a33f3619-3152-4d09-90c7-18809c3ab46a" alt="A screenshot showing how to add a SQL parameter" width="316"><figcaption><p>How to add a SQL parameter</p></figcaption></figure>

* Set the **SQL Text Parameter** configuration as follows and click on "Create parameter" once completed:
  * Values - Add data from a source:
    * &#x20;Source: `usa_severe_weather_paths`
    * Property: `event_type`
  * Naming:
    * Display name: `Event Type`
    * SQL name: `{{event_type}}`

<figure><img src="https://3015558743-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FFEElAdsRIl9DzfMhbRlB%2Fuploads%2F3jKHFO8bvSB4895w5w7G%2Fimage.png?alt=media&#x26;token=608f7e09-33df-404d-bf6a-04584fd370a9" alt="" width="563"><figcaption></figcaption></figure>

* 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:

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

<figure><img src="https://3015558743-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FFEElAdsRIl9DzfMhbRlB%2Fuploads%2FGIDtNTWhOtZDPVgVAN5u%2Fimage.png?alt=media&#x26;token=0a6d4ec3-2081-4c25-84e2-18f390139490" alt=""><figcaption></figcaption></figure>

* 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:
    * &#x20;Source: `usa_severe_weather_paths`
    * Property: `name`Naming:
  * Naming:
    * Display name: `State`
    * SQL name: `{{state}}`

<figure><img src="https://3015558743-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FFEElAdsRIl9DzfMhbRlB%2Fuploads%2FfDu9lddwkAPxBS5aIi8U%2Fimage.png?alt=media&#x26;token=5e6d05b2-2ba7-4ea0-9ec4-373362c4e77b" alt="" width="563"><figcaption></figcaption></figure>

* 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:

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

<figure><img src="https://3015558743-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FFEElAdsRIl9DzfMhbRlB%2Fuploads%2F8u7S804AUVpfXSW7WpsJ%2Fimage.png?alt=media&#x26;token=606b13e3-21cb-43f1-9b99-65c8650892db" alt=""><figcaption></figcaption></figure>

* Finally, we'll add a **SQL Date Parameter** to filter the severe weather events for the specified time frame.&#x20;
  * 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`

<figure><img src="https://3015558743-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FFEElAdsRIl9DzfMhbRlB%2Fuploads%2FCdJHcWEl0TLV9cDvwAPk%2Fimage.png?alt=media&#x26;token=1996b9a4-0407-468a-81c9-2839a2e42b1e" alt="" width="563"><figcaption></figcaption></figure>

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

```sql
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.

<figure><img src="https://3015558743-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FFEElAdsRIl9DzfMhbRlB%2Fuploads%2FUZcZHFSKTuEstXyD1HXk%2Fimage.png?alt=media&#x26;token=faa8f715-faf8-435a-acd3-ccaa6a60a33e" alt=""><figcaption></figcaption></figure>

* 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.&#x20;

```sql
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`.&#x20;

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.&#x20;

<figure><img src="https://3015558743-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FFEElAdsRIl9DzfMhbRlB%2Fuploads%2FdC4UIDvOQ7GILPy03DM0%2Fimage.png?alt=media&#x26;token=c902157c-caf8-4f6d-a26c-b734a813fb20" alt=""><figcaption></figcaption></figure>

* 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.&#x20;
  * Click on the _Data Explorer._
  * Navigate to _CARTO Data Warehouse > carto-demo-data > demo\_tilesets._
  * Select `osm_buildings` tileset.
  * Click "Add Source".

<figure><img src="https://3015558743-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FFEElAdsRIl9DzfMhbRlB%2Fuploads%2FqvnaLlF90Y0qg4eIt4Xw%2Fimage.png?alt=media&#x26;token=9bd5043b-cef6-4be7-bd1c-5af913d28bcf" alt=""><figcaption></figcaption></figure>

* &#x20;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`

<figure><img src="https://3015558743-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FFEElAdsRIl9DzfMhbRlB%2Fuploads%2FuiSMzEa6vCPdVk8X0LUQ%2Fimage.png?alt=media&#x26;token=0805d4c8-1114-4790-ac93-fca212307590" alt=""><figcaption></figcaption></figure>

* Add a **map description** to provide further information to end-users consulting the map. You can use the below description using markdown syntax.&#x20;

```markdown
#### 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.&#x20;

<figure><img src="https://3015558743-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FFEElAdsRIl9DzfMhbRlB%2Fuploads%2FQUq8t7s5Xdk1XU91yfZg%2Fimage.png?alt=media&#x26;token=605e406d-5513-4016-95eb-e4d4df006242" alt=""><figcaption></figcaption></figure>

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

<figure><img src="https://3015558743-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FFEElAdsRIl9DzfMhbRlB%2Fuploads%2FEsxWUh1O6HKHLqzovdUS%2Fimage.png?alt=media&#x26;token=ab220812-a271-45c8-b928-7b0d36baca06" alt=""><figcaption></figcaption></figure>

3. Rename the Workflows to `Aggregating weather events to H3`. In the _Components_ tab, add **ST Buffer** and set the buffer radius to `5 meters.`

<figure><img src="https://3015558743-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FFEElAdsRIl9DzfMhbRlB%2Fuploads%2FKtnh9C6ym65NON7cgzjS%2Fimage.png?alt=media&#x26;token=dd6ec645-42e3-49a9-8854-f5f91df79f00" alt=""><figcaption></figcaption></figure>

4. 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.&#x20;
5. To finish this Workflow, add a **Save as Table** component to save the results as a permanent table.&#x20;
6. 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:

```sql
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
```

7. 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`.&#x20;

<figure><img src="https://3015558743-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FFEElAdsRIl9DzfMhbRlB%2Fuploads%2FewUhLbT2B57YWwqgnm0E%2Fimage.png?alt=media&#x26;token=c9e95a2b-6f1f-4873-814a-161f2c899638" alt=""><figcaption></figcaption></figure>

8. 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.&#x20;

<figure><img src="https://3015558743-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FFEElAdsRIl9DzfMhbRlB%2Fuploads%2FoiIHJEKsuje9Ny4iW0fy%2Fimage.png?alt=media&#x26;token=0524885a-c2e0-4797-9b3f-60e7fc128edf" alt=""><figcaption></figcaption></figure>

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

{% embed url="https://clausa.app.carto.com/map/5d942679-411f-4ab7-afb7-0f6061c9af63" %}
