Setting up GeoLite Data

MySQL

The GeoLite databases are free IP geolocation databases. They are updated on the first Tuesday of each month. These databases are offered in the binary and csv formats. We will be using csv for this setup. Download the files from the following link: http://dev.maxmind.com/geoip/legacy/geolite/

Unzip the zip file and you will find two csv files: GeoLiteCity-Blocks.csv and GeoLiteCity-Location.csv. Place both files in the same directory.

Now setup your instance of MySQL database if you haven't already done so. Setting up MySQL is beyond the scope of this article. Once MySQL is up and running enter MySQL shell and type the following code:

CREATE DATABASE IF NOT EXISTS GEO;

USE GEO;

DROP TABLE IF EXISTS `blocks`; 
CREATE TABLE  `blocks` ( `startIPNum` int(10) unsigned NOT NULL,`endIPNum` int(10) unsigned NOT NULL,`locID` 
int(10) unsigned NOT NULL, PRIMARY KEY  (`startIPNum`,`endIPNum`) ) 
ENGINE=MyISAM DEFAULT CHARSET=latin1 PACK_KEYS=1 DELAY_KEY_WRITE=1;

DROP TABLE IF EXISTS `location`; 
CREATE TABLE  `location` (`locID` int(10) unsigned NOT NULL,`country` char(2) default NULL,`region` char(2)
 default NULL,`city` varchar(45) default NULL,`postalCode` char(7) default NULL,`latitude` double default 
NULL,`longitude` double default NULL,`dmaCode` char(3) default NULL,`areaCode` char(3) default NULL,PRIMARY KEY
  (`locID`),KEY `Index_Country` (`country`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED;

This will create the empty tables. Now we need to load the extracted csv files into these tables. To do so type the following command into the MySQL shell:

load data infile 'GeoLiteCity-Blocks.csv'  into table `blocks`  fields terminated by ',' optionally enclosed by
 '"'  lines terminated by '\n'  ignore 2 lines;

load data infile 'GeoLiteCity-Location.csv'  into table `location`  fields terminated by ',' optionally enclosed
 by '"'  lines terminated by '\n'  ignore 2 lines;

Finally, with the data loaded we need to define a stored function to make it easier on us to query the database. To do so enter the following commands in the MySQL shell:

 DELIMITER $$ 
 DROP FUNCTION IF EXISTS `IPTOLOCID` $$ 
 CREATE FUNCTION `IPTOLOCID`( ip VARCHAR(15)) RETURNS int(10) unsigned 
 BEGIN
    DECLARE ipn INTEGER UNSIGNED;
    DECLARE locID_var INTEGER; 
    IF ip LIKE '192.168.%' OR ip LIKE '10.%' THEN RETURN 0;
    END IF;
    SET ipn = INET_ATON(ip);
    SELECT locID INTO locID_var FROM `blocks` INNER JOIN (SELECT MAX(startIPNum) AS start FROM `blocks` WHERE startIPNum <= ipn) AS s ON (startIPNum = s.start) WHERE endIPNum >= ipn;
    RETURN locID_var; 
END
$$  
DELIMITER ;