Skip to content

Instantly share code, notes, and snippets.

@wriglz

wriglz/nearest_neighbour_same_table.sql

Created November 7, 2023 12:39
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save wriglz/985a38f95c5735be0b7c38fd0abfd185 to your computer and use it in GitHub Desktop.
Save wriglz/985a38f95c5735be0b7c38fd0abfd185 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
nearest_neighbour_same_table.sql
WITH
NN AS (
SELECT
a.osm_id,
-- Select the closest ID
ARRAY_AGG(STRUCT(b.osm_id,
ST_DISTANCE(a.geom, b.geom) AS dist)
ORDER BY
ST_DISTANCE(a.geom, b.geom)
LIMIT
1) [ORDINAL(1)] AS neighbour_id
FROM
`table` a
JOIN
`table` b
ON
ST_DWITHIN(a.geom, b.geom, 1000000000) -- search radius
WHERE
a.osm_id != b.osm_id
GROUP BY
a.osm_id )
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment