Visualize static geometries with attributes varying over time

Context

For many geospatial use cases, it is common to work with identical static geometries where attributes vary over time or across different records. This is particularly relevant when working with administrative boundaries, infrastructure, or road networks, where multiple entries share the same geometry but contain different data attributes.

In this tutorial, you’ll learn how to easily visualize static geometries with changing attributes over time using the Aggregate by Geometry functionality in Builder.

For this example, we’ll use the Global Historical Climatology Network managed by NOAA that provides historical weather and climate data from weather stations worldwide. It includes observations such as temperature, precipitation, wind speed, and other climate indicators. In our case, we'll focus on USA weather station with a timeline covering 2016. By aggregating identical geometries, we can efficiently explore patterns, trends, and interactions while improving map performance.

---

Step 1: Visualize USA Weather Stations

1. Access the Maps tab from your CARTO Workspace using the Navigation menu and create a "New map".

1. To start, let's name our map "GHCN USA Weather Stations" and add the GHCN USA weather stations:

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

   • Search for ghcn_usa_weather_stations.

   • Select the table and click "Add Source".

1. A map layer is automatically added from your source. Rename it to "Weather Stations."

Our source dataset contains over 19 million records, but many rows share identical geometries since weather metrics are recorded over time at the same exact location. To assess this, let's add a Category Widget that counts records for each weather station.

1. Navigate to the Widgets tab, choose Category Widget and set the following configuration:

   • Operation: COUNT

   • Source Category: station_id

   • Formating: 12.3k

   • Behavior: Filter by viewport

As you’ll see, some stations have hundreds or even thousands of records, meaning there are overlapping points. To effectively analyze patterns and trends, we’ll use the Aggregate by Geometry functionality in Builder, which groups features based on their identical geometries, as defined in the spatial column of the data section.

1. Navigate back to the Layer panel and open the advanced options in the Visualization section. Activate Aggregate by geometry functionality. This will aggregate your layer by identical geometries in the spatial column defined in your data source.

As you can see, the Category Widget is still pointed out to the original source, as widgets are link to source level. However, your layer has been aggregated and now the properties link to it require to have an aggregation of choice both for the styling and when defining interactions.

---

Step 2: Extract and transform weather values

Before we start working further with this data, it's essential to correctly extract and transform the weather values on our GHCN-daily dataset because:

• The value column contains data for multiple weather elements, such as temperature, precipitation, and snow.

• The element column defines what type of data each row represents, meaning we must filter and assign the correct interpretation to each value.

• All values are stored in different units (e.g. tenths of ªC for temperature, mm for precipitation, etc.) and require conversions.

See the official NOAA GHCN-Daily ReadMe for details on data format and units.

We can do the pertinent adjustments to our data source by leveraging custom SQL Query as a source in Builder.

1. Go to your source card, click on the three dots and click Query this table.

The SQL Editor panel will open.

1. To make it easier to analyze, you can copy the query below and click "Run". In this query, we'll be tackling the following:

   • Convert raw values into meaningful units (e.g., tenths of °C to °C, tenths of mm to mm).

   • Provide user -friendly labels for each weather element so end-users can easily interpret the data.

   • Normalize values so that different weather elements (e.g., temperature vs. precipitation) can be styled together without distorting the map.

   • Filter out unnecessary elements using a WHERE clause to reduce noise and focus on key variables.

