根据经纬度从mysql获取结果

2021-12-19 00:00:00 select latitude-longitude php mysql

我有一个 mysql 数据库和 2 个表，比如说客户和学校.现在每个表都有纬度和经度列.我需要做一个选择，例如从第二个表中选择，其中学校位于第一个表中一个记录的给定半径内.应根据纬度和经度进行计算.PS:我用的是 PHP.

I have a mysql Database and 2 tables let's say clients and schools. Now each table has columns latitude and longitude. And I need do make a SELECT for example from second table where schools are in a given radius of one record from first table. Calculations should be made based on latitude and longitude. PS: I am using PHP.

推荐答案

您可以使用 计算距离余弦球面定律:

SELECT DEGREES(ACOS(SIN(RADIANS(clients.latitude)) * SIN(RADIANS(schools.latitude)) + 
                    COS(RADIANS(clients.latitude)) * COS(RADIANS(schools.latitude)) 
                                                   * COS(RADIANS(clients.longitude 
                                                               – schools.longitude)))) 
       * 60 * 1.1515 * 1.609344 AS distance
FROM clients, schools HAVING distance < $radius

RADIANS(X) -度到弧度
ACOS(X) - 反余弦X的，即余弦为X的值
DEGREES(X) - 弧度到度

RADIANS(X) - degrees to radians
ACOS(X) - the arc cosine of X, that is, the value whose cosine is X
DEGREES(X) - radians to degrees

60 - 度数
1.1515 - 海里
中的英里1.609344 - 一英里的公里

60 - minutes in a degree
1.1515 - miles in a nautical mile
1.609344 - kilometres in a mile

相关文章