Surya,
Sorry about that. I realize now I misspoke.
If your watersheds are 1 per record, then the answer should be as simple
as
SELECT ws.name,dt.name ,
sum(ST_area(ST_intersection(ws.the_geom,dt.the_geom)))/st_area(ws.the_geom)
As pct
FROM watersheds as ws INNER JOIN districts as dt
ON ST_intersects(ws.the_geom,dt.the_geom)
GROUP BY ws.name , dt.name , ST_area(ws.the_geom)
ORDER BY dt.name
If they are multiple water shed records with same name and you need to add
them up, then answer is a bit more complicated and probably slower but
more
generic solution would be
SELECT ws.name,dt.name ,
sum(ST_area(ST_intersection(ws.the_geom,dt.the_geom)))/wa.thearea As pct
FROM watersheds as ws INNER JOIN (SELECT ST_Sum(st_area(the_geom)) As
thearea, name FROM watersheds GROUP BY name) As wa ON wa.name = ws.name
INNER JOIN districts as dt ON
ST_intersects(ws.the_geom,dt.the_geom)
GROUP BY ws.name , wa.name, dt.name , wa.thearea
ORDER BY dt.name
Hope that helps,
Regina
________________________________
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of surya
Sent: Sunday, September 21, 2008 11:35 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] area resulting from ST_Within
Dear PC,
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%.
In the first watershed, I have 9 different districts. The first district
calculated with the term:
sum(ST_area(ST_intersection(ws.the_geom,dt.the_geom))) 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 %.
Using the proposed term tocalculate percentage:
(sum(ST_area(ST_intersection(ws.the_geom,dt.the_geom)))/sum(st_area(ws.the_g
eom)))*100 gives only 0.18 % instead. I do not know what is wrong.
best regards,
surya
----- Original Message -----
From: Paragon Corporation <mailto:[EMAIL PROTECTED]>
To: 'PostGIS Users Discussion'
<mailto:[email protected]>
Sent: Monday, September 22, 2008 4:44 AM
Subject: RE: [postgis-users] area resulting from ST_Within
Try
SELECT ws.name <http://ws.name/> ,dt.name <http://dt.name/> ,
sum(ST_area(ST_intersection(ws.the_geom,dt.the_geom)))/sum(st_area(ws.the_ge
om)) As pct
FROM watersheds as ws, districts as dt
WHERE ST_intersects(ws.the_geom,dt.the_geom)
GROUP BY ws.name <http://ws.name/> ,dt.name <http://dt.name/>
ORDER BY dt.name <http://dt.name/>
________________________________
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of surya
Sent: Sunday, September 21, 2008 12:42 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] area resulting from ST_Within
Dear Fred,
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.
best regards,
surya
----- Original Message -----
From: Fred Lehodey <mailto:[EMAIL PROTECTED]>
To: PostGIS Users Discussion
<mailto:[email protected]>
Sent: Sunday, September 21, 2008 4:35 PM
Subject: Re: [postgis-users] area resulting from ST_Within
Hi Surya,
you can try something like that:
SELECT ws.name,dt.name,
sum(ST_area(ST_intersection(ws.the_geom,dt.the_geom)))
FROM watersheds as ws, districts as dt
WHERE ST_intersects(ws.the_geom,dt.the_geom)
GROUP BY ws.name,dt.name
ORDER BY dt.name
Fred.
On Sun, Sep 21, 2008 at 3:37 AM, surya
<[EMAIL PROTECTED]> wrote:
Dear List,
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
SELECT watersheds.name <http://watersheds.name/> ,
districts.name <http://districts.name/>
FROM watersheds, districts
WHERE ST_contains (watersheds.the_geom,
districts.the_geom)
GROUP BY watersheds.name <http://watersheds.name/> ,
districts.name <http://districts.name/> ;
best regards,
surya
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
________________________________
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
________________________________
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
