Determine Country from IP Address
The web has no national boundaries, and most websites will attract
visitors from all around the globe.
I needed to be able to determine the country location of each
visitor to a site. Although you can never expect to achieve a 100%
correct answer, you can get close.
A client of mine wanted to provide an free incentive for every
visitor who landed on a particular page of his site. He knew from
his web stats that at least 80% of site visitors were non UK.
As all his business, video production, was UK based, he didn't
want the expense of providing the free CD showreel to overseas
visitors.
I needed a database that maps IP addresses to a country. There
are several sources to choose from, I used IP2Location
which costs $49 and includes free updates for a year. Data is provided
in Access, CSV and MySQL formats. This how the first few rows of
the data looks
| ipFROM |
ipTO |
countrySHORT |
| 0 |
33996343 |
- |
| 33996344 |
33996351 |
UK |
| 33996352 |
50331647 |
- |
| 50331648 |
67277055 |
US |
| 67277056 |
67277119 |
CA |
The total number records is about 60,000. The database is constantly
updated.
I used a Access database on the server as it was simple to use,
there was no setup unlike SQL server and with less than 100 visitors
per day there was no performance concerns.
The code, written in VB ASP, used ADO to perform the database
lookup. Here is the code I use
' This sample code is provided as is,
for aiding understanding.
' I make no warranty about its fitness for purpose.
' If you find it useful please use it.
' Don't expect me to sort out any problems unless you are
prepared to pay me.
' Written by Paul Stanley Software http://www.pssuk.com
' Set IP string, 1st check for proxy
sIP = Request.ServerVariables("HTTP_X_FORWARDED_FOR")
if sIP = "" then
sIP = Request.ServerVariables("REMOTE_ADDR")
end if
' Handle extended IP addresses
nComma = Instr(sIP, ",")
if nComma > 0 then
sIP = Left(sIP, nComma - 1)
end if
set oConnIP = CreateObject("ADODB.Connection")
sConn = "Driver={Microsoft Access Driver (*.mdb)}; Dbq="
sConn = sConn & Server.MapPath(".") & "\IPCountry.mdb"
sConn = sConn & "; Uid=admin; Pwd="
oConnIP.Open sConn
set oRSip = CreateObject("ADODB.RecordSet")
oRSip.ActiveConnection = oConnIP
oRSip.CursorLocation = adUseClient
nIP = Dot2LongIP(sIP)
sSQL = "SELECT [CountryShort] FROM [IPCOUNTRY] WHERE
" & CStr(nIP) & " BETWEEN [ipFrom] AND [ipTo];"
oRSip.Open sSQL, oConnIP, adOpenForwardOnly, adLockReadOnly
sCountry = oRSip("CountryShort")
function Dot2LongIP (ByVal DottedIP)
Dim i, pos, PrevPos, num
' Convert IP String like "1.12.124.22" to 32 bit
integer
if len(DottedIP) < 7 or isnull(DottedIP) or DottedIP =
"unknown" then
Dot2LongIP = 0
else
PrevPos = 0
for i = 1 To 4
pos = InStr(PrevPos
+ 1, DottedIP, ".", 1)
if pos = 0
then
If
i = 4 Then
pos
= Len(DottedIP) + 1
else
Dot2LongIP
= 0
exit
function
end
If
end if
num = Int(Mid(DottedIP,
PrevPos + 1, pos - PrevPos - 1))
PrevPos =
pos
Dot2LongIP
= ((num Mod 256) * (256 ^ (4 - i))) + Dot2LongIP
next
end If
end function
|
There are many other reasons why you might it is useful to determine
the country location of your site visitor:
Web log statistics and analysis
Providing localised contact information
Help identify possible credit card fraud
