IP2Location
|
We sincerely thank IP2Location for allowing us
to use their database on this site. IP2Location is a company that provides mapping between an IP address
and a physical location. They offer several databases proving information like country, city, zip code,
latitude and longitude and weather for a given IP address.
For more information visit http://www.ip2location.com.
The database from IP2Location is available in two different formats:
- Binary File - You will need an API from IP2Location to read this file
- CSV File - comma separated values
|
Live Demo
Note: This demo uses
the same technique as described in this article to fetch location based on an IP address.
|
Usage example
The remainder of this article talks about using the database from IP2Location with a relational database. In the following example, we
will be using:
Step 1 - Downloading the CSV file
Visit IP2Location to download the CSV file. This file is
in the following format.
|
|
Field Number
|
Field Name
|
Field Size
|
Field Description
|
1
|
IP_FROM
|
numerical
|
First IP address in Netblock.
|
2
|
IP_TO
|
numerical
|
Last IP address in Netblock.
|
3
|
COUNTRY_CODE
|
char(2)
|
Two-character country code based on ISO 3166.
|
4
|
COUNTRY_NAME
|
vchar(64)
|
Country name based on ISO 3166.
|
5
|
REGION
|
vchar(128)
|
Region or state name.
|
6
|
CITY
|
vchar(128)
|
City name.
|
|
|
| |
The actual data in the file looks like:
"IPFROM","IPTO","COUNTRYSHORT","COUNTRYLONG","REGION","CITY"
"67297904","67297911","US","UNITED STATES","MASSACHUSETTS","BEDFORD"
"67297912","67297919","US","UNITED STATES","TEXAS","FLOWER MOUND"
"67297920","67297927","US","UNITED STATES","TENNESSEE","MEMPHIS"
Step 2 - Importing into the database
The easiest way to import a .CSV (comma separated value) file into any database is to drag a file from any folder to
WinSQL's catalog window. Click here if you are not familiar with WinSQL.
Although I am using Microsoft SQL Server in this example, WinSQL can be used to import/export data from any database using
simple mouse clicks.
If you run into problems or need more information on how to import this file into SQL Server,
click here
to watch a video tutorial.
During the import process WinSQL will prompt you to change field names and datatypes for the target table. It is important
you use bigint instead of integer when creating a new table. This is because a normal integer is not large
enough to hold an IP address.
Once the data has been imported, you will have a table with the following script in the database.
create table ip2Location(
IPFrom bigint,
IPTo bigint,
CCode char(2),
CName varchar(68),
Region varchar(128),
City varchar(128)
)
Step 3 - Quering the database
The newly imported table contains IP addresses in a numerical format, which is different from the more commonly used string format.
The best way to convert the string representation of an IP address to a numeric value is to
write a function in SQL Server. The following script shows a function that accomplishes this task.
-- Converts a string based IP to Integer
-- For example: 192.168.1.200 --> 3232235976
create function IP2INT(@ipAddress varchar(20))
RETURNS bigint
AS
BEGIN
DECLARE @FirstDot int, @SecondDot int, @ThirdDot int,
@FirstOctet varchar(3), @SecondOctet varchar(3),
@ThirdOctet varchar(3), @FourthOctet varchar(3),
@Result bigint
SET @FirstDot = CHARINDEX('.', @ipAddress)
SET @SecondDot = CHARINDEX('.', @ipAddress, @FirstDot + 1)
SET @ThirdDot = CHARINDEX('.', @ipAddress, @SecondDot + 1)
SET @FirstOctet = SUBSTRING(@ipAddress, 1, @FirstDot - 1)
SET @SecondOctet = SUBSTRING(@ipAddress, @FirstDot + 1, @SecondDot - @FirstDot - 1)
SET @ThirdOctet = SUBSTRING(@ipAddress, @SecondDot + 1, @ThirdDot - @SecondDot - 1)
SET @FourthOctet = SUBSTRING(@ipAddress, @ThirdDot + 1, 3)
SET @Result = 16777216 * CAST(@FirstOctet as bigint) +
65536 * CAST(@SecondOctet as bigint) +
256 * CAST(@ThirdOctet as bigint) +
CAST(@FourthOctet as bigint)
RETURN(@Result);
END
Fetching data from the database
The easiest way to fetch the location of an IP address is the write the following query:
-- Simple but not very efficient query. May take a long
-- time to run.
select *
from ip2Location
where dbo.IP2INT('209.178.205.30') between IPFrom and IPTo
There is a significant problem with this query: it runs very slow. The machine we ran this query on took about almost 1 minute to
run. This is because the table contains almost 3 million entries
and the SELECT query above does not use any index. In fact, it cannot use any index.
Optimizing the SQL to run faster
The first step in optimization is to create a couple of indexes. The following script will create two indexes on the table, one on each field.
-- Create index on IPFrom
CREATE INDEX start_idx
ON dbo.ip2Location(IPFrom)
go
-- Create index on IPTo
CREATE INDEX end_idx
ON dbo.ip2Location(IPTo)
Next, we have to re-write the SELECT statement so it uses the newly created index. One important information to keep in mind
is that field names should always appear on the LHS (left hand side) of the equal sign. When field names appear on the RHS,
most RDBMS won't be able to use an index.
-- The two embedded SELECT statements
-- will use the newly created indexes to
-- pull one IPFrom and one IPTo value
select *
from dbo.ip2Location
where IPFrom = (
select max(IPFrom)
from dbo.ip2Location
where IPFrom <= dbo.ip2int(@ipAddress)
-- Using IPFrom on the LHS will cause
-- SQL Server to use an index
)
and IPTo = (
select min(IPTo)
from dbo.ip2Location
where IPTo >= dbo.ip2int(@ipAddress)
)
The above query will now run significantly faster. In our testing, the response time reduced from 1 minute to about 68 milliseconds.
Making it even better
Optionally, you can create either a stored procedure or a function to make life even easier. The following script show how to
write them.
-- A stored procedure that returns every field
-- for an IP address
create procedure GetCity(@ipAddress varchar(16))
as
begin
select *
from dbo.ip2Location
where IPFrom = (
select max(IPFrom)
from dbo.ip2Location
where IPFrom <= dbo.ip2int(@ipAddress)
)
and IPTo = (
select min(IPTo)
from dbo.ip2Location
where IPTo >= dbo.ip2int(@ipAddress)
)
end
go
-- A function returning one string containing
-- Country, region and city for an ip address
create function GetCityFunction(@ipAddress varchar(16))
RETURNS varchar(325)
as
begin
DECLARE @FinalAnswer varchar(330)
select @FinalAnswer = CName + ', ' + Region + ', ' + City
from dbo.ip2Location
where IPFrom = (
select max(IPFrom)
from dbo.ip2Location
where IPFrom <= dbo.ip2int(@ipAddress)
)
and IPTo = (
select min(IPTo)
from dbo.ip2Location
where IPTo >= dbo.ip2int(@ipAddress)
)
RETURN (@FinalAnswer)
end
Once a stored procedure is created, you can simply type the following to get the location of an IP address.
-- Fetch IP address using a stored procedure
exec GetCity '218.111.3.212'
-- If you create a function, you can write a query similar to:
select urlString, referer, GetCityFunction(IPAddress)
from WebLog
|
