MySQL – What is PostGIS Doing with ST_DistanceSphere?

MySQLspatial

PostGIS ST_Distance_Sphere and ST_DistanceSphere return the same thing in PostGIS 2.2.

ST_DistanceSphere says

Uses a spherical earth and radius derived from the spheroid defined by the SRID.

ST_Distance_Sphere says,

Uses a spherical earth and radius of 6370986 meters

I'm looking into this answer, specifically

SELECT ST_DistanceSphere( ST_MakePoint(0,0), ST_MakePoint(0,1) );
 st_distancesphere 
-------------------
   111195.07973463
(1 row)

Currently that returns 111195.07973463, it seems it should return

SELECT ((6370986 * 1 * pi()) / 180) AS distance_on_sphere
 distance_on_sphere 
--------------------
   111194.682298463
(1 row)

That's a difference of 0.397436166531406, almost half a meter. Wolfram Apha shows

111194.6822984634581396675695437019275676654860169289329663

Which puts Wolfram at a more precise version of my napkin math and on par with MySQL ((6370986 * 1 * pi()) / 180) and validates that ST_DistanceSphere is borked.

What's going on here?

POSTGIS="2.2.2 r14797" GEOS="3.5.1-CAPI-1.9.1 r4246" PROJ="Rel. 4.9.2, 08 September 2015" GDAL="GDAL 2.2.1, released 2017/06/23" LIBXML="2.9.3" LIBJSON="0.12.1" RASTER

Best Answer

There are two problems here.

  1. Though it calculates the distance on a sphere, PostGIS doesn't calculate with the major-axis radius in WGS84 as presumed in the question. The sphere is created with a radius that is the mean radius of the WGS84 ellipsoid. This is by design.
  2. Neither does MySQL, however the radius in MySQL was lifted from a lazy-copy-job from PostGIS that converted a radius in miles to meters from an obscure constant from a random 20-year old PostgreSQL module. Far from by design, this is presumed to be a bug.
Related Question