[postgis-users] k-nearest Polygons

[Sandro Santilli]

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