Spatial SQL -> MS-SQL. Measure distance between two points (in LL coordinates)


/*
  =================================================================
   this proc measure distance between two points
   input: Lat-Long coordinates of given points
   output: distance in kilometers
  =================================================================
*/


IF EXISTS (SELECT name FROM sysobjects
  WHERE name = 'get_distance_LL' AND type = 'P')
  DROP PROCEDURE get_distance_LL
GO

CREATE PROCEDURE get_distance_LL
(@Lon1 FLOAT, @Lat1 FLOAT, @Lon2 FLOAT, @Lat2 FLOAT, @dist FLOAT OUT)
AS
BEGIN


  -- 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;

END
GO

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

DECLARE @dist FLOAT;
EXEC dbo.get_distance_LL 9.1918377,45.4642822,9.1794726,45.4702864, @dist OUT;
PRINT @dist * 1000;


Result will be like this:
1524.74



sqlexamples.info