3

I am testing SQL Server STDistance function in the following way:

DECLARE @g geography;
DECLARE @h geography;

SET @g = geography::STGeomFromText('POINT(53.553813 9.99158)', 4326);
SET @h = geography::STGeomFromText('POINT(52.5234051 13.4113)', 4326);
SELECT @g.STDistance(@h);

These points represent, respectively, Hamburg and Berlin.

The STDistance call above returns 394612.166639773 (aprox. 394km). However, on Google Earth/Maps, when I calculate the straight line between the two points above, I get aprox. 254km.

If I understood correctly, both SQL Server SRID 4326 and Google Earth represent/use the WGS84 reference system, so I don't know what else could be causing this difference.

Any ideas?

|improve this question
  • 1
    Doesn't the thread at gis.stackexchange.com/questions/4844/… answer this question? – whuber♦ Dec 2 '11 at 15:09
  • I'm not at work, where I can check the GM/GE distance, but an online geodesic calculation program returns 394612.1666 m: ngs.noaa.gov/cgi-bin/Inv_Fwd/inverse2.prl – mkennedy Dec 2 '11 at 15:14
  • @whuber I think in that thread the author had the original data in a different format and had to convert it to the one I'm using. But I'm not entirely sure, I'm new to GIS. – Ricardo Dec 2 '11 at 15:30
  • @mkennedy Weird. I just ran it in that URL you sent and got 256414.3059m as the result. – Ricardo Dec 2 '11 at 15:30
  • 3
    This sounds awfully familiar: are you sure you are putting latitude and longitude in the correct order? (That problem has come up here many, many times.) According to examples on the help page, longitude goes first. – whuber♦ Dec 2 '11 at 17:35
5

Bill Huber gave the right answer in the comments. I delayed the answer by running the NGS inverse tool incorrectly.

It looks like confusion between latitude and longitude order. I did put the coordinates in the wrong order when I used the NGS inverse tool. Stupid of me! If I enter values correctly, the geodesic distance is 256414.3059 m. If I enter the coordinates incorrectly (latitude = 13.4113 rather than longitude = 13.4113), the geodesic distance is 394612.1666 m.

Thus, ST_Distance in SQL Server requires coordinates in longitude, latitude order.

|improve this answer
  • 3
    As I indicated, this isn't the first time exactly this mistake has been reported here and it won't be the last. The best diagnostic clue I know of (without redoing the calcs) is to compare the incorrect and correct distances (here, in the ratio 254/394 = about 0.64). When this ratio is approximately the ratio of cosines of the latitudes involved (here, 50 degrees versus 10-13 degrees if the coordinates are switched, about a 0.62:0.98 ratio = 0.64 again), that's a clear sign that either there's a coordinate mixup or the software is using Euclidean calculations on geographic coordinates. – whuber♦ Dec 2 '11 at 20:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.