[postgis-users] area resulting from ST_Within

[Paragon Corporation]

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: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] 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:lr at pcorp.us>  
	To: 'PostGIS Users Discussion'
<mailto:postgis-users at postgis.refractions.net>  
	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: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] 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:lehodey at gmail.com>  
		To: PostGIS Users Discussion
<mailto:postgis-users at postgis.refractions.net>  
		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
<storma-ipb at indo.net.id> 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
			postgis-users at postgis.refractions.net
	
http://postgis.refractions.net/mailman/listinfo/postgis-users
			


		________________________________

	
_______________________________________________
		postgis-users mailing list
		postgis-users at postgis.refractions.net
	
http://postgis.refractions.net/mailman/listinfo/postgis-users
		

	________________________________

		_______________________________________________
	postgis-users mailing list
	postgis-users at postgis.refractions.net
	http://postgis.refractions.net/mailman/listinfo/postgis-users