問題描述

我有一個包含點的緯度/經度的數據庫.如果我想選擇以特定點為中心的特定范圍內的所有點，它可以正常工作，但如果該中心有任何點，則不會被選中！

I have a database which has latitude/longitude of points. If I want to select all points within a specific range centered in a specific point it works fine BUT if there is any point located at this center, it will not get selected!

我使用這個查詢:

SELECT *, ( 6371 * acos( cos( radians(-27.5796498) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians(-48.543221) ) + sin( radians(-27.5796498) ) * sin( radians( latitude ) ) ) ) AS distance FROM map HAVING distancia <= 2

在上面的情況下，半徑為2"，地圖中心位于 [-27.5796498,-27.5796498].這個查詢真的很好，但是如果某個點位于這個非常精確的中心，它就不會被選中.為什么?

In the case above the radius is "2" and the center of the map is at [-27.5796498,-27.5796498]. This query works really fine BUT if some point is located at this very exact center, it will not get selected. Why?

我發現上面的公式為所有點返回了一個很好的值，但是到位于中心的點 MYSQL 將值 NULL 返回到列距離"！專業人士如何處理這種使用SQL在包括中心點在內的范圍內選擇點的問題?

I discovered that the formula above returns a good value for all the points BUT to the point located at the center MYSQL returns the value NULL to the column "distance"! How do the professionals deal with this kind or problem of using SQL to select points within a range including the center point?

我可以創建另一個查詢來選擇位于半徑正中心的所有點，但這效率不高，也許某些數學向導可以提出更好的公式.

I could create another query to select all the points located at the very center of the radius, but that's not efficient, maybe some math wizard could come up with a better formula.

推薦答案

有時 ACOS() 的參數可以略大于 1 -- 稍微超出該函數的域 -- 當距離很小.由于 Vincenty，有一個更好的距離公式可用.它使用 ATAN2(y,x) 函數而不是 ACOS() 函數，因此在數值上更穩定.

Sometimes the parameter to ACOS() can be just slightly greater than 1 -- slightly outside the domain of that function -- when distances are small. There's a better distance formula available, due to Vincenty. It uses the ATAN2(y,x) function rather than the ACOS() function and so is more numerically stable.

就是這樣.

DEGREES(
    ATAN2(
      SQRT(
        POW(COS(RADIANS(lat2))*SIN(RADIANS(lon2-lon1)),2) +
        POW(COS(RADIANS(lat1))*SIN(RADIANS(lat2)) -
             (SIN(RADIANS(lat1))*COS(RADIANS(lat2)) *
              COS(RADIANS(lon2-lon1))) ,2)),
      SIN(RADIANS(lat1))*SIN(RADIANS(lat2)) +
      COS(RADIANS(lat1))*COS(RADIANS(lat2))*COS(RADIANS(lon2-lon1))))

此函數以度為單位返回其結果.一個度有111.045公里.60 海里.69 法定英里.因此，將結果乘以這些數字之一以獲得距離.有一個更完整的文章，包括 MySQL 的存儲函數定義，這里.

This function returns its result in degrees. There are 111.045 km in a degree. 60 nautical miles. 69 statute miles. So multiply the result by one of those numbers to get distance. There's a more complete writeup, including a stored-function definition for MySQL, here.

另一種解決方案是使用 ISNULL(ACOS(formula), 0.0)

Another solution is to use ISNULL(ACOS(formula), 0.0)

這篇關于根據半徑從地圖數據庫中選擇點的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！