mpopp.net blog – Markus Popp

It’s very easy to log the visitor’s IP address in a web application. In PHP, for example, a simple $_SERVER[‘REMOTE_ADDR’] returns the desired value.

But the IP address alone doesn’t tell us much about the person, sometimes it’s not even a person, but a search robot, who just entered the website. More interesting would be, where he (the person or the machine) lives.

I’d like to show you how MySQL 5 can help you to easily query the country from some source data that can be downloaded for free from MaxMind at http://www.maxmind.com/download/geoip/database/GeoIPCountryCSV.zip.

First I’d recommand you to create a new database, if you have the privilege to do so. I have called my database geoips. Download the file mentioned above and extract the file GeoIPCountryWhois.csv from this zip file. The file looks like this:

"2.6.190.56","2.6.190.63","33996344","33996351","GB","United Kingdom"
"3.0.0.0","4.17.135.31","50331648","68257567","US","United States"
"4.17.135.32","4.17.135.63","68257568","68257599","CA","Canada"
"4.17.135.64","4.17.142.255","68257600","68259583","US","United States"
...

First we have to get this data into a new table, which I call ip_ranges. We create it in the geoips database using this CREATE command:

CREATE TABLE ip_ranges (
   ip1_temp char(16), 
   ip2_temp char(16), 
   ip_from int unsigned NOT NULL PRIMARY KEY, 
   ip_to int unsigned NOT NULL UNIQUE, 
   code char(2) NOT NULL, 
   country varchar(100) NOT NULL, 
   INDEX (code)) ENGINE = InnoDB;

I didn’t take much care about the ip1_temp and ip2_temp columns, because we only need them temporarily. We will make our searches based on the values in the third and forth column of the data in the csv file. Later we will put the code and country pairs into a separate table to normalize the data. Because we will put a foreign key on the code column, we put an index on it and we use the InnoDB storage engine. This isn’t a must, but it’s a good way to also show you how to accomplish this task ;-).

To import the csv file into the table we just created we have two possibilities, either via a SQL command or via the mysqlimport program that’s included in MySQL. I’ll show you both ways. You need the FILE privilege for both variants.

Open a console window and change to the directory where the GeoIPCountryWhois.csv file resides. Start your mysql client program with the command

mysql -h [host] -u [username] -p[password] geoips

Then execute this SQL statement:

LOAD DATA LOCAL INFILE 'GeoIPCountryWhois.csv' 
   INTO TABLE ip_ranges 
   FIELDS TERMINATED BY ',' 
   ENCLOSED BY '"' 
   LINES TERMINATED BY '\n';

Instead of starting your mysql client program, you can also use the mysqlimport program. Therefore, the name of the input file must be equal to the table name, so we rename the file GeoIPCountryWhois.csv to ip_ranges.csv.

move GeoIPCountryWhois.csv ip_ranges.csv (in Windows)
mv GeoIPCountryWhois.csv ip_ranges.csv (in Linux)

Then we can use this command (please write it in one line) to import the csv file:

mysqlimport -h [host] -u [username] -p[password] --local 
--fields-terminated-by="," --fields-enclosed-by="\"" 
--lines-terminated-by="\n" geoips ip_ranges.csv

You should now have somthing like 74,000 records in your ip_ranges table.

Change back to your mysql client and the geoips database. We can now remove the columns ip1_temp and ip2_temp from the ip_ranges table, because we won’t need them for searching. If you want to leave them to verify the results, that’s no problem either.

ALTER TABLE ip_ranges
   DROP ip1_temp,
   DROP ip2_temp;

Now we have an interesting task ahead of us. We want to move a list of country codes and country names into a separate table called ip_countries. That’s very easy to do with only one command.

CREATE TABLE ip_countries
   SELECT DISTINCT code, country
      FROM ip_ranges
      ORDER BY code;

We can now

