Hi, thanks for your help. On Fri, May 3, 2013 at 11:31 AM, Jo <[email protected]> wrote:
> 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. > Using EXPLAIN, it says that it will repeat the a Nested Loop to check the ST_CONTAIN > > Maybe indexes on the ST_Centroid functions would help? > For that, do Ihave to "materialize" the point geometry in my table, and create a index? > > Where do g.geom and f.geom come from? > My mistake, I tried to adapt the query a bit to make it more obvious here, and forgot those two. It should be ST_CONTAIN(p.geom, ST_CENTROID(b.geom)) > 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 > >
_______________________________________________ postgis-users mailing list [email protected] http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
