Thanks. Just to clear things up, :point_or_poly bind variable needs a "geometry" type passed in to it, right?
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
