PostGIS ST_Distance_Spheroid or Haversine

Question

I used ST_Distance_Spheroid in PostgreSQL (with Postgis) to calculate the distance between Woking and Edinburgh like as follows:

CREATE TABLE pointsTable (
    id serial NOT NULL,
    name varchar(255) NOT NULL,
    location Point NOT NULL,
    PRIMARY KEY (id)
);

INSERT INTO pointsTable (name, location) VALUES 
( 'Woking', '(51.3168, -0.56)' ),
( 'Edinburgh', '(55.9533, -3.1883)' );

SELECT ST_Distance_Spheroid(geometry(a.location), geometry(b.location), 'SPHEROID["WGS 84",6378137,298.257223563]')
FROM pointsTable a, pointsTable b
WHERE a.id=1 AND b.id=2;

I got a result of 592km (592,053.100454442 meters).

Unfortunately, when I used various sources on the web to make the same calculation I consistently got around the 543km mark which is different by 8.2%.

• source 1 - 338 miles (543.958 km)
• source 2 - 544.410km
• source 3 - 543.8km

Luckily, the third source clarified that they were using the haversine formula. I am not sure about the other two sources.

Did I do something wrong in my queries or is this down to a difference in the formulas used? If so, which calculation is closest to the shortest distance a crow could fly, keeping a constant elevation?

Answer 1

You swapped the latitude and the longitude. If you put them in the right order you would get 544 430m. The distance computation is using the great circle arcs, which is the true shortest distance between points over a sphere.

WITH src AS (
  select st_geomfromtext('POINT(-0.56 51.3168)',4326) pt1,
         st_geomfromtext('POINT(-3.1883 55.9533)',4326) pt2)
SELECT 
   ST_DistanceSpheroid(pt1, pt2, 'SPHEROID["WGS 84",6378137,298.257223563]') Dist_sphere,
   ST_Distance(pt1::geography, pt2::geography) Dist_great_circle
FROM src;

   dist_sphere    | dist_great_circle
------------------+-------------------
 544430.941199621 |   544430.94119962
(1 row)

On a side note, there is a warning

ST_Distance_Spheroid signature was deprecated in 2.2.0. Please use ST_DistanceSpheroid