State County City Problem

Question

Post reply

State County City Problem

cdun2

SSCertifiable

Points: 7893
More actions
September 4, 2008 at 11:52 am

#197277

Im dealing with address data, and based on the schema I have, I can't query to answer the following question: 'What county(s) is Birmingham, AL in?'
Here is what I have:
State2 table:
StateID (Identity, primary key),
Name (full state name)
TwoLetterCode ('AL')
County2
CountyID (Identity, primary key),
Name (full county name),
StateID (from the State2 table)
City
CityID (Identity, primary key)
Name (full city name),
StateID (from the State2 table)
Foreign keys:
County2.StateID to State2.StateID
City.StateID to State2.StateID
It so happens that Birmingham, AL is in two counties (so cities can cross county lines). In our data, there is one CityID for each StateID. Does the countyID need to be in the City table?
What do I need to be able to determine what county(s) that a city resides in?
Thank you for your help!
CSDunn

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

You need one extra table called CityCounties

CityID

CountyID

That's easier said than done. Doing that work for the entire US is not a small task. I'm guessing you'll have to buy that data somewhere.

Gary Johnson-259336 SSCommitted Points: 1673 More actions · Answer 2

Been there, done that. Have fun! 🙂

Create Table dbo.Region

(

RegionID int identity(1,1) NOT NULL,

RegionName varchar(255) NOT NULL,

RegionType tinyint NOT NULL

)

Create Table dbo.RegionType

(

RegionTypeID tinyint NOT NULL,

RegionTypeDesc varchar(50) NOT NULL

)

Create Table dbo.RegionHierarchy

(

RegionIDParent int NOT NULL,

RegionIDChild int NOT NULL

)

INSERT INTO RegionType VALUES(1, 'State')

INSERT INTO RegionType VALUES(2, 'City')

INSERT INTO RegionType VALUES(3, 'County')

INSERT INTO RegionType VALUES(4, 'Zip')

You get the idea. I put records going both directions in the Hierarchy table.

You have to realize that Zipcodes can reside in multiple Counties, and Cities. Cities can reside in multiple States and Counties.

I also ended up creating separate City, State, and County tables as there were specific attributes for each of these Regions that I didn't put in my basic schema above. While this is in some ways duplicating the data, having a single RegionID for each region has been very benificial.

Gary Johnson
Sr Database Engineer

ms8588 Grasshopper Points: 13 More actions · Answer 3

Gary provides a simple and effective solution. He also states that he also has added additional tables to handle unique entity data. There may be a couple more issues you may want to think about. First, I am not sure what you actually need this structure for. How you intend to use it may dictate some of your solution. Consider what is unique here. Only zip codes. Yes States are also unique but their names are not. They may also be used as county and city/town names. Counties are similar. They are unique to a state but Counties with the same name are found in multiple states. There names also show up as cities/towns. City names will show un in many states and on rare occassions can show up more than once in the same state. If I have not given you enough to think about Louisanna does not have counties. They have parishes so you may want/need an identifier for that, just something else to think about. Instead of using an identify column (I assume generated) as the primary key, you might consider using something like a geocode or Dunn&Bradstreet identifier as your primary key to guaranty uniqueness. In the long run this may be simpler. You can then relate them hierarchically, rationally or both. 🙂

GSquared SSC Guru Points: 260824 More actions · Answer 4

Another thing to keep in mind, in a system like this, is that Texarkana (for example) is one city in two states. They have a street called, appropriately, "State Line".

I get a flat-file of city, state, county, Zip, information from http://www.zip-codes.com. It's cheap and does for what I need. You might want to denormalize that way.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Rautakallio Grasshopper Points: 13 More actions · Answer 5

There are a few cities and towns in the US that are not in any county. The county fabric has holes. If I recall correctly the county fabric in Virginia is like this. One or two other states may be like this too.

BTW if you're dealing with a street address it's going to be in one county or another. But one side of a street could be in one county and the opposite side in another.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 6

Another thing to be aware of is that there are some zip codes that have addresses in two states.

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 7

Hiearchy can be a good thing... but I don't believe the op can change the structure of the tables. 😉

CS... Please pardon me if I'm wrong, but this looks a whole lot like a homework problem. You'd probably get a better response if you posted what you've tried, so far.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

peerless Ten Centuries Points: 1114 More actions · Answer 8

do you have a table structure for

coutries

states/provinces

cities

peerless Ten Centuries Points: 1114 More actions · Answer 9

do you have a table structure for

coutries

states/provinces

cities

gischimp Old Hand Points: 363 More actions · Answer 10

Greetings. I would recommend you investigate how the US Census Bureau handles geography. In fact, they already have a comprehensive coding for State/County/Place for the entire US, including areas not covered by all levels of geography - like Native American land, islands, etc. A good place to start is their Guide to State and Local Geography:

http://www.census.gov/geo/reference/geoguide.html

You can find links to hierarchical diagrams, reference materials and other useful documents. You may find the article on ANSI codes of use:

http://www.census.gov/geo/reference/ansi.html

There is even a download page if you want to import there geographical codes:

http://geonames.usgs.gov/domestic/download_data.htm

Working with a major municipal government which happens to fall within five separate counties, we run into these issues a lot.

I would also caution you about using ZIP codes, as they are not really geographic areas but rather collections of postal carrier routes. In fact, the Census Bureau disclaims this on their page dealing with postal codes. US Postal Service builds their ZIP routes solely on business needs and cross city, county, even state lines with a single ZIP code.

The DBA mantra should always be "know your data, know your data" so understanding these relationships is important before you make business decisions based upon them. ZIP codes were meant to facilitate mail delivery - that is all. For other types of business, particularly Public Safety (which we support) you need something different.

I hope that this is useful to you.

Good luck.

Cheers.

Sean Lange SSC Guru Points: 286590 More actions · Answer 11

Please note: this thread started in 2008.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

talgreg Grasshopper Points: 14 More actions · Answer 12

The census web site has raw data available for download. The data for each state will have one master "Geo" file for geography info with a primary key called LOGRECNO and related data files with a forign key of the same name. Look for a column called Summary level or Sumlev. The Summary level for Counties is identified as 050, places (cities) is 160 etc. The County and place columns wil give you the County or Place or identifer, sometimes referred to as the FIPS Code. The "Name" column wil give you it's name. You can query the geo file to get your information.

see the document below. Use the summary levels and other Geo identifying fields\columns will filter data accordingly. It is important to understand the table structures they use.

http://www.census.gov/acs/www/Downloads/handbooks/ACSGeneralHandbook.pdf

Maybe this will help.

andre.southworth Grasshopper Points: 20 More actions · Answer 13

Great points in extending the data by everyone.

If we're working with just the tables in the question, you could replace the StateID column in the City table with a CountyID column so that the structure is:

State2 table:

StateID (Identity, primary key),

Name (full state name)

TwoLetterCode ('AL')

County2

CountyID (Identity, primary key),

Name (full county name),

StateID (from the State2 table)

City

CityID (Identity, primary key)

Name (full city name),

CountyID (from the County2 table, primary key)

Foreign keys:

County2.StateID to State2.StateID

County2.CountyID to City.CountyID

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 14

Please note: 7 year old thread.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass