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 )