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 function of the statistics module. POIs data can be found in the publicly available
cartobq.docs.osm_pois_stockholm 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 function, which returns the Getis-Ord Gi* statistic for each H3 cell, calculated over n_amenity_pois (number of amenity POIs in the cell).
carto-un
carto-un-eu
manual
WITH
stockholm AS (
SELECT h3, CAST(COUNT(*) AS FLOAT64) AS n_amenity_pois,
FROM (
SELECT `carto-un`.carto.H3_FROMGEOGPOINT(geom, 9) AS h3,
FROM cartobq.docs.osm_pois_stockholm
WHERE amenity IS NOT NULL )
GROUP BY h3),
getis_ord AS (
SELECT `carto-un`.carto.GETIS_ORD_H3(ARRAY_AGG(STRUCT(h3, n_amenity_pois)),
4, 'triangular') AS output
FROM stockholm )
SELECT unnested.INDEX AS h3id, unnested.gi
FROM getis_ord, UNNEST(getis_ord.output) AS unnested;
WITH
stockholm AS (
SELECT h3, CAST(COUNT(*) AS FLOAT64) AS n_amenity_pois,
FROM (
SELECT `carto-un-eu`.carto.H3_FROMGEOGPOINT(geom, 9) AS h3,
FROM cartobq.docs.osm_pois_stockholm
WHERE amenity IS NOT NULL )
GROUP BY h3),
getis_ord AS (
SELECT `carto-un-eu`.carto.GETIS_ORD_H3(ARRAY_AGG(STRUCT(h3, n_amenity_pois)),
4, 'triangular') AS output
FROM stockholm )
SELECT unnested.INDEX AS h3id, unnested.gi
FROM getis_ord, UNNEST(getis_ord.output) AS unnested;
WITH
stockholm AS (
SELECT h3, CAST(COUNT(*) AS FLOAT64) AS n_amenity_pois,
FROM (
SELECT carto.H3_FROMGEOGPOINT(geom, 9) AS h3,
FROM cartobq.docs.osm_pois_stockholm
WHERE amenity IS NOT NULL )
GROUP BY h3),
getis_ord AS (
SELECT carto.GETIS_ORD_H3(ARRAY_AGG(STRUCT(h3, n_amenity_pois)),
4, 'triangular') AS output
FROM stockholm )
SELECT unnested.INDEX AS h3id, unnested.gi
FROM getis_ord, UNNEST(getis_ord.output) AS unnested;
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.
This project has received funding from the European Union’s Horizon 2020 research and innovation programme under grant agreement No 960401.