Minor inconsistency while calculating distance between two points

Question

Consider the following function for calculating the distance between two points

CREATE FUNCTION CoordinateDistanceMiles(
@Latitude1 float,
@Longitude1 float,
@Latitude2 float,
@Longitude2 float
)
RETURNS float 
AS  
BEGIN 
-- CONSTANTS
DECLARE @EarthRadiusInMiles float;
SET @EarthRadiusInMiles = 3963.1
DECLARE @PI  float;
SET @PI = PI();
-- RADIANS conversion
DECLARE @lat1Radians float;
DECLARE @long1Radians float;
DECLARE @lat2Radians float;
DECLARE @long2Radians float;
SET @lat1Radians = @Latitude1 * @PI / 180;
SET @long1Radians = @Longitude1 * @PI / 180;
SET @lat2Radians = @Latitude2 * @PI / 180;
SET @long2Radians = @Longitude2 * @PI / 180;
RETURN Acos(
Cos(@lat1Radians) * Cos(@long1Radians) * Cos(@lat2Radians) * Cos(@long2Radians) + 
Cos(@lat1Radians) * Sin(@long1Radians) * Cos(@lat2Radians) * Sin(@long2Radians) + 
Sin(@lat1Radians) * Sin(@lat2Radians)
) * @EarthRadiusInMiles;
END

And the following simplified version using Geography type:

CREATE FUNCTION [dbo].[GetDistanceInMiles]( @lat1 FLOAT , @lon1 FLOAT , @lat2 FLOAT , @lon2 FLOAT)
RETURNS FLOAT
AS
BEGIN
    DECLARE @result FLOAT;

    DECLARE @source GEOGRAPHY = GEOGRAPHY::Point(@lat1, @lon1, 4326)

    DECLARE @target GEOGRAPHY = GEOGRAPHY::Point(@lat2, @lon2, 4326)

    SELECT @result = @source.STDistance(@target) / 1609.344

    RETURN @result
END 

When I run

SELECT dbo.CoordinateDistanceMiles(50.73521,-1.96958,50.75822,-2.07768)

it returns 4.99171837612563

however

SELECT dbo.GetDistanceInMiles(50.73521,-1.96958,50.75822,-2.07768)

returns 5.0005149496216

The results I get is slightly different to each other. Can someone explain

1. Which above function is more accurate?
2. How I would be able make them return equal result?

Answer 1

From MSDN

Point (geography Data Type)

Constructs a geography instance representing a Point instance from its latitude and longitude values and a spatial reference ID (SRID)

In your code you used SRID=4326.

Spatial Reference Identifiers (SRIDs)

Each spatial instance has a spatial reference identifier (SRID). The SRID corresponds to a spatial reference system based on the specific ellipsoid used for either flat-earth mapping or round-earth mapping.

Apparently, your chosen SRID 4326 defines an elliptical shape of the Earth, not a perfect sphere. Your code that calculates the distance using sin/cos must be assuming that Earth is a perfect sphere.

There is a good question and answers on this topic: Geometry column: STGeomFromText and SRID (what is an SRID?)

---

To make both methods return the same result you need to pick SRID that approximates Earth with the same sphere as your code. You'll need to look it up somewhere else. I don't know much about it.