Cómo crear tu propia base de datos de Tracking (Tutorial)

Para empezar a crear nuestra base de datos de tracking nos dirigiremos a la página de GeoNames, para crear la estructura de nuestra base de datos, a la que vamos a llamar “geonames” y nos descargamos el archivo recopilación de paises del mundo countryInfo. Aquí solo aparece un archivo en formato .txt, así que para guardarlo copiamos, pegamos y guardamos en nuestra carpeta.

Abrimos MySQL e introducimos las siguientes instrucciones.

CREATE SCHEMA IF NOT EXISTS `geonames` DEFAULT CHARACTER SET utf8mb4 ;

CREATE TABLE geonames.countryinfo (
LandCode char(2),
iso_alpha3 char(3),
iso_numeric integer,
fips_code character varying(3),
name character varying(200),
capital character varying(200),
areainsqkm double precision NULL,
population integer,
continent char(2),
tld char(3),
currency char(3),
currencyName char(20),
Phone char(20),
postalCodeFormat char(200),
postalCodeRegex char(200),
geonameId int NULL,
languages character varying(200),
neighbours char(50),
equivalentFipsCode char(10),
PRIMARY KEY (`LandCode`)
)ENGINE = InnoDB;

LOAD DATA INFILE 'path/countryInfo.txt'
INTO TABLE geonames.countryinfo IGNORE 51 LINES
(LandCode,iso_alpha3,iso_numeric,fips_code,name,capital,areaInSqKm,population,continent,tld,currency,currencyName,Phone,postalCodeFormat,postalCodeRegex,languages,geonameId,neighbours,equivalentFipsCode);

Donde pone path en LOAD DATA INFILE ‘path/countryInfo.txt’  solo tenemos que poner el lugar donde hemos guardado el archivo.

En las primeras 51 lineas se explica que significa cada columna y sus variantes, pero la que más nos interesa es LandCode o la designación ISO-3166-2 del país, que es la denominación con dos letras. Oficialmente existen 249 paises con designación, pero como entre paises e idiomas nadie se aclara hay una tabla que indica los que hay, los que son y los que han dejado de ser.

Con esto quiero decir que para que nuestra base de datos funcione es bastante posible que haya que añadir alguna designación adicional en nuestra tabla que no se contempla en el archivo countryInfo.txt. Así que os añado algunas para proseguir:

INSERT INTO `geonames`.`countryinfo` (`LandCode`, `name`) VALUES ('FX', 'France Metropolitan');

INSERT INTO `geonames`.`countryinfo` (`LandCode`, `name`) VALUES ('EN', 'Unassigned');

INSERT INTO `geonames`.`countryinfo` (`LandCode`, `name`) VALUES ('JA', 'Jamaica');

INSERT INTO `geonames`.`countryinfo` (`LandCode`, `name`) VALUES ('PO', 'Unassigned');

INSERT INTO `geonames`.`countryinfo` (`LandCode`, `name`) VALUES ('TP', 'East Timor');

INSERT INTO `geonames`.`countryinfo` (`LandCode`, `name`) VALUES ('UK', 'United Kingdom');

INSERT INTO `geonames`.`countryinfo` (`LandCode`, `name`) VALUES ('YU', 'Yugoslavia');

INSERT INTO `geonames`.`countryinfo` (`LandCode`, `name`) VALUES ('AP', 'African Regional Industrial Property Organization');

INSERT INTO `geonames`.`countryinfo` (`LandCode`, `name`) VALUES ('', 'NoCountry');

Hay que prestar atención al cotejamiento, ya que las tablas y nombres de algunas ciudades están en distintos idiomas y mysql se hace un lio con todos esos símbolos. Por ello, hay que escoger un cotejamiento utf8mb4.

Ahora añadiremos las zonas horarias a las que se pueden acceder desde el siguiente enlace.

CREATE TABLE geonames.timeZones (
countryCode VARCHAR(2),
timeZoneId VARCHAR(200),
GMT_offset DECIMAL(3,1),
DST_offset DECIMAL(3,1),
rawOffset DECIMAL(3,1),
PRIMARY KEY (`countryCode`,`timeZoneId`),
INDEX `fk_timeZones_countryinfo_idx` (`countryCode` ASC),
CONSTRAINT `fk_timeZones_countryinfo`
FOREIGN KEY (`countryCode`)
REFERENCES `geonames`.`countryinfo` (`LandCode`)
ON DELETE NO ACTION
ON UPDATE NO ACTION
) ENGINE = InnoDB;

