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_geom)))*100 gives only 0.18 % instead. I do not know what is wrong. best regards, surya ----- Original Message ----- From: Paragon Corporation To: 'PostGIS Users Discussion' Sent: Monday, September 22, 2008 4:44 AM Subject: RE: [postgis-users] area resulting from ST_Within Try SELECT ws.name,dt.name, sum(ST_area(ST_intersection(ws.the_geom,dt.the_geom)))/sum(st_area(ws.the_geom)) As pct 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 ------------------------------------------------------------------------------ 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 To: PostGIS Users Discussion 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, districts.name FROM watersheds, districts WHERE ST_contains (watersheds.the_geom, districts.the_geom) GROUP BY watersheds.name, 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
