Mapping IP addresses to locations

Have you ever wondered how some websites are able to determine where you visited from? It all begins with the IP address that you belong to when you connect to the internet. Every time you log onto the internet your ISP assigns an IP address to you. This IP address belongs to a specific range that is allotted to regions around the world.

So how do you find out which IP address belongs to a particular location? There are a couple of ways to do this. Solutions include commercial web services as well as free databases which you can download, import and use.

A number of commercial offerings are available, but I am not in favor of any solution that involves calling a web service or requesting for the data through some other means remotely. This adds an additional latency to the call, and it also means that your service depends on the service of your vendor and for some websites a downtime of such a service is simply not acceptable. On the positive side, you don't have to worry about how you maintain the data if you are making remote calls to get the location data. There are also commercial solutions that offer the entire database to you for a fee. You can then import this data to your favorite database and make calls as necessary. In this article I will concentrate on a free solution instead.

A free solution

It involves downloading a CSV (comma separated value) file, and then loading this data into a persistent store, say a database. This is comparable to commercial products that offer the same. The free CSV is updated on a daily basis, which is good news. Depending on the accuracy of the data that you need, you will have to refresh the data in your tables daily, weekly or monthly. How much data will be added depends on how many organizations/people register new IP addresses. Lets have a look at the format of this CSV file (the download link is available in the references section)

From and To: Hmm? Now what's with these From and To values? An IP address can be converted to a single number. With the IPV4 addressing system, the maximum value of any given value x in x.y.z.a is 255. Let's say we have an IP address 1.2.3.4. To convert this to a number we do the following

1.2.3.4 = (1 * 256 * 256 * 256) + (2 * 256 * 256) + (3 * 256) + 4 = 16909060

The IPV4 system is a 32 bit representation of an IP address. Here is an example illustration of an IP address and its associated bits:

This is what the numbers in the From and To columns represent.

Registered with: The registry that the IP range is associated with. Here are some examples – APCNIC, ARIN, LACNIC, RIPENCC and AFRINIC, IANA

When: Date when this range was assigned in milliseconds.

Country: Two letter and three letter abbreviations as well as the full country name.

Now lets import this data into a database, e.g. PostgreSQL. You can do this with the following command

copy ip_location from 'd:/temp/IpToCountry_withoutID.csv' WITH CSV

This command says to copy the data in the CSV file to the ip_location table (which we have already defined, the structure is available in the reference section). Note that copy is not a SQL standard command – it is specific to PostgreSQL. (Other databases have similar ETL tools available; check the documentation of the one you're using.) Once we have the data, we can write a small program to return the location result, given a IP address. Lets have a look at a code snippet. Here is a utility function to get a location given an IP address.

public void findLocation (String[] args) {
    BigInteger base = new BigInteger("256");
    if (args.length > 0 && args[0] != null && !args[0].equals("")) {
        String ip = args[0];
        String[] numberTokens = ip.split("\\.");
        int power = 3;
        BigInteger bigResult = new BigInteger("0");
        for (String number : numberTokens) {
            BigInteger bigNumber = new BigInteger(number);
            BigInteger raise = raiseToPow(base, power--);
            bigNumber = bigNumber.multiply(raise);
            bigResult = bigResult.add(bigNumber);
        }
        System.out.println("Calculated result for " + ip + " is " + bigResult);
        printLocationData(bigResult);
    } else {
        System.out.println("USAGE: java LocationFinder x.y.z.a");
    }
}

The method printLocationData() connects to the database and fetches the right values to display. The method raiseToPow() is a utility method that simply raises a number to a given power and returns the result. The code splits the IP address into string tokens containing the numbers that we are interested in. It then generates a result which is looked up in the database using the following query:

select * from ip_location where ? > from_address and ? < to_address

The "?" is parameterized with the IP address number that we calculated. If the number falls within a particular range, we have what we want. Here is a sample output that the program threw for a given IP address

Calculated result for 66.92.21.32 is 1113330976

Registered with: ARIN

Country: UNITED STATES

So there you have it – free IP to location conversion at your service.

References

Free CSV database: http://software77.net/geo-ip/

IPV4 addressing: http://en.wikipedia.org/wiki/IP_address

The ip_location table structure: