Did my query get optimized spatial join?

select a.geoname_id
from `bigquery-public-data.geolite2.ipv4_city_blocks` a
join `bigquery-public-data.geolite2.ipv4_city_blocks` b
on st_distance(a.center_point, b.center_point) < 1000
and a.geoname_id <> b.geoname_id;
Optimized spatial join
select a.geoname_id
from `bigquery-public-data.geolite2.ipv4_city_blocks` a
join `bigquery-public-data.geolite2.ipv4_city_blocks` b
on st_distance(a.center_point, b.center_point)
< if (a.is_anonymous_proxy, 50, 100)
and a.geoname_id <> b.geoname_id;
Unoptimized spatial join

--

--

--

Hi, I'm TL of BigQuery Geospatial project. Posting small recipes and various notes for BQ Geospatial users.

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Why learn to code?

Asset Bundles for Unity

Containerization at a Glance

Seriously? Python really works this way?

Medium newbie

7 must-have extensions of VS Code

How to make my Data Engineering department shine again

Hello, Coherence —  Now with Micronaut!

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
Michael Entin

Michael Entin

Hi, I'm TL of BigQuery Geospatial project. Posting small recipes and various notes for BQ Geospatial users.

More from Medium

Overcoming large-scale geospatial analysis using Google BigQuery

Spatial Autocorrelation and Moran's I in SQL and BigQuery

Extra geography simplification in BigQuery

Why I built the python-bigquery-validator package