[postgis-users] relationship functions not working well

[Obe, Regina]

What does ST_Intersects + ST_Relate give you and timing.

That's the one I was interested in if that is faster than

&& + ST_Relate

In theory those 2 should give you the same answer.


-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net on behalf of William Kyngesburye
Sent: Fri 12/5/2008 5:34 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] relationship functions not working well
 

 From what I read in the docs, Intersects first checks the bbox, then  
does the full intersect test.  While && just does a bbox test.

&& + st_relate gives me what I want, && only gets the touch-only  
neighboring polys I don't want (and seems to be more rigorous in that  
respect than intersects).

Some times - the full database has 625396 polygons (that's all the  
SWBD files).

* a 1 degree selection (an island tile)

st_intersects only:  216 polys, 494.855 ms

&& only:             219 polys,  12.765 ms

&& plus st_relate:   207 polys, 189.360 ms

* a 58x13 degree rectangle (many tiles empty - alaska region)

st_intersects only:  15469 polys, 1460.344 ms

&& only:             15479 polys,  379.317 ms

&& plus st_relate:   15448 polys, 7217.592 ms


This agrees with what I said about && doing a bbox test only.  And  
verifies my guess that larger areas would get real slow.


On Dec 5, 2008, at 3:34 PM, Paragon Corporation wrote:

> Slight correction - I mean that it could be better to do ST_Intersects
> rather than &&.  No need to do both.
>
> -----Original Message-----
> From: Paragon Corporation [mailto:lr at pcorp.us]
> Sent: Friday, December 05, 2008 4:33 PM
> To: 'PostGIS Users Discussion'
> Subject: RE: [postgis-users] relationship functions not working well
>
>
> William,
>
> Skip the intersects and use && instead (or just use ST_Intersects  
> which has
> && embedded in it).  In theory you are doing an intersects check  
> twice (once
> in relate and once in ST_Intersects) so should be worse.
>
> It is possible though that it could be better to do both since  
> ST_Intersects
> has short-cut logic that I think ST_Relate lacks and if you put the
> intersects call in and it fails, it wouldn't need to do the relate.
>
> I would be interested in the timing differences if you did them.
>
> Hope that helps,
> Regina
> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net
> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of  
> William
> Kyngesburye
> Sent: Friday, December 05, 2008 2:31 PM
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users] relationship functions not working well
>
> On Dec 5, 2008, at 1:02 PM, Paul Ramsey wrote:
>
>> add
>>
>> wkb_geometry && SetSRID('BOX3D(-162 55,-161 56)'::box3d,4326)
>>
>> to your where clause to engage the index.
>>
>> P
>>
> Do you mean just that, without the st_relate?  That gives exactly  
> the same
> results as st_intersects, though may be a little faster since it  
> skips the
> full intersection test.
>
> Or replace the st_intersects half?  Again, probably slightly faster.
>
> Doesn't help the st_relate half in a large box selection.

-----
William Kyngesburye <kyngchaos*at*kyngchaos*dot*com>
http://www.kyngchaos.com/

Earth: "Mostly harmless"

- revised entry in the HitchHiker's Guide to the Galaxy


_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users



-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20081205/228ff60d/attachment.html>