[postgis-users] ST_Intersects

[danny]

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> 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
>> 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
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20080805/024ab1b8/attachment.html>