mysql createst,mysql中的ST_Distance_Sphere无法提供两个位置之间的准确距离

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?

解决方案

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.