To run this example you'll need:
An active CARTO organization
The latest version of the installed in your Snowflake database
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
In this example we are going to analyze the spatial correlation of POIs locations in Berlin using OpenStreetMap data and the function available in the statistics module. POIs data can be found in the publicly available CARTO_ACADEMY_DATA.CARTO.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 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 function, which yields a result of 0.673 by applying the query below:
This project has received funding from the research and innovation programme under grant agreement No 960401.
-- Create table with POI counts by grid cell
CREATE TABLE MYDB.MYSCHEMA.BERLIN_POI_COUNT_GRID AS
SELECT
H3, COUNT(*) AS N_POIS
FROM (
SELECT CARTO.CARTO.H3_FROMGEOGPOINT(GEOM, 9) AS h3
FROM CARTO_ACADEMY_DATA.CARTO.OSM_POIS_BERLIN )
GROUP BY H3;
-- Compute Moran's I
CALL CARTO.CARTO.MORANS_I_H3(
'MYDB.MYSCHEMA.BERLIN_POI_COUNT_GRID',
'MYDB.MYSCHEMA.BERLIN_POI_COUNT_GRID_MI',
'H3',
'N_POIS',
1,
'exponential');
-- Read computed value
SELECT * FROM MYDB.MYSCHEMA.BERLIN_POI_COUNT_GRID_MI;