[postgis-users] ST_Intersects

[Guido Lemoine]

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
> postgis-users at postgis.refractions.net
> 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