Identifying amenity hotspots in Stockholm

Requirements

To run this example you'll need:

An active CARTO organization
The latest version of the Analytics Toolbox Advanced installed in your Snowflake database
CARTO Academy - Data for tutorials and examples from Snowflake's Marketplace. With this free listing, users can access a set of sample tables with geospatial data that can be used to test and evaluate the CARTO geospatial analytics platform running natively on Snowflake

Guide

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. POI data can be found in the publicly available CARTO_ACADEMY_DATA.CARTO.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).

-- Create table with POI counts by grid cell
CREATE TABLE MYDB.MYSCHEMA.STOCKHOLM_POI_COUNT_GRID AS
SELECT
    H3, COUNT(*) AS N_AMENITY_POIS
FROM (
    SELECT CARTO.CARTO.H3_FROMGEOGPOINT(GEOM, 9) AS H3
    FROM CARTO_ACADEMY_DATA.CARTO.OSM_POIS_STOCKHOLM
    WHERE AMENITY IS NOT NULL)
GROUP BY H3;

-- Compute Getis-Ord Gi*
CALL CARTO.CARTO.GETIS_ORD_H3(
    'MYDB.MYSCHEMA.STOCKHOLM_POI_COUNT_GRID', 
    'MYDB.MYSCHEMA.STOCKHOLM_POI_COUNT_GRID_GI',
    'H3', 
    'N_AMENITY_POIS', 
    4, 
    'triangular');

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 6 months ago