[postgis-users] Help with query

[Todd Fagin]

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

 

 

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20081230/9b3304f8/attachment.html>