LOAD DATA INFILE 'path/timeZones.txt' INTO TABLE geonames.timeZones IGNORE 1 LINES (countryCode,timeZoneId, GMT_offset, DST_offset,rawOffset);

Y ahora introduciremos las listas que asignan las direcciones IP a cada país y acto seguido las que se asignan a las ciudades y que podréis descargar desde db-ip/countries y db-ip/cities.

CREATE TABLE IF NOT EXISTS `geonames`.`Land_IP` (
`countryCode` VARCHAR(2) NOT NULL,
`IPStart` VARCHAR(16) NULL,
`IPEnd` VARCHAR(16) NULL,
`IPStart_BIN` VARBINARY(16) NULL,
`IPEnd_BIN` VARBINARY(16) NULL,
PRIMARY KEY (`countryCode`, `IPStart`),
INDEX `fk_Land_IP_countryinfo_idx` (`countryCode` ASC),
CONSTRAINT `fk_Land_IP_countryinfo`
FOREIGN KEY (`countryCode`)
REFERENCES `geonames`.`countryinfo` (`LandCode`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;

LOAD DATA INFILE 'path/dbip-country.csv'
INTO TABLE `geonames`.`Land_IP`
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 0 ROWS
(@vIPStart,@vIPEnd,`countryCode`)
SET `IPStart` = @vIPStart, `IPEnd` = @vIPEnd,`IPStart_BIN` = @vIPStart, `IPEnd_BIN` = @vIPEnd;

CREATE TABLE IF NOT EXISTS `geonames`.`City_IP` (
`countryCode` VARCHAR(2) NOT NULL,
`Stateprov` VARCHAR(80),
`City` VARCHAR(80),
`IPStart` VARCHAR(16) NOT NULL,
`IPEnd` VARCHAR(16) NOT NULL,
`IPStart_BIN` VARBINARY(16) NOT NULL,
`IPEnd_BIN` VARBINARY(16) NOT NULL,
PRIMARY KEY (`countryCode`, `IPStart`),
INDEX `fk_City_IP_countryinfo_idx` (`countryCode` ASC),
CONSTRAINT `fk_City_IP_countryinfo`
FOREIGN KEY (`countryCode`)
REFERENCES `geonames`.`countryinfo` (`LandCode`)
ON DELETE NO ACTION
ON UPDATE NO ACTION
)ENGINE = InnoDB;

LOAD DATA INFILE 'path/dbip-city.csv'
INTO TABLE `geonames`.`City_IP`
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 0 LINES
(@vIPStart,@vIPEnd,countryCode,@vStateprov,@vCity)
SET IPStart = @vIPStart, IPEnd = @vIPEnd, IPStart_BIN = @vIPStart, IPEnd_BIN = @vIPEnd, Stateprov = QUOTE(@vStateprov),City= QUOTE(@vCity);

Con todo esto ya tendremos nuestra base de datos preparada y solo tendremos que hacer una consulta de la IP para obtener el país o ciudad desde donde se conectan de la siguiente manera.

Muchas aplicaciones y redes sociales manejan esta información de forma generalizada para sacar estadísticas y casi nunca de forma individualizada, ya que atenta contra el derecho de privacidad. Digamos que lo que interesa a las redes sociales es la acción del usuario en su plataforma y no la persona, por lo que no se puede señalar de forma directa a un usuario de algo si no se cuenta previamente con su consentimiento para hacerlo público.

Este método solo servirá para direcciones IP estáticas. La mejor manera de hacerse invisible ante esta manera de geolocalización es utilizar un router de IP dinámica, o utilizar un programa para ocultar tu dirección IP. Entre ellos TOR es una buena opción.

AVISO: Este tipo de herramientas se han de usar con responsabilidad y madurez. Cualquier información que vulnere la privacidad del usuario no puede ser almacenada en nuestra base de datos durante más de 2 años.