Guido, it's a bufferzone around the contour of europe. On Tue, Aug 5, 2008 at 1:02 PM, Guido Lemoine <[EMAIL PROTECTED]> wrote:
> 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 >
_______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
