IP-based Geolocation with WIP-Mania

Posted:	2009-03-11 20:01
Tags:	Web, Business, PostgreSQL, Hosting, MySQL

There are quite a few online services which can determine a geographic location from an IP address. Even Google Gears now provides the functionality. Most of the web-based APIs have restrictions on the number of API calls which can be made per day so I needed a solution which I could host myself. I only need country based lookups, not specific cities so I was able to use WIP-mania which provides a 460KB zipped SQL file of IP address ranges and their corresponding countries. In this post I'll show you how to import them into a PostgreSQL database and then write a Python API to lookup IPs.

First you need to download the data. It is updated every two months and can be obtained from http://www.wipmania.com/en/base/ under a Creative Commons license which allows commercial use as long as you don't set up a web-based API lookup service. Download the zipped SQL version.

Unzip the code (the zip file doesn't create a directory so it is best to create your own):

mkdir worldip
cd worldip
wget http://static.wipmania.com/static/worldip.sql.zip
unzip worldip.sql.zip

The archive contains the main IP address table in worldip.sql and a set four versions of a table to provide country name lookups against country code, one in English, one in German one in Russian and one in Spanish. I'd like the English one so I use worldip.lands.en.sql.

The SQL files are designed to be used with MySQL so you'll need to tweak them to work with PostgreSQL.

First edit worldip.sql so that these lines at the top:

DROP TABLE IF EXISTS `worldip`;
CREATE TABLE `worldip` (
  `start` int(10) UNSIGNED NOT NULL default '0',
  `end` int(10) UNSIGNED NOT NULL default '0',
  `code` varchar(2) NOT NULL default '',
  PRIMARY KEY (`start`,`end`)
) ENGINE=MyISAM;

LOCK TABLES `worldip` WRITE;

Are replaced with the simpler:

-- DROP TABLE worldip;

CREATE TABLE worldip (
  start_ip BIGINT NOT NULL,
  end_ip BIGINT NOT NULL,
  code CHAR(2) NOT NULL,
  PRIMARY KEY (start_ip, end_ip)
);

Then you need to modify every other line in the file so that it begins with this:

INSERT INTO worldip (start_ip, end_ip, code) VALUES

and ends with a ;. This is because PostgreSQL doesn't support inserting multiple rows at once in the same way MySQL does.

Remove the UNLOCK statement at the end.

I've renamed the columns start_ip and end_ip so that there is no confusion with the pg/sql END keyword. You should also remove the UNLOCK line from the very end of the file.

You can load the data into a database called dcm-dev like this:

$ psql dcm-dev -f worldip.sql
DROP TABLE
psql:worldip.sql:8: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "worldip_pkey" for table "worldip"
CREATE TABLE
INSERT 0 58143

Next edit worldip.lands.en.sql so the top of the file looks like this:

-- DROP TABLE worldip_land;

SET CLIENT_ENCODING TO 'utf8';

CREATE TABLE worldip_land (
  code CHAR(2) NOT NULL,
  country VARCHAR(255) NOT NULL,
  PRIMARY KEY (code)
);

As before you need to modify every other line in the file so that it begins with this:

INSERT INTO worldip_land (code, country) VALUES

and ends with a ;. i

You can then load the data like this:

$ psql dcm-dev -f worldip.lands.en.sql
psql:worldip.lands.en.sql:1: ERROR:  table "worldip_land" does not exist
SET
psql:worldip.lands.en.sql:9: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "worldip_land_pkey" for table "worldip_land"
CREATE TABLE
INSERT 0 238

If you later want to re-insert the data from the two files you will have to uncomment the DROP TABLE lines before running the same two commands.

To use the data from Python you need this function which converts an IP address string into an integer:

def ip_number(ip):
    a, b, c, d = ip.split('.')
    return (16777216*int(a)) + (65536*int(b)) + (256*int(c)) + int(d)

You can now lookup a country code against the database like this:

ip = ip_number('84.9.42.195')
cursor = connection.cursor()
cursor.execute(
    """
    SELECT code
    FROM worldip
    WHERE
        start_ip<=%s
        AND
        end_ip>=%s
    """,
    (ip, ip)
)
rows = cursor.fetchall()
code = rows[0][0]

You can lookup a country like this:

cursor = connection.cursor()
cursor.execute(
    """
    SELECT country
    FROM worldip_land
    WHERE code=%s
    """,
    (code,)
)
rows = cursor.fetchall()
country = rows[0][0]