<div dir="ltr"><div><div><div><div><div><div>Hello<br></div>Ok I'll try..<br></div>By the way I used the collection (that of course don't have geom index) to reduce the possible combination of rows from <span style="font-family:arial,sans-serif;font-size:13px">bacini and </span><span style="font-family:arial,sans-serif;font-size:13px">colture to 327x</span><span style="font-family:arial,sans-serif;font-size:13px">110 (327 basins and 110 collected features)<br>
<br></span></div><span style="font-family:arial,sans-serif;font-size:13px">By the way<br></span></div><span style="font-family:arial,sans-serif;font-size:13px">I just stopped the query, tomorrow I'll try a new run with && and intersects using indexes this way..<br>
</span></div><span style="font-family:arial,sans-serif;font-size:13px">Thanks to all<br></span></div><span style="font-family:arial,sans-serif;font-size:13px">Pietro<br></span></div><div class="gmail_extra"><br><br><div class="gmail_quote">
2014-03-13 16:17 GMT+01:00 Alexandre Neto [via PostGIS] <span dir="ltr"><<a href="/user/SendEmail.jtp?type=node&node=5005879&i=0" target="_top" rel="nofollow" link="external">[hidden email]</a>></span>:<br><blockquote style='border-left:2px solid #CCCCCC;padding:0 1em' class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div class="">
<div dir="ltr">I think Humbert is right.<div><br></div><div>If you don't use any condition, the query will try to preform <span style="font-family:arial,sans-serif;font-size:13px">ST_Intersection</span> in every possible combination of rows from <span style="font-family:arial,sans-serif;font-size:13px">bacini and </span><span style="font-family:arial,sans-serif;font-size:13px">colture, basicly 327x</span><span style="font-family:arial,sans-serif;font-size:13px">614000 combinations</span><span style="font-family:arial,sans-serif;font-size:13px">.</span></div>
<div><span style="font-family:arial,sans-serif;font-size:13px"><br></span></div><div><span style="font-family:arial,sans-serif;font-size:13px">I'm not sure if you need both && and the St_intersects() tho.</span></div>
<div><span style="font-family:arial,sans-serif;font-size:13px"><br></span></div><div><span style="font-family:arial,sans-serif;font-size:13px">If I'm not wrong, && operator check if the boundary boxes of two features interect, but if you are using indexes in both columns St_Intersects will preform the same with the boundaries before doing it with the real geometries.</span></div>
<div><span style="font-family:arial,sans-serif;font-size:13px"><br></span></div><div><span style="font-family:arial,sans-serif;font-size:13px">Alexandre Neto</span></div></div></div><div class="gmail_extra"><br><br><div class="gmail_quote">
<div><div class="h5">
On Thu, Mar 13, 2014 at 2:42 PM, Humberto Cereser Ibanez <span dir="ltr"><<a href="http://user/SendEmail.jtp?type=node&node=5005878&i=0" rel="nofollow" link="external" target="_blank">[hidden email]</a>></span> wrote:<br>
</div></div><blockquote style='border-left:2px solid #CCCCCC;padding:0 1em' style="border-left:2px solid #cccccc;padding:0 1em" class="gmail_quote"><div><div class="h5">
Hi Pietro Rossin,<br>
<br>
<br>
Em Qui, 2014-03-13 às 06:59 -0700, Pietro Rossin escreveu:<br>
<div>> Hi all<br>
> I'm trying to query 2 vector layers;<br>
> b) bacini_elementari that is river basins - 327 elements<br>
> b) particellepac2006 colture parcels - 614000 elements<br>
><br>
> I want to intersect these two layers and get the sum of the area of each<br>
> kind of colture for each river basin.<br>
> To reduce the number of colture vectorial features I made a collect query<br>
> (grouping by kind of colture) and I made a intersection between this<br>
> geometry aggregation and all 327 basins.<br>
><br>
> My query is:<br>
><br>
> SELECT sum(st_area(ST_Intersection(bacini.geom,colture.geom))) as<br>
> areacoltura, codice_bac, nome, codice_cor, colture.descrizione<br>
> FROM idrologia.bacini_elementari as bacini,<br>
> (SELECT st_collect(geom) as geom, descrizione<br>
> FROM varie.particellepac2006<br>
> group by 2) as colture<br>
> group by 2,3,4,5<br>
><br>
> it's 5000000ms that the query is running and I don't have any result yet...<br>
><br>
> The two geometry column have an index (but I think it's not used in this<br>
> query)<br>
><br>
</div>my guess is to include && and st_intersects operators as a condition on<br>
your query:<br>
<br>
where bacini.geom && colture.geom and st_intersects(bacini.geom,<br>
colture.geom)<br>
<br>
I have done this on a similar work.<br>
lgsc.geom_buffer_0 && lgp.geom_buffer_0 and<br>
st_intersects(lgsc.geom_buffer_0, lgp.geom_buffer_0)<br>
<br>
<br>
Best regards,<br>
<br>
Humberto Cereser Ibanez<br>
</div></div><div><div><br>
<br>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="http://user/SendEmail.jtp?type=node&node=5005878&i=1" rel="nofollow" link="external" target="_blank">[hidden email]</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" rel="nofollow" link="external" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a></div></div></blockquote></div><br></div>
<div class="">
<br>_______________________________________________
<br>postgis-users mailing list
<br><a href="http://user/SendEmail.jtp?type=node&node=5005878&i=2" rel="nofollow" link="external" target="_blank">[hidden email]</a>
<br><a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" rel="nofollow" link="external" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a>
<br>
<br>
<hr color="#cccccc" noshade size="1">
</div><div style="color:#444;font:12px tahoma,geneva,helvetica,arial,sans-serif"><div class="">
<div style="font-weight:bold">If you reply to this email, your message will be added to the discussion below:</div>
</div><a href="http://postgis.17.x6.nabble.com/Query-performance-really-slow-tp5005872p5005878.html" target="_blank" rel="nofollow" link="external">http://postgis.17.x6.nabble.com/Query-performance-really-slow-tp5005872p5005878.html</a>
</div><div class="HOEnZb"><div class="h5">
<div style="color:#666;font:11px tahoma,geneva,helvetica,arial,sans-serif;margin-top:.4em;line-height:1.5em">
To unsubscribe from Query performance really slow.., <a href="" target="_blank" rel="nofollow" link="external">click here</a>.<br>
<a href="http://postgis.17.x6.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml" rel="nofollow" style="font:9px serif" target="_blank" link="external">NAML</a>
</div></div></div></blockquote></div><br></div>
<br/><hr align="left" width="300" />
View this message in context: <a href="http://postgis.17.x6.nabble.com/Query-performance-really-slow-tp5005872p5005879.html">Re: Query performance really slow..</a><br/>
Sent from the <a href="http://postgis.17.x6.nabble.com/PostGIS-User-f3516033.html">PostGIS - User mailing list archive</a> at Nabble.com.<br/>