-1

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

PolyGeo
  • 65,136
  • 29
  • 109
  • 338
LarsH
  • 262
  • 5
  • 16

2 Answers2

1

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.

RyanKDalton
  • 23,068
  • 17
  • 110
  • 178
simo
  • 8,580
  • 1
  • 28
  • 56
0

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.

LarsH
  • 262
  • 5
  • 16