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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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" <[EMAIL PROTECTED]> To: "'PostGIS Users Discussion'" <[email protected]> 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: [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.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:[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.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: [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 > _______________________________________________ postgis-users mailing list [email protected] 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. _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
