Efficient spatial matching in BigQuery

Use a spatial join — a SQL join with a ST_* condition

Joins in BigQuery are an efficient way to associate two entities based on their spatial extent. Photo by kgrkz on Unsplash
WITH city AS (
SELECT
name,
urban_area_geom AS geom
FROM `bigquery-public-data`.geo_us_boundaries.urban_areas
),
zip AS (
SELECT
zip_code,
zcta_geom AS geom
FROM `bigquery-public-data`.geo_us_boundaries.zip_codes
)
SELECT
city.name, TO_JSON_STRING(ARRAY_AGG(zip_code)) AS zipcodes
FROM
city
JOIN
zip
ON
ST_INTERSECTS(city.geom, zip.geom)

GROUP BY city.name

--

--

A collection of technical articles and blogs published or curated by Google Cloud Developer Advocates. The views expressed are those of the authors and don't necessarily reflect those of Google.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Lak Lakshmanan

Lak Lakshmanan

8.6K Followers

Operating Executive at a technology investment firm; articles are personal observations and not investment advice.