从基于纬度经度的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

这篇关于从基于纬度经度的mysql获取结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！