Last night I needed a list of all US cities for a custom web app I was building. The requirements were very simple: to get a list or database of the city name, the state name, and the state abbreviation. I looked far and wide but all the solutions I found were not free. Amounts ranged from a couple of dollars to hundreds for a well-maintained up-to-date list.
Since this wasn’t anything mission-critical, I continued to look for a free US city database until I stumbled across the Zip Code Database Project. It’s hosted on SourceForge so it has to be open source. The data is slightly outdated and uses 2000 US census data, but I figured in 10 year’s time there wouldn’t be that many cities incorporated or dissolved in the US plus it didn’t need to be super accurate.
Though it may seem contrary to the name, it actually lists every US city along with its zip code information if you happen to need it. It’s a very simple no-frills list that gets you exactly the information that you need in the universally accepted format of CSV or comma separated value. The information can easily be written into queries to put it into whatever database format that needs to be used. If you also need the geographic longitude and latitude coordinates for displaying on a map, it’s also got you covered. With such a great solution so readily available, I really have no idea why all those list-sellers even have business. I guess you can chalk it all up to marketing. Hmm… a little massaging of this list to just city and state names and you may have yourself a nice little product to resell to people who don’t know better…
Before you decide to go with this list, I’ve given a little preview of the data so you know what you’re getting into before you decide to download that 2MB (extracted) file commitment on your hard drive.
“zip code”, “state abbreviation”, “latitude”, “longitude”, “city”, “state”
“35004″, “AL”, ” 33.606379″, ” -86.50249″, “Moody”, “Alabama”
“35005″, “AL”, ” 33.592585″, ” -86.95969″, “Adamsville”, “Alabama”
“35006″, “AL”, ” 33.451714″, ” -87.23957″, “Adger”, “Alabama”
“35007″, “AL”, ” 33.232422″, ” -86.80871″, “Alabaster”, “Alabama”
“35010″, “AL”, ” 32.903432″, ” -85.92669″, “Alexander City”, “Alabama”
“35014″, “AL”, ” 33.355960″, ” -86.27720″, “Alpine”, “Alabama”
“35016″, “AL”, ” 34.323715″, ” -86.49278″, “Arab”, “Alabama”
“35019″, “AL”, ” 34.292540″, ” -86.63505″, “Baileyton”, “Alabama”
“35020″, “AL”, ” 33.405559″, ” -86.95141″, “Bessemer”, “Alabama”
“35022″, “AL”, ” 33.346817″, ” -86.95252″, “Bessemer”, “Alabama”
“35023″, “AL”, ” 33.443039″, ” -87.01930″, “Bessemer”, “Alabama”
“35031″, “AL”, ” 34.111425″, ” -86.53380″, “Blountsville”, “Alabama”
As you’ll notice, the same city appears more than once and this happens throughout. That’s because this is a zip code oriented list so you may need to do a little housekeeping to prevent duplicates from being created. Personally I’m going to parse everything into a dynamic 2-D array with state as the first key and city as the second to automatically prevent duplicates.
// if city not exist, dynamically create // otherwise, overwrite existing value // end result: no duplicates $myarray[$state][$city] = 0;