[postgis-users] ST_Intersects
danny
whatevar89 at gmail.com
Tue Aug 5 04:14:30 PDT 2008
Guido, it's a bufferzone around the contour of europe.
On Tue, Aug 5, 2008 at 1:02 PM, Guido Lemoine <guido.lemoine at jrc.it> 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 <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
>
>
> _______________________________________________
> 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/533ac8ce/attachment.html>
More information about the postgis-users
mailing list