CODEWITHSUNDEEP
sqlgoogle-bigquery
gcp
gcp

Reputation: 41

Find the first N nearest points in Bigquery

To find the nearest point and its distance in Bigquery I am using this query

    WITH table_a AS (
        SELECT id, geom 
        FROM bqtable
    ), table_b AS (
        SELECT id, geom 
        FROM bqtable
    )
    SELECT AS VALUE ARRAY_AGG(STRUCT<id_a STRING,id_b STRING, dist FLOAT64>(a.id,b.id,ST_DISTANCE(a.geom, b.geom)) ORDER BY ST_DISTANCE(a.geom, b.geom) LIMIT 1)[OFFSET(0)] 
    FROM (SELECT id, geom FROM table_a) a
    CROSS JOIN (SELECT id, geom FROM table_b) b 
    WHERE a.id <> b.id
    GROUP BY a.id

How can I modify this query to find the nearest 10 points and their distances?

Thanks!

Upvotes: 1

Views: 355

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269533

One method uses ORDER BY, LIMIT, and UNNEST(). Using your approach:

SELECT AS VALUE s
FROM (SELECT ARRAY_AGG(STRUCT<id_a STRING,id_b STRING, dist FLOAT64>(a.id, b.id, ST_DISTANCE(a.geom, b.geom))
                       ORDER BY ST_DISTANCE(a.geom, b.geom)
                       LIMIT 10
                      ) as ar
      FROM (SELECT id, geom FROM table_a) a CROSS JOIN
           (SELECT id, geom FROM table_b) b 
      WHERE a.id <> b.id
      GROUP BY a.id
     ) ab CROSS JOIN
     UNNEST(ab.ar) s;

A simpler method would be

select id_a, id_b, ST_DISTANCE(a.geom, b.geom) as dist
from table_a a cross join
     table_b b
where a.id <> b.id
qualify row_number() over (partition by id_a order by dist) <= 10;

Upvotes: 1

Related Questions