ST_Compress

语法

trajectory ST_Compress (trajectory traj, float8 dist);
trajectory ST_Compress (trajectory traj, float8 dist, float8 angle, float8  acceleration);
trajectory ST_Compress (trajectory traj, float8 dist, float8 angle, float8  acceleration, cstring velocity_field);

参数

描述

根据指定的阈值对轨迹进行有损压缩，返回压缩后的轨迹对象。

• 形式一：指定空间距离偏移值进行压缩，只能保留轨迹空间的整体趋势，对于折返点、加速度点等带有重要信息的轨迹点有可能会被删除。

• 形式二：可以同时指定空间距离偏移阈值、角度偏移阈值、加速度阈值三个参数对轨迹进行压缩，在保留轨迹空间整体趋势的同时，可以保留方向变化较大及速度变化较大的重要轨迹点。也可以指定三个参数中的任何一个或者二个阈值进行压缩。

• 形式三：功能等同于形式二，用于轨迹属性中含有速度字段的情况，指定速度字段名称后，直接根据速度属性值计算轨迹点的加速度。

示例

-- 创建数据
Create table If not exists traj_test(id integer, mmsi integer,traj trajectory);
INSERT INTO traj_test(mmsi, traj) VALUES(477027500,ST_makeTrajectory('STPOINT'::leaftype, 'LINESTRING(-179.48077 51.72814,-179.47416 51.73714,-179.47187 51.74027,-179.46964 51.74325,-179.46731 51.74634,-179.46502 51.74934,-179.46183 51.75378,-179.45943 51.75736,-179.45560 51.76273,-179.44845 51.77186,-179.43419 51.78977,-179.42595 51.80094,-179.42343 51.80411,-179.42078 51.80719,-179.41821 51.81025,-179.41562 51.81308,-179.41259 51.81643,-179.41001 51.81941,-179.40751 51.82223,-179.40497 51.82505,-179.40242 51.82796,-179.39981 51.83095,-179.39734 51.83398,-179.39499 51.83709,-179.39264 51.84023,-179.39037 51.84333,-179.38699 51.84791,-179.38467 51.85114,-179.38216 51.85439,-179.37997 51.85762,-179.37772 51.86144,-179.37474 51.86568,-179.37219 51.86869,-179.36983 51.87156,-179.36755 51.87467,-179.36001 51.88423,-179.35754 51.88712,-179.34216 51.90644,-179.33935 51.90995,-179.33704 51.91298,-179.18826 52.10105,-179.18096 52.11031,-179.17504 52.11786,-179.16482 52.12996,-179.16233 52.13289,-179.15967 52.13590,-179.14599 52.15132,-177.76666 52.85042,-177.48459 52.89898,-177.47841 52.90001,-177.47319 52.90084,-177.46251 52.90268,-177.38188 52.91595,-177.37102 52.91765,-177.36378 52.91877,-177.34492 52.92173,-177.33217 52.92364,-177.32581 52.92468,-177.31238 52.92697,-177.03751 52.97394,-176.93063 52.99160,-176.92406 52.99265,-176.91471 52.99423,-176.90643 52.99554,-176.89912 52.99674,-176.89246 52.99791,-176.88342 52.99942,-176.87697 53.00060,-176.86594 53.00256,-176.85946 53.00370,-176.85294 53.00481,-176.84640 53.00592,-176.83985 53.00705,-176.83238 53.00830,-176.82589 53.00950,-176.81848 53.01084,-176.80553 53.01310,-176.79879 53.01419,-176.79115 53.01548,-176.78466 53.01668,-176.77901 53.01765,-176.77256 53.01879,-176.76301 53.02039,-176.75649 53.02141,-176.74700 53.02296,-176.73757 53.02450,-176.71683 53.02795,-176.70741 53.02950,-176.68481 53.03327)'::geometry, ARRAY['2017-01-15 09:06:39'::timestamp,'2017-01-15 09:10:08'::timestamp,'2017-01-15 09:11:20'::timestamp,'2017-01-15 09:12:29'::timestamp,'2017-01-15 09:13:39'::timestamp,'2017-01-15 09:14:48'::timestamp,'2017-01-15 09:16:28'::timestamp,'2017-01-15 09:17:48'::timestamp,'2017-01-15 09:19:48'::timestamp,'2017-01-15 09:23:19'::timestamp,'2017-01-15 09:30:28'::timestamp,'2017-01-15 09:34:40'::timestamp,'2017-01-15 09:35:49'::timestamp,'2017-01-15 09:36:59'::timestamp,'2017-01-15 09:38:09'::timestamp,'2017-01-15 09:39:18'::timestamp,'2017-01-15 09:40:40'::timestamp,'2017-01-15 09:41:49'::timestamp,'2017-01-15 09:42:58'::timestamp,'2017-01-15 09:44:08'::timestamp,'2017-01-15 09:45:18'::timestamp,'2017-01-15 09:46:29'::timestamp,'2017-01-15 09:47:38'::timestamp,'2017-01-15 09:48:49'::timestamp,'2017-01-15 09:49:58'::timestamp,'2017-01-15 09:51:08'::timestamp,'2017-01-15 09:52:49'::timestamp,'2017-01-15 09:53:58'::timestamp,'2017-01-15 09:55:09'::timestamp,'2017-01-15 09:56:18'::timestamp,'2017-01-15 09:57:38'::timestamp,'2017-01-15 09:59:09'::timestamp,'2017-01-15 10:00:20'::timestamp,'2017-01-15 10:01:29'::timestamp,'2017-01-15 10:02:39'::timestamp,'2017-01-15 10:06:29'::timestamp,'2017-01-15 10:07:40'::timestamp,'2017-01-15 10:15:00'::timestamp,'2017-01-15 10:16:20'::timestamp,'2017-01-15 10:17:29'::timestamp,'2017-01-15 11:30:09'::timestamp,'2017-01-15 11:33:58'::timestamp,'2017-01-15 11:36:58'::timestamp,'2017-01-15 11:42:00'::timestamp,'2017-01-15 11:43:10'::timestamp,'2017-01-15 11:44:20'::timestamp,'2017-01-15 11:50:28'::timestamp,'2017-01-15 18:01:00'::timestamp,'2017-01-15 18:54:13'::timestamp,'2017-01-15 18:55:21'::timestamp,'2017-01-15 18:56:22'::timestamp,'2017-01-15 18:58:21'::timestamp,'2017-01-15 19:13:21'::timestamp,'2017-01-15 19:15:21'::timestamp,'2017-01-15 19:16:41'::timestamp,'2017-01-15 19:20:11'::timestamp,'2017-01-15 19:22:31'::timestamp,'2017-01-15 19:23:41'::timestamp,'2017-01-15 19:26:10'::timestamp,'2017-01-15 20:15:49'::timestamp,'2017-01-15 20:34:39'::timestamp,'2017-01-15 20:35:49'::timestamp,'2017-01-15 20:37:30'::timestamp,'2017-01-15 20:39:00'::timestamp,'2017-01-15 20:40:19'::timestamp,'2017-01-15 20:41:30'::timestamp,'2017-01-15 20:43:08'::timestamp,'2017-01-15 20:44:19'::timestamp,'2017-01-15 20:46:19'::timestamp,'2017-01-15 20:47:29'::timestamp,'2017-01-15 20:48:40'::timestamp,'2017-01-15 20:49:49'::timestamp,'2017-01-15 20:50:59'::timestamp,'2017-01-15 20:52:21'::timestamp,'2017-01-15 20:53:29'::timestamp,'2017-01-15 20:54:50'::timestamp,'2017-01-15 20:57:09'::timestamp,'2017-01-15 20:58:20'::timestamp,'2017-01-15 20:59:40'::timestamp,'2017-01-15 21:00:49'::timestamp,'2017-01-15 21:01:50'::timestamp,'2017-01-15 21:02:58'::timestamp,'2017-01-15 21:04:40'::timestamp,'2017-01-15 21:05:50'::timestamp,'2017-01-15 21:07:29'::timestamp,'2017-01-15 21:09:11'::timestamp,'2017-01-15 21:12:49'::timestamp,'2017-01-15 21:14:30'::timestamp,'2017-01-15 21:18:30'::timestamp], '{"leafcount": 89,"attributes" : {"sog" : {"type":"float","length":8,"nullable":false,"value":[10.5,10.4,10.5,10.7,10.8,10.3,10.7,10.4,10.5,10.1,10.2,11.0,11.2,10.8,10.3,10.3,10.1,10.7,10.6,10.0,10.3,10.5,10.6,10.3,10.8,10.9,10.8,10.8,10.8,11.0,11.2,11.2,10.3,10.2,10.8,10.0,10.4,10.7,10.2,10.6,9.1,10.2,10.1,9.7,10.4,10.6,9.9,12.3,12.1,12.0,12.0,12.2,12.3,12.2,12.3,12.2,12.3,12.2,12.2,12.8,12.8,12.9,12.5,12.6,12.5,12.6,12.6,12.3,12.6,12.6,12.5,12.7,12.8,12.5,12.7,12.5,12.8,13.0,12.9,12.6,12.9,12.8,12.7,12.8,13.0,12.7,12.8,12.6,12.7]}, "cog" : {"type":"float","length":8,"nullable":false,"value":[23.3,25.7,25.9,23.6,25.3,24.1,23.0,21.6,20.7,24.8,22.4,28.5,23.1,30.3,26.2,28.1,25.1,28.7,31.4,28.2,30.4,29.4,29.2,23.0,25.1,25.1,23.5,22.7,27.1,23.3,19.2,27.1,31.0,28.8,22.0,30.1,24.6,26.2,26.7,24.7,26.8,29.5,19.9,30.1,28.8,28.7,30.0,74.2,69.1,75.2,81.3,81.3,80.1,72.6,82.4,74.2,74.9,67.7,73.4,74.2,72.2,80.5,78.6,77.3,70.9,80.1,85.4,71.9,67.0,77.5,77.5,72.2,70.5,72.6,70.8,77.8,71.2,71.2,73.8,75.4,67.1,77.5,74.3,76.9,80.1,72.8,76.0,75.4,72.9]},"heading" : {"type":"float","length":8,"nullable":false,"value":[22.0,23.0,23.0,23.0,23.0,21.0,21.0,25.0,24.0,26.0,25.0,27.0,28.0,29.0,31.0,30.0,28.0,29.0,29.0,28.0,28.0,27.0,24.0,24.0,25.0,25.0,25.0,26.0,25.0,24.0,25.0,29.0,31.0,28.0,29.0,31.0,28.0,29.0,29.0,29.0,27.0,27.0,26.0,26.0,26.0,27.0,27.0,69.0,71.0,72.0,72.0,71.0,73.0,72.0,72.0,72.0,72.0,71.0,71.0,72.0,72.0,72.0,73.0,72.0,71.0,72.0,72.0,72.0,71.0,72.0,72.0,73.0,71.0,72.0,71.0,72.0,73.0,72.0,72.0,72.0,72.0,73.0,74.0,73.0,73.0,73.0,73.0,73.0,73.0]}}}'));

