MySQL GIS functions strange results from ST_Distance_Sphere

Question

According to wikipedia latitude article the distance between latitude 0 and 1 degrees is about 110.574 km. I double checked at gps visualizer site for points lat 0, long 0 and lat 1, long 0 and it returns 110.574 km

So I expected receiving a similar result when I check this using ST_Distance_Sphere() function. However the result seems to be differerent. I am using MySQL 5.7.20

+---------------------------------------------------------------------------------+
| ST_Distance_Sphere(ST_GeomFromText('POINT(0 0)'),ST_GeomFromText('POINT(0 1)')) |
+---------------------------------------------------------------------------------+
|                                                              111194.68229846345 |
+---------------------------------------------------------------------------------+

The results are similarly wrong for 89-90 degrees

+-----------------------------------------------------------------------------------+
| ST_Distance_Sphere(ST_GeomFromText('POINT(0 89)'),ST_GeomFromText('POINT(0 90)')) |
+-----------------------------------------------------------------------------------+
|                                                                111194.68229846361 |
+-----------------------------------------------------------------------------------+

OK perhaps because I omitted the radius, but then I used equator radius as 6378137 and result is

+------------------------------------------------------------------------------------------+
| ST_Distance_Sphere(ST_GeomFromText('POINT(0 0)'),ST_GeomFromText('POINT(0 1)'), 6378137) |
+------------------------------------------------------------------------------------------+
|                                                                       111319.49079327357 |
+------------------------------------------------------------------------------------------+

I understand this function is not suppose to be very accurate, but difference is in my opinion significant. Can you explain why I am getting so different result than what I expect?

Answer 1

I think you're getting this wrong. ST_Distance_Sphere calculates the ST_Distance and returns the result after converting the radians to units of radius on a sphere of radius.

Calculations use a spherical earth and a configurable radius. The optional radius argument should be given in meters. If omitted, the default radius is 6,370,986 meters. An ER_WRONG_ARGUMENTS error occurs if the radius argument is present but not positive.

As a matter of trivia, if you're wondering where that radius comes from -- it's pretty amusing. So let's play

d(A,B) = R a 𝜋/180

So...

• a = ST_Distance( POINT(0,0), POINT(0,1) ). This means a=1.
• r = 6370986 meters
• pi = 3.141593 ish... Using pi() here

So your call to ST_Distance_Sphere( Point(0,0), Point(0,1) ); is the same as,

SELECT ((6370986 * 1 * pi()) / 180);
+------------------------------+
| ((6370986 * 1 * pi()) / 180) |
+------------------------------+
|            111194.6822984635 |
+------------------------------+
1 row in set (0.00 sec)

So as you can see ST_Distance_Sphere is a Cartesian distance using basic geometry to get to a sphereacle distance. That makes this not a bug. in fact, PostGIS will return the same results.

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

PostGIS calculates on a sphere using the average mean radius of the WGS84 reference ellipsoid. MySQL uses a totally random value that originates from a constant in PostgreSQL 20 years ago.

In MySQL, this isn't a GIS function. It's a geometry function, you could argue MySQL doesn't support GIS at all as it does not even use Spatial Reference Systems in its calculations. It makes no assumption about an ellipsoidal projection or spatial reference system.

Answer 2

None is completely correct But I would say that half a percent discrepancy is within reason.

• The radius at the equator will not be correct elsewhere -- The earth is not perfect sphere. It is fatter at the equator than pole-to-pole.
• GPS readings are rarely much more precise than that.

But, if you want to complain, file a bug at bugs.mysql.com . They may pass the buck to the authors of the library, which will explain why they picked the coefficients that they picked.

If we stuck with the original definition of "meter" as 1/10,000,000 of the distance from equator to pole, you would have 111111.1111111111.