Archive for July, 2010

MySQL dump of every country and state in the world

As part of a recent project, I needed to compile a list of every country and first level administrative subdivision in the world. I started with the ISO 3166-2 list, but later cleaned up certain countries that had inaccurate data. I also needed timezones for every state, so I put all of those in as well.

This zip file contains a single SQL dump for two tables – region and subregion.

Region contains 248 entries, with the following data for each country: ISO code, 3 digit ISO code, fips code, country name, continent, currency code, currency name, phone prefix, postal code regex, languages and geonameid. Subregions contains region ID, name and timezone. The timezone format is “America/Los_Angeles”, “Europe/Madrid”, etc. Unfortunately, some states have multiple timezones and this is not taken into account.

There might still be some issues with some countries not having an accurate state list, but this list is more comprehensive than anything I was able to find online.

Download the list: region.sql