<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML xmlns="http://www.w3.org/TR/REC-html40" xmlns:o =
"urn:schemas-microsoft-com:office:office" xmlns:w =
"urn:schemas-microsoft-com:office:word" xmlns:st1 =
"urn:schemas-microsoft-com:office:smarttags"><HEAD>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.6000.16788" name=GENERATOR><o:SmartTagType
name="place"
namespaceuri="urn:schemas-microsoft-com:office:smarttags"></o:SmartTagType><o:SmartTagType
name="State"
namespaceuri="urn:schemas-microsoft-com:office:smarttags"></o:SmartTagType><o:SmartTagType
name="PlaceName"
namespaceuri="urn:schemas-microsoft-com:office:smarttags"></o:SmartTagType><o:SmartTagType
name="PlaceType"
namespaceuri="urn:schemas-microsoft-com:office:smarttags"></o:SmartTagType><!--[if !mso]>
<STYLE>st1\:* {
BEHAVIOR: url(#default#ieooui)
}
</STYLE>
<![endif]-->
<STYLE>@font-face {
font-family: Trebuchet MS;
}
@page Section1 {size: 8.5in 11.0in; margin: 1.0in 1.0in 1.0in 1.0in; }
P.MsoNormal {
FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman"
}
LI.MsoNormal {
FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman"
}
DIV.MsoNormal {
FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman"
}
H2 {
FONT-SIZE: 11pt; MARGIN: 6pt 0in 0pt; COLOR: navy; FONT-FAMILY: "Trebuchet MS"
}
A:link {
COLOR: blue; TEXT-DECORATION: underline
}
SPAN.MsoHyperlink {
COLOR: blue; TEXT-DECORATION: underline
}
A:visited {
COLOR: purple; TEXT-DECORATION: underline
}
SPAN.MsoHyperlinkFollowed {
COLOR: purple; TEXT-DECORATION: underline
}
SPAN.EmailStyle17 {
FONT-WEIGHT: normal; COLOR: windowtext; FONT-STYLE: normal; FONT-FAMILY: "Trebuchet MS"; TEXT-DECORATION: none; mso-style-type: personal-compose
}
DIV.Section1 {
page: Section1
}
</STYLE>
</HEAD>
<BODY lang=EN-US vLink=purple link=blue>
<DIV dir=ltr align=left><SPAN class=035485314-30122008><FONT face=Arial
color=#0000ff size=2>Try</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=035485314-30122008><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=035485314-30122008><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Trebuchet MS'">SELECT c2.name
As county, c1.name As city</SPAN></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=035485314-30122008><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Trebuchet MS'"></SPAN></SPAN><SPAN
class=035485314-30122008><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Trebuchet MS'">FROM OKCounties c2
LEFT JOIN OK_Cities c1 </SPAN></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=035485314-30122008><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Trebuchet MS'"></SPAN></SPAN><SPAN
class=035485314-30122008><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Trebuchet MS'"> ON
(st_within(c1.the_geom,c2.the_geom)= true AND c1.feature='County
Seat')</SPAN></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=035485314-30122008><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Trebuchet MS'">WHERE c1.Name IS
NULL</SPAN></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=035485314-30122008><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Trebuchet MS'">Order by c2.Name
asc;<o:p></o:p></SPAN></SPAN></DIV><BR>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B>
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <B>On Behalf Of </B>Todd
Fagin<BR><B>Sent:</B> Tuesday, December 30, 2008 9:46 AM<BR><B>To:</B>
postgis-users@postgis.refractions.net<BR><B>Subject:</B> [postgis-users] Help
with query<BR></FONT><BR></DIV>
<DIV></DIV>
<DIV class=Section1>
<P class=MsoNormal><FONT face="Trebuchet MS" size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Trebuchet MS'">Greetings,<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Trebuchet MS" size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Trebuchet MS'"><o:p> </o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Trebuchet MS" size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Trebuchet MS'">I am rather new to PostGIS
and somewhat new to SQL, so please bear with me.
<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Trebuchet MS" size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Trebuchet MS'"><o:p> </o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Trebuchet MS" size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Trebuchet MS'">I have a question about a
query. I have two spatial tables, OK_Cites and OKCounties. The
former is a list of various cities and towns in <st1:State
w:st="on">Oklahoma</st1:State> and the latter is all of the counties in
<st1:State w:st="on"><st1:place
w:st="on">Oklahoma</st1:place></st1:State>. While investigating the
OK_Cities table, I discovered only 75 cities are coded as county seats.
This is peculiar because there are 77 counties in <st1:State
w:st="on"><st1:place w:st="on">Oklahoma</st1:place></st1:State>, so there should
be 77 county seats. <o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Trebuchet MS" size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Trebuchet MS'"><o:p> </o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Trebuchet MS" size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Trebuchet MS'">I decided I would try to do
a query to figure out which two counties do NOT have an associated city with
attributed as a county seat. I know how to easily select all of the
counties which DO have a city attributed as county seat. For
example:<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Trebuchet MS" size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Trebuchet MS'"><o:p> </o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Trebuchet MS" size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Trebuchet MS'">select c1.name, c2.name
from OK_Cities c1, OKCounties c2 where st_within(c1.the_geom,c2.the_geom)='T'
AND c1.feature='County Seat' Order by c2.Name asc;<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Trebuchet MS" size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Trebuchet MS'"><o:p> </o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Trebuchet MS" size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Trebuchet MS'">This returns 75 rows, as
expected. Now, I want to find those counties in which a selected city is
NOT within. I have tried this a number of ways, but am obviously doing
something wrong. For instance, if I set st_within to ‘F’, I get a record
of all county seats that are not within a county (so, for <st1:place
w:st="on"><st1:PlaceType w:st="on">county</st1:PlaceType> <st1:PlaceName
w:st="on">X</st1:PlaceName></st1:place>, there are 74 county seats that are not
within it). I thought a subquery might work, but I am doing something
painfully wrong with the syntax.<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Trebuchet MS" size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Trebuchet MS'"><o:p> </o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Trebuchet MS" size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Trebuchet MS'">Any
suggestions?<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Trebuchet MS" size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Trebuchet MS'"><o:p> </o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Trebuchet MS" size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Trebuchet MS'"><o:p> </o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Trebuchet MS" color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Trebuchet MS'">Todd
Fagin</SPAN></FONT><FONT color=navy><SPAN
style="COLOR: navy"><o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Times New Roman" color=navy size=3><SPAN
style="FONT-SIZE: 12pt; COLOR: navy"> <o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Trebuchet MS" color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Trebuchet MS'">Coordinate
Solutions, Inc.</SPAN></FONT><FONT color=navy><SPAN
style="COLOR: navy"><o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Trebuchet MS" color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Trebuchet MS'">2804 NW 18th
St.</SPAN></FONT><FONT color=navy><SPAN
style="COLOR: navy"><o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Trebuchet MS" color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Trebuchet MS'">Oklahoma City,
OK 73107</SPAN></FONT><FONT color=navy><SPAN
style="COLOR: navy"><o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Trebuchet MS" color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Trebuchet MS'">405.740.4324
(voice)</SPAN></FONT><FONT color=navy><SPAN
style="COLOR: navy"><o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Trebuchet MS" color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Trebuchet MS'">904.471.5548
(fax)</SPAN></FONT><FONT color=navy><SPAN
style="COLOR: navy"><o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Trebuchet MS" color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Trebuchet MS'">www.coordinatesolutions.com<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Trebuchet MS" color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: 'Trebuchet MS'"><o:p> </o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"><o:p> </o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN
style="FONT-SIZE: 12pt"><o:p> </o:p></SPAN></FONT></P></DIV></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>