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
