按邻近(坐标和半径)排序的结果

Question

给出了一个由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. 第1圈(因为邻近：9.42 <- 50.13 - 40.71)
2. 第3圈(因为邻近：10.21 <- 50.13 - 39.92)
3. 第4圈(因为邻近：30.86 <- 80.99 - 50.13)

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

1. 第3圈(因为邻近：1.79 <- 12 - 10.21)
2. 第1圈(因为邻近：6.42 <- 9.42 - 3)
3. 第4圈(因为邻近：26.86 <- 30.86 - 4)

在SQL中有简单的方法吗？

Answer 1

postgresql控件中提供的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将计算一个以米为单位的值。

现在，让查询做些别的事情，而不是扫描整个圆圈列表，计算每个圆圈的距离，然后对它们进行排序和限制，这就更具有挑战性了。有几种方法：

• 使用多维数据集能够用gist索引，这样您就可以创建索引，在任何圆的中心周围的特定框中搜索，从而减少要考虑的圆圈列表。
• 在编辑圆时，预先计算每个圆与所有其他圆之间的距离，使用触发器在单独的表中维护此计算。

第二种选择基本上从以下几个方面开始：

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)上使用该索引进行快速扫描。

Answer 2

我建议看看PostGIS地理数据类型及其相关功能(例如：ST_Distance)，而不是重新发明轮子

Answer 3

我想请你做以下几件事：

创建一个表，用于计算相对于起始圆的距离。

例如：

id | calc1  | calc2    
---+--------+----------
 1 |  9.42  |    1.97
 3 |  10.21 |    6.42
 4 |  30.86 |   62.86

Calc1是不带半径的计算，calc2是带半径的计算

然后创建一个存储过程，它将首先在运行时删除表，然后用正确的数据填充它，然后从目标表中读取结果。

进入存储过程 为此您将需要游标。