I’ve been having fun this morning writing code to calculate the distance between a web site visitor’s location, and a number of fixed locations, using the visitor’s IP number.
I have a list of events in different geographic locations. The idea is that when someone arrives at the web site we show them information about the event that is closest to them.
I’ve used a couple of tools to make this work.
First up I found the GeoLiteCity database from MaxMind. This is a free database that can calculate the longitude and latitude of an IP number. They say they cover the whole of the IPv4 system (and a fair chunk of the IPv6).
MaxMind does have a web service available (paid) but you can download the GeoLiteCity database and query it directly – they have a good set of example APIs. I’m using the COM object on a Windows server.
The database is a binary file that you reference via file path. They do supply a CSV to import into SQL but they make the point the binary is seriously faster, so unless you need additional access to the data, it’s not worth the loss of speed. If you also use the COM just take note of their instruction to rename the database file from GeoLiteCity.dat to GeoIPCity.dat. I have the file sitting just above the www root folder so it’s not visible to the world.
Finding the site visitor’s latitude and longitude is a doddle:
Dim geoip,visitorLat,visitorLong
set geoip = Server.CreateObject("GeoIPCOMEx.GeoIPEx")
geoip.set_db_path("D:\inetpub\GeoIPDb\")
geoip.find_by_addr(request.ServerVariables("REMOTE_ADDR"))
visitorLat=geoip.latitude
visitorLong=geoip.longitude
I then hunted around online and found the longitute and latitude of the cities where my events are occuring – we’re not looking for ‘to the metre’ resolution here, just a quick guesstimate of which event and city is closest. When I get some more time I’ll sit down and figure out the coordinates of the actual venue for each event.
So now I need a way to calculate the distance between the visitor’s IP, and all of the events. Along the way I found this really useful page from Moveable Type ‘Calculate distance, bearing and more between Latitude/Longitude points‘. It’s a great introduction to all the maths needed.
But another Google search found a forum post with a SQL stored procedure that calculates the distance between two coordinates, and it works well.
CREATE FUNCTION dbo.UDF_Distance
(
@FromLat float, @FromLong float, @ToLat float, @ToLong float
)
RETURNS FLOAT
AS
BEGIN
-------------------------------------------------------------------------------------------------
-- Function Name : UDF_Distance
-- Description : Returns distance in KM between two points on earth
-- Created by : David Eedle
-- Created on : 24 Jan 2011
-- Source: : http://www.dbnewsgroups.net/sqlprogramming/t19131-sql-distance-between-latitude-longitude.aspx
-------------------------------------------------------------------------------------------------
declare @X float
SET @X =
Sin(Radians(@FromLat))
* Sin(Radians(@ToLat))
+ Cos(Radians(@FromLat))
* Cos(Radians(@ToLat))
* Cos(Radians(@ToLong)-Radians(@FromLong))
SET @X = Acos(@X)
RETURN 1.852 * 60.0 * Degrees(@X)
END
GO
/*
select dbo.uf_Distance (41.63,-87.73,41.7,-88.07)
go
*/
I used the Moveable Type page to double check the results and the procedure yields numbers within a few kilometres of MT’s answers over distances of 12,000km. If I was a maths whiz I’d probably now sit down for a day and figure out the inconsistency but I’m not trying to send a rocket to the moon so we’ll leave that for the experts.
Finally it was just a question of writing a quick query that pulls the coordinates of the events out, along with a calculation of the distance between the visitor and each event along the lines of:
SELECT cityId,cityName,cityState,cityDate,
CAST((select dbo.UDF_Distance ("&visitorLat&","&visitorLong&",V_ListCities.cityLat,V_ListCities.cityLong)) AS int) distanceKM
FROM V_ListCities ORDER BY (select dbo.UDF_Distance ("&visitorLat&","&visitorLong&",V_ListCities.cityLat,V_ListCities.cityLong))
David Eedle 