Depending on how many points are in tables A and B, this cross join is
the simplest solution that comes to mind to me.
SELECT a.geom, b.geom
FROM a, b
WHERE ST_Distance(a.geom, b.geom) < your-definition-of-near;
That will find all pairs of points (one from table a, one from table b)
where they are within some definition of near.
If you have a set of points in table A are trying to find a set of
points in table B, I would collect your points in both tables into
MULTIPOINT objects. Thus every "set" is a single geometry so the above
query works.
You can collect your points into a collection by grouping on some common
attribute in the set.
i.e.
SELECT set_id, ST_Collect(geom) AS geom
FROM a
GROUP BY set_id;
So, your whole query might be:
SELECT a.set_id, b.set_id
FROM
(SELECT set_id, ST_Collect(geom) AS geom
FROM a
GROUP BY set_id) AS a,
(SELECT set_id, ST_Collect(geom) AS geom
FROM b
GROUP BY set_id) AS b
WHERE ST_Distance(a.geom, b.geom) < 10000;
Cheers,
Kevin
Babu Naidu wrote:
Hi,
I have a use case where I have to find a set of points near another
set of points. For example, given
Set A = { a1, a2, ... an} and Set B = {b1, b2, b3.. bn}
Set A and B are from two different tables. Can I frame a query (SQL)
by using postgis functions to find a set of points of type B that are
near a given set of points of type A?
Any pointers/hints about postgis functions are greatly appreciated.
Thanks
Babu
------------------------------------------------------------------------
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
