MS SQL 2005 : calcule la distance (en kms) entre deux coordonnées geospatiales. Les versions plus récentes de MSSQL ont un type geospatial et n'ont pas besoin de cette rustine. Testé uniquement avec des coordonnées en Europe / Compute the distance (kms) between two geo-coordinates. More recent version of MS SQL have a geospatial type but SQL 200…

distances_geo_kms.sql

CREATE function [E].[geoloc_get_distance_in_kms](@lat1 as decimal (18,6), @long1 as decimal (18,6),
@lat2 as decimal (18,6), @long2 as decimal (18,6))
returns decimal (18,6)
as
begin
  declare @kms decimal(18,4), @op1 decimal (18,10), @op2 decimal (18,10)
  set @kms = 6370
 
  set @op2 = COS(RADIANS(@lat2))
	set @op2 = @op2 * COS(RADIANS(@lat1))
	set @op2 = @op2 * POWER(SIN((RADIANS(@long1 - @long2 ) ) / 2 ), 2 )
	set @op2 = POWER(SIN((RADIANS(@lat1 - @lat2) ) / 2 ), 2 ) + @op2
	set @op2 = SQRT(1-@op2)
 
	set @op1 = COS(RADIANS(@lat2))
	set @op1 = @op1 * COS(RADIANS(@lat2))
	set @op1 = @op1 * COS(RADIANS(@lat1 ))
	set @op1 = @op1 * POWER(SIN((RADIANS(@long1 - @long2) ) / 2 ), 2 )
	set @op1 = @op1 + POWER(SIN((RADIANS(@lat1 - @lat2 ) ) / 2 ), 2 )
	set @op1 = SQRT(@op1)
	
	return abs(2 * (@kms * ATN2(@op1, @op2)))
end