[postgis-users] area resulting from ST_Within

[surya]

Dear Regina,

Watershed table has only one name per record. The first SQL that you 
proposed works very well.  However, the second SQL  is also very interesting 
to understand for future analysis. Is there any guideline how to assemble 
such complicated but useful  SQL.

best regards,

surya

----- Original Message ----- 
From: "Paragon Corporation" <lr at pcorp.us>
To: "'PostGIS Users Discussion'" <postgis-users at postgis.refractions.net>
Sent: Monday, September 22, 2008 11:07 AM
Subject: RE: [postgis-users] area resulting from ST_Within


> 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
>
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>