Solved

SQL GEO IP lookup, compare and redirect

Posted on 2007-04-04
6
706 Views
Last Modified: 2012-06-22
SQL IP lookup and compare
I'm using IIS 6 and SQL 2000

I need to create a small asp script which detects the closest server and performs a response.redirect based on an existing GeoIP table. So I have 2 tables in SQL, IPTable and serverInventory. When end user connect I call HTTP server variables to request the end users IP address. I then compare the end users IP address to my stored IPtable and determine which of server in my inventory is closest to the end user.

I know that many IP tables are not 100% accurate and I'm ok with that. I also realize that I'll probably need to transform all my ips into long integers.  Can someone explain how the actual comparison would work and perhaps provide a SQL example of how this is done. Also any ideas about existing systems that are out there to accomplish similar tasks would be worth learning about. Obviously we need to boil it down to some sort of geographic measurement at some point. So what's the best way to go? By country? By postal code?

Your help is appreciated.
If anyone wants to build this for me and save me the pain I'm happy to pay them.
thx

0
Comment
Question by:matthewc02
  • 4
  • 2
6 Comments
 

Author Comment

by:matthewc02
Comment Utility
Is it faster to do the initial IP dotted-to-long conversion in asp on the web server or pass the dotted ip to sql and let it do the conversion?
0
 

Author Comment

by:matthewc02
Comment Utility
Performance is a real issue here so I need to keep that in mind as I go through this.
I have 1 table called GeoIP which has about 2 million rows.
My server inventory has about 35 rows.

What do you think the best way to tackling this "determine the closest server to the end user" process is? Should I be writing a function? or going with a stored procedure. I've been reading all kinds of other approaches similar to this but they look largely inefficient. I'm also considering mounting my geoIP table into a dimensional DB. Thoughts?
0
 

Author Comment

by:matthewc02
Comment Utility
Ok here is what I have put together so far.
I found a function we are already using called IpStringToNumber which seems to do the trick in terms of converting IP to long ip.

Here is the distance calculation copied from http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_21729376.html?sfQueryTermInfo=1+closest+lat+lon

SELECT LAT, LONG, FULL_NAME, FULL_NAME_ND,
   SQRT(SQUARE(@lat - LAT) + SQUARE(@long - LONG)) AS Distance
INTO #GeoProximity
FROM [FIS_Demo].[dbo].[LocationData]

SELECT TOP 1 * FROM #GeoProximity
ORDER BY Distance


Here is the function:
IF exists (SELECT * from dbo.sysobjects
      WHERE id = object_id(N'[dbo].[IPStringToNumber]')
      AND OBJECTPROPERTY(id, N'IsScalarFunction') = 1)
DROP FUNCTION [dbo].[IPStringToNumber]
GO

CREATE FUNCTION dbo.IPStringToNumber( @vcIPAddress varchar(15))
/**************************************************************************
DESCRIPTION: Returns Numeric IP, otherwise returns null

PARAMETERS:
            @vcIPAddress      - The string containing a valid IP
            
RETURNS:      IP converted to bigint or null if not a valid IP
            
USAGE:         SELECT  dbo.IPStringToNumber( '10.255.255.255')
            

AUTHOR:      Karen Gayda

DATE:       06/11/2003

MODIFICATION HISTORY:
      WHO            DATE            DESCRIPTION
      ---            ----------      ---------------------------------------------------

***************************************************************************/

      RETURNS bigint
AS
BEGIN
      DECLARE      
            @biOctetA       bigint,
            @biOctetB      bigint,
            @biOctetC      bigint,
            @biOctetD      bigint,
            @biIP                bigint

      DECLARE @tblArray TABLE
         (
            OctetID            smallint,              --Array index
               Octet            bigint                     --Array element contents
         )

      --split the IP string and insert each octet into a table row
      INSERT INTO @tblArray
      SELECT ElementID, Convert(bigint,Element) FROM dbo.Split(@vcIPAddress, '.')
      
      --check that there are four octets and that they are within valid ranges
      IF (SELECT COUNT(*) FROM @tblArray WHERE Octet BETWEEN 0 AND 255) = 4
      BEGIN
            SET @biOctetA = (SELECT (Octet * 256 * 256 * 256) FROM @tblArray WHERE OctetID = 1)
            SET @biOctetB = (SELECT (Octet * 256 * 256 ) FROM @tblArray WHERE OctetID = 2)
            SET @biOctetC = (SELECT (Octet * 256 ) FROM @tblArray WHERE OctetID = 3)
            SET @biOctetD = (SELECT (Octet) FROM @tblArray WHERE OctetID = 4)
            SET @biIP = @biOctetA + @biOctetB + @biOctetC + @biOctetD
      END
            
      RETURN(@biIP)
END


IF exists (SELECT * from dbo.sysobjects
      WHERE id = object_id(N'[dbo].[Split]')
      AND OBJECTPROPERTY(id, N'IsTableFunction') = 1)
DROP FUNCTION [dbo].[Split]
GO


GO
CREATE FUNCTION dbo.Split (      @vcDelimitedString             varchar(8000),
                        @vcDelimiter                  varchar(100) )
/**************************************************************************
DESCRIPTION: Accepts a delimited string and splits it at the specified
            delimiter points.  Returns the individual items as a table data
            type with the ElementID field as the array index and the Element
            field as the data

PARAMETERS:
            @vcDelimitedString            - The string to be split
            @vcDelimiter                  - String containing the delimiter where
                                          delimited string should be split

RETURNS:
            Table data type containing array of strings that were split with
            the delimiters removed from the source string

USAGE:
            SELECT ElementID, Element FROM Split('11111,22222,3333', ',') ORDER BY ElementID

AUTHOR:      Karen Gayda

DATE:       05/31/2001

MODIFICATION HISTORY:
      WHO            DATE            DESCRIPTION
      ---            ----------      ---------------------------------------------------

***************************************************************************/
RETURNS @tblArray TABLE
   (
      ElementID      smallint      IDENTITY(1,1),  --Array index
         Element            varchar(1000)                  --Array element contents
   )
AS
BEGIN

      DECLARE
      @siIndex                              smallint,
      @siStart                              smallint,
      @siDelSize                              smallint


      SET @siDelSize      = LEN(@vcDelimiter)
      --loop through source string and add elements to destination table array
      WHILE LEN(@vcDelimitedString) > 0
      BEGIN
            SET @siIndex = CHARINDEX(@vcDelimiter, @vcDelimitedString)
            IF @siIndex = 0
            BEGIN
                  INSERT INTO @tblArray VALUES(@vcDelimitedString)
                  BREAK
            END
            ELSE
            BEGIN
                  INSERT INTO @tblArray VALUES(SUBSTRING(@vcDelimitedString, 1,@siIndex - 1))
                  SET @siStart = @siIndex + @siDelSize
                  SET @vcDelimitedString = SUBSTRING(@vcDelimitedString, @siStart , LEN(@vcDelimitedString) - @siStart + 1)
            END
      END
      
      RETURN
END
GO


So now I'm wondering how I can put the 2 together. What I need is to pass my dotted ip to a stored procedure, have the procedure run the Ipstringtonumber function, pass the longIP to the distance calculation which will somehow find the closest server in my server inventory to the end user. This is where I need help. I really need to make sure this is done right for performance reasons. Any help would be great.
thx


0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 7

Accepted Solution

by:
tymes earned 500 total points
Comment Utility
I would do something totally different, I would forget about geoIP and all this other stuff.  (And, if I used GeoIP, I wouldn't convert things to an integer... i would probably setup and use DNS cached locally or use string matching or a hash table to quickly find a match with "tables" that are only a few thousand rows in size instead of a few million -- oh, I'm only thinking of per country... I guess if you want to do per state or something then you might need to sorta do what you are doing -- but I don't know how effective your guessing is... ATT may run a network across the country and someone in florida may access your west coast server faster than an east coast server).

What I would do, is have it load small images or frames with images (for a more robust test) from the each of the three different servers and use javascript ONLOAD events to determine which one was loaded and finished first then redirect to that server for everything since it was likely the fastest... this would all be client side, the client would do all the hardwork work with no SQL backend and less headache for you...
0
 
LVL 7

Assisted Solution

by:tymes
tymes earned 500 total points
Comment Utility
Oh, you have 35 servers for some reason I thought you had 3....  that's a different spin, how many geographic locations are your servers?  

And I see you're probably gonna be doing this for media streaming? what is more important bandwidth or latency?

Again, I've accessed servers across the country that are faster than some servers in a data center 3 blocks away.
0
 

Author Comment

by:matthewc02
Comment Utility
Well bandwidth and latency are both concerns with media streaming. And due to the nature of the request we are unable to load images or javascripts etc. We require a server-side redirect because the response must be acceptable to both players, mobiles, and normal browsers.

I've managed to get the procedure for locating the closest server complete. It works well within a couple miles. I will be tying actual perormance quotas, conditions etc. into this procedure down the road but for now we mainly need to keep our germans in germany, brits in britain, texans in texas.

I've got the procedure running on tow different SQL servers, one with SCSI RAID, the other without. I'm able to get the response time down to 1 to 3 seconds. Not bad for a first swing. I'm concerned about performance under load and will continue to look into setting this up on a dimensional database because that seems to be well suited for this application.

On another note I need to find a way to parse incomming url into a proper redirect with querystrings in tact. For example if someone goes to mygeocheck.com/abcclient?a=1&b=2&c=3 how do I get IIS to respond on from mygeocheck.com/default.asp and use the /abcclient as a variable because I will be redirecting to someserver.com/abcclient?a=1&b=2&c=3

Any ideas on that would b great, even just to know the term to search for would be handy. I see site do it all the time, I've just never had a use until now.
thx
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Configuring network clients can be a chore, especially if there are a large number of them or a lot of itinerant users.  DHCP dynamically manages this process, much to the relief of users and administrators alike!
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now