Hi!<div><br></div><div>Thanks for the hint, I will try to use it and to understand what it is doing.</div><div><br></div><div>Alexandre Neto<br><br><div class="gmail_quote">On Fri, May 3, 2013 at 11:32 AM, Hugues François <span dir="ltr"><<a href="mailto:hugues.francois@irstea.fr" target="_blank">hugues.francois@irstea.fr</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><u></u>
<div>
<div dir="ltr" align="left"><font color="#0000ff" face="Arial"><span>Hello,</span></font></div>
<div dir="ltr" align="left"><font color="#0000ff" face="Arial"><span></span></font> </div>
<div dir="ltr" align="left"><font color="#0000ff" face="Arial"><span>I think over() can help you. You should try something
like the query below :</span></font></div>
<div dir="ltr" align="left"><font color="#0000ff" face="Arial"><span></span></font> </div>
<div dir="ltr" align="left"><font color="#0000ff" face="Arial"><span>select b.gid, volume / sum(volume) over(partition by
p.gid) asvol_per, p.bgrill as bgrill, b.geom</span></font></div>
<div dir="ltr" align="left"><font color="#0000ff" face="Arial"><span>from buildings as b, </span></font></div>
<div dir="ltr" align="left"><font color="#0000ff" face="Arial"><span>join stat_parcels as p on st_contains(p.geom,
st_centroid(b.geom))</span></font></div>
<div dir="ltr" align="left"><font color="#0000ff" face="Arial"><span></span></font> </div>
<div dir="ltr" align="left"><font color="#0000ff" face="Arial"><span>Hugues.</span></font></div>
<div> </div>
<div align="left">
<p style="MARGIN:0cm 0cm 0pt" class="MsoNormal" align="left"><span style="FONT-FAMILY:Arial;FONT-SIZE:10pt">--<u></u><u></u><u></u></span></p>
<p style="MARGIN:0cm 0cm 0pt" class="MsoNormal"><span style="FONT-FAMILY:Arial;FONT-SIZE:10pt"><u></u> <u></u></span></p>
<p style="MARGIN:0cm 0cm 0pt" class="MsoNormal"><span style="FONT-FAMILY:Arial;FONT-SIZE:10pt">Hugues
FRANÇOIS<u></u><u></u></span></p></div>
<div> </div><br>
<div dir="ltr" lang="fr" align="left">
<hr>
<font face="Tahoma"><b>From:</b> <a href="mailto:postgis-users-bounces@lists.osgeo.org" target="_blank">postgis-users-bounces@lists.osgeo.org</a>
[mailto:<a href="mailto:postgis-users-bounces@lists.osgeo.org" target="_blank">postgis-users-bounces@lists.osgeo.org</a>] <b>On Behalf Of </b>Alexandre
Neto<br><b>Sent:</b> Friday, May 03, 2013 12:25 PM<br><b>To:</b> PostGIS Users
Discussion<br><b>Subject:</b> [postgis-users] How to improve query with repeated
spatial joinquery<br></font><br></div><div><div class="h5">
<div></div>Hello all,<br>
<div>
<div>
<div><br></div></div>
<div>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.</div></div>
<div><br></div>
<div>I have written the SQL code below (that works), but I think it might
be improved, since I see repeated proceedings in it. </div>
<div><br></div>
<div>I would greatly appreciate if someone give me
some advice about it.</div>
<div><br></div>
<div>Thanks,</div>
<div><br></div>
<div>Alexandre Neto</div>
<div><br></div>
<div>-----------</div>
<div><br></div>
<div>
<div>SELECT </div>
<div><span style="white-space:pre-wrap"></span>b.gid,</div>
<div><span style="white-space:pre-wrap"></span>b.volume /
t.total_volume as vol_per,</div>
<div><span style="white-space:pre-wrap"></span>b.geom,</div>
<div><span style="white-space:pre-wrap"></span>p.bgri11 as
bgri11,</div>
<div>FROM</div>
<div><span style="white-space:pre-wrap"></span>buildings as
b,</div>
<div><span style="white-space:pre-wrap"></span>stat_parcels as p,</div>
<div><span style="white-space:pre-wrap"></span>(SELECT </div>
<div><span style="white-space:pre-wrap"></span>sum(b.volume) as
total_volume,</div>
<div><span style="white-space:pre-wrap"></span>p.bgri11 as
bgri11</div>
<div><span style="white-space:pre-wrap"></span>FROM</div>
<div><span style="white-space:pre-wrap"></span>buildings as
b,</div>
<div><span style="white-space:pre-wrap"></span>stat_parcels as p</div>
<div><span style="white-space:pre-wrap"></span>WHERE</div>
<div><span style="white-space:pre-wrap"></span>ST_CONTAINS(p.geom, St_centroid(b.geom))</div>
<div><span style="white-space:pre-wrap"></span>GROUP BY</div>
<div><span style="white-space:pre-wrap"></span>p.bgri11) as
t</div>
<div>WHERE</div>
<div><span style="white-space:pre-wrap"></span>ST_CONTAINS(g.geom, St_centroid(f.geom))</div>
<div><span style="white-space:pre-wrap"></span>AND p.bgri11 =
t.bgri11;</div></div>
<div><br></div>
<div><br></div>
<div>My tables look like this:
<div><br></div>
<div>
<div>CREATE TABLE buildings</div>
<div> gid serial NOT NULL,</div>
<div> volume double precision,</div>
<div> geom geometry(MultiPolygon,27493),</div>
<div> CONSTRAINT edificios_habitacao_pkey PRIMARY KEY (gid);</div>
<div><br></div>
<div>
<div>CREATE TABLE stat_parcels</div>
<div> gid serial NOT NULL,</div>
<div> bgri11 character varying(11),</div>
<div> geom geometry(MultiPolygon,27493),</div>
<div> CONSTRAINT bgri11_cascais_pkey PRIMARY KEY
(gid)</div></div></div></div></div></div></div>
<br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
<br></blockquote></div><br></div>