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
