postgresql - 如何遍历表并使用列属性在 postGIS 中创建一条线

Question

假设我有一张 table :

ID    X    Y   TIME
---------------------
A     1    2     0
B     9    5     0
A     2    3     1
C     0    0     3
B     9    6     1
B     10   6     2
C     1    0     5
A     2    9     11
...

我希望能够制作这样的线条:

ID    LINE
A     (1,2) -> (2,9)
B     (9,5) -> (10, 6)
C     (0,0) -> (1, 0)

因此，我们为每个 ID 从最早开始时间到最晚结束时间打分。

如何编写 PostGIS SQL 来执行此操作？

Answer 1

如果我理解正确，您想在每个 id 的第一个点和最后一个点之间创建一条直线。如果正确则:

with pr as(select id, x, y, 
                  row_number() over (partition by id order by time, (x,y)) rnk_asc, 
                  row_number() over (partition by id order by time desc,(x,y)) rnk_desc
             from points)

select pr.id, st_makeline(st_point(pr.x, pr.y) , st_point(pr2.x, pr2.y))
  from pr, pr pr2
 where pr.id=pr2.id
   and pr.rnk_asc=1
   and pr2.rnk_desc=1

一些代码解释:

• 我称你的表为“points”
• 我使用“with”语句使代码更清晰，而且比在普通选择的“from”部分执行两次要快一些
• row_number() 是一个窗口函数，您可以在 postgres 文档中阅读更多关于它的信息。我只是用它来对行进行排序并找到第一个和最后一个
• st_point 是用于从 x 和 y(纬度和经度)创建 postgis 点对象的 postgis 函数
• st_makeline 是用于在两点之间创建线的 postgis 函数

希望对您有所帮助。如果您要转换大量数据，请给我一个信号，我可以给您一些提示，使它比这个简单的 SQL 更快。