We’re working on a visualization of real-time clicks on our http://appsto.re 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.
MySQL Data Dump (tnx Dvir Volk)
And here’s the embedded data just for fun:
your programme and aricle are very useful to me ,i am so thankful for you.
Great resource! Especially the SQL dump. Many thanks, my friend.