SELECT 
    *,

    -- Transform Values Based on Element Type
    CASE 
        WHEN element IN ('TMAX', 'TMIN', 'TAVG', 'TOBS') THEN value / 10  -- Convert Tenths of °C to °C
        WHEN element = 'PRCP' THEN value / 10  -- Convert Tenths of mm to mm
        WHEN element = 'SNOW' THEN value  -- Snowfall is already in mm
        WHEN element = 'SNWD' THEN value  -- Snow Depth is already in mm
        ELSE value  
    END AS raw_value,

    -- Normalized Values (0 to 1) for Styling
    CASE 
        WHEN element IN ('TMAX', 'TMIN', 'TAVG', 'TOBS') THEN (value / 10 + 50) / 100  -- Normalize from -50°C to 50°C
        WHEN element = 'PRCP' THEN LEAST(value / 300, 1)  -- Normalize precipitation (max 300mm)
        WHEN element = 'SNOW' THEN LEAST(value / 5000, 1)  -- Normalize snowfall (max 5000mm)
        WHEN element = 'SNWD' THEN LEAST(value / 5000, 1)  -- Normalize snow depth (max 5000mm)
        ELSE NULL  
    END AS normalized_value,

    -- Assign Friendly Labels
    CASE 
        WHEN element = 'TMAX' THEN 'Maximum Temperature (°C)' 
        WHEN element = 'TMIN' THEN 'Minimum Temperature (°C)' 
        WHEN element = 'TAVG' THEN 'Average Temperature (°C)' 
        WHEN element = 'TOBS' THEN 'Observed Temperature (°C)' 
        WHEN element = 'PRCP' THEN 'Total Precipitation (mm)' 
        WHEN element = 'SNOW' THEN 'Snowfall (mm)' 
        WHEN element = 'SNWD' THEN 'Snow Depth (mm)' 
        ELSE element  
    END AS element_friendly_label

FROM `carto-demo-data.demo_tables.ghcn_usa_weather_stations`
WHERE element IN (
    'PRCP',  -- Total Precipitation (mm)
    'SNOW',  -- Snowfall (mm)
    'TMAX',  -- Maximum Temperature (°C)
    'TMIN',  -- Minimum Temperature (°C)
    'TAVG',  -- Average Temperature (°C)
    'SNWD',  -- Snow Depth (mm)
    'TOBS'   -- Observed Temperature (°C)
)

---

Step 3: Extract insights using Widgets

• Now let's add some more Widgets to allow users retrieve insights. Go to the Widgets panel and select Category Widget, name it "Weather Metrics" setting the following configuration:

• Operation: AVG

• Source Category: element_friendly_label

• Aggregation column: raw_value

• Formating: 1.23

• Behavior: Filter by viewport

This will allow users to easily select the weather metric of choice to perform drill down analysis.

1. GHCN-daily dataset contains a timestamp covering 2016. To visualize the temporal pattern of each of the weather metrics, we'll add a new widget. Navigate to Widgets and choose Time Series Widget. Name it "Time Series" and set up the following configuration:

• Data:

  • Date: date

• Metric:

  • Operation: AVG

  • Aggregation column: raw_value

• Multiple series:

  • Split by: element_friendly_label

• Collapsible: True

In this widget, users can see the temporal variation of the weather metrics across 2016. They can either select the weather metric of interest by using the Category widget or leveraging the Time Series widget legend.

1. Add a Histogram widget to allow users inspect weather station elevation. Navigate to Widgets, select Histogram widget type and configure it as follows:

• Property: elevation

• Custom min. value: -61

• Formatting: 1.23

---

Step 4: Style your layer and add interactions

Now let's proceed to style our layer and add properties using aggregated properties.

1. First, let's style our weather station layer. Navigate to the Layer Panel and set the following styling configuration:

• Fill Color:

  • Property: AVG(normalized_value)

  • Palette: Sunset

  • Color Scale: Quantize

• Stroke:

  • Simple: #6b083f

  • Stroke weight: 0.8

1. Now, navigate to the Interactions tab, and enable Interactions for this layer. Select Click-type and Light with highlighted 1s value as the style. Now add the following properties with the corresponding label:

• ANY_VALUE(station_id) labelled as Station Id

• ANY_VALUE(State) labelled as State

• ANY_VALUE(Name) labelled as Name

• MODE(element_friendly_label) labelled as Weather Metric Type (Mode)

