CODEWITHSUNDEEP
sqlgoogle-bigquerygis
Alan
Alan

Reputation: 976

BigQuery ST_DWITHIN multiple points with multiple points

So I've got N points to consider and M points for reference. I'm trying to filter all the Points in N, that are within some radius R of any of the M points.

For the following example, my-table-1 is a table with ids and single coordinate GEOGRAPHY entries

Currently im only able to get this working for N to 1 Point using something like this:

SELECT *
FROM 'my-table-1' as one
WHERE ST_DWITHIN(points, (SELECT points FROM 'my-table-1' WHERE id = '1234'), 10000)

So basically what im trying to accomplish is that same statement but using multiple points to compare. What I want is basically this:

SELECT *
FROM 'my-table-1' as one
WHERE ST_DWITHIN(points, (SELECT points FROM 'my-table-1' WHERE id IN('1234', '2345', ...)), 10000)

But this results in a error because ST_DWITHIN can only compare 1 point to N points. I tried looking into BQ LOOPS and ARRAYS but couldnt figure out a solution so far. The exact error im getting in the second statement is Scalar subquery produced more than one element

Basically comparing all points with a set of specific points in the same table and check if they are within some radius around those

Upvotes: 1

Views: 1210

Answers (2)

Michael Entin
Michael Entin

Reputation: 7744

I'm trying to filter all the Points in N, that are within some radius R of any of the M points.

The way to work with two tables is via JOIN. Your query

SELECT *
FROM 'my-table-1' as one
WHERE ST_DWITHIN(points, (SELECT points FROM 'my-table-1' WHERE id IN ...), 10000)

Should become

SELECT one.id, one.points
FROM 'my-table-1' one JOIN 'my-table-1' two
ON ST_DWITHIN(one.points, two.points, 10000)
WHERE two.id IN ('1234', '2345', ...)

Note this will produce duplicate points, if some point from one is within 10km of several points in two. So you'll probably want to remove duplicates, I would add GROUP BY for this (this assumes we have some unique column id):

SELECT id, ANY_VALUE(points) AS points
FROM (
  SELECT one.id, one.points
  FROM 'my-table-1' one JOIN 'my-table-1' two
  ON ST_DWITHIN(one.points, two.points, 10000)
  WHERE two.id IN ('1234', '2345', ...)
)
GROUP BY id

Upvotes: 2

Daniel Kehlibarov
Daniel Kehlibarov

Reputation: 169

If I understand the case correctly I would probably:

  • Create a buffer with radius R around the M points (ST_Buffer)
  • Union those geometries (ST_Union)
  • Select from the N points using ST_DWithin on the N points and the union/buffer geometry

This is from the top of my head, I can't construct the query in my mind though, but I think a single query should do it or you could throw a WITH statement there.

Let me know if that works for you. I saw that for BigQuery you have no ST_Buffer, but you can use jslibs.turf.ST_BUFFER instead.

Edit: Damn, I just realized this is not a PG question, but a BQ question... Shame on me. Are those functions available there?

Here's an example PG query (tested):

select n.id from "N_Points" as n, (select st_buffer(m.geom, 20000) as geom 
from "M_Points" m where m.id < 10) as p
where st_dwithin(n.geom, p.geom, 0);

where 20000 is the radius (R) in projection units and geom is the geometry/geography column. You can replace it with a join or use a "with" clause instead of that long select st_buffer. Looks like you don't even need to Union those geometries. :) You can use ST_Within too:

ST_Within(n.geom, p.geom)

Upvotes: 1

Related Questions