I am currently building a mileage and distance calculator for one of my clients and needed a solution to calculate the distnace between 2 geocoded longitude and latitude points. I know this can be achieved using the Google GClientGeocoder, but I was looking for an SEO friendly and non Javascript / AJAX solution to produce a complete HTML based page with relevant content.
After some Googling and searching I came across a nice SQL function that can be used for just this job. The function code is:
CREATE function dbo.Distance( @lat1 float , @long1 float , @lat2 float , @long2 float)
RETURNS FLOATAS
BEGIN
DECLARE @DegToRad AS FLOAT
DECLARE @Ans AS FLOAT
DECLARE @Miles AS FLOAT
SET @DegToRad = 57.29577951
SET @Ans = 0
SET @Miles = 0
IF @lat1 IS NULL OR @lat1 = 0 OR @long1 IS NULL OR @long1 = 0 or @lat2 IS NULL OR @lat2 = 0 OR @long2 IS NULL OR @long2 = 0
BEGIN
RETURN (@Miles)
END
SET @Ans = SIN(@lat1 / @DegToRad) * SIN(@lat2 / @DegToRad) + COS(@lat1 / @DegToRad ) * COS( @lat2 / @DegToRad ) * COS(ABS(@long2 – @long1 )/@DegToRad)
SET @Miles = 3959 * ATAN(SQRT(1 – SQUARE(@Ans)) / @Ans)
SET @Miles = CEILING(@Miles)
RETURN (@Miles)
END
Now for the query. I wanted to retrieve all of my mileage calculations in 1 query and 1 call to the database. I already had an array of longitude, latitude and place / town names, so I set about creating a dynamic SQL statement by looping over the place names array and building the select query on the fly. The code looked a little like this:
<cfset lstName = “Place 1,Place 2,Place 3,Place 4″ />
<cfset lstLat = “51.8774,51.889381,51.156799,51.470341″ />
<cfset lstLon = “0.539998,0.26151,-0.16988,-0.45342″ />
<cfset arrName = ListToArray(lstName,”,”) />
<cfset arrLat = ListToArray(lstLat,”,”) />
<cfset arrLon = ListToArray(lstLon,”,”) />
<cfset gKey = “[Your Google API Key]” />
<cfset strLocation = “[Pickup Point / Town Name]” />
<cfset gURL = “http://maps.google.co.uk/maps/geo?q=” & strLocation & “,UK&output=xml&key=” & gKey />
<!— Get Lat / Lon for strLocation over HTTP Request to Google Geocoder —>
<cfhttp url=”#gURL#” method=”get” result=”result”></cfhttp>
<cfset xmlRes = XmlParse(result.fileContent) />
<!— Perfrom Error Chcking Here —>
…………… [If CFHTTP Status Code is 200 OK, then continue]
<cfset intLat = ListGetAt(xmlRes.kml.Response.Placemark.Point.coordinates.xmlText,2,”,”) />
<cfset intLon = ListGetAt(xmlRes.kml.Response.Placemark.Point.coordinates.xmlText,1,”,”) />
<!— Now Get Mileage for all array places —>
<cfquery name=”qryRates” datasource=”[your dsn]“>
<cfloop from=”1″ to=”#ArrayLen(arrName)#” index=”i”>
SELECT dbo.Distance(#arrLat[i]#,#arrLon[i]#,#intLat#,#intLon#) AS Miles, ‘#arrName[i]#’ AS destTo, ‘#strLocation#’ AS destFrom
<cfif i NEQ ArrayLen(arrName)>
UNION ALL
</cfif>
That's it. Once you have your query back, you have the place from, place to and the mileage. One thing to note, is that the mileage calculated by this script is a radial mileage and not necessarily a driving / route mileage. As mentioned previously, driving directions can be obtained using the Google API, but that's another post completely!