

World `countries` – database table for MySQL - pericd
http://echobehind.wordpress.com/2012/09/23/world-countries-database-table-for-mysql/

======
stevoski
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).

~~~
vhf
_People are born in one country (Soviet Union) and die in another (Ukraine),
even though they never moved from their home city (Odessa)._

People are even born in one city (Leningrad) in one country (Soviet Union) and
die in another city (St-Petersburg) in another country (Russia), although they
never moved.

~~~
webjunkie
Wasn't the country Russia still Russia, just within the Union?

~~~
guard-of-terra
It was Russian Soviet Federal Socialist Republic, and now it is Russian
Federation.

You probably still could go with "Russia" every time.

------
dfc
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>

~~~
jlgreco
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_wo...](http://upload.wikimedia.org/wikipedia/commons/9/9f/Tz_map_world2009r_efeledotnet.png)

------
speleding
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>

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).

------
adaml_623
Some countries have more than one currency code, China has CNY and 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
When you trade it you trade CNY with a note saying please settle in Hong Kong,
you cannot use the CNH symbol...

------
llcoolv
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.

------
jph
Similar kinds of data are in my GitHub repo, in case it helps people here.

<https://github.com/sixarm/sixarm_data_geolocation>

Suggestions welcome.

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

------
stuartjmoore
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
Yes, Unicode CLDR[1] provides all of this data and more in XML format.

[1] <http://cldr.unicode.org/>

------
ocharles
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.

------
bergie
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/>

------
gulbrandr
If it may help, here is a list of world countries in CSV, XML and JSON
formats.

<https://github.com/mledoze/countries>

------
randomdrake
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/>

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

------
briteside
Another source, with SQL, XML, JSON, etc.:

<http://data.brighterplanet.com/countries>

and a lot more like it at:

<http://data.brighterplanet.com>

------
thadk
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> source mentioned elsewhere here.

------
einhverfr
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!

------
jpswade
See ISO 3166...

<http://www.iso.org/iso/home/standards/country_codes.htm>

------
SirPalmerston
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).

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

~~~
freework
...or just leave the data in. Having a few extra columns on a 250 row table
isn't going to hurt anything.

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

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

