<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META NAME="Generator" CONTENT="MS Exchange Server version 6.5.7653.38">
<TITLE>RE: [postgis-users] Help with spatial query</TITLE>
</HEAD>
<BODY>
<!-- Converted from text/plain format -->
<P><FONT SIZE=2>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.<BR>
<BR>
<BR>
SELECT d.district_name, w.ws_name<BR>
ST_Area(ST_Intersection(w.the_geom,d.the_geom))/ST_Area(d.the_geom) As per_distinws<BR>
FROM districts As d<BR>
INNER JOIN water_sheds As w ON ST_Intersects(w.the_geom,d.the_geom)<BR>
WHERE w.ws_name = 'Sussex'<BR>
<BR>
<BR>
-----Original Message-----<BR>
From: postgis-users-bounces@postgis.refractions.net on behalf of surya<BR>
Sent: Wed 1/7/2009 10:07 PM<BR>
To: PostGIS Users Discussion<BR>
Subject: Re: [postgis-users] Help with spatial query<BR>
<BR>
Dear Regina,<BR>
<BR>
Thank you so much for the quick response.<BR>
<BR>
best regards,<BR>
<BR>
surya<BR>
<BR>
<BR>
----- Original Message -----<BR>
From: "Obe, Regina" <robe.dnd@cityofboston.gov><BR>
To: "PostGIS Users Discussion" <postgis-users@postgis.refractions.net><BR>
Sent: Thursday, January 08, 2009 2:22 AM<BR>
Subject: RE: [postgis-users] Help with spatial query<BR>
<BR>
<BR>
<BR>
Surya,<BR>
<BR>
Not sure if these answer your question<BR>
<BR>
1) If you want to list all 500 districts for a given watershed<BR>
regardless of if any of it lies in watershed, then you would do<BR>
<BR>
SELECT d.district_name, w.ws_name,<BR>
CASE WHEN ST_Intersects(w.the_geom, d.the_geom)<BR>
THEN<BR>
ST_Area(ST_Intersection(w.the_geom,d.the_geom))/ST_Area(d.the_geom)<BR>
ELSE 0 END As per_distinws<BR>
FROM districts As d CROSS JOIN water_sheds As w<BR>
WHERE w.ws_name = 'Sussex'<BR>
<BR>
2) If you only care about the districts that fully or partly full in<BR>
shed then<BR>
<BR>
SELECT d.district_name, w.ws_name<BR>
CASE WHEN ST_Intersects(w.the_geom, d.the_geom)<BR>
THEN<BR>
ST_Area(ST_Intersection(w.the_geom,d.the_geom))/ST_Area(d.the_geom)<BR>
ELSE 0 END As per_distinws<BR>
FROM districts As d<BR>
INNER JOIN water_sheds As w ON ST_Intersects(w.the_geom,<BR>
d.the_geom)<BR>
WHERE w.ws_name = 'Sussex'<BR>
<BR>
Hope that helps,<BR>
Regina<BR>
<BR>
-----Original Message-----<BR>
From: postgis-users-bounces@postgis.refractions.net<BR>
[<A HREF="mailto:postgis-users-bounces@postgis.refractions.net">mailto:postgis-users-bounces@postgis.refractions.net</A>] On Behalf Of<BR>
storma-ipb@indo.net.id<BR>
Sent: Wednesday, January 07, 2009 4:55 AM<BR>
To: postgis-users@postgis.refractions.net<BR>
Subject: [postgis-users] Help with spatial query<BR>
<BR>
Dear List,<BR>
<BR>
Since the (planned) Postgis book is not yet available, I still<BR>
have to ask List for the following simple SQL.<BR>
I have two tables:<BR>
<BR>
1) One table consist of 150 watershed polygons<BR>
2) Second table consista of 500 district polygons.<BR>
<BR>
Some of the districts are completely whithin one certain<BR>
watershed (100%), but some has intersection with more than one<BR>
watershed. How I can write SQL to know percentage of district<BR>
intersection area with certain watershed (i.e. fraction of<BR>
intersected district area divided by area of one watershed). As<BR>
an output I will have 500 rows consiting name of district and<BR>
percentage of its area in certain watershed.<BR>
best regards,<BR>
<BR>
surya<BR>
<BR>
<BR>
<BR>
___________________________________________________________<BR>
indomail - Your everyday mail - <A HREF="http://indomail.indo.net.id">http://indomail.indo.net.id</A><BR>
<BR>
<BR>
_______________________________________________<BR>
postgis-users mailing list<BR>
postgis-users@postgis.refractions.net<BR>
<A HREF="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>
-----------------------------------------<BR>
The substance of this message, including any attachments, may be<BR>
confidential, legally privileged and/or exempt from disclosure<BR>
pursuant to Massachusetts law. It is intended<BR>
solely for the addressee. If you received this in error, please<BR>
contact the sender and delete the material from any computer.<BR>
_______________________________________________<BR>
postgis-users mailing list<BR>
postgis-users@postgis.refractions.net<BR>
<A HREF="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>
<BR>
_______________________________________________<BR>
postgis-users mailing list<BR>
postgis-users@postgis.refractions.net<BR>
<A HREF="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>
<BR>
</FONT>
</P>
</BODY>
</HTML>
<HTML><BODY><P><hr size=1></P>
<P><STRONG>
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.
</STRONG></P></BODY></HTML>
<P><hr size=1></P>
<P><STRONG><font size="2" color="339900"> Help make the earth a greener place. If at all possible resist printing this email and join us in saving paper. </p> <p> </font></STRONG></P>