'RE: [postgis-users] Help with query'

[prev in list] [next in list] [prev in thread] [next in thread] 

List: postgis-users
Subject: RE: [postgis-users] Help with query
From: "Obe, Regina" <robe.dnd () cityofboston ! gov>
Date: 2008-12-30 14:57:34
Message-ID: 53F9CF533E1AA14EA1F8C5C08ABC08D2053B438E () ZDND ! DND ! boston ! cob
[Download RAW message or body]

--===============0043994443==
Content-class: urn:content-classes:message
Content-Type: multipart/alternative;
	boundary="----_=_NextPart_001_01C96A8E.F314555F"

This is a multi-part message in MIME format.

Try

SELECT  c2.name  As county, c1.name As city
FROM OKCounties c2  LEFT JOIN OK_Cities c1 
    ON  (st_within(c1.the_geom,c2.the_geom)= true AND c1.feature='County
Seat')
WHERE c1.Name IS NULL
Order by c2.Name asc;

________________________________

From: postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] On Behalf Of Todd
Fagin
Sent: Tuesday, December 30, 2008 9:46 AM
To: postgis-users@postgis.refractions.net
Subject: [postgis-users] Help with query

Greetings,

I am rather new to PostGIS and somewhat new to SQL, so please bear with
me.  

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
Oklahoma and the latter is all of the counties in Oklahoma.  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
Oklahoma, so there should be 77 county seats. 

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:

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;

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 county X, 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.

Any suggestions?

Todd Fagin

Coordinate Solutions, Inc.

2804 NW 18th St.

Oklahoma City, OK 73107

405.740.4324 (voice)

904.471.5548 (fax)

www.coordinatesolutions.com

-----------------------------------------
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.
[Attachment #3 (text/html)]

<!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>&nbsp;</DIV>
<DIV dir=ltr align=left><SPAN class=035485314-30122008><SPAN 
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Trebuchet MS'">SELECT&nbsp;&nbsp;c2.name&nbsp; 
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&nbsp; 
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'">&nbsp;&nbsp;&nbsp; ON 
&nbsp;(st_within(c1.the_geom,c2.the_geom)= true&nbsp;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>&nbsp;</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.&nbsp; 
<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>&nbsp;</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.&nbsp; I have two spatial tables, OK_Cites and OKCounties.&nbsp; 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>.&nbsp; While investigating the 
OK_Cities table, I discovered only 75 cities are coded as county seats.&nbsp; 
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>&nbsp;</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.&nbsp; I know how to easily select all of the 
counties which DO have a city attributed as county seat.&nbsp; 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>&nbsp;</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>&nbsp;</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.&nbsp; Now, I want to find those counties in which a selected city \
is  NOT within.&nbsp; I have tried this a number of ways, but am obviously doing 
something wrong.&nbsp; For instance, if I set st_within to &#8216;F&#8217;, 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).&nbsp; 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>&nbsp;</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>&nbsp;</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>&nbsp;</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">&nbsp;<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>&nbsp;</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>&nbsp;</o:p></SPAN></FONT></P> <P \
class=MsoNormal><FONT face="Times New Roman" size=3><SPAN  style="FONT-SIZE: \
12pt"><o:p>&nbsp;</o:p></SPAN></FONT></P></DIV></BODY></HTML>

<HTML><BODY><hr size=1>

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. </BODY></HTML>

<hr size=1>
 Help make the earth a greener place. If at \
all possible resist printing this email and join us in saving paper. \

_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

--===============0043994443==--

[prev in list] [next in list] [prev in thread] [next in thread] 