* add a primary key to the code column of the ip_countries table
* remove the country column from the ip_ranges table
* add a foreign key constraint to the code column of the ip_ranges table, which refers to the code column of the ip_countries table.

We need two more SQL commands to do this:

ALTER TABLE ip_countries ADD PRIMARY KEY (code);
ALTER TABLE ip_ranges 
   DROP country, 
   ADD FOREIGN KEY (code) 
      REFERENCES ip_countries(code);

Now we have all the data we need to start writing a User Defined Function which queries the country based on an IP address that we pass as parameter. Of course we can’t pass the IP address as such a number like we have the values in the ip_from and ip_to columns of the ip_ranges table. So we need to calculate the corresponding number from the IP address.

The page http://www.maxmind.com/app/csv tells us how this value can be calculated. We split up the 4 parts of our IP address and calculate it like this (ip1 = 1st part, ip2 = 2nd part, ip3 = 3rd part, ip4 = 4th part):

value = 16777216 x ip1 + 65536 x ip2 + 256 x ip3 + ip4

I use my current IP address 62.46.14.132 to try this out and get the value 1,043,205,764.

As soon we have this value, we can query the country like this:

SELECT b.country
   FROM ip_ranges a INNER JOIN ip_countries b
   ON a.code = b.code
   WHERE ip_from = 1043205764

The result is Austria, which is the place where I live.

Even though we query the data from two tables with one storing more than 74,000 records, the query only takes about 0.0025 seconds on my machine. That’s because of the indexes we’ve set. Prefixing the keyword EXPLAIN to the last query shows us that MySQL was able to query the data very efficiently.

mysql> EXPLAIN SELECT b.country
    -> FROM ip_ranges a INNER JOIN ip_countries b
    -> ON a.code = b.code
    -> WHERE ip_from  ip_to >= 1043205764 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: a
         type: range
possible_keys: PRIMARY,ip_to,code
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 2322
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: b
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 6
          ref: geoips.a.code
         rows: 1
        Extra:
2 rows in set (0.00 sec)

Finally, we write the User Defined Function, which puts this all together:

DELIMITER $$

DROP FUNCTION IF EXISTS `geoips`.`getCountry`$$
CREATE FUNCTION `geoips`.`getCountry` (pIp CHAR(16)) 
RETURNS VARCHAR(100)
BEGIN
  DECLARE _ip1, _ip2, _ip3, _ip4, _ip_value INT UNSIGNED;
  DECLARE _country VARCHAR(100);

  SELECT substring_index(pIp, '.', 1),
     substring_index(pIp, '.', -3),
     substring_index(pIp, '.', -2),
     substring_index(pIp, '.', -1) INTO _ip1, _ip2, _ip3, _ip4;

  SELECT 16777216 * _ip1 + 65536 * _ip2 + 256 * _ip3 + _ip4 
    INTO _ip_value;

  SELECT b.country
    FROM ip_ranges a INNER JOIN ip_countries b
    ON a.code = b.code
    WHERE ip_from = _ip_value
    INTO _country;

  RETURN _country;

END$$

DELIMITER ;

I used the substring_index function to extract the 4 parts of the IP address. With ‘help substring_index’ we can ask the mysql client to give us an explanation of this function:

mysql> help substring_index;
Name: 'SUBSTRING_INDEX'
Description:
   SUBSTRING_INDEX(str,delim,count)
Returns the substring from string str before count
occurrences of the delimiter delim.
If count is positive, everything to the left of the final 
delimiter (counting from the left) is returned.
If count is negative, everything to the right of the final 
delimiter (counting from the right) is returned.
Examples:
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
        -> 'www.mysql'
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
        -> 'mysql.com'

Finally, a little test whether the getCountry UDF really works:

mysql> SELECT getCountry('62.46.14.132');
+----------------------------+
| getCountry('62.46.14.132') |
+----------------------------+
| Austria                    |
+----------------------------+
1 row in set (0.00 sec)

Great, it does – and that very quickly :-)!