MXimize by Carlos Gallupa, thoughts about and experiments with coldfusion, css, sql, components and javascript

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 distance

The 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 :-)

619 viewed | Your opinion...  | del.icio.us | Digg it | Tjarko @ 21/12/09 23:43 cet