[postgis-devel] Caching function calls with CTEs

[nicklas.aven at jordogskog.no]

No, dfullywithin will giva a too narrow selection since there could be bigger geometrie that wouldn't be "fullywithin" but anyway be closer.

The idea is if I use your example fron bostongis, to find a approx for 300 without knowing anything about the dataset. 
 SELECT DISTINCT ON(g1.gid)  g1.gid As gref_gid, g1.description As gref_description, g2.gid As gnn_gid, 
g2.description As gnn_description  
FROM sometable As g1, sometable As g2   
WHERE g1.gid <> g2.gid AND ST_DWithin(g1.the_geom, g2.the_geom, 300)   
ORDER BY g1.gid, ST_Distance(g1.the_geom,g2.the_geom) 
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.

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.

/Nicklas


2009-07-19 Paragon Corporation  wrote:

>Nicklas,> > I can't quite see how max distance would helpST_DWithin.  It seems it would help the Fully_DWithin, but don't see how ithelps the ST_DWithin.  Granted I haven't given it much thought. > >Thanks,>Regina
>>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 2:39PM
>To: PostGIS Development Discussion
>Subject: Re:[postgis-devel] Caching function calls with CTEs
>
>>
 Yes,that one I can see.
>
>About the nearest neighbour I havethought about using st_max_distance against the bounding boxes to find the onewith smallest max_distance. That value ought to be good to use in the st_dwithinpart of the query. 
>
>Maybe it would even be worth the effort to usest_distance from the geometry found with the "max_distance" boundingbox to usiin in st_dwithin. 
>
>Hope it is possible to understand what I mean. It isat least one release ahead but possible to test:-)
>
>
>/Nicklas
>
>
>
>2009-07-19 Paragon Corporation wrote:
>
>>>Windowingfunctions come in especially handy for nearest neighbor search getting top n perlocation.  We are going to demonstrate that in our OSCON conference. Slides and materials will of course will be available after the conference isover.
>>>>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 DevelopmentDiscussion
>>Subject: Re:[postgis-devel] Caching function calls withCTEs
>>
>>>>
 This is really cool Regina and Leo
>>
>>It's a newway of thinking for me to to use this recursive thing, but I can see that it isvery useful.
>>I hope I will get some time in the near future to try tounderstand it better.
>>
>>Also the windowing functions together withpostgis aggregate function will be fun totry.
>>
>>/Nicklas
>>
>>
>>
>>
>>
>>2009-07-17Paragon Corporation wrote:
>>
>>Nicklas,
>>>
>>>Wetried CTEs against that annoying issue of functions not being cachedin
>>>the same row. Preliminary tests seem to suggest it might be theideal
>>>solution for this.
>>>We suspect it will also be goodin many other scenarios where the planner
>>>refuses to use indexes inthe 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 forour
>>>presentation - oh well:(
>>>
>>>
>>>Anyrate take a look and test with yourdata to 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/06faf883/attachment.html>