mysql - 按接近度排序结果(带有坐标和半径)

给定一个包含4个圆的数据库，其中每个圆都有一个半径和一个地理定位的中心:

id | radius | latitude | longitude
---+--------+----------+----------
 1 |      3 |    40.71 |    100.23
 2 |     10 |    50.13 |    100.23
 3 |     12 |    39.92 |    100.23
 4 |      4 |    80.99 |    100.23

注意:为了使事情简单，每个圆的经度是相同的。

假设我们在第2个圆上，我想根据每个圆的latitude/longitude坐标和radius找到附近的每个圆。

例如，根据纬度/经度坐标，我们有以下顺序:

圈子1(由于接近程度:9.42 <- 50.13 - 40.71)

圈子3(由于邻近度:10.21 <- 50.13 - 39.92)

圈子4(由于邻近度:30.86 <- 80.99 - 50.13)

但是根据纬度/经度坐标和每个圆的半径，我们应该有:

圈子3(由于邻近度:1.79 <- 12 - 10.21)

圈子1(由于接近程度:6.42 <- 9.42 - 3)

圈子4(由于邻近度:26.86 <- 30.86 - 4)

有没有一种简单的方法可以在SQL中执行此操作？

最佳答案

postgresql的contrib中提供的cube和earthdistance扩展名可以处理此操作，以至少产生近似答案。具体来说，他们假设地球是一个简单的球体，这使数学计算变得容易得多。

使用这些扩展名，您可以生成圆2和其他圆之间的距离，如下所示:

select circle.id,
       earth_distance(ll_to_earth(circle.latitude, circle.longitude),
                      ll_to_earth(x.latitude, x.longitude))
 from circle,
      circle x
 where x.id = 2 and circle.id <> x.id
 order by 2;

校正圆半径应该只需要从上面的距离中减去x.radius和circle.radius，尽管您需要考虑半径的单位。默认情况下，earth_distance将以米为单位计算一个值。

现在，让查询执行除扫描整个圈子列表并计算每个圈子的距离，然后对它们进行排序和限制之外的其他工作，这更具挑战性。有两种方法:

使用多维数据集的功能根据要点进行索引，因此您可以创建索引以在任何圆心附近的特定框中搜索，从而减少要考虑的圆的列表。

可以在每次编辑一个圆时预先计算每个圆与所有其他圆之间的距离，并使用触发器在单独的表中保持此计算。

第二个选项主要从以下内容开始:

create table circle_distance as
select a.id as a_id, b.id as b_id,
 earth_distance(ll_to_earth(a.latitude, a.longitude),
                ll_to_earth(b.latitude, b.longitude))
 from circle a, circle b
 where a.id <> b.id;
alter table circle_distance add unique(a_id, b_id);
create index on circle_distance(a_id, earth_distance);

然后是一些相当繁琐的函数，用于在circle_distance中删除/插入相关行，由circle上的触发器调用。这意味着您可以执行以下操作:

select b_id from earth_distance where a_id = $circle_id order by earth_distance limit $n

该查询将能够使用(a_id,earth_distance)上的索引进行快速扫描。