[postgis-users] ST_Intersects

[Guido Lemoine]

Erik,

I don't know why european_waters would be a single complex polygon?
Do you mean it is a MULTIPOLYGON in Postgis? If so, it is easy to
break it apart in single polygons. If it includes rivers, I can't 
imagine why it
is a polygon in the first place. I would expect a LINESTRING (or
MULTILINESTRING in your case).

This explains, of course, why performance is not much improved. The &&
operation does not really affect that.

GL



danny wrote:
> 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 <guido.lemoine at jrc.it 
> <mailto:guido.lemoine at jrc.it>> 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
>         postgis-users at postgis.refractions.net
>         <mailto: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
>
>
>     _______________________________________________
>     postgis-users mailing list
>     postgis-users at postgis.refractions.net
>     <mailto:postgis-users at postgis.refractions.net>
>     http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
> ------------------------------------------------------------------------
>
> _______________________________________________
> 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