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
  • You'll need...
  • Step-by-Step tutorial
  • Creating a Workflow
  • #1 Convert accidents to a H3 grid & filter to a study area
  • #2 Aggregate & calculate hotspots
  • #3 Convert the cycle network to a H3 grid
  • #4 Filter network to accident hotspots
  • Building the map

Was this helpful?

Export as PDF
  1. Creating workflows
  2. Step-by-step tutorials

Understanding accident hotspots

Last updated 9 months ago

Was this helpful?

In this tutorial, we’ll be exploring which parts of Paris’ cycle network could most benefit from improved safety measures through exploring accident rates.

You'll need...

This analysis will be based on two datasets; accident locations and the Paris cycle network.

To access the data:

  • Other clouds:

If you'd like to replicate this analysis for another study area, many local government data hubs will publish similar data on accident locations.


Step-by-Step tutorial

Creating a Workflow

  1. In the CARTO Workspace, head to Workflows and Create a Workflow, using the connection where your data is stored.


#1 Convert accidents to a H3 grid & filter to a study area

  1. First, we'll create a study area. On the left of the screen, switch from the Sources to the Components panel, which is where you can find all of your processing and analytical tools. Locate the Draw Custom Features component and drag it onto the canvas. Select the component to open the component options on the right hand side of the window. Click Draw Features and draw a custom area around the Paris area (see below). 💡 Alternatively of drawing a custom polygon, you can use any polygon table to define your custom area.

  1. Run your workflow! Note you can do this at any time, and only components which you have edited will be re-run.

  2. Now let's turn our attention to the bike accidents. Back in the Components panel, locate H3 from GeoPoint and drag it onto the canvas. Connect this to your bike accidents source, and set a resolution of 10.

  3. Next, use a Join component to essentially filter the accidents. Set the H3 Polyfill (step 2) as the top input, and the H3 from GeoPoint as the bottom input, the join columns as H3, and set the join type as Inner. Check the screenshot above for guidance.

Now might be a good time to add an annotation note around this section of the Workflow to keep it organized. You can do this by clicking Add a note (Aa) at the top of the screen.


#2 Aggregate & calculate hotspots

Now we can start analyzing our data!

  1. Connect a Group by component to the output of the Join we just created. Set the group by column to H3 and the aggregation column H3 and type count. This will result in a hexagonal grid with a field H3_count which holds the number of accidents which have occured in each "cell."

  2. Next, connect this to a Getis Ord* component. This will be used to calculate spatial hotspots; statistically significant clusters of high data values. Set the following parameters:

    1. Index column: H3

    2. Value column: H3_Count

    3. Kernel function: Triangular (this means cells closer to the central cell have a far higher weight).

    4. Size: 3 (the neighborhood size).

  1. Finally, use two connected Simple Filter components with the following conditions:

    1. p_value <= 0.1, meaning we can be 90% confident that the outputs are spatial hotspots.

    2. GI > 0, meaning there is a cluster of high values (with negative values representing clusters of low values).

❗If you are using Google BigQuery, at this stage you will need to rename the Index column "H3" so that we can map it. Use a Create Column component to do this.

Now you have a column named H3, we're ready to map!

  1. Expand the Results panel at the bottom of the window and switch to the Map tab. With your second Simple Filter selectively (or Create Column, if in BigQuery), select Create Map.

