[prev in list] [next in list] [prev in thread] [next in thread]
List: postgis-users
Subject: Re: [postgis-users] k-nearest Polygons
From: Ed Linde <edolinde () gmail ! com>
Date: 2012-10-27 15:34:32
Message-ID: CAK4dkcXr4iw3Y1BSw2LSDCmQF0=pGKEEBkP2zbHwL3S+nLntJQ () mail ! gmail ! com
[Download RAW message or body]
[Attachment #2 (multipart/alternative)]
thanks.
On Sat, Oct 27, 2012 at 4:42 PM, Skye Book <skye.book@gmail.com> wrote:
> http://postgis.refractions.net/docs/geometry_distance_box.html
> http://postgis.refractions.net/docs/geometry_distance_centroid.html
>
> Both operators are insanely handy and very simple to use :)
>
> Skye Book
> http://skyebook.net -- @sbook <https://twitter.com/sbook>
>
> On Oct 27, 2012, at 8:34 AM, Ed Linde <edolinde@gmail.com> wrote:
>
> Could you please link me to the postgis 2.0 section in the docs that talks
> about
> using <#> and <-> ? Those operators look quite handy to me :)
>
> On Sat, Oct 27, 2012 at 3:32 PM, Sandro Santilli <strk@keybit.net> wrote:
>
>> 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@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
>>
>>
>> --
>>
>> 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
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
[Attachment #5 (text/html)]
thanks.<br><br><div class="gmail_quote">On Sat, Oct 27, 2012 at 4:42 PM, Skye Book \
<span dir="ltr"><<a href="mailto:skye.book@gmail.com" \
target="_blank">skye.book@gmail.com</a>></span> wrote:<br><blockquote \
class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex"> <div dir="auto"><div><span \
style="font-family:'.HelveticaNeueUI';font-size:15px;line-height:19px;white-space:nowrap"><a \
href="http://postgis.refractions.net/docs/geometry_distance_box.html" \
target="_blank">http://postgis.refractions.net/docs/geometry_distance_box.html</a></span></div>
<div><span style="font-family:'.HelveticaNeueUI';font-size:15px;line-height:19px;white-space:nowrap"><a \
href="http://postgis.refractions.net/docs/geometry_distance_centroid.html" \
target="_blank">http://postgis.refractions.net/docs/geometry_distance_centroid.html</a></span></div>
<div><span style="font-size:15px;line-height:19px;white-space:nowrap"><font \
face=".HelveticaNeueUI"><br></font></span></div><div><span \
style="font-size:15px;line-height:19px;white-space:nowrap"><font \
face=".HelveticaNeueUI">Both operators are insanely handy and very simple to use \
:)<span class="HOEnZb"><font color="#888888"><br>
</font></span></font></span><span class="HOEnZb"><font color="#888888"><br><div><span \
style="background-color:rgba(255,255,255,0)">Skye Book</span></div><div><font \
color="#000000"><span style="background-color:rgba(255,255,255,0)"><a \
href="http://skyebook.net/" target="_blank">http://skyebook.net</a> -- <a \
href="https://twitter.com/sbook" target="_blank">@sbook</a></span></font></div>
</font></span></div><div><div class="h5"><div><br>On Oct 27, 2012, at 8:34 AM, Ed \
Linde <<a href="mailto:edolinde@gmail.com" \
target="_blank">edolinde@gmail.com</a>> wrote:<br><br></div><blockquote \
type="cite"><div> Could you please link me to the postgis 2.0 section in the docs \
that talks about<div> using <span \
style="color:rgb(34,34,34);font-size:13px;font-family:arial,sans-serif"><#> and \
<-> ? Those operators look quite handy to me :)</span><br> <br><div \
class="gmail_quote">On Sat, Oct 27, 2012 at 3:32 PM, Sandro Santilli <span \
dir="ltr"><<a href="mailto:strk@keybit.net" \
target="_blank">strk@keybit.net</a>></span> wrote:<br><blockquote \
class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex">
<div>On Sat, Oct 27, 2012 at 03:27:13PM +0200, Ed Linde wrote:<br>
> Thanks. Just to clear things up, :point_or_poly bind variable needs a<br>
> "geometry"<br>
> type passed in to it, right?<br>
<br>
</div>Yes<br>
<br>
--strk;<br>
<div><div><br>
><br>
> Thanks,<br>
> Ed<br>
><br>
> On Sat, Oct 27, 2012 at 1:57 PM, Sandro Santilli <<a \
href="mailto:strk@keybit.net" target="_blank">strk@keybit.net</a>> wrote:<br> \
><br> > > On Sat, Oct 27, 2012 at 01:37:05PM +0200, Ed Linde wrote:<br>
> ><br>
> > > Is there a way to know for a user defined "k" what the \
k-nearest polygons<br> > > > to a given polygon or point are in postgis?<br>
> ><br>
> > -- Simple answer:<br>
> > SELECT gid from polygons<br>
> > ORDER BY ST_Distance(the_geom, :point_or_poly)<br>
> > LIMIT :k;<br>
> ><br>
> > More complex: if you have PostGIS-2.0 and need<br>
> > more speed the following versions do use a spatial<br>
> > index, if defined on "polygons":<br>
> ><br>
> > -- k nearest to bounding box of polygons:<br>
> > SELECT gid FROM polygons<br>
> > ORDER BY the_geom <#> :point_or_poly<br>
> > LIMIT :k;<br>
> ><br>
> > -- k nearest to polygon center:<br>
> > SELECT gid FROM polygons<br>
> > ORDER BY the_geom <-> :point_or_poly<br>
> > LIMIT :k;<br>
> ><br>
> > -- k nearest to polygon shape:<br>
> > -- NOTE: assumes the k closest objects<br>
> > -- are among the k*10 objects<br>
> > -- closer to boundingbox<br>
> > --<br>
> > WITH k_times_10_closer AS (<br>
> > SELECT gid, the_geom FROM polygons<br>
> > ORDER BY the_geom <#> :point_or_poly<br>
> > LIMIT :k*10<br>
> > )<br>
> > SELECT gid from k_times_10_closer<br>
> > ORDER BY ST_Distance(the_geom, :point_or_poly)<br>
> > LIMIT :k;<br>
> ><br>
> > --strk;<br>
> ><br>
> > <a href="http://www.cartodb.com" \
target="_blank">http://www.cartodb.com</a> - Map, analyze and build applications with \
your<br> > > data<br>
> ><br>
> > ~~ <a href="http://strk.keybit.net" \
target="_blank">http://strk.keybit.net</a><br> > ><br>
> > _______________________________________________<br>
> > postgis-users mailing list<br>
> > <a href="mailto:postgis-users@postgis.refractions.net" \
target="_blank">postgis-users@postgis.refractions.net</a><br> > > <a \
href="http://postgis.refractions.net/mailman/listinfo/postgis-users" \
target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br> \
> ><br> <br>
> _______________________________________________<br>
> postgis-users mailing list<br>
> <a href="mailto:postgis-users@postgis.refractions.net" \
target="_blank">postgis-users@postgis.refractions.net</a><br> > <a \
href="http://postgis.refractions.net/mailman/listinfo/postgis-users" \
target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br> \
<br> <br>
</div></div>--<br>
<div><div><br>
<a href="http://www.cartodb.com" target="_blank">http://www.cartodb.com</a> - Map, \
analyze and build applications with your data<br> <br>
~~ <a href="http://strk.keybit.net" \
target="_blank">http://strk.keybit.net</a><br> <br>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@postgis.refractions.net" \
target="_blank">postgis-users@postgis.refractions.net</a><br> <a \
href="http://postgis.refractions.net/mailman/listinfo/postgis-users" \
target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br> \
</div></div></blockquote></div><br></div> </div></blockquote><blockquote \
type="cite"><div><span>_______________________________________________</span><br><span>postgis-users \
mailing list</span><br><span><a href="mailto:postgis-users@postgis.refractions.net" \
target="_blank">postgis-users@postgis.refractions.net</a></span><br>
<span><a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" \
target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a></span><br></div></blockquote></div></div></div>
<br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>
<a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" \
target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br> \
<br></blockquote></div><br>
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic