[postgis-users] Help with spatial query

[Obe, Regina]

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-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
storma-ipb at indo.net.id
Sent: Wednesday, January 07, 2009 4:55 AM
To: postgis-users at 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 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.