在mysql中使用经度和纬度查找两点之间的距离

Question

嗨，我有下表

 --------------------------------------------

 |  id  |  city  |  Latitude  |  Longitude  |

 --------------------------------------------

 |  1   |   3    |   34.44444 |   84.3434   |

 --------------------------------------------

 |  2   |   4    | 42.4666667 | 1.4666667   |

 --------------------------------------------

 |  3   |   5    |  32.534167 | 66.078056   |

 --------------------------------------------

 |  4   |   6    |  36.948889 | 66.328611   |

 --------------------------------------------

 |  5   |   7    |  35.088056 | 69.046389   |

 --------------------------------------------

 |  6   |   8    |  36.083056 |   69.0525   |

 --------------------------------------------

 |  7   |   9    |  31.015833 | 61.860278   |

 --------------------------------------------

现在我想获得两点之间的距离。假设一个用户有一个城市3，一个用户有一个城市7。我的情况是一个用户有一个城市和纬度，纬度正在搜索其他用户与城市的距离。例如，拥有城市3的用户正在搜索。他想得到其他城市的用户的距离是7。我已搜索到以下查询

SELECT `locations`.`city`, ( 3959 * acos ( cos ( radians(31.589167) ) * cos( radians( Latitude ) ) * cos( radians( Longitude ) - radians(64.363333) ) + sin ( radians(31.589167) ) * sin( radians( Latitude ) ) ) ) AS `distance` FROM `locations` HAVING (distance < 50)

据我所知，此查询查找从一个点到所有其他点的距离。现在我想获得一个点到另一点的距离。

任何指导方针将不胜感激。

Answer 1

这是MySQL查询和函数，用于获取两个纬度和经度之间的距离，距离将以KM返回。MySQL查询：-SELECT (6371 * acos(&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; cos( radians(lat2) )&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; * cos( radians( lat1 ) )&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; * cos( radians( lng1 ) - radians(lng2) )&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; + sin( radians(lat2) )&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; * sin( radians( lat1 ) )&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ) ) as distance from your_tableMysql功能：-DELIMITER $$CREATE FUNCTION `getDistance`(`lat1` VARCHAR(200), `lng1` VARCHAR(200), `lat2` VARCHAR(200), `lng2` VARCHAR(200)) RETURNS varchar(10) CHARSET utf8begindeclare distance varchar(10);set distance = (select (6371 * acos(&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; cos( radians(lat2) )&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; * cos( radians( lat1 ) )&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; * cos( radians( lng1 ) - radians(lng2) )&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; + sin( radians(lat2) )&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; * sin( radians( lat1 ) )&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ) ) as distance);&nbsp;if(distance is null)then&nbsp;return '';else&nbsp;return distance;end if;end$$DELIMITER ;如何在您的PHP代码中使用SELECT getDistance(lat1,lng1,$lat2,$lng2) as distance&nbsp;FROM your_table.