ST_Distance_Sphere in mysql not giving accurate distance between two locations

Question

My requirement is to calculate the distance between two locations on a given map using mysql. I found a function in mysql named ST_Distance_Sphere which returns the minimum spherical distance between two locations and/or multi locations on a sphere in meters.

When I computed the distance between two locations using ST_Distance_Sphere and the lat_lng_distance function , I found that the ST_Distance_Sphere is not giving the same distance as that of the lat_lng_distance function.

My lat_lng_distance function code is as follows

CREATE FUNCTION `lat_lng_distance` (lat1 FLOAT, lng1 FLOAT, lat2 FLOAT, lng2 FLOAT)
RETURNS FLOAT
DETERMINISTIC
BEGIN
    RETURN 6371 * 2 * ASIN(SQRT(
        POWER(SIN((lat1 - abs(lat2)) * pi()/180 / 2),
        2) + COS(lat1 * pi()/180 ) * COS(abs(lat2) *
        pi()/180) * POWER(SIN((lng1 - lng2) *
        pi()/180 / 2), 2) ));
END

The two locations ((38.898556,-77.037852),(38.897147,-77.043934)) passed to the ST_Distance_Sphere and lat_lng_distance function is as follows

SET @pt1 = ST_GeomFromText('POINT (38.898556 -77.037852)');
SET @pt2 = ST_GeomFromText('POINT (38.897147 -77.043934 )');

SELECT ST_Distance_Sphere(@pt1, @pt2)/1000,lat_lng_distance(38.898556,-77.037852,38.897147,-77.043934 );

The Results Obtained is as follows

I checked the distance between the two locations on google maps and found that lat_lng_distance is close to the actual distance between the two locations. Can someone let me know why is the ST_Distance_Sphere not giving accurate distance between two locations?

Answer 1

ST_DISTANCE_SPHERE requires points to be expressed as POINT(longitude, latitude), you have them reversed in your code

set @lat1 = 38.898556;
set @lon1 = -77.037852;
set @lat2 = 38.897147;
set @lon2 = -77.043934;
SET @pt1 = point(@lon1, @lat1);
SET @pt2 = point(@lon2, @lat2);
SELECT ST_Distance_Sphere(@pt1, @pt2)/1000,
  lat_lng_distance(@lat1,@lon1,@lat2,@lon2);

+-------------------------------------+-------------------------------------------+
| ST_Distance_Sphere(@pt1, @pt2)/1000 | lat_lng_distance(@lat1,@lon1,@lat2,@lon2) |
+-------------------------------------+-------------------------------------------+
|                   0.549154584458455 |                        0.5496311783790588 |
+-------------------------------------+-------------------------------------------+

This gives a result that is much closer to the value returned by your function.

Answer 2

First of all, you could not use default SRID of 0 to do any calculations. When you use geometry from text function you have to provide 4326 (SRID that is degrees) as this is what your input format is. MYSQL might not care about it, but it should done as every serious GIS database does care and demands that input SRID was specified.

Second longitude is X and latitude is Y (not another way around)

SET @pt1 = ST_GeomFromText('POINT (-77.037852 38.898556 )', 4326);
SET @pt2 = ST_GeomFromText('POINT (-77.043934 38.897147  )',4326);

Last but not least when you are calculating distance you must transform coordinates to a local most precise SRID available for the region you are.

For example SRID 2877 is used for USA (where according your coordinates you are).

MYSQL ST_Distance_Sphere function does not care about input SRID and always return results in meters.

However it is not generally right and all other database use designated SRID units of measures applicable to it.

Bellow I am trying to do things right and transforming SRID to 2877 even MYSQL would work the same way if we left everything as 4326 (google mercator).

For 2877 PostGRES would return results in feet for the same query but MYSQL is still giving back meters. So output is devided by 1609 and we are getting the correct result of around 0.34 miles. It is a correct value as was tested using different methods

SELECT ST_Distance_Sphere(ST_GeomFromText(ST_AsText(@pt1),2877), ST_GeomFromText(ST_AsText(@pt2),2877))/1609.344;

Answer 3

As an aside, MySQL internally implements this with an obscure and dated constant. So it really depends on your definition of accurate.

• ST_Distance_Sphere

So in essence, 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.