Country from IP

In a web application, it’s often useful to be able to find out the geographic location of your visitors. For instance, you could use that data to either render the page in a language native to that geography or you could supply user with geographically relevant information. (Google for instance redirects you to google.co.in if you are coming from an IP that belongs to an ISP in India). There are of course caveats – the user might be coming from behind a proxy server which is different from his actual geography. (Often happens to me at work!). Worse still the user could be traveling and checking your website from a cybercafé in Thailand. There are ways you can handle these exceptions gracefully but for now let’s focus on finding user’s country given his IP.

First of all you need to get hold of the IP to Country database. You can obtain it in CSV format from: http://ip-to-country.webhosting.info/ (thanks Anil). The database is updated regularly and you should check for updates every month or so. The database contains a series of IP ranges and the country corresponding to each range. You can import it (DTS) into SQL Server. Your table could look something like:

CREATE TABLE [pubs].[dbo].[ip-to-country] (
[StartIP] bigint NULL, 
[EndIP] bigint NULL, 
[CountryCode] varchar (2) NULL, 
[CountryCodeThreeLetter] varchar (3) NULL, 
[CountryName] varchar (100) NULL
)

Note that we are using bigint data type for the IP data since the IP addresses in the csv file are not in the dotted decimal notation. Once you have the data in this table, you can simply use the stored procedure below to find the country corresponding to an IP (ok so this is not the most elegant piece of t-sql you’ll see but then t-sql has never been my strong point):

create function dbo.IPToCountry (@ip as varchar(15))
returns varchar(30)
as
  begin
 declare @country varchar(30)

 --declare @ip as varchar(15)
 --set @ip = '202.54.0.1'
 
 declare @count1 as int
 declare @count2 as int
 
 set @count1 = 0
 set @count2 = 0
 
 declare @value as bigint
 declare @tempvalue as bigint
 
 set @count1 = charindex('.',@ip)
 
 set @tempvalue = substring(@ip,1,@count1-1)
 set @value = @tempvalue * power(2,24)
 
 set @count2 = @count1 + 1
 set @count1 = charindex('.',@ip,@count2)
 
 set @tempvalue = substring(@ip,@count2,@count1-@count2)
 set @value = @value + (@tempvalue * power(2,16))
 
 set @count2 = @count1 + 1
 set @count1 = charindex('.',@ip,@count2)
 
 set @tempvalue = substring(@ip,@count2,@count1-@count2)
 set @value = @value + (@tempvalue * power(2,8))
 
 set @count2 = @count1 + 1
 
 set @tempvalue = substring(@ip,@count2,1+len(@ip)-@count2 )
 set @value = @value + (@tempvalue * power(2,0))
 
 --select @value
 
 select 
  @country=CountryName
 from
  [ip-to-country]
 where
  @value >=StartIP and @value <=EndIP

 return @country
  end
go

Sometime later I’ll post a quick console application that allows you to lookup country from IP without using a database in the backend.

VSTO Workshop

You can find the code snippets used during the VSTO workshop in Bangalore on Saturday here. A few of you asked about the complete process by which an office document loads an assembly and executes it. This slide sheds light on it: