SQL GEO IP lookup, compare and redirect
Posted on 2007-04-04
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.