[postgis-users] PostGIS KNN best practices

[Alexandre Neto]

I have been around that question to.

http://gis.stackexchange.com/questions/24456/nearest-neighbor-problem-in-postgis-2-0-using-gist-index-function


You have to do it in two steps, like is explained in the operator
page<http://postgis.refractions.net/docs/geometry_distance_centroid.html>.
One faster step to reduce the candidates (by using <-> or <#>) and second
one to get the real distances with ST_Distance.

The problem in finding the KNN for each row in a table is the fact that the
gist index <-> operator only works if one of the geometries is constant.
The workaround would be to create a SQL function to apply to each of the
rows using table.the_geom as a parameter.

Something like this:

----
CREATE OR REPLACE FUNCTION _enn2 (geometry) RETURNS double precision AS $$

WITH index_query as
(SELECT ST_Distance($1,f.the_geom) as dist
FROM "grelha5m" As f
ORDER BY $1 <#> g1.the_geom limit 1000)
SELECT dist
FROM index_query
ORDER BY dist;

$$ LANGUAGE SQL;
---

and I call it like this:

---
Select c.gid as gid, _enn2(c.the_geom) as enn
>From cosn1 as c
Order by c.gid
---

In this case the function returned the smallest distance, but you can
choose any other column.

Hope it helps

Alexandre Neto

On Tue, May 15, 2012 at 5:37 PM, Stephen V. Mather <
svm at clevelandmetroparks.com> wrote:

>  Hi All,****
>
>                 Pretty excited by the new operators <-> and <#>, but a bit
> confused as to how to use them in a query.  The two examples from P. Ramsey
> back in late 2011 (
> http://blog.opengeo.org/2011/09/28/indexed-nearest-neighbour-search-in-postgis/) included doing a KNN on a single point to a cloud of points, i.e.
> ****
>
> ** **
>
> SELECT name, gid****
>
> FROM geonames****
>
> ORDER BY geom <-> st_setsrid(st_makepoint(-90,40),4326)****
>
> LIMIT 10;****
>
> ** **
>
> or doing KNN on non-point different geometries, where the first neighbor
> by <-> or <#> might not be truly the first i.e.****
>
> ** **
>
> with index_query as (****
>
>   select****
>
>     st_distance(geom, 'SRID=3005;POINT(1011102 450541)') as distance,****
>
>     parcel_id, address****
>
>   from parcels****
>
>   order by geom <#> 'SRID=3005;POINT(1011102 450541)' limit 100****
>
> )****
>
> select * from index_query order by distance limit 10;****
>
> ** **
>
> So, how would one grab the first nearest neighbor for all points in a
> dataset?  This is how I used to do it:****
>
> ** **
>
> CREATE TABLE n2180_560_height AS ****
>
> SELECT x, y, height FROM ****
>
> (SELECT DISTINCT ON(veg.gid) veg.gid as gid, ground.gid as gid_ground,
> veg.x as x, veg.y as y, ground.z as z, veg.z - ground.z as height,
> veg.the_geom as geometry, veg.class as class****
>
> FROM (SELECT * FROM n2180_560 WHERE class = 5) As veg, (SELECT * FROM
> n2180_560 WHERE class = 2) As ground****
>
> WHERE veg.class = 5 AND veg.gid <> ground.gid AND ST_DWithin(veg.the_geom,
> ground.the_geom, 10)****
>
> ORDER BY veg.gid, ST_Distance(veg.the_geom,ground.the_geom)) AS vegpoints;
> ****
>
> ** **
>
> ST_DWithin prevents a full cross join, but is a sloppy way to do this, as
> it requires a priori knowledge of the end cases.  I’m hoping there’s a
> subquery or some such magic that would allow me to use the distance
> operator to a similar end… .****
>
> ** **
>
> Thanks,****
>
> Best,****
>
> Steve****
>
> ** **
>
> **[image: http://www.clemetparks.com/images/esig/cmp-ms-90x122.png]**Stephen
> Mather
> Geographic Information Systems (GIS) Manager
> (216) 635-3243****
>
> svm at clevelandmetroparks.com
> clevelandmetroparks.com <http://www.clemetparks.com/>****
>
> ** **
>
> ** **
>
> ** **
>
> ** **
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120516/b3a2039f/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image001.png
Type: image/png
Size: 3772 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120516/b3a2039f/attachment.png>