Slight correction, for the second since you are only comparing things you know intersect, there is no need for a case statement so can be more efficiently written as.
SELECT d.district_name, w.ws_name ST_Area(ST_Intersection(w.the_geom,d.the_geom))/ST_Area(d.the_geom) As per_distinws FROM districts As d INNER JOIN water_sheds As w ON ST_Intersects(w.the_geom,d.the_geom) WHERE w.ws_name = 'Sussex' -----Original Message----- From: [email protected] on behalf of surya Sent: Wed 1/7/2009 10:07 PM To: PostGIS Users Discussion Subject: Re: [postgis-users] Help with spatial query Dear Regina, Thank you so much for the quick response. best regards, surya ----- Original Message ----- From: "Obe, Regina" <[email protected]> To: "PostGIS Users Discussion" <[email protected]> Sent: Thursday, January 08, 2009 2:22 AM Subject: RE: [postgis-users] Help with spatial query Surya, Not sure if these answer your question 1) If you want to list all 500 districts for a given watershed regardless of if any of it lies in watershed, then you would do SELECT d.district_name, w.ws_name, CASE WHEN ST_Intersects(w.the_geom, d.the_geom) THEN ST_Area(ST_Intersection(w.the_geom,d.the_geom))/ST_Area(d.the_geom) ELSE 0 END As per_distinws FROM districts As d CROSS JOIN water_sheds As w WHERE w.ws_name = 'Sussex' 2) If you only care about the districts that fully or partly full in shed then SELECT d.district_name, w.ws_name CASE WHEN ST_Intersects(w.the_geom, d.the_geom) THEN ST_Area(ST_Intersection(w.the_geom,d.the_geom))/ST_Area(d.the_geom) ELSE 0 END As per_distinws FROM districts As d INNER JOIN water_sheds As w ON ST_Intersects(w.the_geom, d.the_geom) WHERE w.ws_name = 'Sussex' Hope that helps, Regina -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of [email protected] Sent: Wednesday, January 07, 2009 4:55 AM To: [email protected] Subject: [postgis-users] Help with spatial query Dear List, Since the (planned) Postgis book is not yet available, I still have to ask List for the following simple SQL. I have two tables: 1) One table consist of 150 watershed polygons 2) Second table consista of 500 district polygons. Some of the districts are completely whithin one certain watershed (100%), but some has intersection with more than one watershed. How I can write SQL to know percentage of district intersection area with certain watershed (i.e. fraction of intersected district area divided by area of one watershed). As an output I will have 500 rows consiting name of district and percentage of its area in certain watershed. best regards, surya ___________________________________________________________ indomail - Your everyday mail - http://indomail.indo.net.id _______________________________________________ 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 _______________________________________________ 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
