2

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?

Community
  • 1
Ashish Gamit
  • 23
  • 1
  • 1
  • 3
  • 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 – Regina Obe 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 Answers2

4

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.

Regina Obe
  • 10,503
  • 1
  • 23
  • 28
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

Community
  • 1
kttii
  • 3,630
  • 1
  • 16
  • 36