Let's start to explore our data in CARTO Builder!

  1. Rename your map "Paris accident hotspots" by clicking on the existing name (likely "Untitled") at the top-left of the window.

  2. Change basemaps: still in the top-left of the window, switch from the Layers to the Basemaps tab. You can choose any you like; we'll go with Google Maps: Dark Matter.

  3. Rename the layer: back in the Layers tab, click on your layer to expand the layer options. Click on the three dots to the right of the layer name (likely "Layer 1") to rename it "Accident hotspots."

  4. Style the layer: still in the layer options...

    1. Change the resolution to 6 so we can see a more detailed view of the data.

    2. Disable the stroke color (it'll end up being "noisy" later on).

    3. In the fill color options, set the color to be based on GI (AVG) and select a color palette; we're using sunset dark. For a more impactful map, reverse the color palette so that the lightest color represents the largest value. Change the color scale to quantile.

  5. Set a blending mode: come out of the layer options so you're in the main Layers panel. To the top-right of the panel, set the Layer blending to additive. This means that layering lighter colors on top of each other will result in an even lighter color. At the moment, that just means that we can see our basemap a little clearer... but just you wait!

Right now, your map is probably looking a little something like...

Let's kick this up a gear! Head back to your workflow for the next step.


#3 Convert the cycle network to a H3 grid

To transform these hotspots into actionable insights, we’ll now work out which parts of the cycle network infrastructure fall within accident hotspots - and so could benefit from some targeted improvements. Rather than using a slower spatial join to do this, we’ll leverage H3 again.

  1. First, connect an ST Buffer component to the cycling network source, setting a distance of 25 meters.

  2. Next connect this to a H3 Polyfill component (resolution 10) again to convert these to a H3 grid - at this stage, we’ll make sure to enable “Keep table input columns.”


#4 Filter network to accident hotspots

  1. Now we'll use another Join to join our cycle network H3 grid to the results of our hotspot analysis. Use the result of "#2 Aggregate & calculate hotspots" as the top input, and the result of H3 Polyfill as the bottom input. The join columns should both be H3, and the join type should be Inner.

  2. Now we will calculate the average GI* score for each section of the cycle network to determine which part of the network is covered by the strongest hotspots. Use one final Group by with the following parameters:

    1. Group by column: CARTODB_ID

    2. Aggregation: GI (AVG), HIGHWAY (ANY), NOM_VOIE_JOINED (ANY) & GEOM_JOINED (ANY). You can also use an ANY aggregation to retain any contextual information from the cycle links, such as highway name.

  1. Connect this final Group by to a Save as Table component to commit the results.

Now we have a table consisting of cycle links which are in an accident hotspot, as well as their respective average GI* score which indicates the strength of the hotspot. You can see the full workflow below.

Building the map

Let's bring everything together into one final map 👇

  1. Head back to Paris accident hotspots map you created earlier.

  2. First, let's add in the cycle links with GI* scores that we just created. In the bottom left of your map, navigate through Sources > Add Source from > Data Explorer > the cycle links table you just created. Add it to the map, and let's style it!

    1. Rename the layer: GI* score by link

    2. Stroke color based on: GI_AVG. We've used the same color palette as the hotspot grid as earlier (Sunset Dark, inverted) with a Quantile scale.

    3. Stroke width: 3.5

  3. To help give more weight to our analysis, let's also add in the original accident locations. Navigate again through Sources > Add Source from > Data Explorer to where you originally accessed the data. If you imported the accidents as a local file through Workflows, you can use a Save as Table component here to commit them to a table on the cloud. Now let's style them:

    1. Rename the layer: Accidents

    2. Fill color: orange, opacity = 1.

    3. Stroke: disabled

    4. Radius: 1

  4. Looking to replicate that "glowy" effect? This is what's known as a "firefly map" and is super easy to replicate"

    1. In the layers panel, click on the three dots next to the Accidents layer and + Duplicate layer.

    2. Drag this layer to beneath the original accidents layer.

    3. Set the radius to 4 and opacity to 0.01.

    4. So it isn't confusing for your users, head to the Legend tab (to the right of Layers) and disable the copied layer in the legend. You can also change the names of layers and classes here.

    1. Number of accidents:

      1. Layer: Accidents

      2. Widget type: formula

      3. Name: Number of accidents

      4. Formatting: Integer with format separator (12,345,678)

    2. GI* by highway type:

      1. Layer: GI* score by link

      2. Widget type: category

      3. Name: GI* score by highway type

      4. Operation: average

      5. Column: HIGHWAY_JOINED_ANY

      6. Aggregation column: GI_AVG

      7. Formatting: 2 decimal places (1.23)

    3. GI* by highway:

      1. Layer: GI* score by link

      2. Widget type: category

      3. Name: GI* score by street

      4. Operation: average

      5. Column: HIGHWAY_JOINED_ANY

      6. Aggregation column: GI_AVG

      7. Formatting: 2 decimal places (1.23)

Now your user should be able to use your map to pinpoint which streets could benefit from targeted safety improvements - such as Rue Malher with a GI* score of 11.98, and 81 accidents in close proximity.

In Snowflake, you can find PARIS_BIKE_ACCIDENTS and PARIS_CYCLING_NETWORK in the listing on the Snowflake Marketplace.

Accident locations can be downloaded from , and dropped directly into your workflow (more on that later).

The cycling network can be sourced from OpenStreetMap; you can follow our guide for accessing data from this source . Alternatively, you can find this in the CARTO Data Warehouse > demo data > demo tables > paris_cycling_network.

Under Sources (to the left of the screen), locate Paris bike accidents & Paris Cycling Network and drag them onto the canvas. If any of your source files are saved locally (for instance, if you downloaded the accident data from , you can drag and drop the files from your Downloads folder directly onto the canvas. This may take a few moments as this is a large dataset!

Back in the Components panel, locate the H3 Polyfill component. And connect the output of draw features to it (see screenshot above). We will use this to create a hexagonal H3 grid across our custom study area. Change the resolution to 10 which is more detailed than the default 8.

For more information on these parameters, check out .

Note that you can do this with any component in your workflow as long as it has either a geometry or reference column. However, the results of every component are only saved for 30 days, so if there is one you'd like to use beyond this period, make sure to use a Save as Table component to commit it.

Now finally let's add some to help our user explore the data. To the right of the Layers tab, open the Widgets tab. Add the following widgets:

CARTO Academy Data
here
here
this link
Spatial Index
this blog
Spatial Index
widgets
More Workflows Tutorials 👉
LogoHow to calculate spatial hotspots and which tools do you need?
Creating a H3 grid of Paris cycling accidents
Drawing custom features
Calculating hotspots with Getis Ord
Converting the cycle network to a H3 grid
The full workflow
Intermediate difficulty banner
A workflow showing how to create a H3 grid of Paris cycling accidents
A screenshot of CARTO Workflows
A screenshot of a workflow showing how to calculate hotspots with a H3 grid
A screenshot of a workflow showing how to convert a linear feature to a H3 grid
A screenshot of CARTO Workflows
A screenshot of the full workflow