[postgis-users] relationship functions not working well

[Paragon Corporation]

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.


>> Well, that works:
>>
>> ST_relate(wkb_geometry, SetSRID('BOX3D(-162 55,-161 56)'::box3d, 
>> 4326),
>> 'T********')
>>
>> But it's painfully slow - looks like relate doesn't do an index 
>> bounding box test.
>>
>> ... added a normal intersect (it's a superset of what I want) and let 
>> short-circuit logic take care of the initial selection:
>>
>> ST_Intersects(wkb_geometry, SetSRID('BOX3D(-162 55,-161 56)'::box3d,
>> 4326))
>> AND ST_relate(wkb_geometry, SetSRID('BOX3D(-162 55,-161 56)'::box3d, 
>> 4326),
>> 'T********')
>>
>> That's reasonably fast, for a small box at least.  It will likely 
>> slow down on a larger box.
>>
>> -----
>> William Kyngesburye <kyngchaos*at*kyngchaos*dot*com> 
>> http://www.kyngchaos.com/
>>
>> "Those people who most want to rule people are, ipso-facto, those 
>> least suited to do it."
>>
>> - A rule of the universe, from 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
>>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users

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

"Those people who most want to rule people are, ipso-facto, those least
suited to do it."

- A rule of the universe, from 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