I think Humbert is right. If you don't use any condition, the query will try to preform ST_Intersection in every possible combination of rows from bacini and colture, basicly 327x614000 combinations.
I'm not sure if you need both && and the St_intersects() tho. 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. Alexandre Neto On Thu, Mar 13, 2014 at 2:42 PM, Humberto Cereser Ibanez < humbe...@pastoraldacrianca.org.br> wrote: > Hi Pietro Rossin, > > > Em Qui, 2014-03-13 às 06:59 -0700, Pietro Rossin escreveu: > > Hi all > > I'm trying to query 2 vector layers; > > b) bacini_elementari that is river basins - 327 elements > > b) particellepac2006 colture parcels - 614000 elements > > > > I want to intersect these two layers and get the sum of the area of each > > kind of colture for each river basin. > > To reduce the number of colture vectorial features I made a collect query > > (grouping by kind of colture) and I made a intersection between this > > geometry aggregation and all 327 basins. > > > > My query is: > > > > SELECT sum(st_area(ST_Intersection(bacini.geom,colture.geom))) as > > areacoltura, codice_bac, nome, codice_cor, colture.descrizione > > FROM idrologia.bacini_elementari as bacini, > > (SELECT st_collect(geom) as geom, descrizione > > FROM varie.particellepac2006 > > group by 2) as colture > > group by 2,3,4,5 > > > > it's 5000000ms that the query is running and I don't have any result > yet... > > > > The two geometry column have an index (but I think it's not used in this > > query) > > > my guess is to include && and st_intersects operators as a condition on > your query: > > where bacini.geom && colture.geom and st_intersects(bacini.geom, > colture.geom) > > I have done this on a similar work. > lgsc.geom_buffer_0 && lgp.geom_buffer_0 and > st_intersects(lgsc.geom_buffer_0, lgp.geom_buffer_0) > > > Best regards, > > Humberto Cereser Ibanez > > > _______________________________________________ > postgis-users mailing list > postgis-users@lists.osgeo.org > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >
_______________________________________________ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
