Nearest neighbor in BigQuery GIS

SELECT 
a.id,
ARRAY_AGG(b.id ORDER BY ST_Distance(a.geog, b.geog) LIMIT 1)
[ORDINAL(1)] as neighbor_id
FROM people_table a JOIN restaurant_table b
ON ST_DWithin(a.geog, b.geom, 100) -- 100 is search radius
GROUP BY a.id
  • If you need more fields from table b, replace expression b.id inside ARRAY_AGG with STRUCT(b.id, b.extra_field, b.one_more_field)
  • If you need more fields from table a, especially in case of GEOGRAPHY that you cannot use as aggregation key, use ANY_VALUE aggregation function:
    SELECT ... , ANY_VALUE(a.geog) ... GROUP BY a.id
WITH missed_people_table AS (
SELECT a.* FROM people_table a
WHERE NOT EXISTS(
SELECT 1
FROM people_with_neighbors n -- result of first SELECT
WHERE n.id = a.id)
SELECT
... -- the same query as above only
-- 1) using missed_people_table
-- 2) larger search radius

--

--

--

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

Data Preprocessing with Orange Tool

Understanding PyTorch Activation Functions: The Maths and Algorithms (Part 2)

Why Are We Unable to Forecast Extreme Values/’Outliers’?

You want to travel and you don’t know where it’s worth it? The data has that answer

Drug Discovery with Graph Neural Networks — part 1

This Little Python Exercise Taught Me a Lot

Web scrapping using Python

Analyzing Domestic Violence During COVID 19 Through Natural Language Processing

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

Extra geography simplification in BigQuery

Overcoming large-scale geospatial analysis using Google BigQuery

Importing Big Data in BigQuery with Data Transfer Service and Terraform

BigQuery: Importing your data with SQL from Google cloud and drive storage