Spatial SQL -> MS-SQL. Measure distance between two LL coordinates


/*
  =================================================================
   this function measure distance between two points
   input: Lat-Long coordinates of given points
   output: distance in kilometers
   NATIVE_COMPILATION option creates in-memory optimized code
   that works in SQLServer 2016
  =================================================================
*/


DROP FUNCTION IF EXISTS [dbo].[func_calc_distance];
GO

CREATE FUNCTION dbo.func_calc_distance
(@lat1 FLOAT, @lon1 FLOAT, @lat2 FLOAT, @lon2 FLOAT)
RETURNS FLOAT
WITH
NATIVE_COMPILATION, SCHEMABINDING
AS
BEGIN
ATOMIC WITH
(TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'English')

DECLARE @dist FLOAT;

  -- calculate distance
  SET @dist = ACOS(SIN(PI() * @lat1/180.0) * SIN(PI() * @lat2/180.0)
  + COS(PI() * @lat1/180.0) * COS(PI() * @lat2/180.0)
  * COS(PI() * @lon2/180.0 - PI() * @lon1/180.0)) * 6371;

RETURN @dist;

END
GO

/*
Milan city. Aproximate distance (meters) from Duomo to Castello Sforzesco
*/


DECLARE @dist FLOAT;
SET @dist = dbo.func_calc_distance (9.1918377,45.4642822,9.1794726,45.4702864);
PRINT @dist * 1000;


Result will be like this:
1524.74



sqlexamples.info