<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="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">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="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"><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>