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: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of storma-...@indo.net.id Sent: Wednesday, January 07, 2009 4:55 AM To: postgis-users@postgis.refractions.net 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 postgis-users@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. _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users