An H3 grid of Starbucks locations
We are going to demonstrate how fast and easy it is to make a visualization of an H3 grid to identify the concentration of Starbucks locations in the US.
The first step is to import the Starbucks locations dataset into a BigQuery table called
starbucks-locations-usa. Then, with a single query, we are going to calculate how many Starbucks locations fall within each H3 grid cell of resolution 4. For this example, let’s assume this table is part of the
examples dataset inside
This query adds two new columns to our dataset:
geom, with the boundary of each of the H3 grid cells where there’s at least one Starbucks, and
agg_total, with the total number of locations that fall within each cell. Finally, we can visualize the result.
Note: this visualization is made using Builder, where you can easily import your BigQuery data using our connector, but you can also create a quick visualization using BigQuery Geo Viz.
Finer resolution H3 for simple cannibalization analysis
In this example we will continue working with the dataset introduced in An H3 grid of Starbucks locations example. In particular, we will analyze in finer detail the grid cell that we have identified contains the highest concentration of Starbucks locations, with ID
We can clearly identify that there are two H3 cells with the highest concentration of Starbucks locations, and therefore at risk of suffering cannibalisation. These are cells with IDs
617711491559718911 respectively. Finally, to complete our analysis, we can calculate how many locations are within one cell distance of the last cell:
Enriching a quadkey grid with population data from the Data Observatory
The pains of working with data in different spatial aggregations can be greately eased by using spatial indexes. In this example we showcase how, in a single query, we can create a quadkey grid of resolution 15 of all supermarket POIs in the US and enrich it with population data. Both datasets, OpenStreetMap POIs and CARTO Spatial Features for the US, are publicly available in BigQuery as part of our Data Observatory offering. You can learn more about our Spatial Features dataset in this blog post.
The query performs three main tasks:
- Filtering the OSM dataset to keep only the ones of type
- Creating a quadkey grid of resolution 15 with the total number of supermarkets that fall within each grid cell (
- Enriching each grid cell with its population. As the Spatial Features dataset is also available in resolution 15, this enrichment is done simply by performing a join between these two tables. Please note that the Spatial Features dataset uses quadkeys as a grid identifier, so we have to convert these to quadints by using the appropriate function from the Spatial Extension.