sp TSQL che dati 2 punti geografici (latitudine e longitutide) ritorna la loro distanza in miglia:
ALTER FUNCTION dbo.udfComputeDistance(
@lat1 float,
@lon1 float,
@lat2 float,
@lon2 float)
RETURNS float
AS
begin
declare @rt float
if @lat2=0 and @lon2=0
begin
set @rt=1000000
end
else
begin
-- dLong represents the differences in longitudes
-- while dLat is the difference in latitudes
declare @dLong float
declare @dLat float
declare @temp float
-- Convert the decimal degrees to radians
set @lat2 = radians(@lat2)
set @lon1 = radians(@lon1)
set @lat1 = radians(@lat1)
set @lon2 = radians(@lon2)
-- Compute the degree differences
set @dLong = @lon2 - @lon1
set @dLat = @lat1 - @lat2
-- Compute the first part of the equation
set @temp = (square(sin(@dLat/2.0))) + cos(@lat2) * cos(@lat1) * (square(sin(@dLong/2.0)))
-- Return the approximate distance in miles
-- Note that 3956 is the approximate median radius of the Earth.
set @rt=(2.0 * atn2(sqrt(@temp), sqrt(1.0-@temp)))*3956.0
end
return @rt
end
posted @ martedì 23 ottobre 2007 04:38