Site Contents
Home
My Blog
ColdFusion
JRun
Flex
Wireless & Networking
Hardware & Gadgets
HDTV
Software Picks
Pictures
Contact Me
Search


My Articles & Papers
Flex Performance
Server/Client
mm.com Under the Hood
Multiple Instances of CFMX
Multiple Instance Config
NLB with ColdFusion
Clustering CFMX for J2EE
Multi-Tier Hardware LB w CFMX
Cisco CSS & Coldfusion MX
JRun 4 Jini based Clustering
WiFi Growth

2ID Tacweb

Other Hobbys
Body-For-Life Challenge

Personal Projects
Family Pool
Deck Gate

username:
password:
 

 
How To: Obtaining a users geographical location based on their IP with IP2Location and ColdFusion

Please post any comments to the following blog posting: http://www.bpurcell.org/blog/index.cfm?mode=entry&entry=1079

In a previous blog posting I discussed how to obtain a users geographical location based on their IP with IP2Location and ColdFusion. In this article I explain how to import the data, break the data into multiple tables for optimization, then create the UDF to perform the lookup.

<cfset qLocData=ipLookup("68.142.226.39")>
<cfdump var="#qLocData#">

Although the original IP2Location data is stored in a 4 million row table it does not make sense to query this large of a table since it a very expensive operation. Instead we break the table into 20 different tables with the steps below. There is also another table that contains the ranges of data that are held within each table. It is worth noting that the below process may need tweaking depending on the version of IP2Location that you purchase. This process is based on  the IP2Location IP-Country-Region-City-Latitude-Longitude-ISP Database that currently sells for $499.

Tables:
IPCITYLATLONGISP : table containing the entire import from the Ip2location data
IPRangeDirectory : contains the range of data stored in the range tables
range1 - range20 : 20 tables containing the IP2location data

Create the table for the import of the IP2Location data

CREATE TABLE [dbo].[IPCITYLATLONGISP] (
[ipFROM] [float] NOT NULL ,
[ipTO] [float] NOT NULL ,
[countrySHORT] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[countryLONG] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ipREGION] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ipCITY] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ipLATITUDE] [float] ,
[ipLONGITUDE] [float] ,
[ipISP] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) 

Create the range tables, in my example I used 20 tables range1-range20, you will have to run this 20 times and change the name or somehow script it. This is a onetime thing so it is not a big deal. The stored procedures will automate the splitting of the data in the future

CREATE TABLE [dbo].[Range1] (
[ipFROM] [float] NOT NULL ,
[ipTO] [float] NOT NULL ,
[countrySHORT] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[countryLONG] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ipREGION] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ipCITY] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ipLATITUDE] [float] NULL ,
[ipLONGITUDE] [float] NULL ,
[ipISP] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
)

Create the table where the ranges are stored

CREATE TABLE [dbo].[IPRangeDirectory] (
[TableName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[rangeStart] [float] NULL ,
[rangeEnd] [float] NULL 
)


Stored procedures - you can download the stored procedures here I figured out how to write the SQL based on looking at other stored procs so they work but are definetily not pretty.
procIP2location_buildRanges : divides up the data from IPCITYLATLONGISP into 20 ranges and inserts the ranges.
procIP2location_splitdata : takes the data from IPRangeDirectory and updates the range1-range20 tables.

Steps for the update or initially loading the tables

  1. Truncate table IPCITYLATLONGISP
  2. After downloading the update from ip2location.com unzip it and use DTS within the SQL Enterprise Manager to import the text file into the table.  . For a source choose a Text file, it will automatically detect the delimiters in the file. The import takes 3-5 minutes as there are 4+ million rows of data.
  3. exec procIP2location_buildRanges (this will take a while, it creates the ranges in the IPRangeDirectory table) this takes around 1min 30sec. note: if you use more than 20 tables then you will need to modify the @totalTables=20 within the stored procedure.
  4. exec procIP2location_splitdata: takes the data from IPRangeDirectory and updates the range1-range20 table from the IPCITYLATLONGISP table. 

Integration with ColdFusion:
Once you have the data split up and ready to go you will need the ColdFusion code that will be used in your site. The easiest way to do this is to create a UDF that can be used with a single line of code anywhere in your application.

The function first converts the IP address to an IP number, the IP number is used for the search. Next we do a lookup on the IPRangeDirectory table to find which table contains the data. I cached this data and do the lookup in CF. Next we go to the rangeX table and find the data and return it as a query.

<cffunction name="ipLookup" output="false" returntype="query" displayname="ipLookup()">
 <cfargument name="IP" type="string" required="no" default="#CGI.REMOTE_ADDR#" displayname="IP Address">
  
 <cfscript>
  var i=1;
  var ipnumber = 0;
  var rangetable = 1;
  //convert the IP address to an IP number to be used in the search
  ipnumber = 16777216 * listFirst(arguments.ip, '.') + 65536 * listGetAt(arguments.ip, 2, '.') + 
                                256 * listGetAt(arguments.ip, 3, '.') + listLast(arguments.ip, '.');
 </cfscript>
 
 <!--- we need to retrieve the ranges first (this query should be cached)--->
 <cfquery datasource="ip2location" name="getRanges" cachedwithin="#CreateTimeSpan(0,1,0,0)#>"> 
  SELECT     TableName, rangeStart, rangeEnd
  FROM         IPRangeDirectory
 </cfquery>
 <!--- find which table the data is in from the range table --->
 <cfloop query="getRanges">
  <cfif ( ipnumber gte rangeStart and ipnumber lte rangeEnd )>
   <cfset rangetable=i>
   <cfbreak>
  </cfif>
  <cfset i=i+1>
 </cfloop>
 
 <!--- <cfoutput>rangetable=#rangeTable#</cfoutput> debugging to make sure it retuns the right table--->
 
 <!--- if the data is not in the IP2Location db then we want to return an empty record
 (can do caching here as well)---> 
 <cfquery datasource="ip2location" name="getlocation"> 
  SET ROWCOUNT 1
  SELECT COUNTRYLONG,COUNTRYSHORT, IPCITY, IPFROM, IPISP, IPLATITUDE, IPLONGITUDE, IPREGION, IPTO
  FROM Range#rangetable#
  WHERE #ipnumber# BETWEEN ipFrom AND ipTo
  SET ROWCOUNT 0 
 </cfquery>
 <cfreturn getlocation>
</cffunction>