Search code examples
sqlsql-serverspatial-querysqlgeography

Distance difference in SQL - why so big discrepancy?

I use 2 ways if calculating distance between coordinates, difference between them is quite big (more than 400 m). Do you know why? And which one of all ways of calculating is the most accurate?

1st:

DECLARE @source geography = 'POINT(53.9202690124512 14.2586479187012)'
DECLARE @target geography = 'POINT(53.8970128 14.2387088)'

SELECT @source.STDistance(@target)

2nd:

SELECT geography::Point(53.9202690124512, 14.2586479187012, 4326).STDistance(geography::Point(53.8970128, 14.2387088, 4326))
Solution

  • geography::Point expects the latitude and longitude to be passed in that order. WKT's POINT expects longitude and latitude to be passed in that order. See the results of:

    DECLARE @wkt_source geography = 'POINT(53.9202690124512 14.2586479187012)'
DECLARE @wkt_target geography = 'POINT(53.8970128 14.2387088)'

select @wkt_source.STAsText() as wkt_source, @wkt_target.STAsText() as wkt_target

declare @point_source geography = geography::Point(53.9202690124512, 14.2586479187012, 4326)
declare @point_target geography = geography::Point(53.8970128, 14.2387088, 4326)


select @point_source.STAsText() as point_source, @point_target.STAsText() as point_target

    So you will need to swap one or other around to get consistent results (depending on which lat/lng is correct).