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:[EMAIL PROTECTED]
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: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] 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@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
