Spatial SQL -> MS-SQL. Compare distance calculation results (in LL coordinates)


-- distance calculation results will be in meters

DECLARE @dist FLOAT,
   @Lat1 FLOAT = 9.1918377, @Lon1 FLOAT = 45.4642822,
   @Lat2 FLOAT = 9.1794726, @Lon2 FLOAT = 45.4702864;
   @point1 GEOGRAPHY, @point2 GEOGRAPHY;

-- calculate distance using "home made function"
SET @dist = dbo.sfn_calc_distance_km_LL (@Lat1,@Lon1,@Lat2,@Lon2);

PRINT 'sfn_calc_distance_km_LL()';
PRINT @dist * 1000;
PRINT '';

SET @point1 = GEOGRAPHY::STGeomFromText(
   'POINT(' + CONVERT(Varchar(30),@Lon1, 2)
   + ' ' + CONVERT(Varchar(30),@Lat1, 2) + ')',4326);

SET @point2 = GEOGRAPHY::STGeomFromText(
   'POINT(' + CONVERT(Varchar(30),@Lon2, 2)
   + ' ' + CONVERT(Varchar(30),@Lat2, 2) + ')',4326);

-- get distance using build-in method .STDistance()
SET @dist = @point1.STDistance(@point2);

PRINT 'STDistance()';
PRINT @dist;


Result will be like this:
sfn_calc_distance_km_LL()
1524.74

STDistance()
1518.48



sqlexamples.info