Computing the spatial autocorrelation of POIs locations in Berlin
In this example we are going to analyze the spatial correlation of POIs locations in Berlin using OpenStreetMap data and the Moran’s I function available in the statistics module. POIs data can be found in the publicly available cartobq.docs.osm_pois_berlin table.
First, we are going to visually analyze the distribution of the POIs in the Berlin area by plotting the aggregation of POIs in each H3 cell of resolution 9. This can be done simply by applying the H3_FROMGEOGPOINT function to compute the H3 cell that each POI belongs to and then performing a group by to count the number of POIs inside each cell (n_pois).
By looking at the resulting map below, it is clear that there is a level of spatial autocorrelation in the distribution of the POIs:
We can measure this spatial autocorrelation using the MORANS_I_H3_TABLE function, which yields a result of 0.673 by applying the query below:
-- Create table with POI counts by grid cellCREATE OR REPLACETABLEproject.dataset.berlin_poi_count_grid ASSELECT h3, COUNT(*) AS n_poisFROM (SELECT`carto-un`.carto.H3_FROMGEOGPOINT(geom, 9) AS h3FROM cartobq.docs.osm_pois_berlin )GROUP BY h3;-- Compute Moran's ICALL`carto-un`.carto.MORANS_I_H3_TABLE('project.dataset.berlin_poi_count_grid', 'project.dataset.berlin_poi_count_grid_mi','h3','n_pois',1,'exponential');-- Read computed valueSELECT*FROM project.dataset.berlin_poi_count_grid_mi;
-- Create table with POI counts by grid cellCREATE OR REPLACETABLEproject.dataset.berlin_poi_count_grid ASSELECT h3, COUNT(*) AS n_poisFROM (SELECT`carto-un-eu`.carto.H3_FROMGEOGPOINT(geom, 9) AS h3FROM cartobq.docs.osm_pois_berlin )GROUP BY h3;-- Compute Moran's ICALL`carto-un-eu`.carto.MORANS_I_H3_TABLE('project.dataset.berlin_poi_count_grid', 'project.dataset.berlin_poi_count_grid_mi','h3','n_pois',1,'exponential');-- Read computed valueSELECT*FROM project.dataset.berlin_poi_count_grid_mi;
-- Create table with POI counts by grid cellCREATE OR REPLACETABLEproject.dataset.berlin_poi_count_grid ASSELECT h3, COUNT(*) AS n_poisFROM (SELECT carto.H3_FROMGEOGPOINT(geom, 9) AS h3FROM cartobq.docs.osm_pois_berlin )GROUP BY h3;-- Compute Moran's ICALL carto.MORANS_I_H3_TABLE('project.dataset.berlin_poi_count_grid', 'project.dataset.berlin_poi_count_grid_mi','h3','n_pois',1,'exponential');-- Read computed valueSELECT*FROM project.dataset.berlin_poi_count_grid_mi;
Last updated
This project has received funding from the European Union’s Horizon 2020 research and innovation programme under grant agreement No 960401.