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
