Wednesday, February 27, 2008

Ip2Country Mapping

I was working on a web application that required mapping every request to its corresponding country in order to use this data later in site statistics

i searched over the internet for a solution that can do this mapping for me, i found a lot of solutions that are great to the extent that they can also get the ISP and geographical location of the request but MOST OF THEM WERE NOT FREE

Since i was only looking for a simple solution that simply maps ip to corresponding country, i searched for free versions of these solutions which are limited but free

i found this Website that offered a nice & easy solution for this issue. Although, this website has a very nice tutorial but i will explain what i did because i modified the steps provided by the website somehow

Just do as follows

1. Download the CSV file that contain all mapping details

2. Create a new table in your database that should carries all data needed to do
this mapping, you will needs these columns
* begin_num - FLOAT
* end_num - FLOAT
These are two magical numbers that will be used later in
our mapping

* ISO - VARCHAR(2)
* Name - VARCHAR(255)

2. Import CSV file into that table but, remember to choose only 3rd, 4th, 5th, and 6th values while importing - neglect 1st and 2nd one because they are useless in our mapping

3. On every request, apply this formula on the IP as follows

ipnum = 16777216*w + 65536*x + 256*y + z

where

IP Address = w.x.y.z


4. Use ipnum to retrieve corresponding ISO or name of country through this simple query

SELECT ((required-columns)) FROM ((table-name)) WHERE ((ipnum)) BETWEEN begin_num AND end_num


That's it, you can now map any request to the corresponding country with an accuracy reaching 98% as stated by this website

Note:
1. This query may return nothing if the IP can't be mapped and this will happen especially when using localhost
2. Always index begin_num and end_num for better performance
3. Always update your CSV file because it is updated every now and then on the website so keep an eye on the website to get updates

No comments: