As far as I understand SQL, it's normal you repeat clauses. I'm pretty sure the optimizer takes care of that and they are not actually executed more than once.
Maybe indexes on the ST_Centroid functions would help? Where do g.geom and f.geom come from? Jo 2013/5/3 Alexandre Neto <[email protected]> > 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
