8 ms·

World `countries` – database table for MySQL

131 points by pericd 14y ago

jph 14y ago
Similar kinds of data are in my GitHub repo, in case it helps people here. https://github.com/sixarm/sixarm_data_geolocation https://github.com/sixarm/sixarm_data_geolocation Suggestions welcome.
randomdrake 14y ago
Cool work. I thought this was going to be a static .SQL file or something from the title. I was pleasantly surprised to find that it's actually a program[1] to dynamically generate a countries table based on current data. In addition: it's possible to define the columns you want to grab like name of the country, population, languages spoken, and so on. [1] - http://peric.github.com/GetCountries/ http://peric.github.com/GetCountries/
- pericd 14y ago
  I was thinking about that, and maybe I'll do it. I'm also thinking about selecting between SQL or XML code.
mxfh 14y ago
Apart from the geometry the dBase tables of the Shapefiles from http://www.naturalearthdata.com/ http://www.naturalearthdata.com/ are also exceptionally useful if imported elsewhere.
SirPalmerston 14y ago
I actually faced a similar problem and I ended up writing an almost identical script which generated a JSON or XML file (depending on the user's need).
deleted 14y ago
[deleted]
stevoski 14y ago
Nice work...but this is not a perfect database of countries. * Territories such as Réunion are listed as countries * Countries that ceased to exist are not listed (Yugoslavia, Soviet Union) although the world's most recent new country (South Sudan) is listed. * Kosovo is listed, although not recognised by all the world. Transnistria is not listed, even though it has de facto sovereignty. * Total land area is a disputable amount in several circumstances. There are several more such examples I could list. Yes I'm being pedantic. But I have a special interest in this area, and I'm aware of just how hard it is. People are born in one country (Soviet Union) and die in another (Ukraine), even though they never moved from their home city (Odessa).
- mjn 14y ago
  This is a problem even in professional data sources. I recently did an admittedly somewhat silly analysis, to see what happens if you take the "voting with your feet" metaphor for emigration seriously, and interpret each country's emigrants as a ranked-ballot "vote" for most appealing destination country, with all the ballots tallied using a Condorcet ranking. Results: http://www.kmjn.org/notes/voting_with_feet.html http://www.kmjn.org/notes/voting_with_feet.html I used bilateral migration data from the World Bank to do it: http://data.worldbank.org/data-catalog/global-bilateral-migration-database http://data.worldbank.org/data-catalog/global-bilateral-migr... I thought this might produce complexities I'd have to deal with, since over the 50 year period this data set covers, countries have come and gone a number of times. But to my surprise, the data set somehow uses only current countries, even when giving data points for 1960 or 1970. It doesn't really explain how that happened, either. Did they somehow impute past data to current countries? What does the 1970 figure for Germany, or Serbia, actually mean? Something was too complex and didn't fit the database, so it got swept under the rug? Made my job writing this off-the-cuff blog post much easier, but now I'm left wondering what the data I used actually means, despite it coming from a highly respected source, and used in many academic papers. edit: Found a paper describing the construction of the World Bank data. It looks like they use what they describe as an admittedly crude method of attributing based on population shares, so e.g. immigrants to "Yugoslavia" are apportioned to Croatia/Serbia/etc. based on population share, assuming the immigration was uniform throughout. Not horrible as a first approximation, probably, though clearly not "right" either. Source: http://papers.ssrn.com/sol3/papers.cfm?abstract_id=969242 http://papers.ssrn.com/sol3/papers.cfm?abstract_id=969242
gllen 14y ago
Isn't it easy enough to just delete columns you don't need after importing one of the public sources?
- freework 14y ago
  ...or just leave the data in. Having a few extra columns on a 250 row table isn't going to hurt anything.
- HyprMusic 14y ago
  But that adds an extra step for the developer, plus this could possibly be bundled in to an installation script.
dfc 14y ago
Another time saver: TimezoneDB [1] TimeZoneDB provides free time zone database for cities of the world. The database is licensed under Creative Commons Attribution 3.0 License. It contains countries name, time zones, abbreviation, GMT offset, and Daylight Saving Time (DST). The data is available in CSV and SQL format. You can download and implement into your projects for free. [1] http://timezonedb.com/download http://timezonedb.com/download
- jlgreco 14y ago
  The tz database that this is sourced for is one of the most impressive compilations of information that I have ever seen. Timezones seem like they should be simple, but the reality is very impressive: http://upload.wikimedia.org/wikipedia/commons/9/9f/Tz_map_world2009r_efeledotnet.png http://upload.wikimedia.org/wikipedia/commons/9/9f/Tz_map_wo...
lathamcity 14y ago
Hmm, I see some countries on here that weren't on the Sporcle map.
adaml_623 14y ago
Some countries have more than one currency code, China has CNY and CNH. http://en.wikipedia.org/wiki/Renminbi#CNH http://en.wikipedia.org/wiki/Renminbi#CNH. I'm not going to debate the fact that CNH isn't official even though it is traded.
- justincormack 14y ago
  When you trade it you trade CNY with a note saying please settle in Hong Kong, you cannot use the CNH symbol...
einhverfr 14y ago
Thanks for this. I think it sounds worthwhile to move my own country table work in PostgreSQL to an extension so everyone else can use it. Thanks for the inspiration/motivation!
thadk 14y ago
It would likely also be useful to have the local language name for the countries as an optional column. You can probably source this from the http://naturalearthdata.com http://naturalearthdata.com source mentioned elsewhere here.
stuartjmoore 14y ago
Is there a single source for data like this? Lists countries, currencies, timezones, dictionary words. A Wiki for massive amounts of data that is basically common knowledge.
- apaprocki 14y ago
  Yes, Unicode CLDR[1] provides all of this data and more in XML format. [1] http://cldr.unicode.org/ http://cldr.unicode.org/
gulbrandr 14y ago
If it may help, here is a list of world countries in CSV, XML and JSON formats. https://github.com/mledoze/countries https://github.com/mledoze/countries
tomd3v 14y ago
Very nice job. That would be great if there were such thing for cities and nationalities.
ocharles 14y ago
Primary key on idCountry, no unique constraints on name/code/isoAlpha3, and 'default' blank values? It's certainly a start, but it's also a bit questionable.
jpswade 14y ago
See ISO 3166... http://www.iso.org/iso/home/standards/country_codes.htm http://www.iso.org/iso/home/standards/country_codes.htm
bergie 14y ago
Geonames has a bunch of freely-available location datasets. I've used it in various apps for cities, countries, postcodes, etc: http://www.geonames.org/export/ http://www.geonames.org/export/
llcoolv 14y ago
Only two comments: 1. It is Czech Republic, not Czechia. At least in English. 2. Having the mulitple spoken languages in a single column is against normalization a bit. If one would have to use those values, they would need to parse the csv, etc.
speleding 14y ago
If you want an "official" (i.e. kept up to date by the UN) machine readable list of countries then this is a better source: http://www.unicode.org/cldr/data/charts/summary/root.html http://www.unicode.org/cldr/data/charts/summary/root.html This list also has currencies and some other stuff, but a Ruby script to extract what you want should not take more than 10 lines. This list has the added advantage of being available in pretty much all the languages in the world, in case you need localisation for your project. Side note: if you want to check if a list is up to date, check for "Aruba" and country code BQ (Caribbean). They changed the status of independent country a year ago so many lists don't have it yet (used to be a Dutch colony).
briteside 14y ago
Another source, with SQL, XML, JSON, etc.: http://data.brighterplanet.com/countries http://data.brighterplanet.com/countries and a lot more like it at: http://data.brighterplanet.com http://data.brighterplanet.com

131 points by pericd 14y ago

jph 14y ago

Similar kinds of data are in my GitHub repo, in case it helps people here. https://github.com/sixarm/sixarm_data_geolocation https://github.com/sixarm/sixarm_data_geolocation Suggestions welcome.

randomdrake 14y ago

Cool work. I thought this was going to be a static .SQL file or something from the title. I was pleasantly surprised to find that it's actually a program[1] to dynamically generate a countries table based on current data. In addition: it's possible to define the columns you want to grab like name of the country, population, languages spoken, and so on. [1] - http://peric.github.com/GetCountries/ http://peric.github.com/GetCountries/

pericd 14y ago
I was thinking about that, and maybe I'll do it. I'm also thinking about selecting between SQL or XML code.

mxfh 14y ago

Apart from the geometry the dBase tables of the Shapefiles from http://www.naturalearthdata.com/ http://www.naturalearthdata.com/ are also exceptionally useful if imported elsewhere.

SirPalmerston 14y ago

I actually faced a similar problem and I ended up writing an almost identical script which generated a JSON or XML file (depending on the user's need).

deleted 14y ago

[deleted]

stevoski 14y ago

Nice work...but this is not a perfect database of countries. * Territories such as Réunion are listed as countries * Countries that ceased to exist are not listed (Yugoslavia, Soviet Union) although the world's most recent new country (South Sudan) is listed. * Kosovo is listed, although not recognised by all the world. Transnistria is not listed, even though it has de facto sovereignty. * Total land area is a disputable amount in several circumstances. There are several more such examples I could list. Yes I'm being pedantic. But I have a special interest in this area, and I'm aware of just how hard it is. People are born in one country (Soviet Union) and die in another (Ukraine), even though they never moved from their home city (Odessa).

mjn 14y ago
This is a problem even in professional data sources. I recently did an admittedly somewhat silly analysis, to see what happens if you take the "voting with your feet" metaphor for emigration seriously, and interpret each country's emigrants as a ranked-ballot "vote" for most appealing destination country, with all the ballots tallied using a Condorcet ranking. Results: http://www.kmjn.org/notes/voting_with_feet.html http://www.kmjn.org/notes/voting_with_feet.html I used bilateral migration data from the World Bank to do it: http://data.worldbank.org/data-catalog/global-bilateral-migration-database http://data.worldbank.org/data-catalog/global-bilateral-migr... I thought this might produce complexities I'd have to deal with, since over the 50 year period this data set covers, countries have come and gone a number of times. But to my surprise, the data set somehow uses only current countries, even when giving data points for 1960 or 1970. It doesn't really explain how that happened, either. Did they somehow impute past data to current countries? What does the 1970 figure for Germany, or Serbia, actually mean? Something was too complex and didn't fit the database, so it got swept under the rug? Made my job writing this off-the-cuff blog post much easier, but now I'm left wondering what the data I used actually means, despite it coming from a highly respected source, and used in many academic papers. edit: Found a paper describing the construction of the World Bank data. It looks like they use what they describe as an admittedly crude method of attributing based on population shares, so e.g. immigrants to "Yugoslavia" are apportioned to Croatia/Serbia/etc. based on population share, assuming the immigration was uniform throughout. Not horrible as a first approximation, probably, though clearly not "right" either. Source: http://papers.ssrn.com/sol3/papers.cfm?abstract_id=969242 http://papers.ssrn.com/sol3/papers.cfm?abstract_id=969242

gllen 14y ago

Isn't it easy enough to just delete columns you don't need after importing one of the public sources?

freework 14y ago
...or just leave the data in. Having a few extra columns on a 250 row table isn't going to hurt anything.
HyprMusic 14y ago
But that adds an extra step for the developer, plus this could possibly be bundled in to an installation script.

dfc 14y ago

Another time saver: TimezoneDB [1] TimeZoneDB provides free time zone database for cities of the world. The database is licensed under Creative Commons Attribution 3.0 License. It contains countries name, time zones, abbreviation, GMT offset, and Daylight Saving Time (DST). The data is available in CSV and SQL format. You can download and implement into your projects for free. [1] http://timezonedb.com/download http://timezonedb.com/download

jlgreco 14y ago
The tz database that this is sourced for is one of the most impressive compilations of information that I have ever seen. Timezones seem like they should be simple, but the reality is very impressive: http://upload.wikimedia.org/wikipedia/commons/9/9f/Tz_map_world2009r_efeledotnet.png http://upload.wikimedia.org/wikipedia/commons/9/9f/Tz_map_wo...

lathamcity 14y ago

Hmm, I see some countries on here that weren't on the Sporcle map.

adaml_623 14y ago

Some countries have more than one currency code, China has CNY and CNH. http://en.wikipedia.org/wiki/Renminbi#CNH http://en.wikipedia.org/wiki/Renminbi#CNH. I'm not going to debate the fact that CNH isn't official even though it is traded.

justincormack 14y ago
When you trade it you trade CNY with a note saying please settle in Hong Kong, you cannot use the CNH symbol...

einhverfr 14y ago

Thanks for this. I think it sounds worthwhile to move my own country table work in PostgreSQL to an extension so everyone else can use it. Thanks for the inspiration/motivation!

thadk 14y ago

It would likely also be useful to have the local language name for the countries as an optional column. You can probably source this from the http://naturalearthdata.com http://naturalearthdata.com source mentioned elsewhere here.

stuartjmoore 14y ago

Is there a single source for data like this? Lists countries, currencies, timezones, dictionary words. A Wiki for massive amounts of data that is basically common knowledge.

apaprocki 14y ago
Yes, Unicode CLDR[1] provides all of this data and more in XML format. [1] http://cldr.unicode.org/ http://cldr.unicode.org/

gulbrandr 14y ago

If it may help, here is a list of world countries in CSV, XML and JSON formats. https://github.com/mledoze/countries https://github.com/mledoze/countries

tomd3v 14y ago

Very nice job. That would be great if there were such thing for cities and nationalities.

ocharles 14y ago

Primary key on idCountry, no unique constraints on name/code/isoAlpha3, and 'default' blank values? It's certainly a start, but it's also a bit questionable.

jpswade 14y ago

See ISO 3166... http://www.iso.org/iso/home/standards/country_codes.htm http://www.iso.org/iso/home/standards/country_codes.htm

bergie 14y ago

Geonames has a bunch of freely-available location datasets. I've used it in various apps for cities, countries, postcodes, etc: http://www.geonames.org/export/ http://www.geonames.org/export/

llcoolv 14y ago

Only two comments: 1. It is Czech Republic, not Czechia. At least in English. 2. Having the mulitple spoken languages in a single column is against normalization a bit. If one would have to use those values, they would need to parse the csv, etc.

speleding 14y ago

If you want an "official" (i.e. kept up to date by the UN) machine readable list of countries then this is a better source: http://www.unicode.org/cldr/data/charts/summary/root.html http://www.unicode.org/cldr/data/charts/summary/root.html This list also has currencies and some other stuff, but a Ruby script to extract what you want should not take more than 10 lines. This list has the added advantage of being available in pretty much all the languages in the world, in case you need localisation for your project. Side note: if you want to check if a list is up to date, check for "Aruba" and country code BQ (Caribbean). They changed the status of independent country a year ago so many lists don't have it yet (used to be a Dutch colony).

briteside 14y ago

Another source, with SQL, XML, JSON, etc.: http://data.brighterplanet.com/countries http://data.brighterplanet.com/countries and a lot more like it at: http://data.brighterplanet.com http://data.brighterplanet.com