Hi! Thanks for the hint, I will try to use it and to understand what it is doing.
Alexandre Neto On Fri, May 3, 2013 at 11:32 AM, Hugues François <[email protected]>wrote: > ** > 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 > >
_______________________________________________ postgis-users mailing list [email protected] http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