-- 轨迹压缩
select st_compress(traj,0.001) as traj from traj_test;
           traj
-------------------------------------------------------------
 {"trajectory":{"version":1,"type":"STPOINT","leafcount":8,"start_time":"2017-01-15 09:06:39","end_time":"2017-01-15 21:18:30","spatial":"LINESTRING(-179.48077 51.72814,-179.42595 51.80094,-179.39734 51.83398,-179.37474 51.86568,-179.17504 52.11786,-179.14599 52.15132,-177.76666 52.85042,-176.68481 53.03327)","timeline":["2017-01-15 09:06:39","2017-01-15 09:34:40","2017-01-15 09:47:38","2017-01-15 09:59:09","2017-01-15 11:36:58","2017-01-15 11:50:28","2017-01-15 18:01:00","2017-01-15 21:18:30"],"attributes":{"leafcount":8,"sog":{"type":"float","length":8,"nullable":false,"value":[10.5,11.0,10.6,11.2,10.1,9.9,12.3,12.7]},"cog":{"type":"float","length":8,"nullable":false,"value":[23.3,28.5,29.2,27.1,19.9,30.0,74.2,72.9]},"heading":{"type":"float","length":8,"nullable":false,"value":[22.0,27.0,24.0,29.0,26.0,27.0,69.0,73.0]}}}}
