April 15, 2007 at 6:03 pm
As I was reminded the other day, in the United State, "ZipCode" (5 digit) and State can not be used as a composite primary key. I was told that up to 5 cities should share the same Zip and State. I was also told that the Zip+5 will insure the uniqueness I want but that we don't collect it. Maybe I need to consider "ZipCode, State, and City" for a composite primary key. I'm totally in the dark on this one.
Can you point me to a URL that would enlighten me on this subject?
TIA,
Bill
April 16, 2007 at 6:19 am
I don't have any references on the subject, but I do have experience
As far as the relationship between cities and zip codes it is a Many to Many relationship. I don't know if there is a maximum permitted valid city names for a zip code, but I have seen some with 5-6 valid city names. I suspect this is a practical consideration more than anything.
I live in the suburbs of Chicago. There are a few towns near me that have petitioned the post office for their own zip code (due to negative associations about the main city in their zip code that usually affects insurance rates and other things set by zip code).
When the post office has denied these requests, it usually indicated the town in question was too small for a unique post office and that zip codes were designed to facilitate delivery of mail, not to identify towns.
I look at this akin to the Social Security number, who's purpose is for social security benifits, but we know how it gets used.
Try using http://zip4.usps.com/zip4/welcome.jsp to look up zip codes / city associations
Zip Code 60452 returns only one valid city name: Oak Forest, IL
Zip code 60445 returns:
Actual City name in 60445
MIDLOTHIAN, IL
Acceptable City names in 60445
CRESTWOOD, IL
This is correct. The main town in this zip code is Midlothian. However, Crestwood is a smaller adjacent town with the same zip code.
At my last job about 6 years ago, I spent about 4 months cleaning up addresses in one of our databases by making sure everything in their had a valid 5 digit zip code (compared against a 5 digit zip code table I downloaded from the internet). The table I found was incomplete and in some cases, had invalid information.
I started by comparing the state names in our address fields with the state associated with the zip code. That kicked out a lot of problems, usually something that had been loaded into the database from a souce that had been in a spreadsheet (which deleted leading zeros off zip codes)
I then worked through comparing for valid zip codes, checking to see if in invalid zip code was actually valid and updating my table. I then compared city names, kicking out wrong names, misspellings, and incorrect abbreviations.
In the process, I learned a bit about how zip codes were laid out (and places where changes had been made that weren't reflected in our database).
As for your idea of storing a Zip plus 4, I will tell you there is a 1 to 1 relationship between the zip plus 4 and city/state. However, there isn't a 1 to 1 relationship between zip plus 4 and deliverable addresses.
If you search on this address:
15953 LeClaire Ave, in zip code 60452, you will actually find 6 deliverable addresses (apartments 1A, 1B, 2A, 2B, 3A, 3B). If the address includes the apartment number, it gets one zip plus 4. If you don't, it gets a different zip plus 4 (and they are not consecutive).
I would say that you need to store the complete street address, complete city name, state, and zip code. In this day and age, the storage required for this isn't significant.
April 16, 2007 at 6:23 am
http://www.usps.com/ncsc/addressinfo/addressinfomenu.htm?from=zclsearch&page=ais
This link will provide some information including info on the USPS web service. The primary key issue will depend on what you are using this data for. We have been down this road stemming from a locality taxing issue. Some zips may contain several local taxing jurisdictions. The zip4 information will help with mailing but will still not guarantee organization of taxing jurisdictions. We subscribe to a service that provides us with Geocode (Geospatial Entity Object Code) based on ranges of the zip-zip4 combinations. For a lighter organization I would suggest using the City-Zip-Zip4 combination.
MISfIT
April 16, 2007 at 9:22 am
You don't say what attributes you want to store in the table that would require duplicate records and cause a primary key violation. You are already aware that you can't just use Zip5 because some zipcodes cross state boundaries, such as 02891, 03852, 20135, 25701, 29693, 37317, 38257, 38334, 38852, 42223, 57078, 57638, 58015, 58053, 59221, 71749, 99128, and 99362. And you know that there are multiple cities in many zipcodes. But a Zip5, City, State primary key could be unique if you are only storing attributes of cities.
If you are creating an address table, you won't get a natural unique primary key even if it is specified with zip+4 and delivery point. The only natural key is the entire address record. Save yourself a lot of grief and use an int identity primary key. You could still create an index on zip5, possibly even a clustered index if it fits your design and would have performance benefits.
April 16, 2007 at 9:23 am
While this might not help with your initial question for guidance I do have some experience in the area of dealing with ZIP Codes. I have been in a position where I needed to report data by ZIP Code, by Distance (in miles) from a ZIP Code, and by geopolitcal association (based on address ZIP Code) and since this was government data being reported it needed to be as acurate as we could make it. I found the following product: http://www.zipinfo.com/products/products.htm extremely helpful. Their information was very complete and accurate and they offer a quarterly subscription that keeps the information up-to-date (ZIP Codes are added, deleted and moved around a lot more often then you would think). They also provided a lot of good reference material. While they get the data from the US Post Office they pull information together from many sources and link it for you.
HTH,
James.
April 16, 2007 at 5:18 pm
Thanks to everyone for your great replies!
Bill
May 22, 2007 at 9:08 pm
Before you use a Zipcode a key, consider using the FIPS code
http://www.census.gov/geo/www/fips/fips.html
http://www.census.gov/geo/www/tiger/zip1999.html
Census stopped using them as a "key" becuse they change too much.
See here
http://www.census.gov/popest/geographic/codes02.html
For my project, which I only needed a state and county key and lookup, it solved the problem I was having with zipcodes.
See census' Geographic Areas Reference Manual
May 24, 2007 at 8:48 am
A geographic location can have many ZIP Codes, as New York City has many, many ZIP codes assigned. There are even buildings (like the old WTC buildings, and some manufacturing facilities - G.E. comes to mind) that can even have one or more ZIP codes assigned to a single building.
A ZIP Code can also be assigned to multiple geographic locations. For instance, "08640" is assigned to both "Trenton, NJ" and "Fort Dix, NJ".
Census uses a designation of their own design, called the ZIP Code Tabulation Area, which is similar to a ZIP Code, but is better defined geographically. Unless you're using Census Bureau data, however, the ZCTA probably won't do you much better than ZIP Codes.
May 24, 2007 at 1:24 pm
That is why I suggest the FPIS code, over the ZCTA code.
The FPIS code for a place is used by the U.S. Board on Geographic Names (http://geonames.usgs.gov/index.html) and can be quiryed on-line or download for free.
Note the FPIS code is being replaced by a GNISID, which will never change.
May 24, 2007 at 2:55 pm
I am totally ignorant of FIPS codes. How are they used in practice? For example, the post office can deliver a letter to an address in zipcode 91607 (North Hollywood, CA) but can the post office deliver an address that uses a FIPS code?
I found this on the Internet:
037=Los Angeles (8,863,164)
So it looks like 06-037 is the FIPS code of Los Angeles county. But it does not appear to be a replacement for a ZIPCode.
TIA,
Barkingdog
May 25, 2007 at 7:30 am
They are not a replacement for ZIP Codes. FIPS (Federal Information Processing Standards) are US Government standards for specific types of data and data processing. Codes from this standard (http://www.itl.nist.gov/fipspubs/index.htm) along with ISO (International Standards Organization) (http://www.iso.org/iso/en/prods-services/popstds/popstdsindex.html) are often used for consistancy between systems.
May 25, 2007 at 9:38 am
As I understand it, FIPS is a direct relationship to a county - state, and covers all of the states and possessions of the USA. I am under the impression the FIPS will not change, whereas ZIP does.
Without more specific details of your goal (data presented by the table) its difficult to offer much more insight. If you are trying to create a PK for a specific location (address, etc), use LAT/LONG. Then you can feed that data into a Geocoding system to extract (reverse lookup) the address. If you are "generalizing" the location data instead, then i would consider something like FIPS (or its replacement).
BTW; thanks to everyone for the various links on FIPS, etc. I got my initial data out of an FCC file on wireless markets.
Beer's Law: Absolutum obsoletum
"if it works it's out-of-date"
May 25, 2007 at 11:00 am
FIPS are Federal Information Processing Standards. FIPS State codes a two-digit state code, county codes are 3 digits. There are also MSA (metropolitan statistical area) codes used by the Census Bureau to designate geographic areas around large cities, etc. Here's more information on FIPS: http://www.itl.nist.gov/fipspubs/by-num.htm
And you can find more info on MSA's, etc. at http://tiger.census.gov.
ZIP Codes are handy if you're delivering mail, but are not the best thing to use for defining geopolitical boundaries.
May 25, 2007 at 3:54 pm
Barkingdog, what are you trying to accomplish? Typically, a FIPS or Zip code is an attribute of an address. Zip is useful if you're delivering mail, FIPS if you care in what county the address sits.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply