Us Zip Code Lookup Table

admin

Active2 years, 3 months ago

I'm new to SQL and relational databases and I have what I would imagine is a common problem.

Find USPS ® Locations Near this Address; Get Shipping Prices for this Address. US ZIP codes are a type of postal code used within the United States to help the United States Postal Service (USPS) route mail more efficiently. Some still refer to ZIP codes as US postal codes. The term ZIP stands for Zone Improvement Plan.

I'm making a website and when each user submits a post they have to provide a location in either a zip code or a City/State.

What is the best practice for handling this? Do I simply create a Zip Code and City and State table and query against them or are there ready made solutions for handling this?

I'm using SQL Server 2005 if it makes a difference.

I need to be able to retrieve a zip code given a city/state or I need to be able to spit out the city state given a zip code.

anon

11 Answers

You have a couple options. You can buy a bulk zip-code library from somebody which will list zip codes, cities, counties, etc. by state, or you can pay someone to access a web service which will perform the same function on a more granular level.

Your best bet would be to go with the zip-code library option, as it'll cost you less than the web service and will provide better performance. How you query or pre-process this library is up to you. You mention SQL Server, so you'd probably want State, Zipcode, and City tables, and include the relevant relationships between them. You'll also need to have provisions for cities that span multiple zipcodes, or for zipcodes that have multiple cities - but none of these issues are insurmountable.

As far as dealing with the vagarities of user input, you may consider enlisting the help of an address validation web service, although most of them require a full shipping address in order to validate.

Edit: looks like there's a SourceForge project offering free zip-code data, including lat/lon data, etc. Not sure how correct or current it is.

Edit 2: After some cursory looking on that SourceForge project's site it looks like this is a dead project. If you use this data, you'll need to provide some allowance for zipcodes / cities that don't exist in your database. Purchased bulk libraries usually come with some sort of guarantee of updates, or a pricing plan for updates, etc., and are probably more reliable.

Erik ForbesErik Forbes
24.4k26 gold badges88 silver badges116 bronze badges

Have a ZipCode table that is related to a CityState table. Some zip codes have multiple cities associated with them, so you may need to have the interface let the user select from the city they want or let them override the default.

I use the paid service from ZipInfo.com since I needed additional information such as lat/long, zip type and county. Zip codes also change several times as year as new zip codes are added or merged with others, so you will need to update your data a few times a year to stay consistent.

DavGarciaDavGarcia
14.2k10 gold badges49 silver badges92 bronze badges

Depending on the details of what you're building, Yelp has a free neighborhoods API that may be able to get you what you need. Be sure to check their Terms of Use and stuff to make sure you stay in compliance.

I know this isn't a db centric answer, but what you're doing may not be best handled in the database itself.

Bramha GhoshZip code lookup table excelBramha Ghosh
4,7444 gold badges25 silver badges27 bronze badges

If the reason for the lookup is the user's convenience, here's an alternate approach that doesn't require licensing any third-party databases:

Just lookup the city/state from your existing name/address table, provided the zip code matches. If somebody has previously made an entry for that zip code, then you'll find the city and state from that entry. IF no previous entry exists, then worst case the user has to entry in the city and state.

This solution assumes your need is for convenience for the user. If you are more concerned about accurate validation of city, state, zip codes then you're better off licensing a verified database.

Us Zip Code Lookup Table Access

KlugeKluge
2,7302 gold badges19 silver badges20 bronze badges

Zip Code County Lookup Table

There are many free geo-coding webservices where you can get a zip from a city-state, and vice-versa. Take a look at the GeoNames webservice. You could do something like check your db, and then if what you are looking for is not there, grab it from the webservice and add it.

Muad'DibMuad'Dib
23.6k5 gold badges48 silver badges62 bronze badges

My understanding is that the USPS web API is free to use, but requires permission that depends on a number of factors, including the nature of the program that will be using it and the reason you need the data.

If you qualify to use it, this would presumably be the most accurate source for the information you need.

Ben DunlapBen Dunlap
1,5481 gold badge14 silver badges17 bronze badges

Not sure I understand the question. Do you need to allow either and, later, return both?

You'll have to be careful, even with a zip/city database that can be purchased, since some cities span multiple zips, so you can't always 'calculate' in that direction. Similar issue in the opposite direction.

alphadoggalphadogg
9,7396 gold badges41 silver badges72 bronze badges

There are libraries that you can buy and import that aren't that expensive. Your problem with them is going to be that you will have an ongoing maintenance cost with it (not a lot). Zip codes change all the time, meaning that in 6 mos or so, your data will be slightly out of date. You might want to look at interfacing with a service like google maps. Here we use a hybrid approach. We spend the amount of the money for updates to the data every 6 months, and if the zip code isn't in the table we verify it against google maps to make sure that information entered is still valid and it just hasn't been entered into our system. If it is valid, we update the system, if not we let the user know that a mistake was made.

Another option would be to ping the USPS website. I believe they have a city/state look up page by zipcode.

kemiller2002kemiller2002
98.6k24 gold badges179 silver badges235 bronze badges

For US States Tables, you can use following query, assuming you have StateId and StateName in the States table and you have StateId auto increment.

This query has 50 US States...

John Slegers
30.6k13 gold badges161 silver badges137 bronze badges
Muhammad BilalMuhammad Bilal

I got that beat. Here's a free one (zipped, csv):

Us Zip Code Lookup Tablet

Headers are zip,city,state,latitude,longitude,timezone,dst

Now, your potential loss is that you aren't paying for updates (which can bite you in the end, but they try to keep it somewhat updated)

user54650user54650
3,9192 gold badges20 silver badges27 bronze badges

I would like to add this website.

You can download the data for free if you use for Personal/Educational (as of 2014-03-20).

Prut UdomwattaweePrut Udomwattawee
  • No recurring fee lock-in. Update when you want.
  • We constantly monitor USPS for changes
  • We only issue updates for significant changes

Us Zip Code Lookup Table 2017

We constantly monitor USPS data for major changes and release new data when there are significant changes. If you can find any major changes before we make data updates, we'll issue a full refund.

Instead of locking our users into a recurring fee, we let you decide when updates are significant enough to warrant the time and cost of updating. Quite frankly, we don't offer weekly/monthly updates because new ZIP codes aren't frequently introduced. We also don't issue data updates for insignificant changes like updating a ZIP to show it has 3869 deliverable addresses instead of 3866.

Because it can take up to 5 years for people to fully utilize a ZIP, ZIP changes are minimized and a 10 year old data set would still contain 99% of currently active 5 digit ZIP codes. Even so, when evaluating how often you want to update your data, keep in mind that many pieces of information about a ZIP code can be updated over time. We recommend you update your list between once every year and once every 5 years depending on your data needs. We've included the number of new ZIP codes added each year to assist you in making your decision.

How often are new ZIP codes created? How many new ZIP codes are created every year?
It is typical for 10-20 new ZIP codes to be created each year.
How often often are zip codes changed? What changes are there for zip codes?
Most of the information that changes about a ZIP code is related to the ZIP+4 (the last 4 digits after the primary 5 digit ZIP code) or the number of deliverable addresses. Neither are included in the ZIP code database so updates can be less frequent. The demographics of a ZIP can change over time so we issue frequent updates for statistics.
Are zip codes ever removed? Are zip codes ever deleted from use?
Over 500 ZIP codes have been decomissioned as post offices close or the needs of the United States Postal Service change.