MSSQL: Get Distance Between two sets of Latitudes and Longitudes

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40

CREATE FUNCTION [dbo].[fx_miles_between_latlon] ( @lat1 FLOAT, @lon1 FLOAT, @lat2 FLOAT, @lon2 FLOAT ) RETURNS FLOAT AS BEGIN --Uses the Haversine Formula expressed in terms of a two-argument inverse tangent --function to calculate the great circle distance between two points on the Earth. --this formula does not take into account the non-spheroidal (ellipsoidal) shape of the Earth. --It will tend to overestimate trans-polar distances and underestimate trans-equatorial distances. --The values used for the radius of the Earth (3961 miles) are optimized for locations --around 39 degrees from the equator. DECLARE @a FLOAT DECLARE @b FLOAT DECLARE @c FLOAT DECLARE @d FLOAT DECLARE @r FLOAT = 3961 --earth radius DECLARE @dlon FLOAT DECLARE @dlat FLOAT SET @lon1 = RADIANS(@lon1) SET @lon2 = RADIANS(@lon2) SET @lat1 = RADIANS(@lat1) SET @lat2 = RADIANS(@lat2) SET @dlon = @lon2 - @lon1 SET @dlat = @lat2 - @lat1 SET @a = POWER(SIN(@dlat / 2),2) + COS(@lat1) * COS(@lat2) * POWER(SIN(@dlon / 2),2) SET @c = 2 * ATN2(SQRT(@a), SQRT(1-@a)) SET @d = @r * @c RETURN ROUND(@d,3) END