GeoIP con mysql
Contents
GeoIP
GeoIp es una base de datos de maxmind. Es comercial pero cierta información es publica. La locazación por rangos de Ip's en rangos de ciudades con cierto tamaño es publico (bajo licencia GPL). Geo Ip tiene una utilidad que perite leer sus ficheros propietarios. Tambien ofrece las bases de datos publicas bajo la forma de ficheros csv (ficheros separados por commas).
Preparación de la base de datos MySQL
La estructura del la base de datos tiene 2 ficheros. El primero es un identificaor de cada ciudad. Y el segundo es el que relaciona los rangos de ips con la clave primaria de las ciudades. Es decir que vamos a necesitar dos tablas.
drop table if exists ciudades; create table ciudades ( id int NOT NULL, pais char(2), provincia char(2), ciudad varchar(50), latitud char(8), longitud char(8), PRIMARY KEY (id) ) ENGINE=INNODB;
Esta tabla contiene los datos de identificación de las ciudades (He obviado los codigos postales y los indicativos telefonicos porque es información que no existe para los paises fuera de Estados unidos y Canada.
drop table if exists ips;
create table ips (
ip_inf BIGINT,
ip_sup BIGINT,
id_ciudad int,
PRIMARY KEY (ip_inf,ip_sup),
INDEX ip_ciud_id (id_ciudad),
FOREIGN KEY (id_ciudad)
REFERENCES ciudades(id)
ON DELETE CASCADE
) ENGINE=INNODB;
Importación de los datos
Lo pimero que he realizado es la descarga del fichero de datos. El zip trae 2 ficheros.
GeoLiteCity_20080401 # ls -l total 107516 -rw-rw-r-- 1 root root 99712064 Apr 1 20:34 GeoLiteCity-Blocks.csv -rw-rw-r-- 1 root root 10262868 Apr 1 20:34 GeoLiteCity-Location.csv GeoLiteCity_20080401 #
Para importar los datos he realizado 2 pequeños awk.
ciudades.awk
Prepara la insert desde el fichero csv
BEGIN {
FS=",";
}
{
pais=substr($2,2,length($2)-2);
prov=substr($3,2,length($3)-2);
ciud=substr($4,2,length($4)-2);
print "insert into ciudades values (" $1 ",'" pais "','" prov "','" ciud "',"$6","$7");";
}
ips.awk
{
print "insert into ips values (" $0 ");";
}
Importación de las ciudades
sed '1,2d' GeoLiteCity_20080401/GeoLiteCity-Location.csv | sed "s/'/\\\'/g" | awk -f ciudades.awk | recode latin1..utf-8 | mysql -p geoipdb
Esta sentencia realiza la importación de las ciudades. El primer sed elimina la cabecera del fichero. El segundo se remplaza ' por \', Para escapar la comilla simple para el mysql. Aplico el awk y luego codifico en Utf-8 para sea compatible con mi juego de caracteres de la base de datos.
Importación de los rangos de IP's
sed '1,2d' GeoLiteCity_20080401/GeoLiteCity-Blocks.csv | sed 's/\"//g' | awk -f ips.awk | mysql -p geoipdb
El primer sed elimina las cabeceras, y el segundo elimina las comillas dobles (lo podría haber realizado por el primero). Pero la cantidad de datos no era tan grande y no me preocupe mucho por el rendimiento en este caso la cosa cambia (para acerelarlo se podria deshabilitar la foreign key).
Referencias
--Daniel Simao 12:26 7 abr 2008 (UTC)
