Hi All,

Once I ran into the situation where I have to find out the CityCountry name & location (Longitude, Longitude) based on the IP address. This was not difficult but rather not straight-forward as IP addresses are stored into database table as strings which is very obvious because there are no standard data type which supports IP address as value.

Anyways, so in my database table there are 2 important columns regarding IP address details i.e. Start_IP_Address and End_IP_Address. And mind-well, a range of IP addresses belongs to a country. Currently my database table looks like following: (Below screenshot shows only 5 records)

By looking at the table screen-shot, you can see that searching Country information is not difficult at-all. But how to make it even faster that matters. According to current database table design, I can not search an IP address (which is passed as string parameter) between Start_IP_Address and End_IP_Address which are also string values. Of course we have to do lots of conversion and range-comparing operations. So I did a simple thing which I am going show you using some test data I found on internet.

Step – 1: Create a Table
CREATE TABLE [dbo].[GeoIpData1](
[Start_IP_Number] [varchar](50) NULL,
[End_IP_Number] [varchar](50) NULL,
[Lang] [varchar](50) NULL,
[Lat] [varchar](50) NULL,
[CountryCode] [varchar](50) NULL,
[Country] [varchar](50) NULL
) ON [PRIMARY]
GO


Step – 2: Insert Test Data in Table
On internet you can find lots of information vendors who can provide you data for money as well as free for DEMO purpose for their actual product i.e. data/information. For this demo I am going to use FREE data provided by http://www.maxmind.com/ website. And for following demo, you have to download this CSV file.

You can choose any method you want; to transfer data from CSV file to Database table. You can choose any table name too. Because our motto is to search country information for given IP address in the table. I have used the simplest method i.e. Import/Export Wizard.

Step – 3: Add 2 required Columns in Table
Once the data is available, you will see that Start_IP_Number and End_IP_Number are not just values, but range of IP addresses for a country. So we have to make these values intelligent before we perform any operations like searching or comparing.

So, I have added 2 more columns in table named Start_Full_IP (Decimal(18,0)) and End_Full_IP (Decimal(18,0)) for storing intelligent data about given Start_IP_Number and End_IP_Number respectively.

Alter Table dbo.GeoIpData1
Add Start_Full_IP Decimal(18,0), End_Full_IP Decimal(18,0)
Go

Step – 4 : Create Scalar Function
Then I have created a function named “GetFullIPValue” which takes string IPAddress as parameter and will return me decimal number. Please see the following Function definition.

Create Function [dbo].[GetFullIPValue] (@IPValue varchar(15))
Returns Decimal(15,0)
As
Begin
Declare @p1 char(3), @p2 char(3), @p3 char(3), @p4 char(3)
-- First part of IP
Set @P1 = left(@IPValue, charindex('.', @IPValue)-1)
If(Len(@p1) = 1) Set @p1 = '00' + @p1
Else If(Len(@p1) = 2) Set @p1 = '0' + @p1

Set @IPValue = Right(@IPValue, Len(@IPValue) - charindex('.', @IPValue))

-- Second part of IP
Set @P2 = left(@IPValue, charindex('.', @IPValue)-1)
If(Len(@p2) = 1) Set @p2 = '00' + @p2
If(Len(@p2) = 2) Set @p2 = '0' + @p2

Set @IPValue = Right(@IPValue, Len(@IPValue) - charindex('.', @IPValue))

-- Third part of IP
Set @P3 = left(@IPValue, charindex('.', @IPValue)-1)
If(Len(@p3) = 1) Set @p3 = '00' + @p3
If(Len(@p3) = 2) Set @p3 = '0' + @p3

Set @IPValue = Right(@IPValue, Len(@IPValue) - charindex('.', @IPValue))

-- Forth part of IP
Set @P4 = @IPValue
If(Len(@p4) = 1) Set @p4 = '00' + @p4
If(Len(@p4) = 2) Set @p4 = '0' + @p4

Return(Convert(Decimal(15,0), @p1 + @p2 + @p3 + @P4))
End
Go

Note: This function is used only to fill 2 columns i.e. Start_Full_IP (Decimal(18,0)) and End_Full_IP (Decimal(18,0))

Step – 5 : Update values of 2 Columns in Table

Update dbo.GeoIpData1
Set
Start_Full_IP = [dbo].[GetFullIPValue] (Start_IP_Number),
End_Full_IP = [dbo].[GetFullIPValue] (End_IP_Number)
Go

Note: FYI, for me this operation takes ~20 seconds for updating 160222 records.

Step – 6 : Write a Search Query or Stored Procedure

Declare @SearchIPAddress Decimal(18,0), @KeyIPAddress varchar(20)
Set @KeyIPAddress = ‘000.000.000.000‘
Set @SearchIPAddress = [dbo].[GetFullIPValue] (@KeyIPAddress)

Select
@KeyIPAddress as [IP Address],
Country as [Country],
Lang as [Longitude],
Lat as [Latitude]
From dbo.GeoIpData1
Where @SearchIPAddress between Start_Full_IP and End_Full_IP
Go

And that’s it. Your POC is ready for testing.

Step – 7 : Test Your POC
1. Open the browser and check your IP address. (I used http://www.whatismyip.com/).
2. Provide your IP address in below query (replace the text in blue with your IP address) to find out the details.

As you can see, my IP address belongs to Singapore 🙂

Disclaimer: This article is just a guideline and direction for educational purpose.

For your testing: Apart from this, you can create indexes on Decimal columns and check the performance of queries. That i leave for you.

Over the years, we have been noticing & using few very important indicators like IP addresses, Rational numbers etc. which seek recognition as a standard data type in database industry. I would recommend and hope that Microsoft or other database providers may think about this in their future releases.

Thanks, Khilit