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:
 

 
Building burcell.org

Welcome to bpurcell.org, the personal homepage of Brandon Purcell. I started building bpurcell.org many, many years ago to share information I ran across everyday in my role as a Support Engineer and Consultant for Macromedia/Adobe. As a support engineer and consultant I always had a lot to blog about but as I moved into managment roles at Adobe my technical content dwindled.

I currently work as the Director of Technology for Universal Mind. My primary responsiblity is managing the SpatialKey project and it has been an amazing experience. I have been spending a lot of time working with Amazon Ec2 and will share my experiences through the blog in the future. I truly believe that Cloud Computing is the IT platform of the future and we have built the SpatialKey architecture on top of Ec2.

Viewing Individual Entry / Main
February 7, 2006

I recently was encountered with a problem where I needed to find what location a user was logging in from based on their IP. There were several reasons why such a system was needed that I highlight below.

Benefits of building such a system

  1. Targeting fraudulent users that share their accounts
  2. Reducing credit card fraud
  3. Display native language and currency
  4. Redirect web pages based on geographical region
  5. Filter access from countries you do not do business with
  6. Geographical targeting for in house ad serving

I first started looking for third party solutions that could be accessed through a webservice but quickly arrived at IP2Location.com. IP2Location offers a downloadable CSV database that you can run on your own system. The cost is not significant depending on the amount of data you need. They offer IP-Country-Region-City-Latitude-Longitude-ZIPCode-ISP-Domain Database if you need that level of information.   Anyways&on to the technical part.

The CSV format that is provided is simple to import into any database, I was using MS SQL server and imported the data using DTS. There are over 4 million rows in the table so it does not make sense to query directly against the data that was imported for performance reasons. Instead to make the queries very quick that return location data I split the main table out into 20 tables, this left 200,000 rows in each table and proved to scale much better. Since IP2Location offers regular updates I wanted to be able to handle the update process and splitting of the data with very little work on my part. To do that I created two stored procedures.

1. IP2RangeTable The first stored procedure looks at the main table containing 4 million records and divides it into 20 different ranges. Those ranges are then stored in a range table containing the start, end and table name that will store the data.
2. The second stored procedure uses the IP2RangeTable created previously and bulk inserts the range of data from the main table into the range tables. They were simply called range1 - range20.

Both of the stored procedures were written in such a way that the data could be broken out into X number of tables. It just takes the number of tables as a parameter. In this case I used 20. The further you break it out the better it will perform.

Next, I created a UDF in ColdFusion (could be done in a stored proc as well) that takes an IP as an argument. The IP is converted to an IP Number. IP Numbers are used in the database to store the IP2Location content. An example for conversion follows.

IP Number = 16777216*w + 65536*x + 256*y + z     (1)
where
IP Address = w.x.y.z

For example, if IP address is "202.186.13.4", then its IP Number "3401190660" is based on the formula (1).

IP Address = 202.186.13.4

So, w = 202, x = 186, y = 13 and z = 4

IP Number = 16777216*202 + 65536*186 + 256*13 + 4
          = 3388997632 + 12189696 + 3328 + 4
          = 3401190660 looks at the IP2Range

Once we have the IP number we need to find what table the record is stored in within the range1-rangeX tables. A query retrieves the values stored in IPRangeTable, from that data we can find the correct range table. Next a query is used against the range table to retrieve the location data.

This may sound complicated but it is really simple. Unlimately you could just query the original table that you imported from the CSV but I found the queries to take way to long and caused a lot of stress on the db server under load. Even with the right indexes in place. By breaking the data out into range tables you increase the scalablity exponentially.

Let me know if you have any other questions about this process and the benefits that the data provides.

Later I will explain how the event gateway was used to log this information for every user that logged into the site as a background process.

Comments

when you get below country level, your political boundary location data becomes fairly "iffy". adding geographic location just compounds the uncertainty.

our geoLocator CFC has been out for some time now: http://www.sustainablegis.com/projects/geolocator/


Brandon, I'm interested in your stored procedure to break down the IP2Location database in multiple sets. I would love to use it to improve the performance on my lookups.

I have a site - VisitorXM - that allows users to embed two lines of javascript on their pages so they can track their users.

A sample report can be found here: http://www.visitorxm.com/report.cfm?uid=F401E11E-5F00-4A9B-AABD-31B4AC0ED51C

Please let me know if you will be releasing the stored procedure that breaks the IP2Location database into multiple sets.


I personal have used this ip to country database in the past. The way I did it through was to use one big table and then use views and a function to retrieve the information from the database.

The benefit of this approach is that you can import the entire csv in one shot. Also since I was using a function to link all the views I could determine which view to hit by ipaddress. In the end, the performance was excellent.

Now adays though I use the Java geolocator written by Nigel and Doug. It's WAY faster than any database solution you can find and removed TONS of load off your database server. I could never understood why I would want to hit my DB server so many times.

Also if you dowload the PERL version of the app, you can compile a new version of the IP to country DB when ever you want. It takes about an hour to compile, but if you want to make sure that you have the latest info, then this is the ticket.


Brandon, would you mind sharing the stored procedures with me (by email)? I've been refreshing this page in hopes of them showing up, but nothing yet :(

I'm busy with the same database but my stored procedure building skills are a bit rusty, I tried doing it all in coldfusion but that's taking forever...

thanks


Brandon, would you mind sharing the stored procedures with me (by email)? I've been refreshing this page in hopes of them showing up, but nothing yet :(

I'm busy with the same database but my stored procedure building skills are a bit rusty, I tried doing it all in coldfusion but that's taking forever...

thanks


Give me some time to clean them up and I will post the stored procs and documentation.


Hey brandon - any updates yet?


IP2Country is amazing. I found this: http://www.ip-adress.com

But they seem to display the location of the isp, i guess.


 

Calendar
SunMonTueWedThuFriSat
  1 2 3 4 5 6
7 8 9 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28 29 30 31      

Subject Archives
Amazon EC2 (15)
ColdFusionMX (155)
Corvette (3)
Flash Lite (4)
Flash Media Server (5)
Flash Player (3)
Flex (39)
General Web Dev (14)
HDTV (3)
Jboss (1)
Jquery (2)
JRun (59)
Max 2003 (3)
Other (33)
PC Hardware (16)
Software (17)
SpatialKey (7)
Wireless (8)
Working Out (1)

Search
RSS Feed
Feed Listing

www.flickr.com
Corvette - October 2005 brandonpurcell's Corvette - October 2005 photoset
Macromedia ColdFusion MX

Featured Links
Stock Footage
Page Render Time:219 user:""