Getting the closest locations based on Latitude / Longitude
I'm not going to explain what is being done here (SQL script) because I just don't understand. I searched a long time for this script and at the moment i'm not even sure where I got it... bottomline.. it works, and really really good.Let's say you got the lat/lng from an http call to the Google API (small example below) and you want to get the nearest 10 other "stores" in the area around you.
<cfhttp url="http://maps.google.com/maps/geo?q=#yourpostcode-address#&gl=nl&output=csv&sensor=false&key=#yourGooglekey#" method="GET" />
<cfset aRet = listToArray(cfhttp.filecontent) />
<!--- Is it a good request and is the accuracy more then 5 (postcode level) --->
<cfif aRet[1] eq 200>
-- call the stored procedure
EXEC getstores @lat = #aRet[3]#
,@lng = #aRet[4]#
</cfif>
<!-- Save procedure in your database MSSQL 2000 and up. -->
CREATE PROCEDURE [dbo].[getstores] @lat float, @lng float AS
DECLARE @radius float, @DegToRad float
SET @DegToRad = 57.29577951
SET @radius = 25000
SELECT TOP 10
sto_city
,sto_name
,sto_lat
,sto_lng
,sto_street
,sto_postcode
,sto_tel
,sto_fax
,sto_email
,sto_url
,sto_supplier
,sto_cnt_code
,ROUND((ACOS((SIN(@lat/57.2958) * SIN(sto_lat/@DegToRad)) +(COS(@lat/@DegToRad) * COS(sto_lat/@DegToRad) *COS(sto_lng/@DegToRad - @lng/@DegToRad))))* 6387.7, 2) AS distance
FROM store
WHERE (sto_lat >= @lat - (@radius/111))
And (sto_lat <= @lat + (@radius/111))
AND (sto_lng >= @lng - (@radius/111))
AND (sto_lng <= @lng + (@radius/111))
AND (
ISNUMERIC(sto_lat) = 1
AND
ISNUMERIC(sto_lat) = 1
)
ORDER BY distanceThe distance column from the stored procedure is the total distance from the location you submitted to the Google API (postcode / address). Hope you can find a good use for this.. I did for several projects already :-)
4762 viewed | 3 opinion(s) | del.icio.us | Digg it | Tjarko @ 21/12/09 23:43 cet