[postgis-users] PostGIS KNN best practices

[Stephen V. Mather]

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-postg
is/ ) 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

 

http://www.clemetparks.com/images/esig/cmp-ms-90x122.pngStephen Mather
Geographic Information Systems (GIS) Manager
(216) 635-3243

svm at clevelandmetroparks.com
 <http://www.clemetparks.com/> clevelandmetroparks.com

 

 

 

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120515/70a77477/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/20120515/70a77477/attachment.png>