Extracting Country from IP Address

Posted by admin

April 12, 2008

There are many services, both Free & Commercial, which provide you the geographic data of the visitors to your website. We will however use the free IP-Country database provided by
http://ip-to-country.webhosting.info/.

Download the CSV database here http://ip-to-country.webhosting.info/downloads/ip-to-country.csv.zip

Database can be imported into other databases like MS-Access, MS-SQL, MySQL, etc. Since we will be using ASP & MS-Access, we will thus download this csv database & import it into an MS-Access database.
The MS-Access database containing the records from the downloaded csv database is attached with this tutorial. The full code used in this tutorial is at the end.

So after importing the CSV file into an MS-Access database, we name the database as ip2country & the table as ip2c.
Now, there are 5 columns in our table ip2c, namely, ip_from, ip_to, co_code2, co_code3 & co_name.
Instead of storing IP Address, which are in format xxx.xxx.xxx.xxx, this database has IP number ranges. The ip_from marks the beginning of range & ip_to ends it. The co_code2 column has the 2Digit country code & co_code3 has 3Digit Country Code. The column co_name has the full country name.

ASPFirstly, we must know how to get the IP Address of the Visitor. To do that, our code will be ;

1
2
3

&lt;%
strIP = Request.ServerVariables("HTTP_REMOTE_ADDR")
%&gt;

The ServerVariables collection contains the combination of values that represent the HTTP headers sent from the visitor’s browser to our server. By passing the ” HTTP_REMOTE_ADDR” parameter to it, we request the IP Address of the visitor.
Through this, we will get an IP Address in the format xxx.xxx.xxx.xxx which may look like 108.89.128.147
But since our database have IP Numbers instead of IP Addresses, we will have to convert the IP Address into an IP Number.
To convert an IP Address into an IP Number, the formula is
A * (256*256*256) + B * (256*256) + C * 256 + D
for the IP Address A.B.C.D
So, for ease of things, we can use the following function to get an IP Number of an IP Address.

&lt;%
Private Function ipAd2ipNum(ipA)
strO = ipA
pos1 = InStr(strO, ".")
intA = CInt(Left(strO, (pos1-1)))
strO2 = Mid(strO, pos1+1, len(strO))
pos2 = InStr(strO2, ".")
intB = CInt(Left(strO2, (pos2-1)))
strO3 = Mid(strO2, pos2+1, len(strO2))
pos3 = InStr(strO3, ".")
intC = CInt(Left(strO3, (pos3-1)))
intD = CInt(Mid(strO3, pos3+1, len(strO3)))
intConvert = (intA*(256*256*256)) + (intB*(256*256)) + (intC*256) + intD
ipAd2ipNum = Trim(intConvert)
End Function
%&gt;

so, we can use this function as

&lt;%
strIP = Request.ServerVariables("HTTP_REMOTE_ADDR")
strIPN = ipAd2ipNum(strIP)
%&gt;

Thus we will get the IP Number of our IP Address. Now we are ready to query our ip2country database.

Running the Query

As we’ve got the IP Number of the visitor’s IP Address, we will now query the ip2country database to find out the Country of the visitor.

&lt;%
strConString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source="
strConString = strConString &amp; Server.MapPath("ip2country.mdb")
 
Set objCon = Server.CreateObject("ADODB.Connection")
objCon.Open strConString
%&gt;
&lt;%
strSQL = "select co_name from ip2c where ip_from&lt;=" &amp; strIPN
strSQL = strSQL &amp; "and ip_to&gt;=" &amp; strIPN
 
Set objRs = Server.CreateObject("ADODB.Recordset")
objRs.Open strSQL,objCon
%&gt;
&lt;%
If NOT(objRs.EOF) OR NOT(objRs.BOF) Then
strCountry = objRs.Fields("co_name")
%&gt;
Your IP Address is :- &lt;%=strIP%&gt;
Your Country is :- &lt;%=strCountry%&gt;
&lt;%
Else
%&gt;
Your IP Address is :- &lt;%=strIP%&gt;
Your Country is :- <strong>unlisted</strong>
&lt;%
End If
 
objRs.Close
objCon.Close
Set objRs = nothing
Set objCon = nothing
Set strSQL = nothing
%&gt;

Share and Enjoy: These icons link to social bookmarking sites where readers can share and discover new web pages.