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. 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. So would be something like SELECT a.gid, b.the_geom FROM sites a, europe_waters b WHERE a.sitecode = 'xxxx' AND ST_DWithin(a.the_geom, b.the_geom, 0.001) The other problem with your code is that your europe_waters is huge which makes the index not all that useful. 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. Hope that helps, Regina
________________________________ From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of danny Sent: Tuesday, August 05, 2008 6:40 AM To: PostGIS Users Discussion Subject: Re: [postgis-users] ST_Intersects Thanks Guido! 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. The european_waters polygon is one single complex polygon, scale 1/100000. 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! 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. explain select sitecode from sites a, europe_waters b where st_intersects(a.the_geom,b.the_geom) and a.sitecode = 'xxxx'; "Nested Loop (cost=0.00..461.71 rows=1 width=10)" " Join Filter: _st_intersects(a.the_geom, b.the_geom)" " -> Seq Scan on sites a (cost=0.00..453.43 rows=1 width=35764)" " Filter: ((sitecode)::text = 'xxxx'::text)" " -> Index Scan using idx_europe_waters on europe_waters b (cost=0.00..8.27 rows=1 width=32)" " Index Cond: (a.the_geom && b.the_geom)" " Filter: (a.the_geom && b.the_geom)" On Tue, Aug 5, 2008 at 10:53 AM, Guido Lemoine <[EMAIL PROTECTED]> wrote: Erik Try this, and see if it is any faster: select st_intersects(a.the_geom,b.the_geom) from sites a, europe_waters b where a.the_geom && b.the_geom and a.sitecode = 'xxxx'; I guess you mean that sitecode is indexed (not spatially indexed), because it seems to be a varchar. Both a and b should have a spatial index on the_geom, but I reckon that is already the case. Also, b (europe_layer) should not be a single polygon, but rather a set of polygons. This is lesson 1 in the PostGIS tutorial, more or less. In return for my 2 minutes effort, you are obliged to report the new performance report, so that future users will benefit. After all, I just saved you half a year... GL danny wrote: Hello, I'm wondering if it's normal to have a 420 second response time for the following query. 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. 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! :) 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.... select st_intersects(a.the_geom,b.the_geom) from sites a, europe_waters b where a.sitecode = 'xxxx'; "Nested Loop (cost=0.00..496.18 rows=1310 width=35786)" " -> Seq Scan on sites a (cost=0.00..453.43 rows=1 width=35754)" " Filter: ((sitecode)::text = 'xxxx'::text)" " -> Seq Scan on europe_waters b (cost=0.00..23.10 rows=1310 width=32)" My true objective would be to join thses two tables through an st_intersects but for the time it is unconceivable. Many thanks, Erik ------------------------------------------------------------------------ _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users -- ---------------------------------------------------------------- Guido Lemoine Joint Research Centre, European Commission Institute for the Protection and Security of the Citizen (IPSC) Support to External Security Via E. Fermi, 2749 TP 267 Ispra 21027 (VA), Italy Tel. +39 0332 786239 (direct line) Fax. +39 0332 785154 WWW: http://ses.jrc.it ---------------------------------------------------------------- Disclaimer: Views expressed are those of the individual and do not represent the views of the European Commission _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users ----------------------------------------- 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.
_______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
