[postgis-users] Help with spatial query

[Obe, Regina]

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: postgis-users-bounces at postgis.refractions.net 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" <robe.dnd at cityofboston.gov>
To: "PostGIS Users Discussion" <postgis-users at postgis.refractions.net>
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: 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.
_______________________________________________
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


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20090108/954fac78/attachment.html>