SQL server GEOGRAPHY STDistance function is returning big difference than other test results

Question

I am calculating distance in meters between two lat long values - 40.1844441 -77.2252771 and
40.319166 -76.7880552

I tested the distance between these two points in below two web sites - http://www.nhc.noaa.gov/gccalc.shtml and http://www.movable-type.co.uk/scripts/latlong.html
Both of these web sites return approximately 40014 meters.

So, I uses SQL server's GEOGRAPHY data type to calculate the distance between these two points again and it returns 48927 meters. It is a huge difference to me.
Below is my code and any suggestion is appreciated.

declare @latlong1 GEOGRAPHY
declare @latlong2 GEOGRAPHY
DECLARE @distance float

set @latlong1 = GEOGRAPHY::STGeomFromText('POINT(40.1844441 -77.2252771)', 4326)
set @latlong2 = GEOGRAPHY::STGeomFromText('POINT(40.319166 -76.7880552)', 4326)

SET @distance = @latlong1.STDistance(@latlong2)
SELECT @distance -- this returns 48927 meters

John Cappelletti · Answer 1 · 2017-02-14T23:12:55.307

2

I too have noticed some odd results, so I tend to use geography::Point() which seems to generate cleaner results. Even then, option 2 is 80 meters off from the UDF which seems to be spot-on.

Example

declare @latlong1 GEOGRAPHY
declare @latlong2 GEOGRAPHY

Set @latlong1 = GEOGRAPHY::STGeomFromText('POINT(40.1844441 -77.2252771)',4326)
Set @latlong2 = GEOGRAPHY::STGeomFromText('POINT(40.319166  -76.7880552)',4326)

Select VeryOdd = @latlong1.STDistance(@latlong2)
      ,SQLGeo  = geography::Point(40.1844441, -77.2252771, 4326).STDistance(geography::Point(40.319166,-76.7880552, 4326)) 
      ,UDFGeo  = [dbo].[udf-Geo-Meters](40.1844441,-77.2252771,40.319166,-76.7880552)

Returns

VeryOdd             SQLGeo              UDFGeo
48927.1485292471    40093.8055001913    40014.8833526855

The UDF if Interested

CREATE Function [dbo].[udf-Geo-Meters](@Lat1 FLOAT, @Lng1 FLOAT, @Lat2 FLOAT, @Lng2 FLOAT)
Returns Float as
Begin
    Return ACOS(SIN(PI()*@Lat1/180.0)*SIN(PI()*@Lat2/180.0)+COS(PI()*@Lat1/180.0)*COS(PI()*@Lat2/180.0)*COS(PI()*@Lng2/180.0-PI()*@Lng1/180.0)) * 6371008.8
    -- 6.371 mean radius of earth in meters
End

edited Feb 14 '17 at 23:12

answered Feb 14 '17 at 23:02

John Cappelletti

79,615
7
44
66

John C: I can confirm your UDFGEO result is correct to 12 significant figures, based on the earth being a sphere of radius 6371008.8 metres. – John Machin Feb 15 '17 at 04:40
@JohnMachin Thanks for the extra effort and confirmation – John Cappelletti Feb 15 '17 at 09:58
@JohnCappelletti - thank you. I also confirmed using "Spherical Law of Cosines" which is exactly the you have calculated and it returns 40014.8833526855 meters. Yes. I used globe's radius as 6371008.8. – Shwe Feb 15 '17 at 16:21
using mean value of semi major axis and semi minor axis of WGS84 ellipsoid you can get more closer results, so try using R=6367444.6 – Hossein Narimani Rad Sep 18 '21 at 14:47

score 0 · Answer 2 · edited May 23 '17 at 12:09

Try reversing the lat and lon in the "VeryOdd" case i.e. use POINT(-77.2252771 40.1844441) and POINT(-76.7880552 40.319166).

My offline "spherical law of cosines" result is 48733 metres which is only about 0.4% difference from the original VeryOdd 48927 metres.

I suggest trying a few more tests with different lats and lons, plus checking the docs for the relevant functions/methods.

Late breaking news: STGeomFromText('POINT(longitude latitude)' etc -- compared with the more frequent protocol: latitude first then longitude

See the selected answer to this question: Create Geometry/Geography Field from Latitude & Longitude fields (SQL Server)

To clear up the remaining differences, somebody needs to search for what radius/radii is/are invoked by the 4326 magic ...

using mean value of semi major axis and semi minor axis of WGS84 ellipsoid you can get more closer results, so try using R=6367444.6 — Hossein Narimani Rad, Sep 18 '21 at 14:47

SQL server GEOGRAPHY STDistance function is returning big difference than other test results

2 Answers2