PostGIS轨迹分析——简化轨迹

需求

对轨迹线进行简化，并将原始轨迹上的两个特征点拉取到简化后的轨迹上

简化线

红色线是简化后的轨迹线，蓝色线是原始轨迹，有两个特征点

知识点：

• st_makeline函数将点连成线
• st_simplify简化线函数，其中第二个参数为坐标系的单位，0.002度大概代表0.002x1.11x10^5≈222米
• st_collect将点放到一个集合里面

with points as (select the_geom, id
                from points 
                where name = 'xxx'
                  and point_time > '2023-09-01'
                  and point_time < '2023-09-02'
                order by point_time ),
     line as (select st_makeline(points.the_geom) as the_geom from points),
     simplify_line as (select st_simplify(line.the_geom, 0.002) as the_geom from line),
     event_points as (select id       as id,
                             the_geom as the_geom
                      from points 
                      where name = 'xxx'
                        and point_time > '2023-09-01'
                        and point_time < '2023-09-02'
                        and id in (18642961, 18642992)
                      order by point_time ),
     event_points_collect as (select st_collect(event_points.the_geom) as the_geom from event_points)
select line.the_geom                 as origin_line,
       simplify_line.the_geom        as simplify_line,
       event_points_collect.the_geom as origin_points_collect
from line
         cross join simplify_line
         cross join event_points_collect
;

将特征点拉取到简化后的轨迹上

最终结果如本文开头截图所示

知识点：

• st_closestpoint函数计算点到线上最近的点
• jsonb_build_object构建json对象
• json_agg将json对象组合成数组

最终sql语句：

with points as (select the_geom, id
                from points 
                where name = 'xxx'
                  and point_time > '2023-09-01'
                  and point_time < '2023-09-02'
                order by point_time ),
     line as (select st_makeline(points.the_geom) as the_geom from points),
     simplify_line as (select st_simplify(line.the_geom, 0.002) as the_geom from line),
     event_points as (select id       as id,
                             the_geom as the_geom
                      from points 
                      where name = 'xxx'
                        and point_time > '2023-09-01'
                        and point_time < '2023-09-02'
                        and id in (18642961, 18642992)
                      order by point_time ),
     event_points_collect as (select st_collect(event_points.the_geom) as the_geom from event_points),
     result_points as (select event_points.id,
                              st_closestpoint(simplify_line.the_geom, event_points.the_geom) as the_geom
                       from line
                                cross join simplify_line
                                cross join event_points),
     result_points_collect as (select st_collect(result_points.the_geom) as the_geom from result_points),
     geojson_features as (select json_agg(
                                         jsonb_build_object(
                                                 'type', 'Feature',
                                                 'geometry', ST_AsGeoJSON(result_points.the_geom)::json,
                                                 'properties', jsonb_build_object('id', result_points.id)
                                         )
                                 ) as feature_list
                          from result_points)
-- select json_array_elements(features)
-- from features;
select line.the_geom                  as origin_traj,
       simplify_line.the_geom         as simplify_line,
       event_points_collect.the_geom  as origin_points_collect,
       result_points_collect.the_geom as result_points_collect,
       geojson_features.feature_list  as feature_list
from line
         cross join simplify_line
         cross join event_points_collect
         cross join result_points_collect
         cross join geojson_features