問題描述
我有一個 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獲取結果的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！