<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META NAME="Generator" CONTENT="MS Exchange Server version 6.5.7653.38">
<TITLE>RE: [postgis-users] relationship functions not working well</TITLE>
</HEAD>
<BODY>
<!-- Converted from text/plain format -->
<BR>
<BR>
<P><FONT SIZE=2>What does ST_Intersects + ST_Relate give you and timing.<BR>
<BR>
That's the one I was interested in if that is faster than<BR>
<BR>
&& + ST_Relate<BR>
<BR>
In theory those 2 should give you the same answer.<BR>
<BR>
<BR>
-----Original Message-----<BR>
From: postgis-users-bounces@postgis.refractions.net on behalf of William Kyngesburye<BR>
Sent: Fri 12/5/2008 5:34 PM<BR>
To: PostGIS Users Discussion<BR>
Subject: Re: [postgis-users] relationship functions not working well<BR>
<BR>
<BR>
From what I read in the docs, Intersects first checks the bbox, then <BR>
does the full intersect test. While && just does a bbox test.<BR>
<BR>
&& + st_relate gives me what I want, && only gets the touch-only <BR>
neighboring polys I don't want (and seems to be more rigorous in that <BR>
respect than intersects).<BR>
<BR>
Some times - the full database has 625396 polygons (that's all the <BR>
SWBD files).<BR>
<BR>
* a 1 degree selection (an island tile)<BR>
<BR>
st_intersects only: 216 polys, 494.855 ms<BR>
<BR>
&& only: 219 polys, 12.765 ms<BR>
<BR>
&& plus st_relate: 207 polys, 189.360 ms<BR>
<BR>
* a 58x13 degree rectangle (many tiles empty - alaska region)<BR>
<BR>
st_intersects only: 15469 polys, 1460.344 ms<BR>
<BR>
&& only: 15479 polys, 379.317 ms<BR>
<BR>
&& plus st_relate: 15448 polys, 7217.592 ms<BR>
<BR>
<BR>
This agrees with what I said about && doing a bbox test only. And <BR>
verifies my guess that larger areas would get real slow.<BR>
<BR>
<BR>
On Dec 5, 2008, at 3:34 PM, Paragon Corporation wrote:<BR>
<BR>
> Slight correction - I mean that it could be better to do ST_Intersects<BR>
> rather than &&. No need to do both.<BR>
><BR>
> -----Original Message-----<BR>
> From: Paragon Corporation [<A HREF="mailto:lr@pcorp.us">mailto:lr@pcorp.us</A>]<BR>
> Sent: Friday, December 05, 2008 4:33 PM<BR>
> To: 'PostGIS Users Discussion'<BR>
> Subject: RE: [postgis-users] relationship functions not working well<BR>
><BR>
><BR>
> William,<BR>
><BR>
> Skip the intersects and use && instead (or just use ST_Intersects <BR>
> which has<BR>
> && embedded in it). In theory you are doing an intersects check <BR>
> twice (once<BR>
> in relate and once in ST_Intersects) so should be worse.<BR>
><BR>
> It is possible though that it could be better to do both since <BR>
> ST_Intersects<BR>
> has short-cut logic that I think ST_Relate lacks and if you put the<BR>
> intersects call in and it fails, it wouldn't need to do the relate.<BR>
><BR>
> I would be interested in the timing differences if you did them.<BR>
><BR>
> Hope that helps,<BR>
> Regina<BR>
> -----Original Message-----<BR>
> From: postgis-users-bounces@postgis.refractions.net<BR>
> [<A HREF="mailto:postgis-users-bounces@postgis.refractions.net">mailto:postgis-users-bounces@postgis.refractions.net</A>] On Behalf Of <BR>
> William<BR>
> Kyngesburye<BR>
> Sent: Friday, December 05, 2008 2:31 PM<BR>
> To: PostGIS Users Discussion<BR>
> Subject: Re: [postgis-users] relationship functions not working well<BR>
><BR>
> On Dec 5, 2008, at 1:02 PM, Paul Ramsey wrote:<BR>
><BR>
>> add<BR>
>><BR>
>> wkb_geometry && SetSRID('BOX3D(-162 55,-161 56)'::box3d,4326)<BR>
>><BR>
>> to your where clause to engage the index.<BR>
>><BR>
>> P<BR>
>><BR>
> Do you mean just that, without the st_relate? That gives exactly <BR>
> the same<BR>
> results as st_intersects, though may be a little faster since it <BR>
> skips the<BR>
> full intersection test.<BR>
><BR>
> Or replace the st_intersects half? Again, probably slightly faster.<BR>
><BR>
> Doesn't help the st_relate half in a large box selection.<BR>
<BR>
-----<BR>
William Kyngesburye <kyngchaos*at*kyngchaos*dot*com><BR>
<A HREF="http://www.kyngchaos.com/">http://www.kyngchaos.com/</A><BR>
<BR>
Earth: "Mostly harmless"<BR>
<BR>
- revised entry in the HitchHiker's Guide to the Galaxy<BR>
<BR>
<BR>
_______________________________________________<BR>
postgis-users mailing list<BR>
postgis-users@postgis.refractions.net<BR>
<A HREF="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>
<BR>
</FONT>
</P>
</BODY>
</HTML>
<HTML><BODY><P><hr size=1></P>
<P><STRONG>
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.
</STRONG></P></BODY></HTML>
<P><hr size=1></P>
<P><STRONG><font size="2" color="339900"> Help make the earth a greener place. If at all possible resist printing this email and join us in saving paper. </p> <p> </font></STRONG></P>