[postgis-users] Help with query

[Obe, Regina]

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 at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Todd
Fagin
Sent: Tuesday, December 30, 2008 9:46 AM
To: postgis-users at 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.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20081230/dcfd3dbc/attachment.html>