12

I have a data set with zipcodes and would like to do a bulk upload to a tool that will give me the cities each zip is in (US only.)

Improve this question
1

2 Answers 2

Reset to default
11

There is no simple answer to this question, because ZIP codes do not represent geographical areas. They represent postal delivery routes, which are sometimes simply a bank of PO boxes in a specific post office, and are sometimes an organization like a University which has its own internal mail processing services.

Therefore, not all ZIP Codes can truly be located in a city, and even those for which a geography can be reasonably defined, they are not necessarily located within a single city.

Probably the closest approximation you could get would be to start with the Census Bureau's ZIP code tabulation area (ZCTA) gazetteer file, which can be downloaded here. Then, using as many of the state-based "place" shape files as you need, load those into a GIS tool. Personally, I'd use PostGIS, but you might also be able to use QGIS, or if you can get your hands on a copy, ArcGIS (commercial software).

You could then:

  1. look up the ZCTA for a given ZIP code
  2. extract the INTPTLAT and INTPTLONG values, which are the lat/long for the "centroid" of the ZCTA
  3. use the GIS tool to identify the place geography which contains the ZCTA's centroid

You will probably have some ZIP codes which aren't in the ZCTA dataset, and it's not really precise, but it's probably good enough for a lot of cases.

Improve this answer
3
  • is right but perhaps more technically accurate than is necessary (though not at all erroneous). Just seems like a little more than the original poster was looking for.
    – Jeffrey
    Commented Jul 24, 2013 at 22:37
  • Also keep in mind that the city names you see in the postal data do not correspond with municipal borders. Some municipalities within the zip code do not show up at all in the post office city names, and some of the city names in the postal records don't correspond at all with a municipality.Here's a lookup tool provided by the USPS. tools.usps.com/go/ZipLookupAction_input
    – Bob Gradeck
    Commented Jul 25, 2013 at 19:32
  • 1
    Here is a useful blog post giving more detailed information about why matching zips to geographic area boundaries can be problematic. The post is about electoral districts, but issues are similar for city boundaries: sunlightfoundation.com/blog/2012/01/19/dont-use-zipcodes
    – skyebend
    Commented Aug 8, 2013 at 18:21
10

There has been some mention about this being a complex problem. It's not hard at all. In fact, matching a zipcode to the corresponding cities is super simple. Granted, trying to find an accurate shapefile that corresponds to a given zipcode, that is a more challenging issue. But simply matching zipcode to city/cities... very easy. The USPS data has a Preferred city for each zipcode (which means the city where the delivering post office is located) and then a list of acceptable city names as well as a list of unacceptable city names. This list is readily available and updated monthly from the US Postal Service. So, even someone with Excel skills (including JOIN or HLOOKUP) could do the matching on a list of zipcodes.

SmartyStreets has an API that you can use anytime. Try out the demo here. If you've got a list you need to process, send it to me ([email protected]) and I'm happy to do it for you.

[I work at SmartyStreets, that's why I have inside information about it and can offer our services.]

Improve this answer
4
  • 4
    Thanks for the answer @Jeffrey and make sure that you put in a disclaimer when pointing to services or sites in which you are involved (e.g. "I work for company XYZ").
    – Jeanne Holm
    Commented Jul 25, 2013 at 11:11
  • Your answer is great. I have a list of 30k US city/state/zip codes in excel. My problem is that I have another list of zip codes (about 2000) and I need to sort the city/states according to that list. I need to only send out to the smaller zip list. Is that something SmartySheets can do? Can it do it in bulk? All the tools I have seen only allow one zip code at a time. Thanks
    – Tess
    Commented Nov 8, 2015 at 21:18
  • @tess Give me a call at SmartyStreets and I can help you. 877.216.8883
    – Jeffrey
    Commented Nov 11, 2015 at 16:52
  • As far as I can tell, the USPS data doesn't qualify as "open data," however. As best I can tell, one must purchase the data from the Postal Service. Feel free to correct me if that's wrong.
    – Joe Germuska
    Commented Jan 10, 2018 at 16:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.