<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:st1="urn:schemas-microsoft-com:office:smarttags" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=Content-Type content="text/html; charset=us-ascii">
<meta name=Generator content="Microsoft Word 11 (filtered medium)">
<o:SmartTagType namespaceuri="urn:schemas-microsoft-com:office:smarttags"
name="place"/>
<o:SmartTagType namespaceuri="urn:schemas-microsoft-com:office:smarttags"
name="State"/>
<o:SmartTagType namespaceuri="urn:schemas-microsoft-com:office:smarttags"
name="PlaceName"/>
<o:SmartTagType namespaceuri="urn:schemas-microsoft-com:office:smarttags"
name="PlaceType"/>
<!--[if !mso]>
<style>
st1\:*{behavior:url(#default#ieooui) }
</style>
<![endif]-->
<style>
<!--
/* Font Definitions */
@font-face
{font-family:"Trebuchet MS";
panose-1:2 11 6 3 2 2 2 2 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman";}
h2
{margin-top:6.0pt;
margin-right:0in;
margin-bottom:0in;
margin-left:0in;
margin-bottom:.0001pt;
page-break-after:avoid;
font-size:11.0pt;
font-family:"Trebuchet MS";
color:navy;}
a:link, span.MsoHyperlink
{color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{color:purple;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:"Trebuchet MS";
color:windowtext;
font-weight:normal;
font-style:normal;
text-decoration:none none;}
@page Section1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.Section1
{page:Section1;}
-->
</style>
</head>
<body lang=EN-US link=blue vlink=purple>
<div class=Section1>
<p class=MsoNormal><font size=2 face="Trebuchet MS"><span style='font-size:
10.0pt;font-family:"Trebuchet MS"'>Greetings,<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face="Trebuchet MS"><span style='font-size:
10.0pt;font-family:"Trebuchet MS"'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face="Trebuchet MS"><span style='font-size:
10.0pt;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 size=2 face="Trebuchet MS"><span style='font-size:
10.0pt;font-family:"Trebuchet MS"'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face="Trebuchet MS"><span style='font-size:
10.0pt;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 size=2 face="Trebuchet MS"><span style='font-size:
10.0pt;font-family:"Trebuchet MS"'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face="Trebuchet MS"><span style='font-size:
10.0pt;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 size=2 face="Trebuchet MS"><span style='font-size:
10.0pt;font-family:"Trebuchet MS"'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face="Trebuchet MS"><span style='font-size:
10.0pt;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 size=2 face="Trebuchet MS"><span style='font-size:
10.0pt;font-family:"Trebuchet MS"'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face="Trebuchet MS"><span style='font-size:
10.0pt;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 size=2 face="Trebuchet MS"><span style='font-size:
10.0pt;font-family:"Trebuchet MS"'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face="Trebuchet MS"><span style='font-size:
10.0pt;font-family:"Trebuchet MS"'>Any suggestions?<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face="Trebuchet MS"><span style='font-size:
10.0pt;font-family:"Trebuchet MS"'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face="Trebuchet MS"><span style='font-size:
10.0pt;font-family:"Trebuchet MS"'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face="Trebuchet MS"><span
style='font-size:10.0pt;font-family:"Trebuchet MS";color:navy'>Todd Fagin</span></font><font
color=navy><span style='color:navy'><o:p></o:p></span></font></p>
<p class=MsoNormal><font size=3 color=navy face="Times New Roman"><span
style='font-size:12.0pt;color:navy'> <o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face="Trebuchet MS"><span
style='font-size:10.0pt;font-family:"Trebuchet MS";color:navy'>Coordinate
Solutions, Inc.</span></font><font color=navy><span style='color:navy'><o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face="Trebuchet MS"><span
style='font-size:10.0pt;font-family:"Trebuchet MS";color:navy'>2804 NW 18th St.</span></font><font
color=navy><span style='color:navy'><o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face="Trebuchet MS"><span
style='font-size:10.0pt;font-family:"Trebuchet MS";color:navy'>Oklahoma City,
OK 73107</span></font><font color=navy><span style='color:navy'><o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face="Trebuchet MS"><span
style='font-size:10.0pt;font-family:"Trebuchet MS";color:navy'>405.740.4324
(voice)</span></font><font color=navy><span style='color:navy'><o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face="Trebuchet MS"><span
style='font-size:10.0pt;font-family:"Trebuchet MS";color:navy'>904.471.5548
(fax)</span></font><font color=navy><span style='color:navy'><o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face="Trebuchet MS"><span
style='font-size:10.0pt;font-family:"Trebuchet MS";color:navy'>www.coordinatesolutions.com<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face="Trebuchet MS"><span
style='font-size:10.0pt;font-family:"Trebuchet MS";color:navy'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'><o:p> </o:p></span></font></p>
</div>
</body>
</html>