From #1823 (comments 38 and 39), Matt ponders a lookup of a city's lat/long rather than storing the lat/long in the log_visit table.

The question remains if we need to store lat/long, depending how fast/easy it is to query lat/long from a given City using GeoIP (maybe this is not possible?)

Greg posits:

Depends on what kind of database you're using. If you're using the CSV database and import it to MySQL tables, than you can run a query like

SELECT latitude,longitude FROM location WHERE city = 'Berlin'

in < 1ms. However, you will get ambiguous results when just looking for city names. Instead, a better idea would be to store the unique GeoIP location-id.

I don't know if any of the GeoIP APIs that work with the binary database (.dat) supports reverse-queries. All I saw was the IP --> location way..

MaxMind provides a CSV file of the world's populated cities (country, region code, city names, population (if available), latitude, longitude).

The space/performance tradeoff is:

• storing lat/long in two DECIMAL(7,4) columns requires 12 bytes per row
• the CSV file is a 32 MB download and about 126 MB uncompressed
• the CSV file does not contain the internal GeoIP location id

Keywords: interesting