LogoLogo
HomeDocumentationLoginTry for free
  • CARTO Academy
  • Working with geospatial data
    • Geospatial data: the basics
      • What is location data?
      • Types of location data
      • Changing between types of geographical support
    • Optimizing your data for spatial analysis
    • Introduction to Spatial Indexes
      • Spatial Index support in CARTO
      • Create or enrich an index
      • Work with unique Spatial Index properties
      • Scaling common geoprocessing tasks with Spatial Indexes
      • Using Spatial Indexes for analysis
        • Calculating traffic accident rates
        • Which cell phone towers serve the most people?
    • The modern geospatial analysis stack
      • Spatial data management and analytics with CARTO QGIS Plugin
      • Using data from a REST API for real-time updates
  • Building interactive maps
    • Introduction to CARTO Builder
    • Data sources & map layers
    • Widgets & SQL Parameters
    • AI Agents
    • Data visualization
      • Build a dashboard with styled point locations
      • Style qualitative data using hex color codes
      • Create an animated visualization with time series
      • Visualize administrative regions by defined zoom levels
      • Build a dashboard to understand historic weather events
      • Customize your visualization with tailored-made basemaps
      • Visualize static geometries with attributes varying over time
      • Mapping the precipitation impact of Hurricane Milton with raster data
    • Data analysis
      • Filtering multiple data sources simultaneously with SQL Parameters
      • Generate a dynamic index based on user-defined weighted variables
      • Create a dashboard with user-defined analysis using SQL Parameters
      • Analyzing multiple drive-time catchment areas dynamically
      • Extract insights from your maps with AI Agents
    • Sharing and collaborating
      • Dynamically control your maps using URL parameters
      • Embedding maps in BI platforms
    • Solving geospatial use-cases
      • Build a store performance monitoring dashboard for retail stores in the USA
      • Analyzing Airbnb ratings in Los Angeles
      • Assessing the damages of La Palma Volcano
    • CARTO Map Gallery
  • Creating workflows
    • Introduction to CARTO Workflows
    • Step-by-step tutorials
      • Creating a composite score for fire risk
      • Spatial Scoring: Measuring merchant attractiveness and performance
      • Using crime data & spatial analysis to assess home insurance risk
      • Identify the best billboards and stores for a multi-channel product launch campaign
      • Estimate the population covered by LTE cells
      • A no-code approach to optimizing OOH advertising locations
      • Optimizing site selection for EV charging stations
      • How to optimize location planning for wind turbines
      • Calculate population living around top retail locations
      • Identifying customers potentially affected by an active fire in California
      • Finding stores in areas with weather risks
      • How to run scalable routing analysis the easy way
      • Geomarketing techniques for targeting sportswear consumers
      • How to use GenAI to optimize your spatial analysis
      • Analyzing origin and destination patterns
      • Understanding accident hotspots
      • Real-Time Flood Claims Analysis
      • Train a classification model to estimate customer churn
      • Space-time anomaly detection for real-time portfolio management
      • Identify buildings in areas with a deficit of cell network antennas
    • Workflow templates
      • Data Preparation
      • Data Enrichment
      • Spatial Indexes
      • Spatial Analysis
      • Generating new spatial data
      • Statistics
      • Retail and CPG
      • Telco
      • Insurance
      • Out Of Home Advertising
      • BigQuery ML
      • Snowflake ML
  • Advanced spatial analytics
    • Introduction to the Analytics Toolbox
    • Spatial Analytics for BigQuery
      • Step-by-step tutorials
        • How to create a composite score with your spatial data
        • Space-time hotspot analysis: Identifying traffic accident hotspots
        • Spacetime hotspot classification: Understanding collision patterns
        • Time series clustering: Identifying areas with similar traffic accident patterns
        • Detecting space-time anomalous regions to improve real estate portfolio management (quick start)
        • Detecting space-time anomalous regions to improve real estate portfolio management
        • Computing the spatial autocorrelation of POIs locations in Berlin
        • Identifying amenity hotspots in Stockholm
        • Applying GWR to understand Airbnb listings prices
        • Analyzing signal coverage with line-of-sight calculation and path loss estimation
        • Generating trade areas based on drive/walk-time isolines
        • Geocoding your address data
        • Find similar locations based on their trade areas
        • Calculating market penetration in CPG with merchant universe matching
        • Measuring merchant attractiveness and performance in CPG with spatial scores
        • Segmenting CPG merchants using trade areas characteristics
        • Store cannibalization: quantifying the effect of opening new stores on your existing network
        • Find Twin Areas of top-performing stores
        • Opening a new Pizza Hut location in Honolulu
        • An H3 grid of Starbucks locations and simple cannibalization analysis
        • Data enrichment using the Data Observatory
        • New police stations based on Chicago crime location clusters
        • Interpolating elevation along a road using kriging
        • Analyzing weather stations coverage using a Voronoi diagram
        • A NYC subway connection graph using Delaunay triangulation
        • Computing US airport connections and route interpolations
        • Identifying earthquake-prone areas in the state of California
        • Bikeshare stations within a San Francisco buffer
        • Census areas in the UK within tiles of multiple resolutions
        • Creating simple tilesets
        • Creating spatial index tilesets
        • Creating aggregation tilesets
        • Using raster and vector data to calculate total rooftop PV potential in the US
        • Using the routing module
      • About Analytics Toolbox regions
    • Spatial Analytics for Snowflake
      • Step-by-step tutorials
        • How to create a composite score with your spatial data
        • Space-time hotspot analysis: Identifying traffic accident hotspots
        • Computing the spatial autocorrelation of POIs locations in Berlin
        • Identifying amenity hotspots in Stockholm
        • Applying GWR to understand Airbnb listings prices
        • Opening a new Pizza Hut location in Honolulu
        • Generating trade areas based on drive/walk-time isolines
        • Geocoding your address data
        • Creating spatial index tilesets
        • A Quadkey grid of stores locations and simple cannibalization analysis
        • Minkowski distance to perform cannibalization analysis
        • Computing US airport connections and route interpolations
        • New supplier offices based on store locations clusters
        • Analyzing store location coverage using a Voronoi diagram
        • Enrichment of catchment areas for store characterization
        • Data enrichment using the Data Observatory
    • Spatial Analytics for Redshift
      • Step-by-step tutorials
        • Generating trade areas based on drive/walk-time isolines
        • Geocoding your address data
        • Creating spatial index tilesets
Powered by GitBook
On this page
  • Context
  • Step 1: Visualize USA Weather Stations
  • Step 2: Extract and transform weather values
  • Step 3: Extract insights using Widgets
  • Step 4: Style your layer and add interactions
  • Step 5: Filter sources via USA state boundaries
  • Step 6: Add map description and share your map

Was this helpful?

Export as PDF
  1. Building interactive maps
  2. Data visualization

Visualize static geometries with attributes varying over time

Last updated 3 months ago

Was this helpful?

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.

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

  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.

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

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

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 functionality in Builder, which groups features based on their identical geometries, as defined in the spatial column of the data section.

See the official for details on data format and units.

Now, let's add 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.

Aggregate by Geometry
Aggregate by Geometry
NOAA GHCN-Daily ReadMe
SQL Parameters
Intermediate difficulty banner