[postgis-users] relationship functions not working well

[William Kyngesburye]

 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