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

--

--

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.