<div dir="ltr">Guido, it's a bufferzone around the contour of europe.<br><br><div class="gmail_quote">On Tue, Aug 5, 2008 at 1:02 PM, 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="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">Erik,<br>
<br>
I don't know why european_waters would be a single complex polygon?<br>
Do you mean it is a MULTIPOLYGON in Postgis? If so, it is easy to<br>
break it apart in single polygons. If it includes rivers, I can't imagine why it<br>
is a polygon in the first place. I would expect a LINESTRING (or<br>
MULTILINESTRING in your case).<br>
<br>
This explains, of course, why performance is not much improved. The &&<br>
operation does not really affect that.<br>
<br>
GL<br>
<br>
<br>
<br>
danny wrote:<br>
<blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"><div class="Ih2E3d">
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. 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><div><div></div><div class="Wj3C7c">
On Tue, Aug 5, 2008 at 10:53 AM, Guido Lemoine <<a href="mailto:guido.lemoine@jrc.it" target="_blank">guido.lemoine@jrc.it</a> <mailto:<a href="mailto:guido.lemoine@jrc.it" target="_blank">guido.lemoine@jrc.it</a>>> wrote:<br>
<br>
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,<br>
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),<br>
because it seems to be a varchar.<br>
Both a and b should have a spatial index on the_geom, but I reckon<br>
that is already the case. Also,<br>
b (europe_layer) should not be a single polygon, but rather a set<br>
of polygons.<br>
<br>
This is lesson 1 in the PostGIS tutorial, more or less. In return<br>
for my 2 minutes effort, you are<br>
obliged to report the new performance report, so that future users<br>
will benefit.<br>
After all, I just saved you half a year...<br>
<br>
GL<br>
<br>
<br>
<br>
danny wrote:<br>
<br>
Hello,<br>
<br>
I'm wondering if it's normal to have a 420 second response<br>
time for the following query.<br>
A spatial index has been set on the spatial field (the_geom)<br>
and other important fields (like sitecode). I've generously<br>
tweaked the memory options for postgresql.<br>
With such a response time I would have to let my query run for<br>
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<br>
always be so slow? The europe_layer is indeed a complex<br>
polygon....<br>
<br>
select st_intersects(a.the_geom,b.the_geom) from sites a,<br>
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<br>
width=35754)"<br>
" Filter: ((sitecode)::text = 'xxxx'::text)"<br>
" -> Seq Scan on europe_waters b (cost=0.00..23.10<br>
rows=1310 width=32)"<br>
<br>
My true objective would be to join thses two tables through an<br>
st_intersects but for the time it is unconceivable.<br>
Many thanks,<br>
<br>
Erik<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></div></div>
<mailto:<a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a>><div class="Ih2E3d"><br>
<a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
<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<br>
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></div>
<mailto:<a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a>><div class="Ih2E3d"><br>
<a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
<br>
<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>
<br>
</div></blockquote><div><div></div><div class="Wj3C7c">
<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>
</div></div></blockquote></div><br></div>