Nearest neighbor using BQ Scripting

We’ve already discussed Nearest Neighbors problem:

The general idea is to start with small search radius, and increase it if we fail to find anything. Since that article, we got BigQuery Scripting, so let’s use it to do this. This time we’ll solve single neighbor search — find nearest neighbor for a specific point, rather than finding neighbors for a set of points.

For demo purposes, we’ll search nearest road in US Tiger database. Note it is not a very good road dataset for actual use, but fine for demo. The search with a specific radius will look like this:

SELECT *
FROM `bigquery-public-data.geo_us_roads.us_national_roads`
WHERE ST_DWithin(center, road_geom, search_r)
ORDER BY ST_Distance(center, road_geom) DESC LIMIT 1

Where search_r is search radius, and center is the point for which we are trying to find a neighbor. We’ll start with search radius of 100 m and increase it 10x each time if we don’t find anything with current radius. The procedure will look like this:

CREATE OR REPLACE PROCEDURE demo.NearestRoad(
center GEOGRAPHY,
OUT road_id STRING,
OUT road_geom GEOGRAPHY)
BEGIN
DECLARE search_r FLOAT64; -- current search radius
DECLARE result STRUCT<road_id STRING, road_geom GEOGRAPHY>;

You can call it like this:

DECLARE id STRING;
DECLARE geom GEOGRAPHY;
CALL demo.NearestNeighbor(ST_GeogPoint(-122.191667, 47.685833),
id, geom);

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

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