[postgis-devel] Caching function calls with CTEs

[nicklas.aven at jordogskog.no]

Yes,that one I can see.

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. 

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. 

Hope it is possible to understand what I mean. It is at least one release ahead but possible to test :-)


/Nicklas



2009-07-19 Paragon Corporation  wrote:

>Windowing functions come in especially handy for nearestneighbor search getting top n per location.  We are going to demonstratethat in our OSCON conference.  Slides and materials will of course will beavailable after the conference is over.
>>From:postgis-devel-bounces at postgis.refractions.net[mailto:postgis-devel-bounces at postgis.refractions.net] On Behalf Ofnicklas.aven at jordogskog.no
>Sent: Sunday, July 19, 2009 1:52PM
>To: PostGIS Development Discussion
>Subject: Re:[postgis-devel] Caching function calls with CTEs
>
>>
 This is really cool Regina and Leo
>
>It's a new way of thinkingfor me to to use this recursive thing, but I can see that it is veryuseful.
>I hope I will get some time in the near future to try to understandit better.
>
>Also the windowing functions together with postgis aggregatefunction will be fun to try.
>
>/Nicklas
>
>
>
>
>
>2009-07-17Paragon Corporation wrote:
>
>Nicklas,
>>
>>We tried CTEs againstthat annoying issue of functions not being cached in
>>the same row.Preliminary tests seem to suggest it might be the ideal
>>solution forthis.
>>We suspect it will also be good in many other scenarios where theplanner
>>refuses to use indexes in the right orderetc.
>>
>>http://www.postgresonline.com/journal/index.php?/archives/127-guid.html
>>
>>Weare going to test with real spatial queries next in preparation forour
>>OSCON presentation nextweek.
>>
>>http://en.oreilly.com/oscon2009/public/schedule/detail/7859
>>Sadlyit looks like we probably won't have 1.4 out in time for our
>>presentation- oh well :(
>>
>>
>>Anyrate take a look and test with your datato see if it speeds up your
>>queries.
>>
>>L &R
>>
>>
>>_______________________________________________
>>postgis-develmailinglist
>>postgis-devel at postgis.refractions.net
>>http://postgis.refractions.net/mailman/listinfo/postgis-devel
>>
>>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-devel/attachments/20090719/230bdc68/attachment.html>