About a year ago, I created a web-based map of the visitors to a web site. I've been asked to switch it from using Urchin and web server log data, to retrieving data from Google analytics.
When I was using the older data sources, I had the IP address of each visit, and I would use the IP address to look up city name / country code / latitude / longitude.
Sometimes the city was unknown in the database I was using, but I would almost always get the country code, with a centroid latitude/longitude.
This is important data, because I would put markers on a map showing where visitors were from, and sometimes a large proportion of visits from a given country were from unknown cities. For example, I have results like
Country: Anguilla Visits: 2 City: (not set) Latitude: 0.0000 Longitude: 0.0000
This is the only data point from Anguilla. So if I don't plot it, Anguilla is completely omitted.
Now with the Google Analytics Data Export API, IP address is unavailable. Thankfully, city name / country name / latitude / longitude are available most of the time, so I don't have to look those up by IP. But in the cases when the city is unknown, but country name is given, lat/long is given as 0.0000,0.0000.
How can I plot these country points on a map? It's difficult to look up country locations in a database because the country name is given, rather than a country code. Country names of course are somewhat variable. It really surprises me that Google would not make country code available at all, but I don't see it listed under the allowed "dimensions" for an API query.
I guess I could build up my own lookup table, fine-tuned to match Google's country names, but it would be subject to breakage if Google ever changed spelling or phrasing (e.g. "Democratic Republic of the Congo" vs. "Congo-Kinshasa").
Thanks for any suggestions.
Best Answer
You can go the administrative way by taking the capital coordinates or go the geometrical way by calculating country's centroid.
In any of these cases, Geonames database could help you.
Update : You can also use the Creative Commons dataset format world borders shapefile in which any countriy has a lat/long coordinates associated.
I don't know how it is computed. Using that shapefile you will also be able to compute centroids if needed.