1

I am trying to calculate the distance using the ST_Distance function in the postgres database.

I am using following query:

SELECT ST_Distance(
  ST_GeogFromText('SRID=4326;POINT(52.3551151038034 4.87660185549649)')
  , ST_GeogFromText('SRID=4326;POINT(52.3549985339677 4.87622001617791)')
);

For this the distance is returned as 44.159975697 meters.

When I am comparing this value with other sources, for example Haversine formula explained in this post. The Haversine formula return the value as 0.0289913126501404, which is roughly 29 meters.

What am I doing wrong with my query here?

  • 1
    Just in case, did you checked that you didn't swap latitude and longitude when entering your coordinates ? ST_GeogFromText use WKT geometry so coordinates have to be POINT(longitude, latitude) – mgc Nov 11 '16 at 17:16
  • I would think if he swapped lat and long his calculated difference would be greater than 29 meters.. – Evan Carroll Nov 11 '16 at 17:27
  • 1
    mgc was right. He flipped the coords. I posted an answer – LR1234567 Nov 11 '16 at 19:17
  • @mgc, yes you are correct. I had by mistake swapped the lat-log values. Switching them back gives the correct result. – Ashish Gamit Nov 11 '16 at 20:33
2

You swapped the long, lat. Common mistake.

If you do this:

SELECT ST_Distance(
 ST_GeogFromText('SRID=4326;POINT(4.87660185549649 52.3551151038034 )')
, ST_GeogFromText('SRID=4326;POINT(4.87622001617791 52.3549985339677 )')
);

Answer is:

29.07032499 meters

You can even get closer to Havershine, by forcing the distance formula to use sphere instead of spheroid by passing in false for the optional use_spheroid arg (it defaults to true).

SELECT ST_Distance(
  ST_GeogFromText('SRID=4326;POINT(4.87660185549649 52.3551151038034 )')
, ST_GeogFromText('SRID=4326;POINT(4.87622001617791 52.3549985339677   )'), false);

Yields:

28.99135257

Which is pretty close to your Havershine answer.

0

The Haversine formula assumes the earth is a perfect sphere whereas SRID 4326 is a better, more oddly shaped representation.

ST_Distance on SRID 4326 does not result in meters but rather degrees.

You'll need to choose a map projection with its measurement defined in meters (or feet) or cast into a geography.

here is another useful link: Why is result of ST_Area or ST_Distance in SRID 4326 so small

  • the points are in geography.. ST_Distance( geog, geog ) is not ST_Distance( geom, geom ) – Evan Carroll Nov 11 '16 at 17:57
  • In fact, the accept answer tells him to use Geography, which is exactly what Ashish is already doing with ST_GeogFromText – Evan Carroll Nov 11 '16 at 17:59
  • 1
    You are right, I glossed right over that! – kttii Nov 11 '16 at 17:59
  • @EvanCarroll that was some good research/information in the answer you deleted – kttii Nov 11 '16 at 19:35
  • I could bring it back in a different question if you thought it was helpful, but it didn't address the problem here. – Evan Carroll Nov 11 '16 at 21:50

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.