ip2location.com - http://www.ip2location.com


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
Enter IP Address:

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
First IP address in Netblock.
Last IP address in Netblock.
Two-character country code based on ISO 3166.
Country name based on ISO 3166.
Region or state name.
City name.

The actual data in the file looks like:

"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.

Importing a text file into SQL server using WinSQL

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.

Specify SQL to change target fields

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: --> 3232235976
create function IP2INT(@ipAddress varchar(20))
RETURNS bigint

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)

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('') 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)
-- Create index on IPTo
    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))
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)


-- A function returning one string containing
-- Country, region and city for an ip address
create function GetCityFunction(@ipAddress varchar(16))
RETURNS varchar(325)

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)

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 ''

-- If you create a function, you can write a query similar to:
select urlString, referer, GetCityFunction(IPAddress)
from WebLog

This website is owned and maintained by Synametrics Technologies Inc. For questions contact support@synametrics.com
Support | Privacy Policy | Company Info Need to get physical address from an IP? Try IP2Location , powered by IP2Location.com