[postgis-users] k-nearest Polygons

[Sandro Santilli]

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 <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
> >

> _______________________________________________
> postgis-users mailing list
> postgis-users at 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