[postgis-users] area resulting from ST_Within

[Obe, Regina]

I've always liked the Celko line of SQL for Smarties books. They are a
bit dated but nevertheless good.  There is a bit of a bias on Oracle
syntax, but over all I think they are fairly Ansi compliant SQL.

His earlier works have been the best but lately he has gotten a bit to
pedantic and annoying in other ways for my liking.

http://www.celko.com/books.htm

Thanks,
Regina



 

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
surya
Sent: Monday, September 22, 2008 8:45 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] area resulting from ST_Within

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_ge
om)
> 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.t
he_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.th
e_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
> 

_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.