Data constraints in US state/county/city/zip database?
I have a database of US zip codes and their corresponding states, cities and counties. It was supplied as a flat file and I'm trying to normalize the data and figure out exactly which entities depend on which others.
One problem I've come across is that some cities seem to exist in more than one county. I was under the impression that in the US, there is a hierarchy of State -> County -> City -> Zip.
However, this data seems to show otherwise for some cities:
Is my data set incorrect or is this actually a feature of US geography?
No, there isn't a clean hierarchy like that.
You're also liable to find cities that straddle state borders (cities in two states), and ZIP codes that take in more than one city. Not long ago, there were ZIP codes that straddled state borders, too. (ZIP codes are more about the route followed to deliver mail than about geography.) There might still be some.
As far as I know, no county is split between two states. But if there happened to be one, it wouldn't surprise me.
Depending on your application, you might discover even weirder things. I used to have to deal with addresses in the mountains that were "in" one county geographically, but were "in" a second county for emergency services (fire, police), and "in" yet a third county for non-emergency services (water, sewer, garbage collection). It depended on where the address was in relation to mountain ridges and roads.
- Model.create is not a function - strapi
- select multiple cells inner join one table
- Unable to copy or move file from databases folder with RNFS in react native
- Android Unit Tests Requiring Context
- Delphi FireDAC with MS Access 2010 database. Why does it convert ACE to Jet?
- database - flattened out vs. normalized
- Extract dirname from full path stored in database
- How to find a string inside a entire database?
- Implementing result paging in hibernate (getting total number of rows)
- Options to enforce a logical constraint in an Oracle database
- How can I tell a database to only return data in discrete portions?
- Where is the boundary for creating a table with types in the database?
- How to sort numbers before letters with linguistic sorting?
- How to implement database engine independent paging?
- How to simulate lost update in MySQL?
- Looking for fast ways to process 2 billion rows of data
- Does a SELECT query always return rows in the same order? Table with clustered index
- Delphi XE5 FireDAC Error: Cannot load vendor library [libmysql.dll or libmysqld.dll]
- Can an executable file display a website by precising the url? Can I run a specific website on my own .exe file?
- No start database manager command was issued. SQLSTATE=57019
- How to visualize database tables in postgresql using pgAdmin?
- Django Filter __In Preserve Order
- PostgreSQL - query all tables' all table columns
- Which database should I use for my Flutter application?
- Unique key with NULLs
- Is better use an empty value as a '' or as NULL?
- How to show the result when I click the submit button? (Like View history)
- Split a string with no delimiters into columns
- Django complex partitioning & ordering for tree Table
- Laravel Database Insert Error in artisan tinker