问题描述
我的requirement
是在给定的locations之间的distance
使用 mysql ="=" noreferrer>地图.我在 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.
当我使用 ST_Distance_Sphere计算两个位置之间的距离时和lat_lng_distance
函数,我发现 ST_Distance_Sphere 的距离与lat_lng_distance
函数的距离不同.
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.
我的lat_lng_distance
功能代码如下
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
两个位置((38.898556,-77.037852),(38.897147,-77.043934))
传递给 ST_Distance_Sphere 和lat_lng_distance
函数如下
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
我检查了 Google地图上的两个位置之间的距离,发现lat_lng_distance
接近两个位置之间的实际距离.有人可以告诉我为什么 ST_Distance_Sphere 没有给出两个位置之间的准确距离?
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 要求将点表示为POINT(longitude, latitude)
,您在代码中将它们颠倒了
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.
这篇关于mysql中的ST_Distance_Sphere无法提供两个位置之间的准确距离的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!