MySQL大圓距離(Haversine公式)我有一個可以工作的PHP腳本，它獲取經(jīng)度和緯度值，然后將它們輸入到MySQL查詢中。我只想讓它成為MySQL。下面是我當(dāng)前的PHP代碼：if ($distance != "Any" && $customer_zip != "") { //get the great circle distance
//get the origin zip code info
$zip_sql = "SELECT * FROM zip_code WHERE zip_code = '$customer_zip'";
$result = mysql_query($zip_sql);
$row = mysql_fetch_array($result);
$origin_lat = $row['lat'];
$origin_lon = $row['lon'];
//get the range
$lat_range = $distance/69.172;
$lon_range = abs($distance/(cos($details[0]) * 69.172));
$min_lat = number_format($origin_lat - $lat_range, "4", ".", "");
$max_lat = number_format($origin_lat + $lat_range, "4", ".", "");
$min_lon = number_format($origin_lon - $lon_range, "4", ".", "");
$max_lon = number_format($origin_lon + $lon_range, "4", ".", "");
$sql .= "lat BETWEEN '$min_lat' AND '$max_lat' AND lon BETWEEN '$min_lon' AND '$max_lon' AND ";
}有誰知道如何把這個變成完整的MySQL嗎？我瀏覽過一些互聯(lián)網(wǎng)，但它上的大部分文獻(xiàn)都令人困惑。
3 回答
慕斯王
TA貢獻(xiàn)1864條經(jīng)驗 獲得超2個贊
這是SQL語句，它將找到距離37，-122坐標(biāo)半徑25英里內(nèi)最接近的20個位置。它根據(jù)該行的緯度/經(jīng)度和目標(biāo)緯度/經(jīng)度計算距離，然后只請求距離值小于25的行，按距離對整個查詢進(jìn)行排序，并將其限制為20個結(jié)果。要搜索公里而不是英里，請將3959替換為6371。
SELECT id, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin(radians(lat)) ) ) AS distance FROM markers HAVING distance < 25 ORDER BY distance LIMIT 0 , 20;
慕標(biāo)琳琳
TA貢獻(xiàn)1830條經(jīng)驗 獲得超9個贊
$greatCircleDistance = acos( cos($latitude0) * cos($latitude1) * cos($longitude0 - $longitude1) + sin($latitude0) * sin($latitude1));
SELECT acos( cos(radians( $latitude0 )) * cos(radians( $latitude1 )) * cos(radians( $longitude0 ) - radians( $longitude1 )) + sin(radians( $latitude0 )) * sin(radians( $latitude1 )) ) AS greatCircleDistance FROM yourTable;
395963713440
SELECT
id
FROM spatialEnabledTable
WHERE
MBRWithin(ogc_point, GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))'))添加回答
舉報
0/150
提交
取消