GEOLOCATIONS CODE

REFERENCE:

http://www.geodatasource.com/developers/mssql

create function dbo.Distance( @lat1 float , @long1 float , @lat2 float , @long2 float)

returns float

as

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

Reference:

http://www.mssqltips.com/sqlservertip/2690/calculate-the-geographical-distance-between-two-cities-in-sql-server/

TO GET THE MILES AND KILOMETRES:

CREATE PROCEDURE DBO.GEOLOCATIONDISTANCE (@CUSTOMERINDEXKEY_ID INT,@unit varchar(5)) 

AS 

BEGIN 

DECLARE @POSTCODE [VARCHAR](9), @LATITUDE1 [DECIMAL](18,15),@LONGITUDE1 [DECIMAL](18,15) 

SELECT TOP 1 @POSTCODE = REPLACE(POSTCODE,' ','') 

FROM ADDRESS(NOLOCK) WHERE CUSTOMERINDEXKEY_ID = @CUSTOMERINDEXKEY_ID 

ORDER BY SEQNUM DESC 

SELECT @CUSTOMERINDEXKEY_ID AS CUSTOMERINDEXKEY_ID, @POSTCODE AS POSTCODE 

 

SELECT @LATITUDE1 = LATITUDE, @LONGITUDE1 = LONGITUDE FROM PRDDOL.POSTCODELATLNG (NOLOCK) WHERE POSTCODE = @POSTCODE 

 

SELECT @LATITUDE1,@LONGITUDE1 

DECLARE @orig_lat DECIMAL(12,9) 

DECLARE @orig_lng DECIMAL(12,9) 

 

SET @orig_lat=@LATITUDE1 set @orig_lng=@LONGITUDE1 

 

DECLARE @orig geography = geography::Point(@orig_lat, @orig_lng, 4326); 

if @unit = 'miles' 

SELECT *, 

    @orig.STDistance(geography::Point(dest.Latitude, dest.Longitude, 4326))/1609.344  

       AS distance 

--INTO #includeDistances 

FROM  DEALER  dest WHERE ([LATITUDE] IS NOT NULL AND LONGITUDE IS NOT NULL) 

ORDER BY DISTANCE ASC

else

SELECT *, 

    @orig.STDistance(geography::Point(dest.Latitude, dest.Longitude, 4326))/1000

       AS distance 

--INTO #includeDistances 

FROM  DEALER  dest WHERE ([LATITUDE] IS NOT NULL AND LONGITUDE IS NOT NULL)

ORDER BY DISTANCE ASC 

 

END