Identifying amenity hotspots in Stockholm

In this example we are going to identify hotspots of amenity POIs in Stockholm using OpenStreetMap data and the GETIS_ORD_H3_TABLE function of the statistics module. POIs data can be found in the publicly available table.

The process consists of three simple steps:

  • First, we retrieve all POIs from OpenstreetMaps which belong to the category “amenity”.

  • Next, we find the H3 cell of resolution 9 to which each POI belongs and count the number of amenity POIs inside each cell.

  • Finally, we call the GETIS_ORD_H3_TABLE function, which returns the Getis-Ord Gi* statistic for each H3 cell, calculated over n_amenity_pois (number of amenity POIs in the cell).

-- Create table with POI counts by grid cell
CREATE TABLE project.dataset.stockholm_poi_count_grid AS
    h3, COUNT(*) AS n_amenity_pois
    SELECT `carto-un`.carto.H3_FROMGEOGPOINT(geom, 9) AS h3,
    WHERE amenity IS NOT NULL )

-- Compute Getis-Ord Gi*
CALL `carto-un`.carto.GETIS_ORD_H3_TABLE(

The results can be explored in the map below, where we can use the histogram widget to narrow down the cells with the highest Gi* values, which correspond to the location of the hotspots of amenity POIs in Stockholm.

Last updated