根据半径从地图数据库中选择点

2021-11-15 00:00:00 api sql mysql sql-server maps

我有一个包含点的纬度/经度的数据库.如果我想选择以特定点为中心的特定范围内的所有点，它可以正常工作，但如果该中心有任何点，则不会被选中！

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)

相关文章