<html>
<head>
<title></title>
<meta name="GENERATOR" content="MSHTML 8.00.6001.18783"></meta>
</head>
<body>
<div align="left">I have tried to make an example of what I mean but I think there is som way to do it more efficient.</div>
<div align="left"> </div>
<div align="left">the goal is to get a dynamic value for st_dwithin in a query like:</div>
<div align="left"><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 />
</div>
<div align="left">to get the smallest maxdistance:<br />
SELECT DISTINCT ON(g1.gid) g1.gid As gref_gid, g2.gid As gnn_gid <br />
FROM sometable As g1, sometable As g2 <br />
WHERE g1.gid <> g2.gid<br />
ORDER BY g1.gid, ST_max_Distance(st_envelope(g1.the_geom),st_envelope(g2.the_geom))</div>
<div align="left"> </div>
<div align="left">but I think it's worth the effort to go one step further and look for st_distance to the found geometry:</div>
<div align="left"><br />
Select gref_gid, st_distance(t1.geom1, t1.geom2) as dist from<br />
(SELECT DISTINCT ON(g1.gid) g1.gid As gref_gid, g2.gid As gnn_gid , g1.the_geom as geom1, g2.the_geom as geom2<br />
FROM sometable As g1, sometable As g2 <br />
WHERE g1.gid <> g2.gid<br />
ORDER BY g1.gid, ST_max_Distance(st_envelope(g1.the_geom),st_envelope(g2.the_geom)) ) t1<br />
</div>
<div align="left">I tried </div>
<div align="left">SELECT DISTINCT ON(g1.gid) g1.gid As gref_gid, g2.gid As gnn_gid , st_distance(g1.the_geom, g2.the_geom)<br />
FROM sometable As g1, sometable As g2 <br />
WHERE g1.gid <> g2.gid<br />
ORDER BY g1.gid, ST_max_Distance(st_envelope(g1.the_geom),st_envelope(g2.the_geom))</div>
<div align="left">but then it calculates st_distance for ALL combinations not just the distinct.</div>
<div align="left"> </div>
<div align="left">now we have got our dynamic value for st_dwithin and I use it like this:</div>
<div align="left"><br />
with dl as<br />
(select gref_gid, st_distance(t1.geom1, t1.geom2) as dist from<br />
(SELECT DISTINCT ON(g1.gid) g1.gid As gref_gid, g2.gid As gnn_gid , g1.the_geom as geom1, g2.the_geom as geom2<br />
FROM sometable As g1, sometable As g2 <br />
WHERE g1.gid <> g2.gid<br />
ORDER BY g1.gid, ST_max_Distance(st_envelope(g1.the_geom),st_envelope(g2.the_geom)) ) t1) <br />
SELECT DISTINCT ON(g1.gid) g1.gid As gref_gid, g2.gid As gnn_gid <br />
FROM sometable As g1, sometable As g2, dl <br />
WHERE dl.gref_gid= g1.gid and g1.gid <> g2.gid AND ST_DWithin(g1.the_geom, g2.the_geom, dl.dist) <br />
ORDER BY g1.gid, ST_Distance(g1.the_geom,g2.the_geom) ;</div>
<div align="left"> </div>
<div align="left">It seems to work and it seems fast, but there is something very strange about the behavior of st_dwithin in my functions.</div>
<div align="left"> </div>
<div align="left">My functions is faster than the ones used today in every test I have done except one clean point to point where it was som percent slower and that is understandable.</div>
<div align="left">But</div>
<div align="left">st_dwithin is slower but _st_dwithin is faster</div>
<div align="left">I can't explain it and I think it has to do with some bad handling of memory because I don't know enough.</div>
<div align="left">I will make a separate opst of that, but I wanted to mention it here because I think when that is working the above will be quite a lot faster.</div>
<div align="left"> </div>
<div align="left">/Nicklas</div>
<div align="left"> </div>
<div align="left"> </div>
<div align="left"><br />
<br />
2009-07-19 nicklas.aven@jordogskog.no wrote:<br />
<br />
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 />
> </div>
<pre> SELECT DISTINCT ON(g1.gid) g1.gid As gref_gid, g1.description As gref_description, g2.gid As gnn_gid, <br />
></pre>g2.description As gnn_description <br />
>FROM sometable As g1, sometable As g2 <br />
>WHERE g1.gid <> g2.gid AND ST_DWithin(g1.the_geom, g2.the_geom, 300) <br />
>ORDER BY g1.gid, ST_Distance(g1.the_geom,g2.the_geom) <br />
>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; COLOR: rgb(0,0,255); FONT-SIZE: 10pt">Nicklas,</span></div>> >
<div align="left"><span style="FONT-FAMILY: Arial; COLOR: rgb(0,0,255); FONT-SIZE: 10pt"> </span></div>> >
<div align="left"><span style="FONT-FAMILY: Arial; COLOR: rgb(0,0,255); FONT-SIZE: 10pt"> 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; COLOR: rgb(0,0,255); FONT-SIZE: 10pt">Granted I haven't given it much thought. </span></div>> >
<div align="left"><span style="FONT-FAMILY: Arial; COLOR: rgb(0,0,255); FONT-SIZE: 10pt"> </span></div>> >
<div align="left"><span style="FONT-FAMILY: Arial; COLOR: rgb(0,0,255); FONT-SIZE: 10pt">Thanks,</span></div>> >
<div align="left"><span style="FONT-FAMILY: Arial; COLOR: rgb(0,0,255); FONT-SIZE: 10pt">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 />
> >>>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 />
> >>>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 />
> >>>postgis.refractions.net/mailman/listinfo/postgis-devel<br />
> >>><br />
> >>>
</body>
</html>