Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers 2

up vote 1 down vote accepted

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.

share|improve this answer
    
Thank you, that might be just what I need. –  LarsH Nov 5 '11 at 20:27
    
So far I've only found a single set of latitude and longitude fields for each place name. Can you tell me how to get the capital, or the centroid? –  LarsH Nov 6 '11 at 12:19
    
For capitals, you will have to make yourself an associated file/dictionnary. Maybe it already exists (as flat text file or python dictionnary), if not you can do it using some open sources. –  simo Nov 6 '11 at 18:05
    
see also my update on reply –  simo Nov 6 '11 at 20:00
add comment

A good answer I found was to use the csv table given here:

Google Analytics Country Names

Note the corrections in my comments at the end, including:

ST: São Tomé and Principe
VN: Vietnam
SY: Syria
CD: Congo [DRC]
CG: Congo

As a bonus, this table contains lat/lon coordinates for each country, so I didn't have to join to another table (using the country code) to get those.

This doesn't solve the problem of having to keep updating the list as Google changes its country names. But it works well for now.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.