(1 row)

select st_compress(traj,0.001,null,null) as traj from traj_test where traj_id=5;
           traj
-------------------------------------------------------------
{"trajectory":{"type":"STPOINT","leafsize":8,"starttime":"2017-01-15 09:06:39","endtime":"2017-01-15 21:18:30","spatial":"LINESTRING(-179.48077 51.72814,-179.42595 51.80094,-179.39734 51.83398,-179.37474 51.86568,-179.17504 52.11786,-179.14599 52.15132,-177.76666 52.85042,-176.68481 53.03327)","timeline":["2017-01-15 09:06:39","2017-01-15 09:34:40","2017-01-15 09:47:38","2017-01-15 09:59:09","2017-01-15 11:36:58","2017-01-15 11:50:28","2017-01-15 18:01:00","2017-01-15 21:18:30"],"themeline":{"leafs":8,"sog":[10.5,11.0,10.6,11.2,10.1,9.9,12.3,12.7],"cog":[23.3,28.5,29.2,27.1,19.9,30.0,74.2,72.9],"heading":[22.0,27.0,24.0,29.0,26.0,27.0,69.0,73.0]}}}
(1 row)

select st_compress(traj,0.001,5,0.3) as traj from traj_test;
         traj
-------------------------------------------------------------
{"trajectory":{"version":1,"type":"STPOINT","leafcount":13,"start_time":"2017-01-15 09:06:39","end_time":"2017-01-15 21:18:30","spatial":"LINESTRING(-179.48077 51.72814,-179.42595 51.80094,-179.39734 51.83398,-179.37474 51.86568,-179.35754 51.88712,-179.18826 52.10105,-179.17504 52.11786,-179.14599 52.15132,-177.76666 52.85042,-177.47841 52.90001,-177.47319 52.90084,-176.83238 53.0083,-176.68481 53.03327)","timeline":["2017-01-15 09:06:39","2017-01-15 09:34:40","2017-01-15 09:47:38","2017-01-15 09:59:09","2017-01-15 10:07:40","2017-01-15 11:30:09","2017-01-15 11:36:58","2017-01-15 11:50:28","2017-01-15 18:01:00","2017-01-15 18:55:21","2017-01-15 18:56:22","2017-01-15 20:52:21","2017-01-15 21:18:30"],"attributes":{"leafcount":13,"sog":{"type":"float","length":8,"nullable":false,"value":[10.5,11.0,10.6,11.2,10.4,9.1,10.1,9.9,12.3,12.0,12.0,12.5,12.7]},"cog":{"type":"float","length":8,"nullable":false,"value":[23.3,28.5,29.2,27.1,24.6,26.8,19.9,30.0,74.2,75.2,81.3,72.6,72.9]},"heading":{"type":"float","length":8,"nullable":false,"value":[22.0,27.0,24.0,29.0,28.0,27.0,26.0,27.0,69.0,72.0,72.0,72.0,73.0]}}}}
(1 row)

