<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content="text/html; charset=iso-8859-1" http-equiv=Content-Type>
<META name=GENERATOR content="MSHTML 8.00.6001.19412"></HEAD>
<BODY>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=497422610-03052013>Hello,</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=497422610-03052013></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=497422610-03052013>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 size=2 face=Arial><SPAN
class=497422610-03052013></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=497422610-03052013>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 size=2 face=Arial><SPAN
class=497422610-03052013>from buildings as b, </SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=497422610-03052013>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 size=2 face=Arial><SPAN
class=497422610-03052013></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=497422610-03052013>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">--<?xml:namespace prefix = o ns =
"urn:schemas-microsoft-com:office:office" /><o:p></o:p></SPAN></P>
<P style="MARGIN: 0cm 0cm 0pt" class=MsoNormal><SPAN
style="FONT-FAMILY: Arial; FONT-SIZE: 10pt"><o:p> </o:p></SPAN></P>
<P style="MARGIN: 0cm 0cm 0pt" class=MsoNormal><SPAN
style="FONT-FAMILY: Arial; FONT-SIZE: 10pt">Hugues
FRANÇOIS<o:p></o:p></SPAN></P></DIV>
<DIV> </DIV><BR>
<DIV dir=ltr lang=fr class=OutlookMessageHeader align=left>
<HR tabIndex=-1>
<FONT size=2 face=Tahoma><B>From:</B> postgis-users-bounces@lists.osgeo.org
[mailto:postgis-users-bounces@lists.osgeo.org] <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>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" class=Apple-tab-span></SPAN>b.gid,</DIV>
<DIV><SPAN style="WHITE-SPACE: pre" class=Apple-tab-span></SPAN>b.volume /
t.total_volume as vol_per,</DIV>
<DIV><SPAN style="WHITE-SPACE: pre" class=Apple-tab-span></SPAN>b.geom,</DIV>
<DIV><SPAN style="WHITE-SPACE: pre" class=Apple-tab-span></SPAN>p.bgri11 as
bgri11,</DIV>
<DIV>FROM</DIV>
<DIV><SPAN style="WHITE-SPACE: pre" class=Apple-tab-span></SPAN>buildings as
b,</DIV>
<DIV><SPAN style="WHITE-SPACE: pre"
class=Apple-tab-span></SPAN>stat_parcels as p,</DIV>
<DIV><SPAN style="WHITE-SPACE: pre"
class=Apple-tab-span></SPAN>(SELECT </DIV>
<DIV><SPAN style="WHITE-SPACE: pre" class=Apple-tab-span></SPAN>sum(b.volume) as
total_volume,</DIV>
<DIV><SPAN style="WHITE-SPACE: pre" class=Apple-tab-span></SPAN>p.bgri11 as
bgri11</DIV>
<DIV><SPAN style="WHITE-SPACE: pre" class=Apple-tab-span></SPAN>FROM</DIV>
<DIV><SPAN style="WHITE-SPACE: pre" class=Apple-tab-span></SPAN>buildings as
b,</DIV>
<DIV><SPAN style="WHITE-SPACE: pre"
class=Apple-tab-span></SPAN>stat_parcels as p</DIV>
<DIV><SPAN style="WHITE-SPACE: pre" class=Apple-tab-span></SPAN>WHERE</DIV>
<DIV><SPAN style="WHITE-SPACE: pre"
class=Apple-tab-span></SPAN>ST_CONTAINS(p.geom, St_centroid(b.geom))</DIV>
<DIV><SPAN style="WHITE-SPACE: pre" class=Apple-tab-span></SPAN>GROUP BY</DIV>
<DIV><SPAN style="WHITE-SPACE: pre" class=Apple-tab-span></SPAN>p.bgri11) as
t</DIV>
<DIV>WHERE</DIV>
<DIV><SPAN style="WHITE-SPACE: pre"
class=Apple-tab-span></SPAN>ST_CONTAINS(g.geom, St_centroid(f.geom))</DIV>
<DIV><SPAN style="WHITE-SPACE: pre" class=Apple-tab-span></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></BODY></HTML>