Small inconsistency in calculating the distance between two points

Question

Consider the following function to calculate 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 a 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 started

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

it returns 4.99171837612563

but

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

returns 5.0005149496216

The results that I get are slightly different from each other. Can someone explain

• Which function above is more accurate?
• How can I make them return an equal result?

Answer 1

From MSDN

Point (geography data type)

Creates a geography instance representing a Point instance from its latitude and longitude value and spatial reference identifier (SRID)

In your code, you used SRID = 4326.

Spatial Link Identifiers (SRIDs)

Each spatial instance has a spatial reference identifier (SRID). The SRID corresponds to a spatial reference system based on the particular ellipsoid used for planar or cartographic mapping.

It appears that your chosen SRID 4326 defines the elliptical shape of the Earth, not the ideal sphere. Your code, which calculates the distance using sin/cos , should assume that the Earth is an ideal sphere.

There are good questions and answers on this topic: Geometry column: STGeomFromText and SRID (what is SRID?)

---

In order for both methods to return the same result, you need to select an SRID that approaches the Earth with the same scope as your code. You need to look for it somewhere else. I know little about this.