Spatial Extension for BigQuery

Spatial Extension for BigQuery

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 supermarket.
  • Creating a quadkey grid of resolution 15 with the total number of supermarkets that fall within each grid cell (agg_total).
  • 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.
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
SELECT
  d.*,
  p.population
FROM
  `carto-do-public-data.carto.derived_spatialfeatures_usa_quadgrid15_v1_yearly_2020` p
JOIN (
  WITH
    data AS (
    SELECT
      d.*,
      g.geom
    FROM `carto-do-public-data.openstreetmap.pointsofinterest_nodes_usa_latlon_v1_quarterly_v1` d
    INNER JOIN `carto-do-public-data.openstreetmap.geography_usa_latlon_v1` g
    ON d.geoid = g.geoid
    WHERE shop = 'supermarket' )
  SELECT
    bqcartost.quadkey.ST_ASQUADINT(geom, 15) AS qid,
    COUNT(*) AS agg_total
  FROM data
  GROUP BY qid 
  ) d
ON qid = bqcarto.quadkey.QUADINT_FROMQUADKEY(geoid)