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]

Further reading:

* http://www.wipmania.com/
* http://www.wipmania.com/en/blog/database-in-sql-and-ip-numbers/