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
  • Step 1. Get the data ready
  • Step 2. Generate space-time bins
  • Step 3. Perform space-time hotspot analysis
  • Step 4. Getting cold and hotspots

Was this helpful?

Export as PDF
  1. Advanced spatial analytics
  2. Spatial Analytics for BigQuery
  3. Step-by-step tutorials

Space-time hotspot analysis: Identifying traffic accident hotspots

Last updated 11 months ago

Was this helpful?

Spatio-temporal analysis plays a crucial role in extracting meaningful insights from data that possess both spatial and temporal components. By incorporating spatial information, such as geographic coordinates, with temporal data, such as timestamps, spatio-temporal analysis unveils dynamic behaviors and dependencies across various domains. This applies to different industries and use cases like car sharing and micromobility planning, urban planning, transportation optimization, and more.

In this example, we will perform space temporal analysis to identify traffic accident hotspots using the location and time of accidents in the city of Barcelona in 2018.

Step 1. Get the data ready

The dataset can be found in cartobq.docs.bcn_accidents_2018. For the purpose of this analysis, only the location and time of accidents are relevant. The table below shows an extraction of 10 of these accidents.

SELECT
 ST_GEOGFROMTEXT(geometry) AS geolocation,
 datetime
FROM
 `cartobq.docs.bcn_accidents_2018`
LIMIT
 10

In addition, the map below shows all accidents in the city of Barcelona in 2018.

Step 2. Generate space-time bins

The next step is to bucketize the data in space bins and time intervals. For this example, a spatial index H3 at resolution 9 and weekly time intervals were chosen. The data is aggregated by H3 cell and week. This can be achieved with the following code:

CREATE TABLE project.dataset.bcn_accidents_count_grid AS
SELECT
 `carto-un`.carto.H3_FROMGEOGPOINT(ST_GEOGFROMTEXT(geometry), 9) as h3,
 DATETIME_TRUNC(CAST(datetime AS DATETIME), WEEK) AS datetime,
 COUNT(*) AS value
FROM
 `cartobq.docs.bcn_accidents_2018`
GROUP BY
 `carto-un`.carto.H3_FROMGEOGPOINT(ST_GEOGFROMTEXT(geometry), 9),
 DATETIME_TRUNC(CAST(datetime AS DATETIME), WEEK)
CREATE TABLE project.dataset.bcn_accidents_count_grid AS
SELECT
 `carto-un-eu`.carto.H3_FROMGEOGPOINT(ST_GEOGFROMTEXT(geometry), 9) as h3,
 DATETIME_TRUNC(CAST(datetime AS DATETIME), WEEK) AS datetime,
 COUNT(*) AS value
FROM
 `cartobq.docs.bcn_accidents_2018`
GROUP BY
 `carto-un-eu`.carto.H3_FROMGEOGPOINT(ST_GEOGFROMTEXT(geometry), 9),
 DATETIME_TRUNC(CAST(datetime AS DATETIME), WEEK)
CREATE TABLE project.dataset.bcn_accidents_count_grid AS
SELECT
 carto.H3_FROMGEOGPOINT(ST_GEOGFROMTEXT(geometry), 9) as h3,
 DATETIME_TRUNC(CAST(datetime AS DATETIME), WEEK) AS datetime,
 COUNT(*) AS value
FROM
 `cartobq.docs.bcn_accidents_2018`
GROUP BY
 carto.H3_FROMGEOGPOINT(ST_GEOGFROMTEXT(geometry), 9),
 DATETIME_TRUNC(CAST(datetime AS DATETIME), WEEK)

Step 3. Perform space-time hotspot analysis

This function needs the following inputs:

  • A table with the h3 cells and their corresponding date-time and number of accidents (input).

  • A table's fully qualified name to save results (output_table).

  • The name of the column with the h3 indexes (index_col).

  • The name of the column with the date (date_col).

  • The name of the column with the values to use for the spacetime Getis-Ord computation (value_col).

  • The size of the k-ring (size). This is the spatial lag used for computing the corresponding Gi* statistic. In our case, we will take 1 ring around each h3 cell.

  • The time unit (time_freq). Equivalent to the h3 resolution for space aggregation time_freq is the time aggregation we will use. We select week as our unit of time aggregation.

  • The size of the time bandwidth (time_bw). This determines the neighboring weeks to be considered for calculating the corresponding Gi* statistic. For this example, we will take 2 weeks, i.e., for every week, we consider the two prior and the two posterior weeks as neighbors.

  • The kernel functions to be used for spatial (kernel) and time weights (kernel_time). For this example, we use uniform kernel for space and quartic kernel for time.

And returns a table with the following schema:

  • index: H3 spatial index at the provided resolution, same as input

  • date: date-time at the provided resolution, same as input

  • gi: the z-score

  • p-value: The two-tail p-value

Running the following, the Getis Ord Gi* for each H3 cell and week is returned.

CALL `carto-un`.carto.GETIS_ORD_SPACETIME_H3_TABLE(
    'project.dataset.bcn_accidents_count_grid',
    'project.dataset.bcn_accidents_count_grid_stgi',
    'h3',
    'datetime',
    'value',
    1,
    'WEEK',
    2,
    'uniform',
    'quartic'
);
CALL `carto-un-eu`.carto.GETIS_ORD_SPACETIME_H3_TABLE(
    'project.dataset.bcn_accidents_count_grid',
    'project.dataset.bcn_accidents_count_grid_stgi',
    'h3',
    'datetime',
    'value',
    1,
    'WEEK',
    2,
    'uniform',
    'quartic'
);
CALL carto.GETIS_ORD_SPACETIME_H3_TABLE(
    'project.dataset.bcn_accidents_count_grid',
    'project.dataset.bcn_accidents_count_grid_stgi',
    'h3',
    'datetime',
    'value',
    1,
    'WEEK',
    2,
    'uniform',
    'quartic'
);

Step 4. Getting cold and hotspots

We can now filter the previous table to keep only the rows whose p value is less or equal than 5% and gi positive. This results in keeping only the cells and weeks which are considered as hotspots. Respectively, for coldspots, we need to filter the p value to be less or equal than 5% and gi negative. Then we aggregate per H3 cells the count of weeks left.

SELECT index AS h3, COUNT(*) AS n_weeks 
FROM project.dataset.bcn_accidents_count_grid_stgi
WHERE p_value < 0.05 AND gi > 0
GROUP BY index
SELECT index AS h3, COUNT(*) AS n_weeks 
FROM project.dataset.bcn_accidents_count_grid_stgi
WHERE p_value < 0.05 AND gi > 0
GROUP BY index
SELECT index AS h3, COUNT(*) AS n_weeks 
FROM project.dataset.bcn_accidents_count_grid_stgi
WHERE p_value < 0.05 AND gi > 0
GROUP BY index

The output is shown in the following map, and the number of weeks per cell with a significantly high number of accidents is shown.

On the left panel, the exact locations of the accidents are shown, while on the right one, the aggregated number of accidents per is displayed. At the bottom of the map, the number of accidents over time is shown, where a periodicity can be observed.

Now let us use the space-time Getis-Ord Gi* function to calculate the z-score for each H3 cell and week. For that purpose, we will use the function of the Analytics Toolbox.

H3 cell at resolution 9
GETIS_ORD_SPACETIME_H3_TABLE
Advanced difficulty banner