Oct 132012
 

MaxMind es una empresa que ofrece servicios de geolocalización.IP, y de detección de fraude. Esta empresa ofrece la descarga gratuita de una base de datos que incluye la latitud y longitud de las principales ciudades del mundo. En este artículo vamos a explicar cómo importar y procesar esta información en una base de datos MySQL.

Descarga de la base de datos e importación en MySQL

En primer lugar, accedemos a la zona de descargas de MaxMind en la URL: http://www.maxmind.com/download/geoip/database/GeoLiteCity_CSV/, y descargamos la versión más reciente de la base de datos (en nuestro caso, GeoLiteCity_20121002.zip).

Al descomprimir el fichero zip, vemos que contiene dos ficheros CSV en su interior. Las primeras líneas de estos ficheros contienen las cabeceras con los nombres de los campos de datos, y a continuación se encuentran los datos en sí:

Como vemos, el primer fichero ‘GeoLiteCity-Blocks.csv’ contiene una serie de rangos de direcciones IP y su asociación a códigos de ubicación. A efectos de este artículo, esta información no es relevante.

El segundo fichero ‘ GeoLiteCity-Location.csv’ contiene la asociación entre ciudades y pares de coordenadas (latitud,longitud) que nos interesa. Con un editor de texto, eliminamos las dos primeras líneas del fichero. Después creamos en mySQL la tabla que van a contener los datos:

Y procedemos a importarlo:

Nota: El fichero utiliza el conjunto de caracteres ‘latin1’. Si nuestra base de datos utiliza utf-8, deberemos realizar la conversión de latin1 a utf8. Una posibilidad es utilizar ‘iconv’:

Análisis de los datos

  • El campo ‘country’ contiene el código de país de dos caracteres (en el caso de España, “ES”).
  • El campo ‘region’ contiene el código de la división administrativa de segundo nivel,
  • El campo ‘city’ contiene el nombre de ciudad.
  • Los campos postalCode, metroCode y areaCode sólo contienen información para ciudades de EEUU.

Si analizamos esta información con algunas sentencias SQL, vemos que:

1. Hay un total de 249 códigos de país

2. Hay un total de 18 códigos de región para España:

Desglosando esta información, vemos que hay un código de región en blanco, y 17 códigos de región que corresponden a las 17 comunidades autónomas en que se encuentra dividida España administrativamente.

Si realizamos ahora la búsqueda de toda la información para la ciudad denominada ‘Madrid’, vemos que hay un total de 10 ciudades con este nombre, de las cuales 4 se encuentran en Estados Unidos. También podemos ver que los campos postalCode, metroCode y areaCode sólo contienen valores para las ciudades de “US”.

Eliminación de duplicados

En principio, un mismo par de coordenadas (latitud, longitud) debería corresponder a una única ciudad, pero con una rápida consulta a la base de datos comprobamos que no es así, y que la base de datos contiene numerosos duplicados:

Para solucionar este problema, cargamos en una nueva tabla un registro por cada combinación única de country, region y city, y guardamos como latitud y longitud los valores medios de latitud y longitud de todos los registros existentes para cada combinación:

 Publicado por en 6:14 pm

  9 Respuestas a “Cómo obtener una base de datos de coordenadas de ciudades”

  1. Hola podrías explicar como descifrar a que equivale el rango del primer bloque por favor? GeoLiteCity-Blocks.csv

    por ejemplo “7602176”,”7864319″,”16″

    A que rango coresponde? me refiero a por ejemplo:–> 188.1.1.5 hasta 188.3.8.255

    Muchas gracias por tu información. Saludos!

    • Hola Victor,

      La conversión de una dirección IP a.b.c.d a número entero y viceversa es sencilla, porque cada uno de los valores a,b,c y d es un byte, que puede valer de 0 a 255.

      Por lo tanto, una dirección IP se convierte a un número entero con la fórmula:

      ((a*256+b)*256+c)*256+d

      Y para convertir un entero a dirección IP, puedes utilizar por ejemplo este código PHP:

      Que imprime la línea:

      IP: 0.119.255.255

  2. Muchas gracias por la información.Mr. Admin!

    Load ” “

  3. Como realizo la consulta para buscar por latitud y longitud?

    • Hola Julio,
      Al estar los datos en una tabla de MySQL, se puede realizar una consulta SELECT normal. Por ejemplo:

      Y para buscar en un rango de coordenadas, se puede utilizar el operador “BETWEEN”:

  4. Tengo problemas con la configuración de caracteres, lo abro con libreoffice y en el campo de city me salen algunas ciudades con algún �, y antes de abrirlo lo pongo en utf-8 idioma ingles. He probado con varios con varias codificaciones de caracteres y no hay forma, y si lo exporto a mysql me sigen saliendo caracteres raros, ¿me podrias ayudar?
    muchas gracias.
    Santi.

    • Me respondo yo mismo y puede que a alguien le sirva.
      Se abre bien con el juego de caracteres Occidental (ISO-8859-15)
      Gracias de todas formas.
      Santi

  5. Nuestro Saludo al Blog, muy bueno. Me pregunto si la Base de datos podemos utilizarla para solamente, atraves de los metadatos de imagenes que dan la latitud y longitud podemos con dicha BD ver de que lugar fue tomada la foto. Lo que queremos resolver es que cuando yo le pase la latitud y la longitud me obtenga el pais y la ciudad almenos. Lo otro que esta BD en un server de apache no me deja montarla, solo 2MB. Nos pueden ayudar porfavor.

    • Hola, GrupoUnicornio.

      En primer lugar, gracias por tus comentarios.

      En cuanto a tu primera pregunta, si tienes un par de coordenadas (latitud, longitud), puedes hacer una consulta a la base de datos MySQL para encontrar la ciudad más próxima.

      Por ejemplo, puedes encontrar la ciudad más próxima al punto (40.2368, -3.6734) utilizando la consulta:

      “select locId,country,region,city,(abs(latitude – 40.2368) + abs(longitude – (-3.6734))) as distancia from GeoLiteCity order by distancia limit 1;”

      En cuanto a la segunda pregunta, necesitaría mas información para entender cuál es el problema.

 Deja un comentario

(requerido)

(requerido)