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
#1 by tre on May 23, 2011 - 1:58 am
awesome effort, thanks a lot
#2 by Girish on May 22, 2011 - 6:21 am
Thanks a lot. i really like it. Well organized. once again thanks.
#3 by visual77 on December 18, 2010 - 1:29 pm
The main reason why I used the locale format that I did was for daylight saving time. If I had set Arizona to GMT-7 and Nevada to GMT-8, that would’ve failed to account for DST. During summer, Nevada goes onto daylight saving and is GMT-7, but Arizona does not and remains GMT-7. Using America/Los_Angeles and America/Phoenix, and then running it through a tool to calculate time (like PHP’s date object) will accurately account for this.
#4 by Ryan on December 18, 2010 - 12:49 pm
After a quick look at the database it looks quite good, the only problem I see is the time zone should have really been put in as the time zone code itself, e.g. Gold Coast Australia should be +10.5 and Sao Paulo Brazil -3, etc…
Other than this it is appears to be a front runner for a list of countries and regions