如何将纬度经度数据系列转换为最长路径

Question

我正在处理地理数据，这些数据包含了数百万个经纬度和轨迹ID。这些数据是按时间顺序排列的。我想做的是，按照轨迹ID遍历所有的经纬度，找出特定经纬度之间的最远距离，并把这些数据存储为起点和终点，以便获取每条轨迹的方向。因此，我想把这些经纬度转换成多条最远距离的路径。

目前，我正在使用Postgres数据库和Python，但任何相关的优化方案都会对我有帮助。

Answer 1

首先，你需要添加 PostGIS 这个扩展，然后把坐标传给 st_makepoint()，接着用 st_makeline() 把这些坐标按时间聚合在一起。这里有一个 演示：

create extension if not exists postgis;

create table series_of_locations_as_paths as
select track_id,
       st_makeline(st_makepoint(lon,lat) order by tstz) as track,
       min(tstz) as started,
       max(tstz) as finished
from test
group by track_id;

select track_id,
       finished-started as duration,
       st_length(track)/nullif(extract('epoch' from finished-started),0) avg_speed,
       st_astext(track)
from series_of_locations_as_paths;

演员ID	路径描述	持续时间	平均速度
1	LINESTRING(0 0,0 1,1 1,1 2,2 2)	04:00:00	0.0002777777777777778
2	LINESTRING(0 0,0 1,0 2,0 3)	03:00:00	0.0002777777777777778
3	LINESTRING(0 0)	00:00:00	null

如果你想找出每条路径中最长的部分，可以使用 st_longestline() 来找到合适的线段，然后用 st_locatepoint() 找到这条线的两端在你的路径上的位置，最后用 st_linesubstring() 根据这些位置来截取路径的那部分。这会把路径中“最远距离”的部分切出来，连接轨迹的两端。

select track_id,
       longest_line longest_line,
         st_linesubstring(
            track
            ,least( ST_LineLocatePoint(track,a)
                   ,ST_LineLocatePoint(track,b) )
            ,greatest( ST_LineLocatePoint(track,a)
                      ,ST_LineLocatePoint(track,b) )
         ) as part_of_path_that_connected_extremities
from series_of_locations_as_paths,
lateral (select st_longestline(track,track) )as ll(longest_line),
lateral (select st_startpoint(longest_line),
                st_endpoint(longest_line) )extremities(a,b);

从任意一点沿着每条路径/轨迹走多远是类似的任务：你可以找到你的点和路径之间的 st_longestline()。

select track_id,
       longest_line longest_line,
         st_linesubstring(
            track
            ,least( ST_LineLocatePoint(track,a)
                   ,ST_LineLocatePoint(track,b) )
            ,greatest( ST_LineLocatePoint(track,a)
                      ,ST_LineLocatePoint(track,b) )
         ) as path_reaching_furthest_away_from_point
from series_of_locations_as_paths,
lateral (select st_longestline(track,'POINT(1 3)'::geometry) 
        )as ll(longest_line),
lateral (select st_startpoint(longest_line),
                st_endpoint(longest_line) )extremities(a,b);

你还可以在 QGIS 中用不同的颜色绘制每条轨迹。虚线表示从一个自定义点出发，达到最远的路径：