Efficient spatial matching in BigQuery

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

Lak Lakshmanan
Jul 23, 2019 · 2 min read

This was the question (suitably anonymized):

“I have a huge dataset of points and another huge dataset of polygons. I have to loop through the polygons one-by-one and find all the points inside each one. Can BigQuery do this?”

The answer? Yes, BigQuery can do it, but you don’t have to loop through the polygons. If your experience consists primarily of functional/object-oriented programming, SQL can seem very weird. This is the sort of thing that is quite efficient to perform with a spatial join.

Image for post
Image for post
Joins in BigQuery are an efficient way to associate two entities based on their spatial extent. Photo by kgrkz on Unsplash

The spatial join works for any two geometries and a wide variety of spatial functions. For example, let’s find all the zipcodes that cover an urban-area:

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

The key bit is that we do a join of the city table to the zipcode table on the condition that the city geometry intersects the zipcode geometry.

There are other functions you can use, of course. You can check that the distance of a point from a polygon is < 100 km. Or that one geography fully contains the other geography.

Try it out in BigQuery!

Image for post
Image for post

Many of my blog posts come from customer questions even if I don’t always mention the conversation that prompted the post. So, if you’ve got a question on doing data analytics or ML on GCP, tweet it to me at @lak_gcp.

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

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