<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.2900.2180" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffff>
<DIV><FONT face=Arial size=2>Dear PC,</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>After summing up percentage of all
districts in one watershed, it does not add up to 100%. Moreover, after
multiplication with 100, the total percentage of all districts in one
watershed is far below 100%.</FONT></DIV>
<DIV><FONT face=Arial size=2> </FONT></DIV>
<DIV><FONT face=Arial size=2>In the first watershed, I have 9 different
districts. The first district calculated with the term<FONT
face="Times New Roman" size=3>:
sum(ST_area(ST_intersection(ws.the_geom,dt.the_geom))) </FONT>gives 25 sq. km,
meanwhile the watershed area itself is 646 sq. km. Therefore, the percentage of
this district should be (25/646)* 100 = 3.9 %. </FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Using the proposed term tocalculate percentage:
<FONT size=3><FONT face="Times New Roman">(</FONT><FONT
face="Times New Roman">sum(ST_area(ST_intersection(ws.the_geom,dt.the_geom)))<SPAN
class=371554021-21092008>/sum(st_area(ws.the_geom)))*100 gives only 0.18 %
instead. <FONT face=Arial size=2>I do not know what is
wrong.</FONT></SPAN></FONT></FONT></FONT></DIV>
<DIV><FONT face=Arial size=2><FONT size=3><FONT face="Times New Roman"><SPAN
class=371554021-21092008><FONT face=Arial
size=2></FONT></SPAN></FONT></FONT></FONT> </DIV>
<DIV><FONT face=Arial size=2><FONT size=3><FONT face="Times New Roman"><SPAN
class=371554021-21092008><FONT face=Arial size=2>best
regards,</FONT></SPAN></FONT></FONT></FONT></DIV>
<DIV><FONT face=Arial size=2><FONT size=3><FONT face="Times New Roman"><SPAN
class=371554021-21092008></SPAN></FONT></FONT></FONT> </DIV>
<DIV><FONT face=Arial size=2><FONT size=3><FONT face="Times New Roman"><SPAN
class=371554021-21092008><FONT face=Arial
size=2>surya</FONT> </SPAN></FONT></FONT></FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2> </FONT>----- Original Message ----- </DIV>
<BLOCKQUOTE
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV
style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black"><B>From:</B>
<A title=lr@pcorp.us href="mailto:lr@pcorp.us">Paragon Corporation</A> </DIV>
<DIV style="FONT: 10pt arial"><B>To:</B> <A
title=postgis-users@postgis.refractions.net
href="mailto:postgis-users@postgis.refractions.net">'PostGIS Users
Discussion'</A> </DIV>
<DIV style="FONT: 10pt arial"><B>Sent:</B> Monday, September 22, 2008 4:44
AM</DIV>
<DIV style="FONT: 10pt arial"><B>Subject:</B> RE: [postgis-users] area
resulting from ST_Within</DIV>
<DIV><BR></DIV>
<DIV dir=ltr align=left><SPAN class=371554021-21092008><FONT face=Arial
color=#0000ff size=2>Try</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=371554021-21092008><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=371554021-21092008><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV>SELECT <A href="http://ws.name/">ws.name</A>,<A
href="http://dt.name/">dt.name</A>,
sum(ST_area(ST_intersection(ws.the_geom,dt.the_geom)))<SPAN
class=371554021-21092008>/sum(st_area(ws.the_geom)) As pct</SPAN></DIV>
<DIV>FROM watersheds as ws, districts as dt</DIV>
<DIV>WHERE ST_intersects(ws.the_geom,dt.the_geom)</DIV>
<DIV>GROUP BY <A href="http://ws.name/">ws.name</A>,<A
href="http://dt.name/">dt.name</A></DIV>
<DIV>ORDER BY <A href="http://dt.name/">dt.name</A></DIV><BR>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B> <A
href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</A>
[mailto:postgis-users-bounces@postgis.refractions.net] <B>On Behalf Of
</B>surya<BR><B>Sent:</B> Sunday, September 21, 2008 12:42 PM<BR><B>To:</B>
PostGIS Users Discussion<BR><B>Subject:</B> Re: [postgis-users] area resulting
from ST_Within<BR></FONT><BR></DIV>
<DIV></DIV>
<DIV><FONT face=Arial size=2>Dear Fred,</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Thank you very much. It works correctly !!. I try
to further convert the area of each district in a watershed as
percentage to the watershed area in question. Please give me more
help.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>best regards,</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>surya</FONT></DIV>
<BLOCKQUOTE
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV style="FONT: 10pt arial">----- Original Message ----- </DIV>
<DIV
style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black"><B>From:</B>
<A title=lehodey@gmail.com href="mailto:lehodey@gmail.com">Fred Lehodey</A>
</DIV>
<DIV style="FONT: 10pt arial"><B>To:</B> <A
title=postgis-users@postgis.refractions.net
href="mailto:postgis-users@postgis.refractions.net">PostGIS Users
Discussion</A> </DIV>
<DIV style="FONT: 10pt arial"><B>Sent:</B> Sunday, September 21, 2008 4:35
PM</DIV>
<DIV style="FONT: 10pt arial"><B>Subject:</B> Re: [postgis-users] area
resulting from ST_Within</DIV>
<DIV><BR></DIV>
<DIV dir=ltr>
<DIV>Hi Surya,</DIV>
<DIV>you can try something like that:</DIV>
<DIV> </DIV>
<DIV>SELECT <A href="http://ws.name">ws.name</A>,<A
href="http://dt.name">dt.name</A>,
sum(ST_area(ST_intersection(ws.the_geom,dt.the_geom)))</DIV>
<DIV>FROM watersheds as ws, districts as dt</DIV>
<DIV>WHERE ST_intersects(ws.the_geom,dt.the_geom)</DIV>
<DIV>GROUP BY <A href="http://ws.name">ws.name</A>,<A
href="http://dt.name">dt.name</A></DIV>
<DIV>ORDER BY <A href="http://dt.name">dt.name</A></DIV>
<DIV> </DIV>
<DIV>Fred.</DIV>
<DIV> </DIV>
<DIV><BR><BR> </DIV>
<DIV class=gmail_quote>On Sun, Sep 21, 2008 at 3:37 AM, surya <SPAN
dir=ltr><<A
href="mailto:storma-ipb@indo.net.id">storma-ipb@indo.net.id</A>></SPAN>
wrote:<BR>
<BLOCKQUOTE class=gmail_quote
style="PADDING-LEFT: 1ex; MARGIN: 0px 0px 0px 0.8ex; BORDER-LEFT: #ccc 1px solid">Dear
List,<BR><BR>I have two tables, one table contains 5 polygons
depicting 5 watersheds or river basin boundaries. Another table contain
district bondaries. Using ST-contain function below, I have obtained
districts belong to a certain watershed . My qestion is how to calculate
area of each district contained in a certain watershed using sum(area)(),
taking into consideration that one district might be splitted in two or
more different watershed<BR><BR>SELECT <A href="http://watersheds.name/"
target=_blank>watersheds.name</A>, <A href="http://districts.name/"
target=_blank>districts.name</A><BR>FROM watersheds, districts<BR>WHERE
ST_contains (watersheds.the_geom, districts.the_geom)<BR>GROUP BY <A
href="http://watersheds.name/" target=_blank>watersheds.name</A>, <A
href="http://districts.name/" target=_blank>districts.name</A>
;<BR><BR>best
regards,<BR><BR>surya<BR><BR>_______________________________________________<BR>postgis-users
mailing list<BR><A href="mailto:postgis-users@postgis.refractions.net"
target=_blank>postgis-users@postgis.refractions.net</A><BR><A
href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target=_blank>http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR></BLOCKQUOTE></DIV><BR></DIV>
<P>
<HR>
<P></P>_______________________________________________<BR>postgis-users
mailing
list<BR>postgis-users@postgis.refractions.net<BR>http://postgis.refractions.net/mailman/listinfo/postgis-users<BR></BLOCKQUOTE>
<P>
<HR>
<P></P>_______________________________________________<BR>postgis-users
mailing
list<BR>postgis-users@postgis.refractions.net<BR>http://postgis.refractions.net/mailman/listinfo/postgis-users<BR></BLOCKQUOTE></BODY></HTML>