[postgis-users] k-nearest Polygons

[Ed Linde]

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 <strk at 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 at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20121027/65bdd257/attachment.html>