Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

State County City Problem Expand / Collapse
Author
Message
Posted Thursday, September 04, 2008 11:52 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, January 24, 2014 12:43 PM
Points: 477, Visits: 276
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






Post #564044
Posted Thursday, September 04, 2008 12:07 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, January 16, 2014 12:03 PM
Points: 21,376, Visits: 9,594
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.
Post #564059
Posted Thursday, September 04, 2008 2:26 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, October 10, 2013 4:59 PM
Points: 79, Visits: 183
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
Post #564171
Posted Friday, September 05, 2008 10:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 30, 2009 10:03 AM
Points: 1, Visits: 4
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. :)
Post #564743
Posted Friday, September 05, 2008 10:50 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:34 PM
Points: 15,442, Visits: 9,588
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 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
Post #564756
Posted Friday, September 05, 2008 4:56 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 18, 2008 10:56 AM
Points: 1, Visits: 4
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.
Post #564944
Posted Friday, September 05, 2008 7:27 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 1:11 PM
Points: 3,081, Visits: 11,230
Another thing to be aware of is that there are some zip codes that have addresses in two states.


Post #564965
Posted Friday, September 05, 2008 8:25 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:03 PM
Points: 35,954, Visits: 30,243
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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #564970
Posted Friday, February 04, 2011 2:25 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, February 03, 2014 11:36 AM
Points: 68, Visits: 112
do you have a table structure for

coutries

states/provinces

cities
Post #1059036
Posted Friday, February 04, 2011 2:26 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, February 03, 2014 11:36 AM
Points: 68, Visits: 112
do you have a table structure for

coutries

states/provinces

cities
Post #1059037
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse