14

Lately we've started getting issues with outdated countries / regions list being presented to users of our web-application.

We currently have a few DB tables to store localized country names along with their regions (states). However as the planet goes, that list is in constant evolution and it's proving to be a pain to maintain as some regions are deleted, some merged - existing data needs to be updated all the time.

What are, if any exist, the best practices when it come to dealing with multi-locale countries/regions list?

Is there a place or a standard in place? I know of ISO 3166, but their list isn't exactly DB friendly ... plus it's not fully localized.

An ideal solution would simply allow us to "sync" to it? Preferably in multiple language. The solution would preferably be free or subscription based with an historic of what changed so we could update our data (aka tblAddress)

Thanks!

jfrobishow
  • 2,897
  • 2
  • 27
  • 42
  • 1
    This has always done my nut in. Always wanted a deep XML or SQL dump of world->continent->country->region->city->province and so on, with meta data. – Aiden Bell Jun 24 '09 at 20:19

9 Answers9

14

geonames is pretty accurate in this respect, and they update regularly.

http://www.geonames.org/export/

Jason
  • 2,035
  • 11
  • 13
7

There is no such thing. This is a political issue, which you can only solve in the context of your own application. Deciding to use ISO 3166 could be the easiest to defend. I know of issues with at least:

  • China/Taiwan
  • Israel/Palestine
  • China/Tibet
  • Greece/Macedonia
Stephan Eggermont
  • 15,847
  • 1
  • 38
  • 65
  • Not sure why this gets negative scores. – Stephan Eggermont Jul 02 '09 at 23:21
  • 1
    Me neither; btw, Croatia/Slovenia – Rook Jul 03 '09 at 19:07
  • Concerning the last two comments before this one: Bavaria is a state in Germany, no country by its own. Since 1991 Croatia and Slovenia are two independent countries. --- The original comment pointed to political issues concerning international acceptance of the mentioned nations in their own right, or territorial disputes, which is correct. Adding the last two entries to the above list would not be correct, as I would not know a single nation objecting the situation. – Kariem Jul 04 '09 at 15:20
  • Germany/Bavaria was a joke. Maybe only obvious for Germans. Or Bavarians. – Nikolai Ruhe Jul 05 '09 at 12:15
  • Nope, obvious also for the Dutch :) – Stephan Eggermont Jul 05 '09 at 12:42
  • All I know of Bavaria is that they brew good beer :) So does Germany though. – jfrobishow Jul 06 '09 at 02:51
  • I understood it as a joke too. But then I saw the comment about Croatia and Slovenia, so I just had to correct that - might have not been necessary, sorry. – Kariem Jul 06 '09 at 21:26
3

The ISO lists here are DB friendly, though they only include short names and codes.

Jacob Mattison
  • 50,258
  • 9
  • 107
  • 126
  • Thanks, that link allowed me to find this: http://www.iso.org/iso/publications_and_e-products/databases.htm#PUB100050 which is an Access db you can order containing ISO 3166-1 (country) code and ISO 3166-2 (region) code. However it's not ideal as it's not automated (you have to buy each version) nor does it allow you to keep track of what changed to update existing data. Plus it's appears to only be available in english and french. Good find anyway +1 – jfrobishow Jun 24 '09 at 20:47
  • That link is now dead. – gregoltsov Mar 27 '14 at 17:48
2

This one looks very good: Multiple languages, update option, database independent file format for import, countries/regions/cities information, and some other features you might use or not.

And it's quite affordable if you need it for only one server.

MicSim
  • 26,265
  • 16
  • 90
  • 133
  • Agreed - I will look more into it. This is promising, sure their are political issues but at some point you have to call the country something... – jfrobishow Jul 03 '09 at 20:00
2

You can try CLDR

http://cldr.unicode.org/

This set of data is maintained by the Unicode organization. It is updated regularly and the data is versioned so it is easy for you to manage the state of your list.

Clint
  • 1,014
  • 1
  • 10
  • 15
2

Hy! you can find a free dump of all countries with their respective continents https://gist.github.com/kamermans/1441495, its much easy to use.just download the dump & upload in your data base.

zohaib
  • 1
  • 1
1

Well, wait, do you just want an up-to-date list of countries? Or do you need to know that Country X has split into Country Y and Country Z? Because I'm not aware of any automated way to get the latter. Even updates to the ISO databases are distributed as PDFs (you're on your own for implementing the change)

Eli
  • 5,500
  • 1
  • 29
  • 27
  • Well updates such as X as split into Y/Z would be the best - as it would allow update to existing data. How do you typically deal with "orphaned" address once a country disapear/merge? or more importantly a state, which seems to happen more often. – jfrobishow Jul 02 '09 at 11:36
1

The EU maintains data about Local Administrative Units (LAUs) which can be downloaded as hierarchical XLS files in several languages.

devio
  • 36,858
  • 7
  • 80
  • 143
0

United Nations Statistics Division, Standard country or area codes for statistical use (M49).

Look for "Search and Download: Full View" on page left. That leads here.

Groups countries by continent, sub-continental region, Least Developed Countries, and so on.

If you cannot import the excel version, note that the csv has unquoted" fields and a comma in one country name that will bust your import ("Bonaire, Sint Eustatius and Saba"). Perhaps open it first in LibreOffice or whatever, fix the broken country name and shunt its other right-most columns back into place. Then set all cells to type Text, saveAs csv with [Edit Filter Settings] checked [x] in the saveAs dialog, and make sure string delimiter is set to ", as it should be by default.

markling
  • 1,232
  • 1
  • 15
  • 28