[postgis-users] How to improve query with repeated spatial join query

[Alexandre Neto]

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)
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20130503/32f450e8/attachment.html>