Countries, Approximate Lat-Lon and ISO 3166-1 alpha-2

We’re working on a visualization of real-time clicks on our short URL. The cheap GeoIP database we use to convert IP address to geographical coordinates only gives us country-codes. ISO 3166-1 defines the encoding of ”countries, dependent territories, and special areas of geographical interest” into codes, and the 2-character encoding subset is defined in ISO 3166-1 alpha-2. A country like France ends up being ‘FR’, Israel is ‘IL’, you get it.

When we wanted to plot this onto Google Maps we needed to convert country-codes to Lat/Lon (map coordinates) and apparently there isn’t such a simple mapping. One reason may be that a country like France has a political border which is well defined but there’s no real point on the map that defines it, that’s pretty arbitrary. A bunch of companies (Google, Yahoo, etc) have what’s called Geocoders which are pieces of software that will take an address and return the Lat/Lon representation of it. Most Geocoders don’t like 2-letter country-code though, bummer.

What we ended up doing is digging out this information from a few Geocoders that do like to chew up 2-character codes, and for the sake of other developers we wanted to curate the data and make it easily available and consumable for other developers. I’m including a Google Spreadhseet with all the values, as well as a JSON dictionary that gives you country name, lat and lon given a country-code. I also included this data for all USA and Canadian states. Feel free to use this to facilitate visualizations of data or anything else you think is cool.

The data:

Online spreadsheet
JSON file
MySQL Data Dump (tnx Dvir Volk)

And here’s the embedded data just for fun: