sql - 计算有序数组中节点之间的总距离

Question

我有一个网络表，其中的节点由数组中的整数指定，我在输入数据时已对其进行排序。这些整数对应于位置表中的 ID，其中包含空间中点的 WKT 表示。如何计算网络从开始到结束的距离？

下面是最简单的情况

 Network Table
 ---------------
| Nodes         |
 ---------------
| {1,2,3}       |
 ---------------


 Location Table
 ---------------
| ID, Point     |
 ---------------
| 1, (1,0)      |
| 2, (2,0)      |
| 3, (2,1)      |
 ---------------

我想为上面的简单情况生成值 2。结果应扩展到复杂的网络。

通过使用 ST_distance，我可以计算网络中所有单独点之间的距离，但我很难保留数组描述的唯一路径。

Answer 1

您将需要 <-> operator对于 PostgreSQL 的 point输入(你似乎有):

select   network_id, sum(dist)
from     (
  select     n.id network_id, point <-> lag(point) over (partition by n.id order by i) dist
  from       network n
  cross join unnest(nodes) i
  join       location l on l.id = i
) s
group by network_id

或者，如果您确实有几何图形和 PostGIS，您也可以使用 ST_Distance(尽管 <-> 是 supported by PostGIS too，因为无论如何它都是距离运算符的一般“形式”):

select   network_id, sum(dist)
from     (
  select     n.id network_id, ST_Distance(point, lag(point) over (partition by n.id order by i)) dist
  from       network n
  cross join unnest(nodes) i
  join       location l on l.id = i
) s
group by network_id

http://rextester.com/ESQA1611