select st_compress(traj,0.001,5,1.1,'sog') as traj from traj_test;
     
            traj
-------------------------------------------------------------
{"trajectory":{"version":1,"type":"STPOINT","leafcount":10,"start_time":"2017-01-15 09:06:39","end_time":"2017-01-15 21:18:30","spatial":"LINESTRING(-179.48077 51.72814,-179.42595 51.80094,-179.39734 51.83398,-179.37474 51.86568,-179.33704 51.91298,-179.18826 52.10105,-179.17504 52.11786,-179.14599 52.15132,-177.76666 52.85042,-176.68481 53.03327)","timeline":["2017-01-15 09:06:39","2017-01-15 09:34:40","2017-01-15 09:47:38","2017-01-15 09:59:09","2017-01-15 10:17:29","2017-01-15 11:30:09","2017-01-15 11:36:58","2017-01-15 11:50:28","2017-01-15 18:01:00","2017-01-15 21:18:30"],"attributes":{"leafcount":10,"sog":{"type":"float","length":8,"nullable":false,"value":[10.5,11.0,10.6,11.2,10.6,9.1,10.1,9.9,12.3,12.7]},"cog":{"type":"float","length":8,"nullable":false,"value":[23.3,28.5,29.2,27.1,24.7,26.8,19.9,30.0,74.2,72.9]},"heading":{"type":"float","length":8,"nullable":false,"value":[22.0,27.0,24.0,29.0,29.0,27.0,26.0,27.0,69.0,73.0]}}}}
(1 row)