Hugues, it worked like a charm! I also checked this<http://www.postgresql.org/docs/9.2/static/tutorial-window.html> to understand what was it doing. And was exactly what I needed.
Like this, the nested loop was processed only once, and I saved around 33% of the query time. Thanks, Alexandre Neto On Fri, May 3, 2013 at 11:55 AM, Alexandre Neto <[email protected]>wrote: > Hi! > > Thanks for the hint, I will try to use it and to understand what it is > doing. > > Alexandre Neto > > On Fri, May 3, 2013 at 11:32 AM, Hugues François < > [email protected]> wrote: > >> ** >> Hello, >> >> I think over() can help you. You should try something like the query >> below : >> >> select b.gid, volume / sum(volume) over(partition by p.gid) asvol_per, >> p.bgrill as bgrill, b.geom >> from buildings as b, >> join stat_parcels as p on st_contains(p.geom, st_centroid(b.geom)) >> >> Hugues. >> >> >> --****** >> >> ** ** >> >> Hugues FRANÇOIS**** >> >> >> ------------------------------ >> *From:* [email protected] [mailto: >> [email protected]] *On Behalf Of *Alexandre Neto >> *Sent:* Friday, May 03, 2013 12:25 PM >> *To:* PostGIS Users Discussion >> *Subject:* [postgis-users] How to improve query with repeated spatial >> joinquery >> >> Hello all, >> >> My goal is to calculate for each building it's volume >> percentage relatively to the total buildings volume inside the statistical >> parcel he is in. >> >> I have written the SQL code below (that works), but I think it might be >> improved, since I see repeated proceedings in it. >> >> I would greatly appreciate if someone give me some advice about it. >> >> Thanks, >> >> Alexandre Neto >> >> ----------- >> >> SELECT >> b.gid, >> b.volume / t.total_volume as vol_per, >> b.geom, >> p.bgri11 as bgri11, >> FROM >> buildings as b, >> stat_parcels as p, >> (SELECT >> sum(b.volume) as total_volume, >> p.bgri11 as bgri11 >> FROM >> buildings as b, >> stat_parcels as p >> WHERE >> ST_CONTAINS(p.geom, St_centroid(b.geom)) >> GROUP BY >> p.bgri11) as t >> WHERE >> ST_CONTAINS(g.geom, St_centroid(f.geom)) >> AND p.bgri11 = t.bgri11; >> >> >> My tables look like this: >> >> CREATE TABLE buildings >> gid serial NOT NULL, >> volume double precision, >> geom geometry(MultiPolygon,27493), >> CONSTRAINT edificios_habitacao_pkey PRIMARY KEY (gid); >> >> CREATE TABLE stat_parcels >> gid serial NOT NULL, >> bgri11 character varying(11), >> geom geometry(MultiPolygon,27493), >> CONSTRAINT bgri11_cascais_pkey PRIMARY KEY (gid) >> >> _______________________________________________ >> postgis-users mailing list >> [email protected] >> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >> >> >
_______________________________________________ postgis-users mailing list [email protected] http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
