根据半径从地图数据库中选择点 [英] Select points from map database according to radius

问题描述

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

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?

我发现上面的公式为所有点BUT到位于中心MYSQL的点都返回了一个好的值，将空"值返回给距离"列！专业人士如何处理此类问题或使用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))))

有更完整的文章，包括MySQL的存储函数定义，

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)

这篇关于根据半径从地图数据库中选择点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！