<div dir="ltr"><div><div>Hello everybody<br></div>my query came to a result!<br>I followed the example by Paul Ramsey and I used a select case/else<br><br>This is the query<br>*******************************<br>SELECT sum(st_area(geom)) as areacoltura, descrizione, codice_bac, nome, codice_cor FROM<br>
(SELECT<br>a.descrizione,<br>b.codice_bac,<br>b.nome,<br>b.codice_cor,<br>CASE <br>WHEN ST_Within(a.geom,b.geom) <br>THEN a.geom<br>ELSE ST_Multi(ST_Intersection(a.geom,b.geom))<br>END AS geom<br>FROM varie.particellepac2006 a<br>
JOIN idrologia.bacini_elementari b<br>ON a.geom && b.geom) as foo<br>group by descrizione, codice_bac, nome, codice_cor<br>*****************************<br><br>Execution time was: 3865564 ms (still really slow...)<br>
6922 rows result.<br><br></div>Explain is:<br>"GroupAggregate (cost=31670075.75..32322147.79 rows=20522 width=29555)"<br>" Output: sum(st_area(CASE WHEN ((a.geom && b.geom) AND _st_contains(b.geom, a.geom)) THEN a.geom ELSE st_multi(st_intersection(a.geom, b.geom)) END)), a.descrizione, b.codice_bac, b.nome, b.codice_cor"<br>
" -> Sort (cost=31670075.75..31673194.73 rows=1247592 width=29555)"<br>" Output: a.geom, b.geom, a.descrizione, b.codice_bac, b.nome, b.codice_cor"<br>" Sort Key: a.descrizione, b.codice_bac, b.nome, b.codice_cor"<br>
" -> Nested Loop (cost=0.00..5497.61 rows=1247592 width=29555)"<br>" Output: a.geom, b.geom, a.descrizione, b.codice_bac, b.nome, b.codice_cor"<br>" -> Seq Scan on idrologia.bacini_elementari b (cost=0.00..140.32 rows=332 width=28397)"<br>
" Output: <a href="http://b.id" target="_top" rel="nofollow" link="external">b.id</a>, b.geom, b.objectid, b.codice_bac, b.nome, b.ordine, b.quota_medi, b.area_kmq, b.codice_cor"<br>" -> Index Scan using sidx_particellepac2006_geom on varie.particellepac2006 a (cost=0.00..16.10 rows=3 width=1158)"<br>
" Output: a.id_pac, a.geom, a.cod_nazion, a.foglio, a.particella, a.sub, a.prog_polig, a.area_colt, a.cod_coltur, a.anno_foto, a.mese_foto, a.cod_variet, a.istatp, a.stato_colt, a.descrizione, a.shape_leng, a.shape_area"<br>
" Index Cond: (a.geom && b.geom)"<br><br><div><br></div><div>Is there anything I can do to make it perform faster?<br></div><div><br></div><div>Is there some parameter to tune in Server Configuration?<br>
</div><div>I use Winxp32bit 4Gb ram, dual operon 252.<br></div><div><br>Thanks!<br></div><div>Pietro<br></div><div><br><br></div></div><div class="gmail_extra"><br><br><div class="gmail_quote">2014-03-14 2:43 GMT+01:00 Paul Ramsey-3 [via PostGIS] <span dir="ltr"><<a href="/user/SendEmail.jtp?type=node&node=5005908&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="">
You're doing it wrong.
<br><br><a href="http://gis.stackexchange.com/questions/31310/acquiring-arcgis-like-speed-in-postgis/31562#31562" rel="nofollow" link="external" target="_blank">http://gis.stackexchange.com/questions/31310/acquiring-arcgis-like-speed-in-postgis/31562#31562</a><br>
<br></div><div><div class="h5">On Thu, Mar 13, 2014 at 6:59 AM, Pietro Rossin <<a href="http://user/SendEmail.jtp?type=node&node=5005881&i=0" rel="nofollow" link="external" target="_blank">[hidden email]</a>> wrote:
</div></div><div><div><div class="h5"><div class='shrinkable-quote'><br>> 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>> Mi pc id dual opteron with 4Gb RAM,
<br>> "PostgreSQL 9.1.3, compiled by Visual C++ build 1500, 32-bit"
<br>>
<br>> "POSTGIS="2.0.0 r9605" GEOS="3.3.3-CAPI-1.7.4" PROJ="Rel. 4.8.0, 6 March
<br>> 2012" GDAL="GDAL 1.9.0, released 2011/12/29" LIBXML="2.7.8"
<br>> LIBJSON="UNKNOWN" TOPOLOGY RASTER"
<br>>
<br>>
<br>>
<br>> --
<br>> View this message in context: <a href="http://postgis.17.x6.nabble.com/Query-performance-really-slow-tp5005872.html" rel="nofollow" link="external" target="_blank">http://postgis.17.x6.nabble.com/Query-performance-really-slow-tp5005872.html</a></div>
> Sent from the PostGIS - User mailing list archive at Nabble.com.
<br>> _______________________________________________
<br>> postgis-users mailing list
<br></div></div>> <a href="http://user/SendEmail.jtp?type=node&node=5005881&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>_______________________________________________
<br>postgis-users mailing list
<br><div class=""><a href="http://user/SendEmail.jtp?type=node&node=5005881&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>
<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-tp5005872p5005881.html" target="_blank" rel="nofollow" link="external">http://postgis.17.x6.nabble.com/Query-performance-really-slow-tp5005872p5005881.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-tp5005872p5005908.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/>