Following our recent blogpost describing the availability of the Geographically Weighted Regression method to estimate local spatial relationships, we have now incorporated support for a set of new Spatial SQL functions to analyze the spatial patterns of point-based data in our Analytics Toolbox for BigQuery.
Although leading data warehouses already offer some level of support for spatial data, they lack some of the advanced analytical capabilities required for many geospatial use cases. CARTO Analytics Toolbox extends the geospatial capabilities of the most popular cloud data warehouses using spatial SQL with more than 60 advanced spatial functions. Please refer to our product documentation to learn about the different functions that we have in the Analytics Toolbox for BigQuery, Snowflake, Redshift, and Databricks.
Point-based data describes distinct locations in space, such as Points of Interest (POIs), like grocery stores, restaurants, offices, hospitals, etc. Often when dealing with point data a preliminary analysis looking at the relationships between these locations is needed.
In this blog post, we introduce three new functions for the analysis of point data that are now available in our Analytics Toolbox for BigQuery: the k-nearest neighbors function, to find the k-nearest neighbors of each point amongst a set of point data; the Local Outlier Factor function, to identify spatial outliers; and, finally, the G-function, to inspect the spatial distribution of point data.
When working with Points of Interest data, as we described in this blogpost, we might want to find for example the closest 10 restaurants to each Starbucks in Houston. As showcased in the example that follows, in which we used some POI data from our data partner SafeGraph, we can do so by using the k-nearest neighbors (KNN) function:
SELECT a.*, b.city, b.brands FROM UNNEST(( SELECT `carto-un`.statistics.KNN(ARRAY_AGG( STRUCT( geoid , geom)),10) FROM cartobq.docs.safegraph_restaurants_houston)) a JOIN cartobq.docs.safegraph_restaurants_houston b ON a.geoid = b.geoid WHERE brands = 'Starbucks' ORDER BY geoid
where the table cartobq.docs.safegraph_restaurants_houston stores the locations and brands of all Starbucks in Houston from Safegraph.
These are the results on a map:
The k-nearest neighbors function can also be leveraged to spot spatial outliers by computing an index called the Local Outlier Factor (LOF). The LOF is computed by comparing the local density of each point to the local density of its k-neighbors, where the local density is estimated by the typical distance at which a point can be reached from its k-neighbors.
Taking into account a sample of POIs for the Houston area, we can compute the LOF for each data point using the Local Outlier Factor (LOF) function in the Analytics Toolbox:
SELECT a.*, b.geom, b.brands, b.top_category FROM UNNEST(( SELECT `carto-un`.statistics.LOF(ARRAY_AGG( STRUCT( geoid , geom)),100) FROM cartobq.docs.safegraph_sample_houston)) a JOIN cartobq.docs.safegraph_sample_houston b ON a.geoid = b.geoid
where for each point stored in the cartobq.docs.safegraph_sample_houston table the local density is computed considering the nearest k=100 neighbors. Although in the query above we joined the results of the LOF function with the input table to retrieve the LOF score for each POI together with its brand and category, we could have used the LOF_TABLE procedure instead, which automatically stores the result in an output table along with the other input columns.
As we can see from the map below, assuming that points with a LOF factor larger than 10 (»1) are spatial outliers, we detect a point whose postal code does not belong to the Huston area:
Finally, a distance-based analysis can also be applied to point data to determine if there is a tendency of the locations of the data to exhibit a systematic pattern over an area as opposed to being randomly distributed. For this purpose we can compute the G-function (GFUN), which represents the cumulative frequency distribution (CDF) of the 1-order nearest neighbor distances.
We can determine if the locations are randomly distributed by comparing this empirical G-function to its expected value under the hypothesis that the data points are distributed randomly (Complete Spatial Randomness, CSR), which can be estimated as the CDF of an homogeneous Poisson point process of intensity , computed as the total number of points data divided by the area of the study region. Values suggest a random pattern, while values indicate a clustered pattern (the nearest neighbour distances are shorter than for a Poisson process) and values a regular (inhibited) pattern where points tend to avoid each other.
By computing the G-function for the sample POIs dataset in the Huston area
SELECT * FROM UNNEST(( SELECT `carto-un`.statistics.GFUN(ARRAY_AGG(geom)) FROM cartobq.docs.safegraph_sample_houston))
and plotting the results, we can see that their locations are clustered, suggesting that the location of a POI is not independent from the others:
As of today, the KNN, LOF and G-function methods are available in our Spatial Extension for BigQuery. We will be announcing further cloud native developments in the coming weeks, so stay tuned for regular updates from us!
|This project has received funding from the European Union's Horizon 2020 research and innovation programme under grant agreement No 960401.|
What is the first thing that you do when you open a web map? For most of us, we scroll and scroll until we’re zoomed right in to exactly where we live to find out what’s ha...Use Cases
One of the most important decisions retailers can make revolves around the site selection component within their network expansion plans. Afterall, at the end of the day, t...Use Cases
While travel and commuting pick up speed once again across the world, investment in out-of-home advertising (OOH) is growing in tandem. With OOH ad spend projected to grow ...Use Cases
Please fill out the below form and we'll be in touch real soon.