• AVG(raw_value) labelled as Weather Metric Value (Avg)

• AVG(normalized_value) labelled as Norm Weather Metric Value (Avg)

• ANY_VALUE(elevation) labelled as Elevation

1. Customize your legend by setting a label for the property used for styling. Simply rename it to "normalized weather metric value".

Now let's change the default basemap. You can do so by using the basemap menu located below the zoom control. Choose CARTO Basemap > Voyager.

---

Step 5: Filter sources via USA state boundaries

We want to allow users filtering weather stations by state. To achieve so, we'll add a dataset containing USA state boundaries and the state codes so we can use it to filter both the state boundary as well as the related stations.

1. To include USA State boundaries, let's add the source as a Custom SQL Query by:

• Add source from..

• Custom SQL Query (SQL)

• Choose CARTO Data Warehouse connection

• Add source

1. Open the SQL Editor, add the following query which retrieves the state code as well as the geometry boundary and click "Run".

WITH data_ AS(
  SELECT 
	SPLIT(name_alt, '|')[SAFE_OFFSET(0)] AS state_code, 
	geom
FROM  `carto-demo-data.demo_tables.usa_states_boundaries`)
SELECT * FROM data_

1. A new layer will appear in the layer panel. Move the layer down just below the Weather Stations layer and rename it "USA State Boundaries"

2. Style your layer following the configuration below:

   • Stroke:

     1. Simple: #16084d

     2. Opacity: 30%

     3. Stroke weight: 2

1. Now, let's add SQL Parameters which will allow us to upload the state codes to the parameter control so we can use them within placeholders of our custom SQL Query. Go to SQL Parameters button located in the top right of your source card.

1. Choose SQL Text Parameter and add the state codes using the state_code property available in the recently added source. Define your parameter name as State and the SQL name as {{state}}. Then, click "Add".

The parameter control will be added to the right side of your panel with a disabled status. Now let's use it in both of our queries.

1. Open the SQL Editor for the USA state boundaries and edit your query as below, including the WHERE statement. Then, click "Run".

WITH data_ AS(
  SELECT 
	SPLIT(name_alt, '|')[SAFE_OFFSET(0)] AS state_code, 
	geom
FROM  `carto-demo-data.demo_tables.usa_states_boundaries`)
SELECT * FROM data_
WHERE state_code IN {{state}}

1. Now, in the Weather Stations source, include the following statement in the existing query source. Then, click "Run".

AND state IN {{state}}

1. Now, the parameter control should appear enabled and you can use the multi-selector to choose which boundaries and weather stations should be visible in the map. The parameter will take action both on your layers as well as the linked widgets.

---

Step 6: Add map description and share your map

1. Before sharing the map, let’s add a map description to provide context on the data source and guide users on how to interact with it. Click on the "i" icon in the top right of the header bar. Then copy and paste the following Markdown syntax into the description field.

### GHCN Weather Stations
--- 

![Image: NOOA Icon](https://app-gallery.cartocdn.com/builder/noaa.png)
This map visualizes historical weather data from NOAA's [Global Historical Climatology Network (GHCN)](https://www.ncei.noaa.gov/products/land-based-station/global-historical-climatology-network-daily). 

It aggregates identical station geometries and allows interactive analysis of temperature, precipitation, and snowfall.

---

### How to Use This Map
- Use the **Weather Metrics Widget** to filter by temperature, precipitation, or snow.
- Explore **historical trends** with the **Time Series Widget**.
- Use the **State Filter** to analyze specific regions.
- Click on a station to view its **historical weather data**.

1. Use the "Preview" option to see how your map will appear to others before publishing. Once you're satisfied, click the Share icon to distribute the map within your organization, to specific users, SSO groups, or publicly. Copy the link to share access.

Now, end-users will be able to explore historical weather statistics from USA weather stations across 2016, analyzing trends in temperature, precipitation, and snowfall with interactive widgets and time-series visualizations.