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 <[EMAIL PROTECTED]
<mailto:[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]
<mailto:[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]
<mailto:[email protected]>
http://postgis.refractions.net/mailman/listinfo/postgis-users
------------------------------------------------------------------------
_______________________________________________
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
