[postgis-users] Nearest neighbors for a set of points
Kevin Neufeld
kneufeld at refractions.net
Wed Mar 4 22:06:01 PST 2009
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
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
More information about the postgis-users
mailing list