<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.6000.16481" name=GENERATOR></HEAD>
<BODY>
<DIV dir=ltr align=left><SPAN class=550155810-05082008><FONT face=Arial
color=#0000ff size=2>Have you tried ST_DWithin instead of intersects. On
some occassions it does perform a little better than ST_Intersects. I
think in 1.3.4 Paul Ramsey had put in a distance short-cut optimization for
ST_DWithin that makes it perform even better but I haven't had a chance to test
that out yet with my set. </FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=550155810-05082008><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=550155810-05082008><FONT face=Arial
color=#0000ff size=2>Also you really should put the b.site_code check before
your ST_Intersects clause or ST_Intersects in your inner join (unless of cause
you are going to get rid of that site code check anyway). The costing
metrics on the postgis functions are not set yet for 8.3 which sometimes makes
them get applied before regular old stuff.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=550155810-05082008><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=550155810-05082008><FONT face=Arial
color=#0000ff size=2><SPAN class=550155810-05082008><FONT face=Arial
color=#0000ff size=2>So would be something like
</FONT></SPAN></FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=550155810-05082008><FONT face=Arial
color=#0000ff size=2>SELECT a.gid, b.the_geom</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=550155810-05082008><FONT face=Arial
color=#0000ff size=2>FROM sites a, europe_waters b</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=550155810-05082008><FONT face=Arial
color=#0000ff size=2>WHERE a.sitecode = 'xxxx' AND ST_DWithin(a.the_geom,
b.the_geom, 0.001)</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=550155810-05082008><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=550155810-05082008><FONT face=Arial
color=#0000ff size=2>The other problem with your code is that your europe_waters
is huge which makes the index not all that useful.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=550155810-05082008><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=550155810-05082008><FONT face=Arial
color=#0000ff size=2>You might want to dice that up a bit using intersection
clipping. You'll get more rows, but overall I think the performance would
be better.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=550155810-05082008><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=550155810-05082008><FONT face=Arial
color=#0000ff size=2>Hope that helps,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=550155810-05082008><FONT face=Arial
color=#0000ff size=2>Regina</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=550155810-05082008><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=550155810-05082008><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=550155810-05082008><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=550155810-05082008><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV><BR>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B>
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <B>On Behalf Of
</B>danny<BR><B>Sent:</B> Tuesday, August 05, 2008 6:40 AM<BR><B>To:</B> PostGIS
Users Discussion<BR><B>Subject:</B> Re: [postgis-users]
ST_Intersects<BR></FONT><BR></DIV>
<DIV></DIV>
<DIV dir=ltr>Thanks Guido!<BR><BR>The sample you gave performed slightly better:
304 seconds. I'll still have to wait a few months to let it run on all my data
though. <BR>Yes, sitecode has a "normal" index, not a spatial one. Both
the_geom's have spatial ones. <BR>The european_waters polygon is one single
complex polygon, scale 1/100000.<BR>What's annoying is that when I do an
intersect in Arc View it's almost instantaneous. Of course I don't get exaclty
what I wan't but with some tweaking I can maybe get closer. That would be a
great disappointment though. I was really looking forward to using ST_Within
intensively! <BR><BR>Thanks Jean David, no performance gain though by using it
in the WHERE clause, where I intended to use it in the first place. I put it in
the Select for testing purposes. <BR><BR>explain select sitecode from
sites a, europe_waters b <BR>where st_intersects(a.the_geom,b.the_geom)
and a.sitecode = 'xxxx';<BR><BR>"Nested Loop (cost=0.00..461.71 rows=1
width=10)"<BR>" Join Filter: _st_intersects(a.the_geom,
b.the_geom)"<BR>" -> Seq Scan on sites a (cost=0.00..453.43
rows=1 width=35764)"<BR>" Filter:
((sitecode)::text = 'xxxx'::text)"<BR>" -> Index Scan using
idx_europe_waters on europe_waters b (cost=0.00..8.27 rows=1
width=32)"<BR>" Index Cond:
(a.the_geom &&
b.the_geom)"<BR>" Filter: (a.the_geom
&& b.the_geom)"<BR><BR><BR><BR>
<DIV class=gmail_quote>On Tue, Aug 5, 2008 at 10:53 AM, Guido Lemoine <SPAN
dir=ltr><<A
href="mailto:guido.lemoine@jrc.it">guido.lemoine@jrc.it</A>></SPAN>
wrote:<BR>
<BLOCKQUOTE class=gmail_quote
style="PADDING-LEFT: 1ex; MARGIN: 0pt 0pt 0pt 0.8ex; BORDER-LEFT: rgb(204,204,204) 1px solid">Erik<BR><BR>Try
this, and see if it is any faster:<BR><BR>select
st_intersects(a.the_geom,b.the_geom) from sites a, europe_waters b
where a.the_geom && b.the_geom and<BR>a.sitecode = 'xxxx';<BR><BR>I
guess you mean that sitecode is indexed (not spatially indexed), because it
seems to be a varchar.<BR>Both a and b should have a spatial index on
the_geom, but I reckon that is already the case. Also,<BR>b (europe_layer)
should not be a single polygon, but rather a set of polygons.<BR><BR>This is
lesson 1 in the PostGIS tutorial, more or less. In return for my 2 minutes
effort, you are<BR>obliged to report the new performance report, so that
future users will benefit.<BR>After all, I just saved you half a
year...<BR><BR>GL<BR><BR><BR><BR>danny wrote:<BR>
<BLOCKQUOTE class=gmail_quote
style="PADDING-LEFT: 1ex; MARGIN: 0pt 0pt 0pt 0.8ex; BORDER-LEFT: rgb(204,204,204) 1px solid">
<DIV>
<DIV></DIV>
<DIV class=Wj3C7c>Hello,<BR><BR>I'm wondering if it's normal to have a 420
second response time for the following query.<BR>A spatial index has been
set on the spatial field (the_geom) and other important fields (like
sitecode). I've generously tweaked the memory options for
postgresql.<BR>With such a response time I would have to let my query run
for half a year before getting the answer I'm interested in!
:)<BR><BR>Anybody know how I can boost up the process or is it doomed to
always be so slow? The europe_layer is indeed a complex
polygon....<BR><BR>select st_intersects(a.the_geom,b.the_geom) from
sites a, europe_waters b where a.sitecode = 'xxxx';<BR><BR>"Nested
Loop (cost=0.00..496.18 rows=1310 width=35786)"<BR>" ->
Seq Scan on sites a (cost=0.00..453.43 rows=1 width=35754)"<BR>"
Filter: ((sitecode)::text = 'xxxx'::text)"<BR>"
-> Seq Scan on europe_waters b (cost=0.00..23.10
rows=1310 width=32)"<BR><BR>My true objective would be to join thses two
tables through an st_intersects but for the time it is unconceivable.
<BR>Many
thanks,<BR><BR>Erik<BR></DIV></DIV>------------------------------------------------------------------------<BR><BR>_______________________________________________<BR>postgis-users
mailing list<BR><A href="mailto:postgis-users@postgis.refractions.net"
target=_blank>postgis-users@postgis.refractions.net</A><BR><A
href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target=_blank>http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR> <BR></BLOCKQUOTE><BR>--
<BR>----------------------------------------------------------------<BR>Guido
Lemoine<BR>Joint Research Centre, European Commission<BR>Institute for the
Protection and Security of the Citizen (IPSC)<BR>Support to External
Security<BR>Via E. Fermi, 2749 TP 267 Ispra 21027 (VA), Italy<BR>Tel. +39 0332
786239 (direct line) Fax. +39 0332 785154<BR>WWW: <A href="http://ses.jrc.it"
target=_blank>http://ses.jrc.it</A><BR>----------------------------------------------------------------<BR>Disclaimer:<BR>Views
expressed are those of the individual and do not represent the views of the
European
Commission<BR><BR><BR>_______________________________________________<BR>postgis-users
mailing list<BR><A href="mailto:postgis-users@postgis.refractions.net"
target=_blank>postgis-users@postgis.refractions.net</A><BR><A
href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target=_blank>http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR></BLOCKQUOTE></DIV><BR></DIV></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>