On Sat, Oct 27, 2012 at 03:27:13PM +0200, Ed Linde wrote: > Thanks. Just to clear things up, :point_or_poly bind variable needs a > "geometry" > type passed in to it, right?
Yes --strk; > > Thanks, > Ed > > On Sat, Oct 27, 2012 at 1:57 PM, Sandro Santilli <s...@keybit.net> wrote: > > > On Sat, Oct 27, 2012 at 01:37:05PM +0200, Ed Linde wrote: > > > > > Is there a way to know for a user defined "k" what the k-nearest polygons > > > to a given polygon or point are in postgis? > > > > -- Simple answer: > > SELECT gid from polygons > > ORDER BY ST_Distance(the_geom, :point_or_poly) > > LIMIT :k; > > > > More complex: if you have PostGIS-2.0 and need > > more speed the following versions do use a spatial > > index, if defined on "polygons": > > > > -- k nearest to bounding box of polygons: > > SELECT gid FROM polygons > > ORDER BY the_geom <#> :point_or_poly > > LIMIT :k; > > > > -- k nearest to polygon center: > > SELECT gid FROM polygons > > ORDER BY the_geom <-> :point_or_poly > > LIMIT :k; > > > > -- k nearest to polygon shape: > > -- NOTE: assumes the k closest objects > > -- are among the k*10 objects > > -- closer to boundingbox > > -- > > WITH k_times_10_closer AS ( > > SELECT gid, the_geom FROM polygons > > ORDER BY the_geom <#> :point_or_poly > > LIMIT :k*10 > > ) > > SELECT gid from k_times_10_closer > > ORDER BY ST_Distance(the_geom, :point_or_poly) > > LIMIT :k; > > > > --strk; > > > > http://www.cartodb.com - Map, analyze and build applications with your > > data > > > > ~~ http://strk.keybit.net > > > > _______________________________________________ > > postgis-users mailing list > > postgis-users@postgis.refractions.net > > http://postgis.refractions.net/mailman/listinfo/postgis-users > > > _______________________________________________ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users -- http://www.cartodb.com - Map, analyze and build applications with your data ~~ http://strk.keybit.net _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
