<html>
<head>
</head>
<body>No, dfullywithin will giva a too narrow selection since there could be bigger geometrie that wouldn't be "fullywithin" but anyway be closer.<br />
<br />
The idea is if I use your example fron bostongis, to find a approx for 300 without knowing anything about the dataset. <br />
<span class="content">
<pre><span class="syntax0"> <span class="syntax8">SELECT DISTINCT ON</span>(g1.gid) g1.gid <span class="syntax8">As</span> gref_gid, g1.description <span class="syntax8">As</span> gref_description, g2.gid <span class="syntax8">As</span> gnn_gid, <br />
g2.description <span class="syntax8">As</span> gnn_description <br />
<span class="syntax8">FROM</span> sometable <span class="syntax8">As</span> g1, sometable <span class="syntax8">As</span> g2 <br />
<span class="syntax8">WHERE</span> g1.gid <span class="syntax18"><</span><span class="syntax18">></span> g2.gid <span class="syntax8">AND</span> <span class="syntax6">ST_DWithin</span>(g1.the_geom, g2.the_geom, <span class="syntax5">300</span>) <br />
<span class="syntax8">ORDER</span> <span class="syntax8">BY</span> <span class="syntax6">g1.gid, ST_Distance</span>(g1.the_geom,g2.the_geom) <br />
</span></pre></span>The first step could then be to find the one with smalest max_distance to the boundingbox (because the boundingbox probably is much faster to calculate than the geometry itself). Then we take the st_distance from the geometry in that box and I think we get the smallest value in st_dwithin that we can use without knowing anything about the data.<br />
<br />
But I don't know if it's worth the effort. I guess it is if it is a widely spread dataset, with complex geometries.<br />
<br />
/Nicklas<br />
<br />
<br />
2009-07-19 Paragon Corporation wrote:<br />
<br />
>
<div align="left"><span style="font-family: Arial; font-size: 10pt; color: rgb(0, 0, 255);">Nicklas,</span></div>
>
<div align="left"><span style="font-family: Arial; font-size: 10pt; color: rgb(0, 0, 255);"></span> </div>
>
<div align="left"><span style="font-family: Arial; font-size: 10pt; color: rgb(0, 0, 255);"> I can't quite see how max distance would help
ST_DWithin. It seems it would help the Fully_DWithin, but don't see how it
helps the ST_DWithin. </span><span style="font-family: Arial; font-size: 10pt; color: rgb(0, 0, 255);">Granted I haven't given it much thought.
</span></div>
>
<div align="left"><span style="font-family: Arial; font-size: 10pt; color: rgb(0, 0, 255);"></span> </div>
>
<div align="left"><span style="font-family: Arial; font-size: 10pt; color: rgb(0, 0, 255);">Thanks,</span></div>
>
<div align="left"><span style="font-family: Arial; font-size: 10pt; color: rgb(0, 0, 255);">Regina</span></div><br />
>>
<div align="left">
<hr />
<span style="font-family: Tahoma; font-size: 10pt;"><span style="font-weight: bold;">From:</span>
postgis-devel-bounces@postgis.refractions.net
[mailto:postgis-devel-bounces@postgis.refractions.net] <span style="font-weight: bold;">On Behalf Of
</span>nicklas.aven@jordogskog.no<br />
><span style="font-weight: bold;">Sent:</span> Sunday, July 19, 2009 2:39
PM<br />
><span style="font-weight: bold;">To:</span> PostGIS Development Discussion<br />
><span style="font-weight: bold;">Subject:</span> Re:
[postgis-devel] Caching function calls with CTEs<br />
></span><br />
></div>
>
<div> </div>Yes,that one I can see.<br />
><br />
>About the nearest neighbour I have
thought about using st_max_distance against the bounding boxes to find the one
with smallest max_distance. That value ought to be good to use in the st_dwithin
part of the query. <br />
><br />
>Maybe it would even be worth the effort to use
st_distance from the geometry found with the "max_distance" boundingbox to usi
in in st_dwithin. <br />
><br />
>Hope it is possible to understand what I mean. It is
at least one release ahead but possible to test
:-)<br />
><br />
><br />
>/Nicklas<br />
><br />
>
<pre><br />
></pre><br />
>2009-07-19 Paragon Corporation wrote:<br />
><br />
>>
>
<div align="left"><span style="font-family: Arial; color: rgb(0, 0, 255); font-size: 10pt;">Windowing
functions come in especially handy for nearest neighbor search getting top n per
location. We are going to demonstrate that in our OSCON conference.
Slides and materials will of course will be available after the conference is
over.</span></div><br />
>>>
>
<div align="left">
<hr />
<span style="font-family: Tahoma; font-size: 10pt;"><span style="font-weight: bold;">From:</span>
postgis-devel-bounces@postgis.refractions.net
[mailto:postgis-devel-bounces@postgis.refractions.net] <span style="font-weight: bold;">On Behalf Of
</span>nicklas.aven@jordogskog.no<br />
>><span style="font-weight: bold;">Sent:</span> Sunday, July 19, 2009 1:52
PM<br />
>><span style="font-weight: bold;">To:</span> PostGIS Development
Discussion<br />
>><span style="font-weight: bold;">Subject:</span> Re:
[postgis-devel] Caching function calls with
CTEs<br />
>></span><br />
>></div>>
>
<div> </div>This is really cool Regina and Leo<br />
>><br />
>>It's a new
way of thinking for me to to use this recursive thing, but I can see that it is
very useful.<br />
>>I hope I will get some time in the near future to try to
understand it better.<br />
>><br />
>>Also the windowing functions together with
postgis aggregate function will be fun to
try.<br />
>><br />
>>/Nicklas<br />
>><br />
>><br />
>><br />
>><br />
>><br />
>>2009-07-17
Paragon Corporation wrote:<br />
>><br />
>>Nicklas,<br />
>>><br />
>>>We
tried CTEs against that annoying issue of functions not being cached
in<br />
>>>the same row. Preliminary tests seem to suggest it might be the
ideal<br />
>>>solution for this.<br />
>>>We suspect it will also be good
in many other scenarios where the planner<br />
>>>refuses to use indexes in
the right order
etc.<br />
>>><br />
>>>http://www.postgresonline.com/journal/index.php?/archives/127-guid.html<br />
>>><br />
>>>We
are going to test with real spatial queries next in preparation for
our<br />
>>>OSCON presentation next
week.<br />
>>><br />
>>>http://en.oreilly.com/oscon2009/public/schedule/detail/7859<br />
>>>Sadly
it looks like we probably won't have 1.4 out in time for
our<br />
>>>presentation - oh well
:(<br />
>>><br />
>>><br />
>>>Anyrate take a look and test with your
data to see if it speeds up your<br />
>>>queries.<br />
>>><br />
>>>L
&
R<br />
>>><br />
>>><br />
>>>_______________________________________________<br />
>>>postgis-devel
mailing
list<br />
>>>postgis-devel@postgis.refractions.net<br />
>>>http://postgis.refractions.net/mailman/listinfo/postgis-devel<br />
>>><br />
>>>
</body>
</html>