MySQL Spatial Functions – Strange Results from ST_Distance_Sphere

MySQLspatial

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?

Best Answer

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.

Related Question