Ganos轨迹引擎
轨迹（Trajectory）是时空对象中的一个重要模型，用来支持行人、汽车、船只、飞机等移动对象的处理和分析。轨迹数据在应用上有两种视角，一种是离散的点，将轨迹视为一个点集进行操作；另一种是连续的线，将轨迹视为一条空间中随时间变化的折线。两种表示各有其适用的范围，连续的轨迹线对采样频率不敏感，可以当作折线进行各类的空间运算；离散的轨迹点虽然对采样方式、采样频率更敏感，但对算法来说更加友善和简单，常见的相似度计算、轨迹切分等函数，都是基于对点的操作。
time |
x |
y |
speed |
|
2020-04-11 17:42:30 |
114.35 |
39.28 |
4.3 |
|
2020-04-11 17:43:30 |
114.36 |
39.28 |
4.8 |
|
2020-04-11 17:45:00 |
114.35 |
39.29 |
3.5 |
阿里云时空数据库Ganos原生构建了轨迹引擎，新增了Trajectory类型、索引及相关时空算子，用于解决移动对象的存储、检索与分析，Ganos轨迹引擎能力依托阿里云云原生关系型数据库PolarDB、云原生数据仓库AnalyticDB和云数据库RDS PG建设输出。
轨迹采样点常用处理操作
业务需求
在实际业务使用中，轨迹往往由TMS、AIS之类的系统根据GPS信号采样入库，虽然GPS采集的频次均匀，但受限于车辆自身的行驶状态，往往会出现以下影响轨迹使用的问题：
- GPS信号导致的轨迹点异常，如：GPS信号差造成的定位点漂移形成噪点等；
- 运输行为导致的无效轨迹点偏多，如：船舶进港前等待泊位在港口边环绕，车辆停留在服务区休息等；
- 部分轨迹点丢失导致整条轨迹采样不均匀，如：由于设备损坏、人为因素等造成的轨迹采样不均匀，后续导致轨迹分析（计算相似度，……）出现较大误差等；
- 轨迹数据量大导致前端绘图效率低下，如：数亿轨迹点前端渲染导致无法加载程序卡顿等；
功能解析
基于上述业务需求，Ganos自v5.2版本起提供了一整套轨迹采样点处理方法，帮助用户方便处理轨迹信息，满足业务分析与渲染展示等需求。
轨迹过滤
轨迹过滤主要剔除轨迹线上明显出现错误运动趋势的点，例如，如果一个点偏离了其它点很长的距离，它很大可能来自于采样的误差，许多情况下在我们开展下一步深度分析之前，需要删除这些漂移点。同时，除了距离漂移外，在采样比较密集但精度不足的场景下，物体在短时间内可能在一个范围内不断跳跃，产生极快的速度，这种速度异常点往往也需要去除。
函数
函数名 |
函数语法 |
函数功能 |
ST_removeDriftPoints（参考手册） |
trajectory ST_removeDriftPoints(trajectory traj, float8 speed, float8 dist, interval offset); |
删除轨迹线中指定阈值的漂移点 |
参数
参数名称 |
参数解释 |
traj |
轨迹对象 |
speed |
速度阈值（m/s） |
dist |
距离阈值（m） |
offset |
时间间隔 |
删除轨迹对象中满足条件的轨迹点，满足的条件要求轨迹点速度大于阈值speed（米/秒），且与上一轨迹点的距离大于阈值dist（米），时间间隔大于阈值offset，之后返回新的轨迹对象，该轨迹对象至少包含2个轨迹点。如果原始轨迹本身就只有两个轨迹点，则不做任何删除。
参数speed是由轨迹位置和时间偏移值实时计算的结果，其单位固定是speed为米/秒；距离阈值dist单位为米，时间阈值offset为时间间隔值。轨迹坐标空间参考系默认为WGS84，如果不为该空间参考系，请在构建轨迹时指定空间参考值或使用st_setsrid设置轨迹的空间参考值，最终会根据空间参考值统一计算球面距离（单位为米）。
示例
--原始轨迹 select id, st_leafcount(traj) from table_name where id = 1; id | st_leafcount ----+-------------- 1 | 53 (1 row) update table_name set traj=st_removeDriftPoints(traj,25.72, 9620, interval '30 seconds') where id = 1; --删除了漂移点 select id, st_leafcount(traj) from table_name where id = 1; id | st_leafcount ----+-------------- 1 | 49 (1 row)
轨迹切分
轨迹采样点处理的一个重点难题在于用户需求的多样性，每个用户需要根据自己的业务需求修正轨迹点，因此提供方便的轨迹切分能力使用户可以自行处理每段轨迹成为基础需求，轨迹切分一般分为三种场景：
- 切分点cut_point：选取一些轨迹的采样点，将轨迹在采样点处断开（如下图，在点B上切开，切开后两条子轨迹都有点B）。常用场景：轨迹过长时，将轨迹切分成几段比较短的轨迹。
- 切分边cut_edge：类似切分轨迹点，但可以选的点不一定是原先的采样点，而是可以选取原先在轨迹边上的点（如下图中插值得到点C，从C上切开，两边都有点C）。常用场景：将轨迹切分成固定的形状，例如按时间网格或空间网格切开。
- 去除边drop_edge：选取一条边并将其删除，之后此边的两个端点分别属于两个子轨迹。常用场景：提取轨迹中有意义的部分。例如，共享单车停留时的轨迹段可以去掉，留下来其运行时的轨迹。
函数
函数名 |
函数语法 |
函数功能 |
ST_Split（参考手册） |
trajectory[] ST_Split(trajectory traj, text config); |
将一条轨迹切分成多条（子）轨迹 |
参数
参数名称 |
参数解释 |
traj |
轨迹对象 |
config |
切分轨迹的选项 |
config所规定的是设定好的切分规则（见下表），返回切分后的（子）轨迹数组，每次调用此函数时，仅可选择一个规则。
规则名 |
规则参数 |
规则解释 |
cut_point.max_point |
正整数 |
每间隔参数个点，将轨迹在采样点处切开成为两条子轨迹 |
cut_point.even_divide |
正整数 |
固定将每条轨迹切分为参数条子轨迹（如果轨迹所包含的边少于参数，则将每条边拆成一条子轨迹） |
cut_edge.time_interval |
时间段字符串（同Interval类型），需为正。 仅支持天、时、分、秒的指定，不支持指定月和年。 |
每间隔固定时间间隔，将轨迹沿边切开成为两条子轨迹 如需指定起始时间（如一天的开始），可以在时间段后指定切分起始时间，以逗号分隔。 |
cut_edge.geohash |
正偶数 |
按照参数位的GeoHash值（需确保源数据由经纬度表示）划分空间，切分轨迹使得每段子轨迹包含于一个GeoHash网格 |
drop_edge.temporal_length |
时间段字符串（同Interval类型） |
将时间间隔超过参数的边删除 |
drop_edge.spatial_distance_2d |
浮点数 |
将空间跨度超过参数的边删除（按照二维欧氏距离计算） |
示例
create table tr_split_traj(id integer, traj trajectory); INSERT INTO tr_split_traj VALUES(3, ST_MakeTrajectory('STPOINT'::leaftype, st_geomfromtext('LINESTRING(99.027 29.7555,99.313 29.9975,99.852 30.0745,104.879 35.0795,105.044 35.1235,105.187 35.0685,109.906 35.0795,110.071 35.1675,110.192 35.0355,110.544 35.0245,111.017 34.8045)', 4326), ARRAY['2010-01-01 14:30'::timestamp,'2010-01-01 15:00','2010-01-01 15:10','2010-01-01 15:20','2010-01-01 15:30','2010-01-01 15:40','2010-01-01 15:50','2010-01-01 16:00','2010-01-01 16:10','2010-01-01 16:20','2010-01-01 16:30'],'{"leafcount":11,"attributes":{"velocity": {"type": "integer", "length": 2,"nullable" : true,"value": [120, 130, 140, 150, 160, 170, 180, 190, 200, 210, 220]}}}')); select id, unnest(st_split(traj, st_geomfromtext('MULTIPOINT(100 30,105 35,110 35)'), 23000)) as subtraj from tr_split_traj; id | subtraj ----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 3 | {"trajectory":{"version":1,"type":"STPOINT","leafcount":3,"start_time":"Fri Jan 01 14:30:00 2010","end_time":"Fri Jan 01 15:10:00 2010","spatial":"SRID=4326;LINESTRING(99.027 29.7555,99.313 29.9975,99.852 30.0745)","timeline":["Fri Jan 01 14:30:00 2010","Fri Jan 01 15:00:00 2010","Fri Jan 01 15:10:00 2010"],"attributes":{"leafcount":3,"velocity":{"type":"integer","length":2,"nullable":true,"value":[120,130,140]}}}} 3 | {"trajectory":{"version":1,"type":"STPOINT","leafcount":2,"start_time":"Fri Jan 01 15:10:00 2010","end_time":"Fri Jan 01 15:20:00 2010","spatial":"SRID=4326;LINESTRING(99.852 30.0745,104.879 35.0795)","timeline":["Fri Jan 01 15:10:00 2010","Fri Jan 01 15:20:00 2010"],"attributes":{"leafcount":2,"velocity":{"type":"integer","length":2,"nullable":true,"value":[140,150]}}}} 3 | {"trajectory":{"version":1,"type":"STPOINT","leafcount":2,"start_time":"Fri Jan 01 15:40:00 2010","end_time":"Fri Jan 01 15:50:00 2010","spatial":"SRID=4326;LINESTRING(105.187 35.0685,109.906 35.0795)","timeline":["Fri Jan 01 15:40:00 2010","Fri Jan 01 15:50:00 2010"],"attributes":{"leafcount":2,"velocity":{"type":"integer","length":2,"nullable":true,"value":[170,180]}}}} 3 | {"trajectory":{"version":1,"type":"STPOINT","leafcount":3,"start_time":"Fri Jan 01 16:10:00 2010","end_time":"Fri Jan 01 16:30:00 2010","spatial":"SRID=4326;LINESTRING(110.192 35.0355,110.544 35.0245,111.017 34.8045)","timeline":["Fri Jan 01 16:10:00 2010","Fri Jan 01 16:20:00 2010","Fri Jan 01 16:30:00 2010"],"attributes":{"leafcount":3,"velocity":{"type":"integer","length":2,"nullable":true,"value":[200,210,220]}}}} With traj as( select '{"trajectory":{"version":1,"type":"STPOINT","leafcount":19,"start_time":"2000-01-01 00:01:19.067179","end_time":"2000-01-01 03:24:25.946085","spatial":"LINESTRING(-100 -100 -100,-88.8925775739675 -86.6512698383691 -92.3767832526937,-79.6904716538265 -80.6515727923252 -84.2357598245144,-75.8435507711644 -73.7572890928326 -80.5007370118983,-70.6238425321256 -67.8213750167439 -74.5733173238113,-61.6014582272619 -61.0636760429479 -67.9874239303172,-56.1098577060426 -54.4264591250879 -64.5007972046733,-46.9800617334743 -49.4026757289345 -61.6160059720278,-41.7122942996211 -46.3224360072054 -56.5283147455193,-35.5646221285375 -38.1688933617746 -49.2775720101781,-31.7230528349367 -33.6970051738123 -44.1693710885011,-23.1585765127093 -26.5895827477798 -40.6539742602035,-16.7020264320696 -21.6133877349397 -37.3055470525287,-12.1044529232507 -14.1236051704424 -28.2295028120279,-3.77185660181567 -7.74744770256802 -24.3842111621052,0.488159407706304 -3.68223926316326 -19.9478872027248,6.33406881305078 4.54123636645575 -15.0410129944794,15.6666049417108 10.5611746329814 -11.2770220567472,14 11 -10)","timeline":["2000-01-01 00:01:19.067179","2000-01-01 00:12:36.116007","2000-01-01 00:23:53.164835","2000-01-01 00:35:10.213663","2000-01-01 00:46:27.262491","2000-01-01 00:57:44.311319","2000-01-01 01:09:01.360147","2000-01-01 01:20:18.408975","2000-01-01 01:31:35.457803","2000-01-01 01:42:52.506631","2000-01-01 01:54:09.555459","2000-01-01 02:05:26.604287","2000-01-01 02:16:43.653115","2000-01-01 02:28:00.701943","2000-01-01 02:39:17.750771","2000-01-01 02:50:34.799599","2000-01-01 03:01:51.848427","2000-01-01 03:13:08.897255","2000-01-01 03:24:25.946085"]}}'::trajectory as a ) select unnest(ST_split(a, '{"cut_point.max_point":4}')) from traj; unnest ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ {"trajectory":{"version":1,"type":"STPOINT","leafcount":5,"start_time":"2000-01-01 00:01:19","end_time":"2000-01-01 00:46:27","spatial":"LINESTRING(-100 -100 -100,-88.8925775739675 -86.6512698383691 -92.3767832526937,-79.6904716538265 -80.6515727923252 -84.2357598245144,-75.8435507711644 -73.7572890928326 -80.5007370118983,-70.6238425321256 -67.8213750167439 -74.5733173238113)","timeline":["2000-01-01 00:01:19","2000-01-01 00:12:36","2000-01-01 00:23:53","2000-01-01 00:35:10","2000-01-01 00:46:27"]}} {"trajectory":{"version":1,"type":"STPOINT","leafcount":5,"start_time":"2000-01-01 00:46:27","end_time":"2000-01-01 01:31:35","spatial":"LINESTRING(-70.6238425321256 -67.8213750167439 -74.5733173238113,-61.6014582272619 -61.0636760429479 -67.9874239303172,-56.1098577060426 -54.4264591250879 -64.5007972046733,-46.9800617334743 -49.4026757289345 -61.6160059720278,-41.7122942996211 -46.3224360072054 -56.5283147455193)","timeline":["2000-01-01 00:46:27","2000-01-01 00:57:44","2000-01-01 01:09:01","2000-01-01 01:20:18","2000-01-01 01:31:35"]}} {"trajectory":{"version":1,"type":"STPOINT","leafcount":5,"start_time":"2000-01-01 01:31:35","end_time":"2000-01-01 02:16:44","spatial":"LINESTRING(-41.7122942996211 -46.3224360072054 -56.5283147455193,-35.5646221285375 -38.1688933617746 -49.2775720101781,-31.7230528349367 -33.6970051738123 -44.1693710885011,-23.1585765127093 -26.5895827477798 -40.6539742602035,-16.7020264320696 -21.6133877349397 -37.3055470525287)","timeline":["2000-01-01 01:31:35","2000-01-01 01:42:53","2000-01-01 01:54:10","2000-01-01 02:05:27","2000-01-01 02:16:44"]}} {"trajectory":{"version":1,"type":"STPOINT","leafcount":5,"start_time":"2000-01-01 02:16:44","end_time":"2000-01-01 03:01:52","spatial":"LINESTRING(-16.7020264320696 -21.6133877349397 -37.3055470525287,-12.1044529232507 -14.1236051704424 -28.2295028120279,-3.77185660181567 -7.74744770256802 -24.3842111621052,0.488159407706304 -3.68223926316326 -19.9478872027248,6.33406881305078 4.54123636645575 -15.0410129944794)","timeline":["2000-01-01 02:16:44","2000-01-01 02:28:01","2000-01-01 02:39:18","2000-01-01 02:50:35","2000-01-01 03:01:52"]}} {"trajectory":{"version":1,"type":"STPOINT","leafcount":3,"start_time":"2000-01-01 03:01:52","end_time":"2000-01-01 03:24:26","spatial":"LINESTRING(6.33406881305078 4.54123636645575 -15.0410129944794,15.6666049417108 10.5611746329814 -11.2770220567472,14 11 -10)","timeline":["2000-01-01 03:01:52","2000-01-01 03:13:09","2000-01-01 03:24:26"]}} (5 rows) With traj as( select '{"trajectory":{"version":1,"type":"STPOINT","leafcount":19,"start_time":"2000-01-01 00:01:19.067179","end_time":"2000-01-01 03:24:25.946085","spatial":"LINESTRING(-100 -100 -100,-88.8925775739675 -86.6512698383691 -92.3767832526937,-79.6904716538265 -80.6515727923252 -84.2357598245144,-75.8435507711644 -73.7572890928326 -80.5007370118983,-70.6238425321256 -67.8213750167439 -74.5733173238113,-61.6014582272619 -61.0636760429479 -67.9874239303172,-56.1098577060426 -54.4264591250879 -64.5007972046733,-46.9800617334743 -49.4026757289345 -61.6160059720278,-41.7122942996211 -46.3224360072054 -56.5283147455193,-35.5646221285375 -38.1688933617746 -49.2775720101781,-31.7230528349367 -33.6970051738123 -44.1693710885011,-23.1585765127093 -26.5895827477798 -40.6539742602035,-16.7020264320696 -21.6133877349397 -37.3055470525287,-12.1044529232507 -14.1236051704424 -28.2295028120279,-3.77185660181567 -7.74744770256802 -24.3842111621052,0.488159407706304 -3.68223926316326 -19.9478872027248,6.33406881305078 4.54123636645575 -15.0410129944794,15.6666049417108 10.5611746329814 -11.2770220567472,14 11 -10)","timeline":["2000-01-01 00:01:19.067179","2000-01-01 00:12:36.116007","2000-01-01 00:23:53.164835","2000-01-01 00:35:10.213663","2000-01-01 00:46:27.262491","2000-01-01 00:57:44.311319","2000-01-01 01:09:01.360147","2000-01-01 01:20:18.408975","2000-01-01 01:31:35.457803","2000-01-01 01:42:52.506631","2000-01-01 01:54:09.555459","2000-01-01 02:05:26.604287","2000-01-01 02:16:43.653115","2000-01-01 02:28:00.701943","2000-01-01 02:39:17.750771","2000-01-01 02:50:34.799599","2000-01-01 03:01:51.848427","2000-01-01 03:13:08.897255","2000-01-01 03:24:25.946085"]}}'::trajectory as a ) select ST_split(a, '{"cut_point.max_point":10}') from traj; st_split ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ {"{\"trajectory\":{\"version\":1,\"type\":\"STPOINT\",\"leafcount\":10,\"start_time\":\"Sat Jan 01 00:01:19 2000\",\"end_time\":\"Sat Jan 01 01:42:53 2000\",\"spatial\":\"LINESTRING(-100 -100 -100,-88.8925775739675 -86.6512698383691 -92.3767832526937,-79.6904716538265 -80.6515727923252 -84.2357598245144,-75.8435507711644 -73.7572890928326 -80.5007370118983,-70.6238425321256 -67.8213750167439 -74.5733173238113,-61.6014582272619 -61.0636760429479 -67.9874239303172,-56.1098577060426 -54.4264591250879 -64.5007972046733,-46.9800617334743 -49.4026757289345 -61.6160059720278,-41.7122942996211 -46.3224360072054 -56.5283147455193,-35.5646221285375 -38.1688933617746 -49.2775720101781)\",\"timeline\":[\"Sat Jan 01 00:01:19 2000\",\"Sat Jan 01 00:12:36 2000\",\"Sat Jan 01 00:23:53 2000\",\"Sat Jan 01 00:35:10 2000\",\"Sat Jan 01 00:46:27 2000\",\"Sat Jan 01 00:57:44 2000\",\"Sat Jan 01 01:09:01 2000\",\"Sat Jan 01 01:20:18 2000\",\"Sat Jan 01 01:31:35 2000\",\"Sat Jan 01 01:42:53 2000\"]}}","{\"trajectory\":{\"version\":1,\"type\":\"STPOINT\",\"leafcount\":10,\"start_time\":\"Sat Jan 01 01:42:53 2000\",\"end_time\":\"Sat Jan 01 03:24:26 2000\",\"spatial\":\"LINESTRING(-35.5646221285375 -38.1688933617746 -49.2775720101781,-31.7230528349367 -33.6970051738123 -44.1693710885011,-23.1585765127093 -26.5895827477798 -40.6539742602035,-16.7020264320696 -21.6133877349397 -37.3055470525287,-12.1044529232507 -14.1236051704424 -28.2295028120279,-3.77185660181567 -7.74744770256802 -24.3842111621052,0.488159407706304 -3.68223926316326 -19.9478872027248,6.33406881305078 4.54123636645575 -15.0410129944794,15.6666049417108 10.5611746329814 -11.2770220567472,14 11 -10)\",\"timeline\":[\"Sat Jan 01 01:42:53 2000\",\"Sat Jan 01 01:54:10 2000\",\"Sat Jan 01 02:05:27 2000\",\"Sat Jan 01 02:16:44 2000\",\"Sat Jan 01 02:28:01 2000\",\"Sat Jan 01 02:39:18 2000\",\"Sat Jan 01 02:50:35 2000\",\"Sat Jan 01 03:01:52 2000\",\"Sat Jan 01 03:13:09 2000\",\"Sat Jan 01 03:24:26 2000\"]}}"} (1 row) With traj as( select '{"trajectory":{"version":1,"type":"STPOINT","leafcount":19,"start_time":"2000-01-01 00:01:19.067179","end_time":"2000-01-01 03:24:25.946085","spatial":"LINESTRING(-100 -100 -100,-88.8925775739675 -86.6512698383691 -92.3767832526937,-79.6904716538265 -80.6515727923252 -84.2357598245144,-75.8435507711644 -73.7572890928326 -80.5007370118983,-70.6238425321256 -67.8213750167439 -74.5733173238113,-61.6014582272619 -61.0636760429479 -67.9874239303172,-56.1098577060426 -54.4264591250879 -64.5007972046733,-46.9800617334743 -49.4026757289345 -61.6160059720278,-41.7122942996211 -46.3224360072054 -56.5283147455193,-35.5646221285375 -38.1688933617746 -49.2775720101781,-31.7230528349367 -33.6970051738123 -44.1693710885011,-23.1585765127093 -26.5895827477798 -40.6539742602035,-16.7020264320696 -21.6133877349397 -37.3055470525287,-12.1044529232507 -14.1236051704424 -28.2295028120279,-3.77185660181567 -7.74744770256802 -24.3842111621052,0.488159407706304 -3.68223926316326 -19.9478872027248,6.33406881305078 4.54123636645575 -15.0410129944794,15.6666049417108 10.5611746329814 -11.2770220567472,14 11 -10)","timeline":["2000-01-01 00:01:19.067179","2000-01-01 00:12:36.116007","2000-01-01 00:23:53.164835","2000-01-01 00:35:10.213663","2000-01-01 00:46:27.262491","2000-01-01 00:57:44.311319","2000-01-01 01:09:01.360147","2000-01-01 01:20:18.408975","2000-01-01 01:31:35.457803","2000-01-01 01:42:52.506631","2000-01-01 01:54:09.555459","2000-01-01 02:05:26.604287","2000-01-01 02:16:43.653115","2000-01-01 02:28:00.701943","2000-01-01 02:39:17.750771","2000-01-01 02:50:34.799599","2000-01-01 03:01:51.848427","2000-01-01 03:13:08.897255","2000-01-01 03:24:25.946085"]}}'::trajectory as a ) select ST_split(a, '{"cut_point.max_point":3}') from traj; st_split ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ {"{\"trajectory\":{\"version\":1,\"type\":\"STPOINT\",\"leafcount\":3,\"start_time\":\"Sat Jan 01 00:01:19 2000\",\"end_time\":\"Sat Jan 01 00:23:53 2000\",\"spatial\":\"LINESTRING(-100 -100 -100,-88.8925775739675 -86.6512698383691 -92.3767832526937,-79.6904716538265 -80.6515727923252 -84.2357598245144)\",\"timeline\":[\"Sat Jan 01 00:01:19 2000\",\"Sat Jan 01 00:12:36 2000\",\"Sat Jan 01 00:23:53 2000\"]}}","{\"trajectory\":{\"version\":1,\"type\":\"STPOINT\",\"leafcount\":4,\"start_time\":\"Sat Jan 01 00:23:53 2000\",\"end_time\":\"Sat Jan 01 00:57:44 2000\",\"spatial\":\"LINESTRING(-79.6904716538265 -80.6515727923252 -84.2357598245144,-75.8435507711644 -73.7572890928326 -80.5007370118983,-70.6238425321256 -67.8213750167439 -74.5733173238113,-61.6014582272619 -61.0636760429479 -67.9874239303172)\",\"timeline\":[\"Sat Jan 01 00:23:53 2000\",\"Sat Jan 01 00:35:10 2000\",\"Sat Jan 01 00:46:27 2000\",\"Sat Jan 01 00:57:44 2000\"]}}","{\"trajectory\":{\"version\":1,\"type\":\"STPOINT\",\"leafcount\":4,\"start_time\":\"Sat Jan 01 00:57:44 2000\",\"end_time\":\"Sat Jan 01 01:31:35 2000\",\"spatial\":\"LINESTRING(-61.6014582272619 -61.0636760429479 -67.9874239303172,-56.1098577060426 -54.4264591250879 -64.5007972046733,-46.9800617334743 -49.4026757289345 -61.6160059720278,-41.7122942996211 -46.3224360072054 -56.5283147455193)\",\"timeline\":[\"Sat Jan 01 00:57:44 2000\",\"Sat Jan 01 01:09:01 2000\",\"Sat Jan 01 01:20:18 2000\",\"Sat Jan 01 01:31:35 2000\"]}}","{\"trajectory\":{\"version\":1,\"type\":\"STPOINT\",\"leafcount\":4,\"start_time\":\"Sat Jan 01 01:31:35 2000\",\"end_time\":\"Sat Jan 01 02:05:27 2000\",\"spatial\":\"LINESTRING(-41.7122942996211 -46.3224360072054 -56.5283147455193,-35.5646221285375 -38.1688933617746 -49.2775720101781,-31.7230528349367 -33.6970051738123 -44.1693710885011,-23.1585765127093 -26.5895827477798 -40.6539742602035)\",\"timeline\":[\"Sat Jan 01 01:31:35 2000\",\"Sat Jan 01 01:42:53 2000\",\"Sat Jan 01 01:54:10 2000\",\"Sat Jan 01 02:05:27 2000\"]}}","{\"trajectory\":{\"version\":1,\"type\":\"STPOINT\",\"leafcount\":4,\"start_time\":\"Sat Jan 01 02:05:27 2000\",\"end_time\":\"Sat Jan 01 02:39:18 2000\",\"spatial\":\"LINESTRING(-23.1585765127093 -26.5895827477798 -40.6539742602035,-16.7020264320696 -21.6133877349397 -37.3055470525287,-12.1044529232507 -14.1236051704424 -28.2295028120279,-3.77185660181567 -7.74744770256802 -24.3842111621052)\",\"timeline\":[\"Sat Jan 01 02:05:27 2000\",\"Sat Jan 01 02:16:44 2000\",\"Sat Jan 01 02:28:01 2000\",\"Sat Jan 01 02:39:18 2000\"]}}","{\"trajectory\":{\"version\":1,\"type\":\"STPOINT\",\"leafcount\":4,\"start_time\":\"Sat Jan 01 02:39:18 2000\",\"end_time\":\"Sat Jan 01 03:13:09 2000\",\"spatial\":\"LINESTRING(-3.77185660181567 -7.74744770256802 -24.3842111621052,0.488159407706304 -3.68223926316326 -19.9478872027248,6.33406881305078 4.54123636645575 -15.0410129944794,15.6666049417108 10.5611746329814 -11.2770220567472)\",\"timeline\":[\"Sat Jan 01 02:39:18 2000\",\"Sat Jan 01 02:50:35 2000\",\"Sat Jan 01 03:01:52 2000\",\"Sat Jan 01 03:13:09 2000\"]}}","{\"trajectory\":{\"version\":1,\"type\":\"STPOINT\",\"leafcount\":2,\"start_time\":\"Sat Jan 01 03:13:09 2000\",\"end_time\":\"Sat Jan 01 03:24:26 2000\",\"spatial\":\"LINESTRING(15.6666049417108 10.5611746329814 -11.2770220567472,14 11 -10)\",\"timeline\":[\"Sat Jan 01 03:13:09 2000\",\"Sat Jan 01 03:24:26 2000\"]}}"} (1 row) With traj as( select '{"trajectory":{"version":1,"type":"STPOINT","leafcount":19,"start_time":"2000-01-01 00:01:19.067179","end_time":"2000-01-01 03:24:25.946085","spatial":"LINESTRING(-100 -100 -100,-88.8925775739675 -86.6512698383691 -92.3767832526937,-79.6904716538265 -80.6515727923252 -84.2357598245144,-75.8435507711644 -73.7572890928326 -80.5007370118983,-70.6238425321256 -67.8213750167439 -74.5733173238113,-61.6014582272619 -61.0636760429479 -67.9874239303172,-56.1098577060426 -54.4264591250879 -64.5007972046733,-46.9800617334743 -49.4026757289345 -61.6160059720278,-41.7122942996211 -46.3224360072054 -56.5283147455193,-35.5646221285375 -38.1688933617746 -49.2775720101781,-31.7230528349367 -33.6970051738123 -44.1693710885011,-23.1585765127093 -26.5895827477798 -40.6539742602035,-16.7020264320696 -21.6133877349397 -37.3055470525287,-12.1044529232507 -14.1236051704424 -28.2295028120279,-3.77185660181567 -7.74744770256802 -24.3842111621052,0.488159407706304 -3.68223926316326 -19.9478872027248,6.33406881305078 4.54123636645575 -15.0410129944794,15.6666049417108 10.5611746329814 -11.2770220567472,14 11 -10)","timeline":["2000-01-01 00:01:19.067179","2000-01-01 00:12:36.116007","2000-01-01 00:23:53.164835","2000-01-01 00:35:10.213663","2000-01-01 00:46:27.262491","2000-01-01 00:57:44.311319","2000-01-01 01:09:01.360147","2000-01-01 01:20:18.408975","2000-01-01 01:31:35.457803","2000-01-01 01:42:52.506631","2000-01-01 01:54:09.555459","2000-01-01 02:05:26.604287","2000-01-01 02:16:43.653115","2000-01-01 02:28:00.701943","2000-01-01 02:39:17.750771","2000-01-01 02:50:34.799599","2000-01-01 03:01:51.848427","2000-01-01 03:13:08.897255","2000-01-01 03:24:25.946085"]}}'::trajectory as a ) select ST_split(a, '{"cut_point.even_divide":100}') from traj; st_split -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- {"{\"trajectory\":{\"version\":1,\"type\":\"STPOINT\",\"leafcount\":2,\"start_time\":\"Sat Jan 01 00:01:19 2000\",\"end_time\":\"Sat Jan 01 00:12:36 2000\",\"spatial\":\"LINESTRING(-100 -100 -100,-88.8925775739675 -86.6512698383691 -92.3767832526937)\",\"timeline\":[\"Sat Jan 01 00:01:19 2000\",\"Sat Jan 01 00:12:36 2000\"]}}","{\"trajectory\":{\"version\":1,\"type\":\"STPOINT\",\"leafcount\":2,\"start_time\":\"Sat Jan 01 00:12:36 2000\",\"end_time\":\"Sat Jan 01 00:23:53 2000\",\"spatial\":\"LINESTRING(-88.8925775739675 -86.6512698383691 -92.3767832526937,-79.6904716538265 -80.6515727923252 -84.2357598245144)\",\"timeline\":[\"Sat Jan 01 00:12:36 2000\",\"Sat Jan 01 00:23:53 2000\"]}}","{\"trajectory\":{\"version\":1,\"type\":\"STPOINT\",\"leafcount\":2,\"start_time\":\"Sat Jan 01 00:23:53 2000\",\"end_time\":\"Sat Jan 01 00:35:10 2000\",\"spatial\":\"LINESTRING(-79.6904716538265 -80.6515727923252 -84.2357598245144,-75.8435507711644 -73.7572890928326 -80.5007370118983)\",\"timeline\":[\"Sat Jan 01 00:23:53 2000\",\"Sat Jan 01 00:35:10 2000\"]}}","{\"trajectory\":{\"version\":1,\"type\":\"STPOINT\",\"leafcount\":2,\"start_time\":\"Sat Jan 01 00:35:10 2000\",\"end_time\":\"Sat Jan 01 00:46:27 2000\",\"spatial\":\"LINESTRING(-75.8435507711644 -73.7572890928326 -80.5007370118983,-70.6238425321256 -67.8213750167439 -74.5733173238113)\",\"timeline\":[\"Sat Jan 01 00:35:10 2000\",\"Sat Jan 01 00:46:27 2000\"]}}","{\"trajectory\":{\"version\":1,\"type\":\"STPOINT\",\"leafcount\":2,\"start_time\":\"Sat Jan 01 00:46:27 2000\",\"end_time\":\"Sat Jan 01 00:57:44 2000\",\"spatial\":\"LINESTRING(-70.6238425321256 -67.8213750167439 -74.5733173238113,-61.6014582272619 -61.0636760429479 -67.9874239303172)\",\"timeline\":[\"Sat Jan 01 00:46:27 2000\",\"Sat Jan 01 00:57:44 2000\"]}}","{\"trajectory\":{\"version\":1,\"type\":\"STPOINT\",\"leafcount\":2,\"start_time\":\"Sat Jan 01 00:57:44 2000\",\"end_time\":\"Sat Jan 01 01:09:01 2000\",\"spatial\":\"LINESTRING(-61.6014582272619 -61.0636760429479 -67.9874239303172,-56.1098577060426 -54.4264591250879 -64.5007972046733)\",\"timeline\":[\"Sat Jan 01 00:57:44 2000\",\"Sat Jan 01 01:09:01 2000\"]}}","{\"trajectory\":{\"version\":1,\"type\":\"STPOINT\",\"leafcount\":2,\"start_time\":\"Sat Jan 01 01:09:01 2000\",\"end_time\":\"Sat Jan 01 01:20:18 2000\",\"spatial\":\"LINESTRING(-56.1098577060426 -54.4264591250879 -64.5007972046733,-46.9800617334743 -49.4026757289345 -61.6160059720278)\",\"timeline\":[\"Sat Jan 01 01:09:01 2000\",\"Sat Jan 01 01:20:18 2000\"]}}","{\"trajectory\":{\"version\":1,\"type\":\"STPOINT\",\"leafcount\":2,\"start_time\":\"Sat Jan 01 01:20:18 2000\",\"end_time\":\"Sat Jan 01 01:31:35 2000\",\"spatial\":\"LINESTRING(-46.9800617334743 -49.4026757289345 -61.6160059720278,-41.7122942996211 -46.3224360072054 -56.5283147455193)\",\"timeline\":[\"Sat Jan 01 01:20:18 2000\",\"Sat Jan 01 01:31:35 2000\"]}}","{\"trajectory\":{\"version\":1,\"type\":\"STPOINT\",\"leafcount\":2,\"start_time\":\"Sat Jan 01 01:31:35 2000\",\"end_time\":\"Sat Jan 01 01:42:53 2000\",\"spatial\":\"LINESTRING(-41.7122942996211 -46.3224360072054 -56.5283147455193,-35.5646221285375 -38.1688933617746 -49.2775720101781)\",\"timeline\":[\"Sat Jan 01 01:31:35 2000\",\"Sat Jan 01 01:42:53 2000\"]}}","{\"trajectory\":{\"version\":1,\"type\":\"STPOINT\",\"leafcount\":2,\"start_time\":\"Sat Jan 01 01:42:53 2000\",\"end_time\":\"Sat Jan 01 01:54:10 2000\",\"spatial\":\"LINESTRING(-35.5646221285375 -38.1688933617746 -49.2775720101781,-31.7230528349367 -33.6970051738123 -44.1693710885011)\",\"timeline\":[\"Sat Jan 01 01:42:53 2000\",\"Sat Jan 01 01:54:10 2000\"]}}","{\"trajectory\":{\"version\":1,\"type\":\"STPOINT\",\"leafcount\":2,\"start_time\":\"Sat Jan 01 01:54:10 2000\",\"end_time\":\"Sat Jan 01 02:05:27 2000\",\"spatial\":\"LINESTRING(-31.7230528349367 -33.6970051738123 -44.1693710885011,-23.1585765127093 -26.5895827477798 -40.6539742602035)\",\"timeline\":[\"Sat Jan 01 01:54:10 2000\",\"Sat Jan 01 02:05:27 2000\"]}}","{\"trajectory\":{\"version\":1,\"type\":\"STPOINT\",\"leafcount\":2,\"start_time\":\"Sat Jan 01 02:05:27 2000\",\"end_time\":\"Sat Jan 01 02:16:44 2000\",\"spatial\":\"LINESTRING(-23.1585765127093 -26.5895827477798 -40.6539742602035,-16.7020264320696 -21.6133877349397 -37.3055470525287)\",\"timeline\":[\"Sat Jan 01 02:05:27 2000\",\"Sat Jan 01 02:16:44 2000\"]}}","{\"trajectory\":{\"version\":1,\"type\":\"STPOINT\",\"leafcount\":2,\"start_time\":\"Sat Jan 01 02:16:44 2000\",\"end_time\":\"Sat Jan 01 02:28:01 2000\",\"spatial\":\"LINESTRING(-16.7020264320696 -21.6133877349397 -37.3055470525287,-12.1044529232507 -14.1236051704424 -28.2295028120279)\",\"timeline\":[\"Sat Jan 01 02:16:44 2000\",\"Sat Jan 01 02:28:01 2000\"]}}","{\"trajectory\":{\"version\":1,\"type\":\"STPOINT\",\"leafcount\":2,\"start_time\":\"Sat Jan 01 02:28:01 2000\",\"end_time\":\"Sat Jan 01 02:39:18 2000\",\"spatial\":\"LINESTRING(-12.1044529232507 -14.1236051704424 -28.2295028120279,-3.77185660181567 -7.74744770256802 -24.3842111621052)\",\"timeline\":[\"Sat Jan 01 02:28:01 2000\",\"Sat Jan 01 02:39:18 2000\"]}}","{\"trajectory\":{\"version\":1,\"type\":\"STPOINT\",\"leafcount\":2,\"start_time\":\"Sat Jan 01 02:39:18 2000\",\"end_time\":\"Sat Jan 01 02:50:35 2000\",\"spatial\":\"LINESTRING(-3.77185660181567 -7.74744770256802 -24.3842111621052,0.488159407706304 -3.68223926316326 -19.9478872027248)\",\"timeline\":[\"Sat Jan 01 02:39:18 2000\",\"Sat Jan 01 02:50:35 2000\"]}}","{\"trajectory\":{\"version\":1,\"type\":\"STPOINT\",\"leafcount\":2,\"start_time\":\"Sat Jan 01 02:50:35 2000\",\"end_time\":\"Sat Jan 01 03:01:52 2000\",\"spatial\":\"LINESTRING(0.488159407706304 -3.68223926316326 -19.9478872027248,6.33406881305078 4.54123636645575 -15.0410129944794)\",\"timeline\":[\"Sat Jan 01 02:50:35 2000\",\"Sat Jan 01 03:01:52 2000\"]}}","{\"trajectory\":{\"version\":1,\"type\":\"STPOINT\",\"leafcount\":2,\"start_time\":\"Sat Jan 01 03:01:52 2000\",\"end_time\":\"Sat Jan 01 03:13:09 2000\",\"spatial\":\"LINESTRING(6.33406881305078 4.54123636645575 -15.0410129944794,15.6666049417108 10.5611746329814 -11.2770220567472)\",\"timeline\":[\"Sat Jan 01 03:01:52 2000\",\"Sat Jan 01 03:13:09 2000\"]}}","{\"trajectory\":{\"version\":1,\"type\":\"STPOINT\",\"leafcount\":2,\"start_time\":\"Sat Jan 01 03:13:09 2000\",\"end_time\":\"Sat Jan 01 03:24:26 2000\",\"spatial\":\"LINESTRING(15.6666049417108 10.5611746329814 -11.2770220567472,14 11 -10)\",\"timeline\":[\"Sat Jan 01 03:13:09 2000\",\"Sat Jan 01 03:24:26 2000\"]}}"} (1 row) With traj as( select '{"trajectory":{"version":1,"type":"STPOINT","leafcount":19,"start_time":"2000-01-01 00:01:19.067179","end_time":"2000-01-01 03:24:25.946085","spatial":"LINESTRING(-100 -100 -100,-88.8925775739675 -86.6512698383691 -92.3767832526937,-79.6904716538265 -80.6515727923252 -84.2357598245144,-75.8435507711644 -73.7572890928326 -80.5007370118983,-70.6238425321256 -67.8213750167439 -74.5733173238113,-61.6014582272619 -61.0636760429479 -67.9874239303172,-56.1098577060426 -54.4264591250879 -64.5007972046733,-46.9800617334743 -49.4026757289345 -61.6160059720278,-41.7122942996211 -46.3224360072054 -56.5283147455193,-35.5646221285375 -38.1688933617746 -49.2775720101781,-31.7230528349367 -33.6970051738123 -44.1693710885011,-23.1585765127093 -26.5895827477798 -40.6539742602035,-16.7020264320696 -21.6133877349397 -37.3055470525287,-12.1044529232507 -14.1236051704424 -28.2295028120279,-3.77185660181567 -7.74744770256802 -24.3842111621052,0.488159407706304 -3.68223926316326 -19.9478872027248,6.33406881305078 4.54123636645575 -15.0410129944794,15.6666049417108 10.5611746329814 -11.2770220567472,14 11 -10)","timeline":["2000-01-01 00:01:19.067179","2000-01-01 00:12:36.116007","2000-01-01 00:23:53.164835","2000-01-01 00:35:10.213663","2000-01-01 00:46:27.262491","2000-01-01 00:57:44.311319","2000-01-01 01:09:01.360147","2000-01-01 01:20:18.408975","2000-01-01 01:31:35.457803","2000-01-01 01:42:52.506631","2000-01-01 01:54:09.555459","2000-01-01 02:05:26.604287","2000-01-01 02:16:43.653115","2000-01-01 02:28:00.701943","2000-01-01 02:39:17.750771","2000-01-01 02:50:34.799599","2000-01-01 03:01:51.848427","2000-01-01 03:13:08.897255","2000-01-01 03:24:25.946085"]}}'::trajectory as a ) select ST_split(a, '{"cut_edge.geohash":2}') from traj; st_split ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ {"{\"trajectory\":{\"version\":1,\"type\":\"STPOINT\",\"leafcount\":2,\"start_time\":\"Sat Jan 01 00:01:19 2000\",\"end_time\":\"Sat Jan 01 00:09:46.164345 2000\",\"spatial\":\"LINESTRING(-100 -100 -100,-91.679041907702 -90 -94.2891820757467)\",\"timeline\":[\"Sat Jan 01 00:01:19 2000\",\"Sat Jan 01 00:09:46.164345 2000\"]}}","{\"trajectory\":{\"version\":1,\"type\":\"STPOINT\",\"leafcount\":16,\"start_time\":\"Sat Jan 01 00:09:46.164345 2000\",\"end_time\":\"Sat Jan 01 02:49:17.421906 2000\",\"spatial\":\"LINESTRING(-91.679041907702 -90 -94.2891820757467,-88.8925775739675 -86.6512698383691 -92.3767832526937,-79.6904716538265 -80.6515727923252 -84.2357598245144,-75.8435507711644 -73.7572890928326 -80.5007370118983,-70.6238425321256 -67.8213750167439 -74.5733173238113,-61.6014582272619 -61.0636760429479 -67.9874239303172,-56.1098577060426 -54.4264591250879 -64.5007972046733,-46.9800617334743 -49.4026757289345 -61.6160059720278,-41.7122942996211 -46.3224360072054 -56.5283147455193,-35.5646221285375 -38.1688933617746 -49.2775720101781,-31.7230528349367 -33.6970051738123 -44.1693710885011,-23.1585765127093 -26.5895827477798 -40.6539742602035,-16.7020264320696 -21.6133877349397 -37.3055470525287,-12.1044529232507 -14.1236051704424 -28.2295028120279,-3.77185660181567 -7.74744770256802 -24.3842111621052,0 -4.14807548057343 -20.4562499111193)\",\"timeline\":[\"Sat Jan 01 00:09:46.164345 2000\",\"Sat Jan 01 00:12:36 2000\",\"Sat Jan 01 00:23:53 2000\",\"Sat Jan 01 00:35:10 2000\",\"Sat Jan 01 00:46:27 2000\",\"Sat Jan 01 00:57:44 2000\",\"Sat Jan 01 01:09:01 2000\",\"Sat Jan 01 01:20:18 2000\",\"Sat Jan 01 01:31:35 2000\",\"Sat Jan 01 01:42:53 2000\",\"Sat Jan 01 01:54:10 2000\",\"Sat Jan 01 02:05:27 2000\",\"Sat Jan 01 02:16:44 2000\",\"Sat Jan 01 02:28:01 2000\",\"Sat Jan 01 02:39:18 2000\",\"Sat Jan 01 02:49:17.421906 2000\"]}}","{\"trajectory\":{\"version\":1,\"type\":\"STPOINT\",\"leafcount\":3,\"start_time\":\"Sat Jan 01 02:49:17.421906 2000\",\"end_time\":\"Sat Jan 01 02:55:38.141408 2000\",\"spatial\":\"LINESTRING(0 -4.14807548057343 -20.4562499111193,0.488159407706304 -3.68223926316326 -19.9478872027248,3.10579191624359 0 -17.7507280351428)\",\"timeline\":[\"Sat Jan 01 02:49:17.421906 2000\",\"Sat Jan 01 02:50:35 2000\",\"Sat Jan 01 02:55:38.141408 2000\"]}}","{\"trajectory\":{\"version\":1,\"type\":\"STPOINT\",\"leafcount\":4,\"start_time\":\"Sat Jan 01 02:55:38.141408 2000\",\"end_time\":\"Sat Jan 01 03:24:26 2000\",\"spatial\":\"LINESTRING(3.10579191624359 0 -17.7507280351428,6.33406881305078 4.54123636645575 -15.0410129944794,15.6666049417108 10.5611746329814 -11.2770220567472,14 11 -10)\",\"timeline\":[\"Sat Jan 01 02:55:38.141408 2000\",\"Sat Jan 01 03:01:52 2000\",\"Sat Jan 01 03:13:09 2000\",\"Sat Jan 01 03:24:26 2000\"]}}"} (1 row) With traj as( select '{"trajectory":{"version":1,"type":"STPOINT","leafcount":19,"start_time":"2000-01-01 00:01:19.067179","end_time":"2000-01-01 03:24:25.946085","spatial":"LINESTRING(-100 -100 -100,-88.8925775739675 -86.6512698383691 -92.3767832526937,-79.6904716538265 -80.6515727923252 -84.2357598245144,-75.8435507711644 -73.7572890928326 -80.5007370118983,-70.6238425321256 -67.8213750167439 -74.5733173238113,-61.6014582272619 -61.0636760429479 -67.9874239303172,-56.1098577060426 -54.4264591250879 -64.5007972046733,-46.9800617334743 -49.4026757289345 -61.6160059720278,-41.7122942996211 -46.3224360072054 -56.5283147455193,-35.5646221285375 -38.1688933617746 -49.2775720101781,-31.7230528349367 -33.6970051738123 -44.1693710885011,-23.1585765127093 -26.5895827477798 -40.6539742602035,-16.7020264320696 -21.6133877349397 -37.3055470525287,-12.1044529232507 -14.1236051704424 -28.2295028120279,-3.77185660181567 -7.74744770256802 -24.3842111621052,0.488159407706304 -3.68223926316326 -19.9478872027248,6.33406881305078 4.54123636645575 -15.0410129944794,15.6666049417108 10.5611746329814 -11.2770220567472,14 11 -10)","timeline":["2000-01-01 00:01:19.067179","2000-01-01 00:12:36.116007","2000-01-01 00:23:53.164835","2000-01-01 00:35:10.213663","2000-01-01 00:46:27.262491","2000-01-01 00:57:44.311319","2000-01-01 01:09:01.360147","2000-01-01 01:20:18.408975","2000-01-01 01:31:35.457803","2000-01-01 01:42:52.506631","2000-01-01 01:54:09.555459","2000-01-01 02:05:26.604287","2000-01-01 02:16:43.653115","2000-01-01 02:28:00.701943","2000-01-01 02:39:17.750771","2000-01-01 02:50:34.799599","2000-01-01 03:01:51.848427","2000-01-01 03:13:08.897255","2000-01-01 03:24:25.946085"]}}'::trajectory as a ) select ST_split(a, '{"cut_edge.geohash":20}') from traj; st_split ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- {"{\"trajectory\":{\"version\":1,\"type\":\"STPOINT\",\"leafcount\":2,\"start_time\":\"Sat Jan 01 00:01:19 2000\",\"end_time\":\"Sat Jan 01 00:09:46.164345 2000\",\"spatial\":\"LINESTRING(-100 -100 -100,-91.679041907702 -90 -94.2891820757467)\",\"timeline\":[\"Sat Jan 01 00:01:19 2000\",\"Sat Jan 01 00:09:46.164345 2000\"]}}","{\"trajectory\":{\"version\":1,\"type\":\"STPOINT\",\"leafcount\":2,\"start_time\":\"Sat Jan 01 00:09:46.164345 2000\",\"end_time\":\"Sat Jan 01 00:11:28.502343 2000\",\"spatial\":\"LINESTRING(-91.679041907702 -90 -94.2891820757467,-90 -87.9821531498204 -93.1368264797063)\",\"timeline\":[\"Sat Jan 01 00:09:46.164345 2000\",\"Sat Jan 01 00:11:28.502343 2000\"]}}","{\"trajectory\":{\"version\":1,\"type\":\"STPOINT\",\"leafcount\":3,\"start_time\":\"Sat Jan 01 00:11:28.502343 2000\",\"end_time\":\"Sat Jan 01 00:23:13.683811 2000\",\"spatial\":\"LINESTRING(-90 -87.9821531498204 -93.1368264797063,-88.8925775739675 -86.6512698383691 -92.3767832526937,-80.2248759822245 -81 -84.7085427065378)\",\"timeline\":[\"Sat Jan 01 00:11:28.502343 2000\",\"Sat Jan 01 00:12:36 2000\",\"Sat Jan 01 00:23:13.683811 2000\"]}}","{\"trajectory\":{\"version\":1,\"type\":\"STPOINT\",\"leafcount\":4,\"start_time\":\"Sat Jan 01 00:23:13.683811 2000\",\"end_time\":\"Sat Jan 01 00:38:30.421485 2000\",\"spatial\":\"LINESTRING(-80.2248759822245 -81 -84.7085427065378,-79.6904716538265 -80.6515727923252 -84.2357598245144,-75.8435507711644 -73.7572890928326 -80.5007370118983,-74.2982897989444 -72 -78.7459626278695)\",\"timeline\":[\"Sat Jan 01 00:23:13.683811 2000\",\"Sat Jan 01 00:23:53 2000\",\"Sat Jan 01 00:35:10 2000\",\"Sat Jan 01 00:38:30.421485 2000\"]}}","{\"trajectory\":{\"version\":1,\"type\":\"STPOINT\",\"leafcount\":2,\"start_time\":\"Sat Jan 01 00:38:30.421485 2000\",\"end_time\":\"Sat Jan 01 00:43:28.511363 2000\",\"spatial\":\"LINESTRING(-74.2982897989444 -72 -78.7459626278695,-72 -69.3863576001334 -76.1360603920884)\",\"timeline\":[\"Sat Jan 01 00:38:30.421485 2000\",\"Sat Jan 01 00:43:28.511363 2000\"]}}","{\"trajectory\":{\"version\":1,\"type\":\"STPOINT\",\"leafcount\":3,\"start_time\":\"Sat Jan 01 00:43:28.511363 2000\",\"end_time\":\"Sat Jan 01 00:54:30.015135 2000\",\"spatial\":\"LINESTRING(-72 -69.3863576001334 -76.1360603920884,-70.6238425321256 -67.8213750167439 -74.5733173238113,-64.1866960348325 -63 -69.8745194055467)\",\"timeline\":[\"Sat Jan 01 00:43:28.511363 2000\",\"Sat Jan 01 00:46:27 2000\",\"Sat Jan 01 00:54:30.015135 2000\"]}}","{\"trajectory\":{\"version\":1,\"type\":\"STPOINT\",\"leafcount\":4,\"start_time\":\"Sat Jan 01 00:54:30.015135 2000\",\"end_time\":\"Sat Jan 01 01:09:58.469202 2000\",\"spatial\":\"LINESTRING(-64.1866960348325 -63 -69.8745194055467,-61.6014582272619 -61.0636760429479 -67.9874239303172,-56.1098577060426 -54.4264591250879 -64.5007972046733,-55.3348472217782 -54 -64.255912931582)\",\"timeline\":[\"Sat Jan 01 00:54:30.015135 2000\",\"Sat Jan 01 00:57:44 2000\",\"Sat Jan 01 01:09:01 2000\",\"Sat Jan 01 01:09:58.469202 2000\"]}}","{\"trajectory\":{\"version\":1,\"type\":\"STPOINT\",\"leafcount\":2,\"start_time\":\"Sat Jan 01 01:09:58.469202 2000\",\"end_time\":\"Sat Jan 01 01:11:37.451871 2000\",\"spatial\":\"LINESTRING(-55.3348472217782 -54 -64.255912931582,-54 -53.2654837710153 -63.8341340031287)\",\"timeline\":[\"Sat Jan 01 01:09:58.469202 2000\",\"Sat Jan 01 01:11:37.451871 2000\"]}}","{\"trajectory\":{\"version\":1,\"type\":\"STPOINT\",\"leafcount\":4,\"start_time\":\"Sat Jan 01 01:11:37.451871 2000\",\"end_time\":\"Sat Jan 01 01:33:24.965894 2000\",\"spatial\":\"LINESTRING(-54 -53.2654837710153 -63.8341340031287,-46.9800617334743 -49.4026757289345 -61.6160059720278,-41.7122942996211 -46.3224360072054 -56.5283147455193,-40.7151936044906 -45 -55.3523052869103)\",\"timeline\":[\"Sat Jan 01 01:11:37.451871 2000\",\"Sat Jan 01 01:20:18 2000\",\"Sat Jan 01 01:31:35 2000\",\"Sat Jan 01 01:33:24.965894 2000\"]}}","{\"trajectory\":{\"version\":1,\"type\":\"STPOINT\",\"leafcount\":2,\"start_time\":\"Sat Jan 01 01:33:24.965894 2000\",\"end_time\":\"Sat Jan 01 01:42:04.984069 2000\",\"spatial\":\"LINESTRING(-40.7151936044906 -45 -55.3523052869103,-36 -38.7463268915818 -49.7910692902516)\",\"timeline\":[\"Sat Jan 01 01:33:24.965894 2000\",\"Sat Jan 01 01:42:04.984069 2000\"]}}","{\"trajectory\":{\"version\":1,\"type\":\"STPOINT\",\"leafcount\":3,\"start_time\":\"Sat Jan 01 01:42:04.984069 2000\",\"end_time\":\"Sat Jan 01 01:48:21.349176 2000\",\"spatial\":\"LINESTRING(-36 -38.7463268915818 -49.7910692902516,-35.5646221285375 -38.1688933617746 -49.2775720101781,-33.7014373194637 -36 -46.8000630466357)\",\"timeline\":[\"Sat Jan 01 01:42:04.984069 2000\",\"Sat Jan 01 01:42:53 2000\",\"Sat Jan 01 01:48:21.349176 2000\"]}}","{\"trajectory\":{\"version\":1,\"type\":\"STPOINT\",\"leafcount\":3,\"start_time\":\"Sat Jan 01 01:48:21.349176 2000\",\"end_time\":\"Sat Jan 01 02:04:47.906716 2000\",\"spatial\":\"LINESTRING(-33.7014373194637 -36 -46.8000630466357,-31.7230528349367 -33.6970051738123 -44.1693710885011,-23.6531311667283 -27 -40.8569704267813)\",\"timeline\":[\"Sat Jan 01 01:48:21.349176 2000\",\"Sat Jan 01 01:54:10 2000\",\"Sat Jan 01 02:04:47.906716 2000\"]}}","{\"trajectory\":{\"version\":1,\"type\":\"STPOINT\",\"leafcount\":3,\"start_time\":\"Sat Jan 01 02:04:47.906716 2000\",\"end_time\":\"Sat Jan 01 02:14:27.901294 2000\",\"spatial\":\"LINESTRING(-23.6531311667283 -27 -40.8569704267813,-23.1585765127093 -26.5895827477798 -40.6539742602035,-18 -22.6137624729753 -37.9786882742987)\",\"timeline\":[\"Sat Jan 01 02:04:47.906716 2000\",\"Sat Jan 01 02:05:27 2000\",\"Sat Jan 01 02:14:27.901294 2000\"]}}","{\"trajectory\":{\"version\":1,\"type\":\"STPOINT\",\"leafcount\":3,\"start_time\":\"Sat Jan 01 02:14:27.901294 2000\",\"end_time\":\"Sat Jan 01 02:22:10.613419 2000\",\"spatial\":\"LINESTRING(-18 -22.6137624729753 -37.9786882742987,-16.7020264320696 -21.6133877349397 -37.3055470525287,-14.4839626118998 -18 -32.9268796268747)\",\"timeline\":[\"Sat Jan 01 02:14:27.901294 2000\",\"Sat Jan 01 02:16:44 2000\",\"Sat Jan 01 02:22:10.613419 2000\"]}}","{\"trajectory\":{\"version\":1,\"type\":\"STPOINT\",\"leafcount\":3,\"start_time\":\"Sat Jan 01 02:22:10.613419 2000\",\"end_time\":\"Sat Jan 01 02:37:05.008004 2000\",\"spatial\":\"LINESTRING(-14.4839626118998 -18 -32.9268796268747,-12.1044529232507 -14.1236051704424 -28.2295028120279,-5.40873786119054 -9 -25.1395922697196)\",\"timeline\":[\"Sat Jan 01 02:22:10.613419 2000\",\"Sat Jan 01 02:28:01 2000\",\"Sat Jan 01 02:37:05.008004 2000\"]}}","{\"trajectory\":{\"version\":1,\"type\":\"STPOINT\",\"leafcount\":3,\"start_time\":\"Sat Jan 01 02:37:05.008004 2000\",\"end_time\":\"Sat Jan 01 02:49:17.421906 2000\",\"spatial\":\"LINESTRING(-5.40873786119054 -9 -25.1395922697196,-3.77185660181567 -7.74744770256802 -24.3842111621052,0 -4.14807548057343 -20.4562499111193)\",\"timeline\":[\"Sat Jan 01 02:37:05.008004 2000\",\"Sat Jan 01 02:39:18 2000\",\"Sat Jan 01 02:49:17.421906 2000\"]}}","{\"trajectory\":{\"version\":1,\"type\":\"STPOINT\",\"leafcount\":3,\"start_time\":\"Sat Jan 01 02:49:17.421906 2000\",\"end_time\":\"Sat Jan 01 02:55:38.141408 2000\",\"spatial\":\"LINESTRING(0 -4.14807548057343 -20.4562499111193,0.488159407706304 -3.68223926316326 -19.9478872027248,3.10579191624359 0 -17.7507280351428)\",\"timeline\":[\"Sat Jan 01 02:49:17.421906 2000\",\"Sat Jan 01 02:50:35 2000\",\"Sat Jan 01 02:55:38.141408 2000\"]}}","{\"trajectory\":{\"version\":1,\"type\":\"STPOINT\",\"leafcount\":3,\"start_time\":\"Sat Jan 01 02:55:38.141408 2000\",\"end_time\":\"Sat Jan 01 03:10:13.430886 2000\",\"spatial\":\"LINESTRING(3.10579191624359 0 -17.7507280351428,6.33406881305078 4.54123636645575 -15.0410129944794,13.2463610753472 9 -12.2531528591899)\",\"timeline\":[\"Sat Jan 01 02:55:38.141408 2000\",\"Sat Jan 01 03:01:52 2000\",\"Sat Jan 01 03:10:13.430886 2000\"]}}","{\"trajectory\":{\"version\":1,\"type\":\"STPOINT\",\"leafcount\":3,\"start_time\":\"Sat Jan 01 03:10:13.430886 2000\",\"end_time\":\"Sat Jan 01 03:24:26 2000\",\"spatial\":\"LINESTRING(13.2463610753472 9 -12.2531528591899,15.6666049417108 10.5611746329814 -11.2770220567472,14 11 -10)\",\"timeline\":[\"Sat Jan 01 03:10:13.430886 2000\",\"Sat Jan 01 03:13:09 2000\",\"Sat Jan 01 03:24:26 2000\"]}}"} (1 row) With traj as( select '{"trajectory":{"version":1,"type":"STPOINT","leafcount":19,"start_time":"2000-01-01 00:01:19.067179","end_time":"2000-01-01 03:24:25.946085","spatial":"LINESTRING(-100 -100 -100,-88.8925775739675 -86.6512698383691 -92.3767832526937,-79.6904716538265 -80.6515727923252 -84.2357598245144,-75.8435507711644 -73.7572890928326 -80.5007370118983,-70.6238425321256 -67.8213750167439 -74.5733173238113,-61.6014582272619 -61.0636760429479 -67.9874239303172,-56.1098577060426 -54.4264591250879 -64.5007972046733,-46.9800617334743 -49.4026757289345 -61.6160059720278,-41.7122942996211 -46.3224360072054 -56.5283147455193,-35.5646221285375 -38.1688933617746 -49.2775720101781,-31.7230528349367 -33.6970051738123 -44.1693710885011,-23.1585765127093 -26.5895827477798 -40.6539742602035,-16.7020264320696 -21.6133877349397 -37.3055470525287,-12.1044529232507 -14.1236051704424 -28.2295028120279,-3.77185660181567 -7.74744770256802 -24.3842111621052,0.488159407706304 -3.68223926316326 -19.9478872027248,6.33406881305078 4.54123636645575 -15.0410129944794,15.6666049417108 10.5611746329814 -11.2770220567472,14 11 -10)","timeline":["2000-01-01 00:01:19.067179","2000-01-01 00:12:36.116007","2000-01-01 00:23:53.164835","2000-01-01 00:35:10.213663","2000-01-01 00:46:27.262491","2000-01-01 00:57:44.311319","2000-01-01 01:09:01.360147","2000-01-01 01:20:18.408975","2000-01-01 01:31:35.457803","2000-01-01 01:42:52.506631","2000-01-01 01:54:09.555459","2000-01-01 02:05:26.604287","2000-01-01 02:16:43.653115","2000-01-01 02:28:00.701943","2000-01-01 02:39:17.750771","2000-01-01 02:50:34.799599","2000-01-01 03:01:51.848427","2000-01-01 03:13:08.897255","2000-01-01 03:24:25.946085"]}}'::trajectory as a ) select ST_split(a, '{"drop_edge.spatial_distance_2d":13}') from traj; st_split ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- {"{\"trajectory\":{\"version\":1,\"type\":\"STPOINT\",\"leafcount\":18,\"start_time\":\"Sat Jan 01 00:12:36 2000\",\"end_time\":\"Sat Jan 01 03:24:26 2000\",\"spatial\":\"LINESTRING(-88.8925775739675 -86.6512698383691 -92.3767832526937,-79.6904716538265 -80.6515727923252 -84.2357598245144,-75.8435507711644 -73.7572890928326 -80.5007370118983,-70.6238425321256 -67.8213750167439 -74.5733173238113,-61.6014582272619 -61.0636760429479 -67.9874239303172,-56.1098577060426 -54.4264591250879 -64.5007972046733,-46.9800617334743 -49.4026757289345 -61.6160059720278,-41.7122942996211 -46.3224360072054 -56.5283147455193,-35.5646221285375 -38.1688933617746 -49.2775720101781,-31.7230528349367 -33.6970051738123 -44.1693710885011,-23.1585765127093 -26.5895827477798 -40.6539742602035,-16.7020264320696 -21.6133877349397 -37.3055470525287,-12.1044529232507 -14.1236051704424 -28.2295028120279,-3.77185660181567 -7.74744770256802 -24.3842111621052,0.488159407706304 -3.68223926316326 -19.9478872027248,6.33406881305078 4.54123636645575 -15.0410129944794,15.6666049417108 10.5611746329814 -11.2770220567472,14 11 -10)\",\"timeline\":[\"Sat Jan 01 00:12:36 2000\",\"Sat Jan 01 00:23:53 2000\",\"Sat Jan 01 00:35:10 2000\",\"Sat Jan 01 00:46:27 2000\",\"Sat Jan 01 00:57:44 2000\",\"Sat Jan 01 01:09:01 2000\",\"Sat Jan 01 01:20:18 2000\",\"Sat Jan 01 01:31:35 2000\",\"Sat Jan 01 01:42:53 2000\",\"Sat Jan 01 01:54:10 2000\",\"Sat Jan 01 02:05:27 2000\",\"Sat Jan 01 02:16:44 2000\",\"Sat Jan 01 02:28:01 2000\",\"Sat Jan 01 02:39:18 2000\",\"Sat Jan 01 02:50:35 2000\",\"Sat Jan 01 03:01:52 2000\",\"Sat Jan 01 03:13:09 2000\",\"Sat Jan 01 03:24:26 2000\"]}}"} (1 row) With traj as( select '{"trajectory":{"version":1,"type":"STPOINT","leafcount":19,"start_time":"2000-01-01 00:01:19.067179","end_time":"2000-01-01 03:24:25.946085","spatial":"LINESTRING(-100 -100 -100,-88.8925775739675 -86.6512698383691 -92.3767832526937,-79.6904716538265 -80.6515727923252 -84.2357598245144,-75.8435507711644 -73.7572890928326 -80.5007370118983,-70.6238425321256 -67.8213750167439 -74.5733173238113,-61.6014582272619 -61.0636760429479 -67.9874239303172,-56.1098577060426 -54.4264591250879 -64.5007972046733,-46.9800617334743 -49.4026757289345 -61.6160059720278,-41.7122942996211 -46.3224360072054 -56.5283147455193,-35.5646221285375 -38.1688933617746 -49.2775720101781,-31.7230528349367 -33.6970051738123 -44.1693710885011,-23.1585765127093 -26.5895827477798 -40.6539742602035,-16.7020264320696 -21.6133877349397 -37.3055470525287,-12.1044529232507 -14.1236051704424 -28.2295028120279,-3.77185660181567 -7.74744770256802 -24.3842111621052,0.488159407706304 -3.68223926316326 -19.9478872027248,6.33406881305078 4.54123636645575 -15.0410129944794,15.6666049417108 10.5611746329814 -11.2770220567472,14 11 -10)","timeline":["2000-01-01 00:01:19.067179","2000-01-01 00:12:36.116007","2000-01-01 00:23:53.164835","2000-01-01 00:35:10.213663","2000-01-01 00:46:27.262491","2000-01-01 00:57:44.311319","2000-01-01 01:09:01.360147","2000-01-01 01:20:18.408975","2000-01-01 01:31:35.457803","2000-01-01 01:42:52.506631","2000-01-01 01:54:09.555459","2000-01-01 02:05:26.604287","2000-01-01 02:16:43.653115","2000-01-01 02:28:00.701943","2000-01-01 02:39:17.750771","2000-01-01 02:50:34.799599","2000-01-01 03:01:51.848427","2000-01-01 03:13:08.897255","2000-01-01 03:24:25.946085"]}}'::trajectory as a ) select ST_split(a, '{"drop_edge.spatial_distance_2d":10}') from traj; st_split ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- {"{\"trajectory\":{\"version\":1,\"type\":\"STPOINT\",\"leafcount\":3,\"start_time\":\"Sat Jan 01 00:23:53 2000\",\"end_time\":\"Sat Jan 01 00:46:27 2000\",\"spatial\":\"LINESTRING(-79.6904716538265 -80.6515727923252 -84.2357598245144,-75.8435507711644 -73.7572890928326 -80.5007370118983,-70.6238425321256 -67.8213750167439 -74.5733173238113)\",\"timeline\":[\"Sat Jan 01 00:23:53 2000\",\"Sat Jan 01 00:35:10 2000\",\"Sat Jan 01 00:46:27 2000\"]}}","{\"trajectory\":{\"version\":1,\"type\":\"STPOINT\",\"leafcount\":2,\"start_time\":\"Sat Jan 01 00:57:44 2000\",\"end_time\":\"Sat Jan 01 01:09:01 2000\",\"spatial\":\"LINESTRING(-61.6014582272619 -61.0636760429479 -67.9874239303172,-56.1098577060426 -54.4264591250879 -64.5007972046733)\",\"timeline\":[\"Sat Jan 01 00:57:44 2000\",\"Sat Jan 01 01:09:01 2000\"]}}","{\"trajectory\":{\"version\":1,\"type\":\"STPOINT\",\"leafcount\":2,\"start_time\":\"Sat Jan 01 01:20:18 2000\",\"end_time\":\"Sat Jan 01 01:31:35 2000\",\"spatial\":\"LINESTRING(-46.9800617334743 -49.4026757289345 -61.6160059720278,-41.7122942996211 -46.3224360072054 -56.5283147455193)\",\"timeline\":[\"Sat Jan 01 01:20:18 2000\",\"Sat Jan 01 01:31:35 2000\"]}}","{\"trajectory\":{\"version\":1,\"type\":\"STPOINT\",\"leafcount\":2,\"start_time\":\"Sat Jan 01 01:42:53 2000\",\"end_time\":\"Sat Jan 01 01:54:10 2000\",\"spatial\":\"LINESTRING(-35.5646221285375 -38.1688933617746 -49.2775720101781,-31.7230528349367 -33.6970051738123 -44.1693710885011)\",\"timeline\":[\"Sat Jan 01 01:42:53 2000\",\"Sat Jan 01 01:54:10 2000\"]}}","{\"trajectory\":{\"version\":1,\"type\":\"STPOINT\",\"leafcount\":3,\"start_time\":\"Sat Jan 01 02:05:27 2000\",\"end_time\":\"Sat Jan 01 02:28:01 2000\",\"spatial\":\"LINESTRING(-23.1585765127093 -26.5895827477798 -40.6539742602035,-16.7020264320696 -21.6133877349397 -37.3055470525287,-12.1044529232507 -14.1236051704424 -28.2295028120279)\",\"timeline\":[\"Sat Jan 01 02:05:27 2000\",\"Sat Jan 01 02:16:44 2000\",\"Sat Jan 01 02:28:01 2000\"]}}","{\"trajectory\":{\"version\":1,\"type\":\"STPOINT\",\"leafcount\":2,\"start_time\":\"Sat Jan 01 02:39:18 2000\",\"end_time\":\"Sat Jan 01 02:50:35 2000\",\"spatial\":\"LINESTRING(-3.77185660181567 -7.74744770256802 -24.3842111621052,0.488159407706304 -3.68223926316326 -19.9478872027248)\",\"timeline\":[\"Sat Jan 01 02:39:18 2000\",\"Sat Jan 01 02:50:35 2000\"]}}","{\"trajectory\":{\"version\":1,\"type\":\"STPOINT\",\"leafcount\":2,\"start_time\":\"Sat Jan 01 03:13:09 2000\",\"end_time\":\"Sat Jan 01 03:24:26 2000\",\"spatial\":\"LINESTRING(15.6666049417108 10.5611746329814 -11.2770220567472,14 11 -10)\",\"timeline\":[\"Sat Jan 01 03:13:09 2000\",\"Sat Jan 01 03:24:26 2000\"]}}"} (1 row) With traj as( select '{"trajectory":{"version":1,"type":"STPOINT","leafcount":19,"start_time":"2000-01-01 00:01:19.067179","end_time":"2000-01-01 03:24:25.946085","spatial":"LINESTRING(-100 -100 -100,-88.8925775739675 -86.6512698383691 -92.3767832526937,-79.6904716538265 -80.6515727923252 -84.2357598245144,-75.8435507711644 -73.7572890928326 -80.5007370118983,-70.6238425321256 -67.8213750167439 -74.5733173238113,-61.6014582272619 -61.0636760429479 -67.9874239303172,-56.1098577060426 -54.4264591250879 -64.5007972046733,-46.9800617334743 -49.4026757289345 -61.6160059720278,-41.7122942996211 -46.3224360072054 -56.5283147455193,-35.5646221285375 -38.1688933617746 -49.2775720101781,-31.7230528349367 -33.6970051738123 -44.1693710885011,-23.1585765127093 -26.5895827477798 -40.6539742602035,-16.7020264320696 -21.6133877349397 -37.3055470525287,-12.1044529232507 -14.1236051704424 -28.2295028120279,-3.77185660181567 -7.74744770256802 -24.3842111621052,0.488159407706304 -3.68223926316326 -19.9478872027248,6.33406881305078 4.54123636645575 -15.0410129944794,15.6666049417108 10.5611746329814 -11.2770220567472,14 11 -10)","timeline":["2000-01-01 00:01:19.067179","2000-01-01 00:12:36.116007","2000-01-01 00:23:53.164835","2000-01-01 00:35:10.213663","2000-01-01 00:46:27.262491","2000-01-01 00:57:44.311319","2000-01-01 01:09:01.360147","2000-01-01 01:20:18.408975","2000-01-01 01:31:35.457803","2000-01-01 01:42:52.506631","2000-01-01 01:54:09.555459","2000-01-01 02:05:26.604287","2000-01-01 02:16:43.653115","2000-01-01 02:28:00.701943","2000-01-01 02:39:17.750771","2000-01-01 02:50:34.799599","2000-01-01 03:01:51.848427","2000-01-01 03:13:08.897255","2000-01-01 03:24:25.946085"]}}'::trajectory as a ) select ST_split(a, '{"drop_edge.spatial_distance_2d":1}') from traj; st_split ---------- {} (1 row) With traj as( select '{"trajectory":{"version":1,"type":"STPOINT","leafcount":19,"start_time":"2000-01-01 00:01:19.067179","end_time":"2000-01-01 03:24:25.946085","spatial":"LINESTRING(-100 -100 -100,-88.8925775739675 -86.6512698383691 -92.3767832526937,-79.6904716538265 -80.6515727923252 -84.2357598245144,-75.8435507711644 -73.7572890928326 -80.5007370118983,-70.6238425321256 -67.8213750167439 -74.5733173238113,-61.6014582272619 -61.0636760429479 -67.9874239303172,-56.1098577060426 -54.4264591250879 -64.5007972046733,-46.9800617334743 -49.4026757289345 -61.6160059720278,-41.7122942996211 -46.3224360072054 -56.5283147455193,-35.5646221285375 -38.1688933617746 -49.2775720101781,-31.7230528349367 -33.6970051738123 -44.1693710885011,-23.1585765127093 -26.5895827477798 -40.6539742602035,-16.7020264320696 -21.6133877349397 -37.3055470525287,-12.1044529232507 -14.1236051704424 -28.2295028120279,-3.77185660181567 -7.74744770256802 -24.3842111621052,0.488159407706304 -3.68223926316326 -19.9478872027248,6.33406881305078 4.54123636645575 -15.0410129944794,15.6666049417108 10.5611746329814 -11.2770220567472,14 11 -10)","timeline":["2000-01-01 00:01:19.067179","2000-01-01 00:12:36.116007","2000-01-01 00:23:53.164835","2000-01-01 00:35:10.213663","2000-01-01 00:46:27.262491","2000-01-01 00:57:44.311319","2000-01-01 01:09:01.360147","2000-01-01 01:20:18.408975","2000-01-01 01:31:35.457803","2000-01-01 01:42:52.506631","2000-01-01 01:54:09.555459","2000-01-01 02:05:26.604287","2000-01-01 02:16:43.653115","2000-01-01 02:28:00.701943","2000-01-01 02:39:17.750771","2000-01-01 02:50:34.799599","2000-01-01 03:01:51.848427","2000-01-01 03:13:08.897255","2000-01-01 03:24:25.946085"]}}'::trajectory as a ) select ST_split(a, '{"cut_edge.time_interval":"50 minute"}') from traj; st_split -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- {"{\"trajectory\":{\"version\":1,\"type\":\"STPOINT\",\"leafcount\":6,\"start_time\":\"Sat Jan 01 00:01:19 2000\",\"end_time\":\"Sat Jan 01 00:51:19 2000\",\"spatial\":\"LINESTRING(-100 -100 -100,-88.8925775739675 -86.6512698383691 -92.3767832526937,-79.6904716538265 -80.6515727923252 -84.2357598245144,-75.8435507711644 -73.7572890928326 -80.5007370118983,-70.6238425321256 -67.8213750167439 -74.5733173238113,-66.7323562440603 -64.9066806292278 -71.7327251954505)\",\"timeline\":[\"Sat Jan 01 00:01:19 2000\",\"Sat Jan 01 00:12:36 2000\",\"Sat Jan 01 00:23:53 2000\",\"Sat Jan 01 00:35:10 2000\",\"Sat Jan 01 00:46:27 2000\",\"Sat Jan 01 00:51:19 2000\"]}}","{\"trajectory\":{\"version\":1,\"type\":\"STPOINT\",\"leafcount\":6,\"start_time\":\"Sat Jan 01 00:51:19 2000\",\"end_time\":\"Sat Jan 01 01:41:19 2000\",\"spatial\":\"LINESTRING(-66.7323562440603 -64.9066806292278 -71.7327251954505,-61.6014582272619 -61.0636760429479 -67.9874239303172,-56.1098577060426 -54.4264591250879 -64.5007972046733,-46.9800617334743 -49.4026757289345 -61.6160059720278,-41.7122942996211 -46.3224360072054 -56.5283147455193,-36.4169542584517 -39.2993255279553 -50.2828372271723)\",\"timeline\":[\"Sat Jan 01 00:51:19 2000\",\"Sat Jan 01 00:57:44 2000\",\"Sat Jan 01 01:09:01 2000\",\"Sat Jan 01 01:20:18 2000\",\"Sat Jan 01 01:31:35 2000\",\"Sat Jan 01 01:41:19 2000\"]}}","{\"trajectory\":{\"version\":1,\"type\":\"STPOINT\",\"leafcount\":7,\"start_time\":\"Sat Jan 01 01:41:19 2000\",\"end_time\":\"Sat Jan 01 02:31:19 2000\",\"spatial\":\"LINESTRING(-36.4169542584517 -39.2993255279553 -50.2828372271723,-35.5646221285375 -38.1688933617746 -49.2775720101781,-31.7230528349367 -33.6970051738123 -44.1693710885011,-23.1585765127093 -26.5895827477798 -40.6539742602035,-16.7020264320696 -21.6133877349397 -37.3055470525287,-12.1044529232507 -14.1236051704424 -28.2295028120279,-9.66744543190043 -12.2587910217879 -27.1048828021539)\",\"timeline\":[\"Sat Jan 01 01:41:19 2000\",\"Sat Jan 01 01:42:53 2000\",\"Sat Jan 01 01:54:10 2000\",\"Sat Jan 01 02:05:27 2000\",\"Sat Jan 01 02:16:44 2000\",\"Sat Jan 01 02:28:01 2000\",\"Sat Jan 01 02:31:19 2000\"]}}","{\"trajectory\":{\"version\":1,\"type\":\"STPOINT\",\"leafcount\":6,\"start_time\":\"Sat Jan 01 02:31:19 2000\",\"end_time\":\"Sat Jan 01 03:21:19 2000\",\"spatial\":\"LINESTRING(-9.66744543190043 -12.2587910217879 -27.1048828021539,-3.77185660181567 -7.74744770256802 -24.3842111621052,0.488159407706304 -3.68223926316326 -19.9478872027248,6.33406881305078 4.54123636645575 -15.0410129944794,15.6666049417108 10.5611746329814 -11.2770220567472,14.4603473029541 10.8787882664217 -10.3527372593969)\",\"timeline\":[\"Sat Jan 01 02:31:19 2000\",\"Sat Jan 01 02:39:18 2000\",\"Sat Jan 01 02:50:35 2000\",\"Sat Jan 01 03:01:52 2000\",\"Sat Jan 01 03:13:09 2000\",\"Sat Jan 01 03:21:19 2000\"]}}","{\"trajectory\":{\"version\":1,\"type\":\"STPOINT\",\"leafcount\":2,\"start_time\":\"Sat Jan 01 03:21:19 2000\",\"end_time\":\"Sat Jan 01 03:24:26 2000\",\"spatial\":\"LINESTRING(14.4603473029541 10.8787882664217 -10.3527372593969,14 11 -10)\",\"timeline\":[\"Sat Jan 01 03:21:19 2000\",\"Sat Jan 01 03:24:26 2000\"]}}"} (1 row) With traj as( select '{"trajectory":{"version":1,"type":"STPOINT","leafcount":19,"start_time":"2000-01-01 00:01:19.067179","end_time":"2000-01-01 03:24:25.946085","spatial":"LINESTRING(-100 -100 -100,-88.8925775739675 -86.6512698383691 -92.3767832526937,-79.6904716538265 -80.6515727923252 -84.2357598245144,-75.8435507711644 -73.7572890928326 -80.5007370118983,-70.6238425321256 -67.8213750167439 -74.5733173238113,-61.6014582272619 -61.0636760429479 -67.9874239303172,-56.1098577060426 -54.4264591250879 -64.5007972046733,-46.9800617334743 -49.4026757289345 -61.6160059720278,-41.7122942996211 -46.3224360072054 -56.5283147455193,-35.5646221285375 -38.1688933617746 -49.2775720101781,-31.7230528349367 -33.6970051738123 -44.1693710885011,-23.1585765127093 -26.5895827477798 -40.6539742602035,-16.7020264320696 -21.6133877349397 -37.3055470525287,-12.1044529232507 -14.1236051704424 -28.2295028120279,-3.77185660181567 -7.74744770256802 -24.3842111621052,0.488159407706304 -3.68223926316326 -19.9478872027248,6.33406881305078 4.54123636645575 -15.0410129944794,15.6666049417108 10.5611746329814 -11.2770220567472,14 11 -10)","timeline":["2000-01-01 00:01:19.067179","2000-01-01 00:12:36.116007","2000-01-01 00:23:53.164835","2000-01-01 00:35:10.213663","2000-01-01 00:46:27.262491","2000-01-01 00:57:44.311319","2000-01-01 01:09:01.360147","2000-01-01 01:20:18.408975","2000-01-01 01:31:35.457803","2000-01-01 01:42:52.506631","2000-01-01 01:54:09.555459","2000-01-01 02:05:26.604287","2000-01-01 02:16:43.653115","2000-01-01 02:28:00.701943","2000-01-01 02:39:17.750771","2000-01-01 02:50:34.799599","2000-01-01 03:01:51.848427","2000-01-01 03:13:08.897255","2000-01-01 03:24:25.946085"]}}'::trajectory as a ) select unnest(ST_split(a, '{"cut_edge.time_interval":"1 hour, 2000-01-01"}')) from traj; unnest ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- {"trajectory":{"version":1,"type":"STPOINT","leafcount":7,"start_time":"Sat Jan 01 00:01:19 2000","end_time":"Sat Jan 01 01:00:00 2000","spatial":"LINESTRING(-100 -100 -100,-88.8925775739675 -86.6512698383691 -92.3767832526937,-79.6904716538265 -80.6515727923252 -84.2357598245144,-75.8435507711644 -73.7572890928326 -80.5007370118983,-70.6238425321256 -67.8213750167439 -74.5733173238113,-61.6014582272619 -61.0636760429479 -67.9874239303172,-60.498271121079 -59.7303503400986 -67.2870085171893)","timeline":["Sat Jan 01 00:01:19 2000","Sat Jan 01 00:12:36 2000","Sat Jan 01 00:23:53 2000","Sat Jan 01 00:35:10 2000","Sat Jan 01 00:46:27 2000","Sat Jan 01 00:57:44 2000","Sat Jan 01 01:00:00 2000"]}} {"trajectory":{"version":1,"type":"STPOINT","leafcount":7,"start_time":"Sat Jan 01 01:00:00 2000","end_time":"Sat Jan 01 02:00:00 2000","spatial":"LINESTRING(-60.498271121079 -59.7303503400986 -67.2870085171893,-56.1098577060426 -54.4264591250879 -64.5007972046733,-46.9800617334743 -49.4026757289345 -61.6160059720278,-41.7122942996211 -46.3224360072054 -56.5283147455193,-35.5646221285375 -38.1688933617746 -49.2775720101781,-31.7230528349367 -33.6970051738123 -44.1693710885011,-27.2953324320126 -30.0225622652283 -42.3519576617594)","timeline":["Sat Jan 01 01:00:00 2000","Sat Jan 01 01:09:01 2000","Sat Jan 01 01:20:18 2000","Sat Jan 01 01:31:35 2000","Sat Jan 01 01:42:53 2000","Sat Jan 01 01:54:10 2000","Sat Jan 01 02:00:00 2000"]}} {"trajectory":{"version":1,"type":"STPOINT","leafcount":7,"start_time":"Sat Jan 01 02:00:00 2000","end_time":"Sat Jan 01 03:00:00 2000","spatial":"LINESTRING(-27.2953324320126 -30.0225622652283 -42.3519576617594,-23.1585765127093 -26.5895827477798 -40.6539742602035,-16.7020264320696 -21.6133877349397 -37.3055470525287,-12.1044529232507 -14.1236051704424 -28.2295028120279,-3.77185660181567 -7.74744770256802 -24.3842111621052,0.488159407706304 -3.68223926316326 -19.9478872027248,5.36694642989187 3.18077954146708 -15.8527853893442)","timeline":["Sat Jan 01 02:00:00 2000","Sat Jan 01 02:05:27 2000","Sat Jan 01 02:16:44 2000","Sat Jan 01 02:28:01 2000","Sat Jan 01 02:39:18 2000","Sat Jan 01 02:50:35 2000","Sat Jan 01 03:00:00 2000"]}} {"trajectory":{"version":1,"type":"STPOINT","leafcount":4,"start_time":"Sat Jan 01 03:00:00 2000","end_time":"Sat Jan 01 03:24:26 2000","spatial":"LINESTRING(5.36694642989187 3.18077954146708 -15.8527853893442,6.33406881305078 4.54123636645575 -15.0410129944794,15.6666049417108 10.5611746329814 -11.2770220567472,14 11 -10)","timeline":["Sat Jan 01 03:00:00 2000","Sat Jan 01 03:01:52 2000","Sat Jan 01 03:13:09 2000","Sat Jan 01 03:24:26 2000"]}} (4 rows) With traj as( select '{"trajectory":{"version":1,"type":"STPOINT","leafcount":19,"start_time":"2000-01-01 00:01:19.067179","end_time":"2000-01-01 03:24:25.946085","spatial":"LINESTRING(-100 -100 -100,-88.8925775739675 -86.6512698383691 -92.3767832526937,-79.6904716538265 -80.6515727923252 -84.2357598245144,-75.8435507711644 -73.7572890928326 -80.5007370118983,-70.6238425321256 -67.8213750167439 -74.5733173238113,-61.6014582272619 -61.0636760429479 -67.9874239303172,-56.1098577060426 -54.4264591250879 -64.5007972046733,-46.9800617334743 -49.4026757289345 -61.6160059720278,-41.7122942996211 -46.3224360072054 -56.5283147455193,-35.5646221285375 -38.1688933617746 -49.2775720101781,-31.7230528349367 -33.6970051738123 -44.1693710885011,-23.1585765127093 -26.5895827477798 -40.6539742602035,-16.7020264320696 -21.6133877349397 -37.3055470525287,-12.1044529232507 -14.1236051704424 -28.2295028120279,-3.77185660181567 -7.74744770256802 -24.3842111621052,0.488159407706304 -3.68223926316326 -19.9478872027248,6.33406881305078 4.54123636645575 -15.0410129944794,15.6666049417108 10.5611746329814 -11.2770220567472,14 11 -10)","timeline":["2000-01-01 00:01:19.067179","2000-01-01 00:12:36.116007","2000-01-01 00:23:53.164835","2000-01-01 00:35:10.213663","2000-01-01 00:46:27.262491","2000-01-01 00:57:44.311319","2000-01-01 01:09:01.360147","2000-01-01 01:20:18.408975","2000-01-01 01:31:35.457803","2000-01-01 01:42:52.506631","2000-01-01 01:54:09.555459","2000-01-01 02:05:26.604287","2000-01-01 02:16:43.653115","2000-01-01 02:28:00.701943","2000-01-01 02:39:17.750771","2000-01-01 02:50:34.799599","2000-01-01 03:01:51.848427","2000-01-01 03:13:08.897255","2000-01-01 03:24:25.946085"]}}'::trajectory as a ) select unnest(ST_split(a, '{1,3,5}'::int[])) from traj; unnest ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- {"trajectory":{"version":1,"type":"STPOINT","leafcount":3,"start_time":"Sat Jan 01 00:12:36 2000","end_time":"Sat Jan 01 00:35:10 2000","spatial":"LINESTRING(-88.8925775739675 -86.6512698383691 -92.3767832526937,-79.6904716538265 -80.6515727923252 -84.2357598245144,-75.8435507711644 -73.7572890928326 -80.5007370118983)","timeline":["Sat Jan 01 00:12:36 2000","Sat Jan 01 00:23:53 2000","Sat Jan 01 00:35:10 2000"]}} {"trajectory":{"version":1,"type":"STPOINT","leafcount":3,"start_time":"Sat Jan 01 00:35:10 2000","end_time":"Sat Jan 01 00:57:44 2000","spatial":"LINESTRING(-75.8435507711644 -73.7572890928326 -80.5007370118983,-70.6238425321256 -67.8213750167439 -74.5733173238113,-61.6014582272619 -61.0636760429479 -67.9874239303172)","timeline":["Sat Jan 01 00:35:10 2000","Sat Jan 01 00:46:27 2000","Sat Jan 01 00:57:44 2000"]}} (2 rows) With traj as( select '{"trajectory":{"version":1,"type":"STPOINT","leafcount":19,"start_time":"2000-01-01 00:01:19.067179","end_time":"2000-01-01 03:24:25.946085","spatial":"LINESTRING(-100 -100 -100,-88.8925775739675 -86.6512698383691 -92.3767832526937,-79.6904716538265 -80.6515727923252 -84.2357598245144,-75.8435507711644 -73.7572890928326 -80.5007370118983,-70.6238425321256 -67.8213750167439 -74.5733173238113,-61.6014582272619 -61.0636760429479 -67.9874239303172,-56.1098577060426 -54.4264591250879 -64.5007972046733,-46.9800617334743 -49.4026757289345 -61.6160059720278,-41.7122942996211 -46.3224360072054 -56.5283147455193,-35.5646221285375 -38.1688933617746 -49.2775720101781,-31.7230528349367 -33.6970051738123 -44.1693710885011,-23.1585765127093 -26.5895827477798 -40.6539742602035,-16.7020264320696 -21.6133877349397 -37.3055470525287,-12.1044529232507 -14.1236051704424 -28.2295028120279,-3.77185660181567 -7.74744770256802 -24.3842111621052,0.488159407706304 -3.68223926316326 -19.9478872027248,6.33406881305078 4.54123636645575 -15.0410129944794,15.6666049417108 10.5611746329814 -11.2770220567472,14 11 -10)","timeline":["2000-01-01 00:01:19.067179","2000-01-01 00:12:36.116007","2000-01-01 00:23:53.164835","2000-01-01 00:35:10.213663","2000-01-01 00:46:27.262491","2000-01-01 00:57:44.311319","2000-01-01 01:09:01.360147","2000-01-01 01:20:18.408975","2000-01-01 01:31:35.457803","2000-01-01 01:42:52.506631","2000-01-01 01:54:09.555459","2000-01-01 02:05:26.604287","2000-01-01 02:16:43.653115","2000-01-01 02:28:00.701943","2000-01-01 02:39:17.750771","2000-01-01 02:50:34.799599","2000-01-01 03:01:51.848427","2000-01-01 03:13:08.897255","2000-01-01 03:24:25.946085"]}}'::trajectory as a ) select unnest(ST_split(a, '{0}'::int[]||'{0,1,3,5}'::int[] || ST_leafcount(a) - 1)) from traj; unnest --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- {"trajectory":{"version":1,"type":"STPOINT","leafcount":2,"start_time":"Sat Jan 01 00:01:19 2000","end_time":"Sat Jan 01 00:12:36 2000","spatial":"LINESTRING(-100 -100 -100,-88.8925775739675 -86.6512698383691 -92.3767832526937)","timeline":["Sat Jan 01 00:01:19 2000","Sat Jan 01 00:12:36 2000"]}} {"trajectory":{"version":1,"type":"STPOINT","leafcount":3,"start_time":"Sat Jan 01 00:12:36 2000","end_time":"Sat Jan 01 00:35:10 2000","spatial":"LINESTRING(-88.8925775739675 -86.6512698383691 -92.3767832526937,-79.6904716538265 -80.6515727923252 -84.2357598245144,-75.8435507711644 -73.7572890928326 -80.5007370118983)","timeline":["Sat Jan 01 00:12:36 2000","Sat Jan 01 00:23:53 2000","Sat Jan 01 00:35:10 2000"]}} {"trajectory":{"version":1,"type":"STPOINT","leafcount":3,"start_time":"Sat Jan 01 00:35:10 2000","end_time":"Sat Jan 01 00:57:44 2000","spatial":"LINESTRING(-75.8435507711644 -73.7572890928326 -80.5007370118983,-70.6238425321256 -67.8213750167439 -74.5733173238113,-61.6014582272619 -61.0636760429479 -67.9874239303172)","timeline":["Sat Jan 01 00:35:10 2000","Sat Jan 01 00:46:27 2000","Sat Jan 01 00:57:44 2000"]}} {"trajectory":{"version":1,"type":"STPOINT","leafcount":14,"start_time":"Sat Jan 01 00:57:44 2000","end_time":"Sat Jan 01 03:24:26 2000","spatial":"LINESTRING(-61.6014582272619 -61.0636760429479 -67.9874239303172,-56.1098577060426 -54.4264591250879 -64.5007972046733,-46.9800617334743 -49.4026757289345 -61.6160059720278,-41.7122942996211 -46.3224360072054 -56.5283147455193,-35.5646221285375 -38.1688933617746 -49.2775720101781,-31.7230528349367 -33.6970051738123 -44.1693710885011,-23.1585765127093 -26.5895827477798 -40.6539742602035,-16.7020264320696 -21.6133877349397 -37.3055470525287,-12.1044529232507 -14.1236051704424 -28.2295028120279,-3.77185660181567 -7.74744770256802 -24.3842111621052,0.488159407706304 -3.68223926316326 -19.9478872027248,6.33406881305078 4.54123636645575 -15.0410129944794,15.6666049417108 10.5611746329814 -11.2770220567472,14 11 -10)","timeline":["Sat Jan 01 00:57:44 2000","Sat Jan 01 01:09:01 2000","Sat Jan 01 01:20:18 2000","Sat Jan 01 01:31:35 2000","Sat Jan 01 01:42:53 2000","Sat Jan 01 01:54:10 2000","Sat Jan 01 02:05:27 2000","Sat Jan 01 02:16:44 2000","Sat Jan 01 02:28:01 2000","Sat Jan 01 02:39:18 2000","Sat Jan 01 02:50:35 2000","Sat Jan 01 03:01:52 2000","Sat Jan 01 03:13:09 2000","Sat Jan 01 03:24:26 2000"]}} (4 rows)
轨迹重采样
很多时间，由于去除噪点或者其他人工预处理之后，轨迹点会变得分散不均，影响局部轨迹段的表现与可分析性，这时候我们需要将轨迹进行重采样，将被简化的轨迹重新变得采样均匀。Ganos设计了两种重采样策略：
- add_point: 升采样，可以将点的密度增大，以对轨迹点进行密度统计，相似度匹配等功能；
- drop_point:降采样，将点的密度降低，获取轨迹的在更长时间上的趋势，可以作为另一种轨迹简化功能；
函数
函数名 |
函数语法 |
函数功能 |
ST_Resample（参考手册） |
trajectory ST_Resample(trajectory traj, config); |
对一条轨迹重采样 |
参数
参数名称 |
参数解释 |
traj |
轨迹对象 |
config |
重采样轨迹的选项 |
config所规定的是设定好的重采样规则（见下表），返回重采样之后的轨迹对象。目前包含以下可选规则，每次调用此函数时，仅可选择一个规则
规则名 |
规则参数 |
规则解释 |
add_point.distance_lesser |
浮点数 |
在每条轨迹线上均匀添加点，使每一段之间的距离均小于参数 |
add_point.period_lesser |
可以转化为Interval类型的字符串 |
在每条轨迹线上均匀添加点，使每一段的时长均小于参数 |
drop_point.distance_lesser |
浮点数 |
当相邻多段轨迹线段的端点两两之间距离均小于给定的时间长度时，将其合并（仅保留起点和终点） |
drop_point.period_lesser |
可以转化为Interval类型的字符串 |
当相邻多段轨迹线段的时间长度之和小于给定的时间长度时，将其合并（仅保留起点和终点） |
示例
SELECT ST_Resample(ST_MakeTrajectory('POINT(1 1)'), '{"add_point.distance_lesser":3}'); st_resample ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- {"trajectory":{"version":1,"type":"STPOINT","leafcount":1,"start_time":"2000-01-01 00:00:00","end_time":"2000-01-01 00:00:00","spatial":"POINT(1 1)","timeline":["2000-01-01 00:00:00"]}} (1 row) SELECT ST_Resample(ST_MakeTrajectory('POINT(1 1)'), '{"add_point.period_lesser":"2 day"}'); st_resample ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- {"trajectory":{"version":1,"type":"STPOINT","leafcount":1,"start_time":"2000-01-01 00:00:00","end_time":"2000-01-01 00:00:00","spatial":"POINT(1 1)","timeline":["2000-01-01 00:00:00"]}} (1 row) SELECT ST_Resample(ST_MakeTrajectory('POINT(1 1)'), '{"drop_point.distance_lesser":3}'); st_resample ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- {"trajectory":{"version":1,"type":"STPOINT","leafcount":1,"start_time":"2000-01-01 00:00:00","end_time":"2000-01-01 00:00:00","spatial":"POINT(1 1)","timeline":["2000-01-01 00:00:00"]}} (1 row)
轨迹简化
轨迹简化、即轨迹的有损压缩，目的是在保留轨迹的大致移动方向的基础上，减少轨迹采样点的行为，轨迹压缩可以有效降低数据的存储，提供更好的渲染能力。
如上图所示，二维的轨迹简化往往采用道格拉斯-普克算法，首先需要用户指定一个容差，用于表征轨迹的简化版本和原始轨迹间的最大差异；之后在轨迹上从首尾相连开始，依次去判断是否满足容差，通过不断重复这个“选择差距最大的点-获取新的简化轨迹-判断差异是否小于容差”的过程，直到原始轨迹上所有点到简化后的轨迹的距离均小于容差。
但道格拉斯-普克算法只是考虑了二维轨迹，要求所有轨迹采样点全在平面上。而在时空轨迹的问题上，会出现：轨迹在一个点停留很久，如果从三维角度观察，它形成一条与时间轴平行的长直线，在二维角度观察，这条直线则变成一个点，依据二维的道格拉斯-普克算法，必会将此直线的起点和终点舍去一个，破坏轨迹在三维上的结构。因此针对存在时间戳的轨迹，Ganos使用Sychronized Euclidean Distance(SED，同步时间欧式距离)取代上述的垂直欧式距离，确保轨迹的时空压缩更加合理有效。
函数
- 基于道格拉斯-普克算法的二维轨迹简化
函数名 |
函数语法 |
函数功能 |
ST_Compress（参考手册） |
trajectory ST_Compress (trajectory traj, float8 dist); |
将trajectory对象按一定规则进行压缩 |
- 基于SED距离的时空轨迹（二维+时间）简化
函数名 |
函数语法 |
函数功能 |
ST_CompressSED（参考手册） |
trajectory ST_CompressSED (trajectory traj, float8 dist); |
将trajectory对象按一定规则进行压缩 |
参数
参数名称 |
参数解释 |
traj |
轨迹对象 |
dist |
欧式距离或时间同步距离（SED）偏移阈值 |
示例
--创建数据 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_compressSED(traj, 0.001) as traj from traj_test; traj ------------------------------------------------------------- {"trajectory":{"version":1,"type":"STPOINT","leafcount":12,"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.18826 52.10105,-179.16482 52.12996,-179.14599 52.15132,-177.76666 52.85042,-177.47319 52.90084,-177.31238 52.92697,-177.03751 52.97394,-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:30:09","2017-01-15 11:42:00","2017-01-15 11:50:28","2017-01-15 18:01:00","2017-01-15 18:56:22","2017-01-15 19:26:10","2017-01-15 20:15:49","2017-01-15 21:18:30"],"attributes":{"leafcount":12,"sog":{"type":"float","length":8,"nullable":false,"value":[10.5,11.0,10.6,11.2,9.1,9.7,9.9,12.3,12.0,12.2,12.8,12.7]},"cog":{"type":"float","length":8,"nullable":false,"value":[23.3,28.5,29.2,27.1,26.8,30.1,30.0,74.2,81.3,73.4,74.2,72.9]},"heading":{"type":"float","length":8,"nullable":false,"value":[22.0,27.0,24.0,29.0,27.0,26.0,27.0,69.0,72.0,71.0,72.0,73.0]}}}} (1 row)
案例简述
Ganos编写了针对轨迹引擎使用的最佳实践案例（基于Ganos轨迹引擎的运输车辆到达性分析），用户可通过该实践文章系统掌握Ganos轨迹引擎的使用方法。以下还提供了三个常见的轨迹处理用法，供参考。
案例1
某船舶客户，通过获取AIS系统向数据库的点表中插入船舶经纬度信息，需要将表处理为轨迹对象并剔除漂移点：
SELECT ST_removeDriftPoints (-- 删除漂移点 ST_SetSRID ( -- 设置SRID ST_MakeTrajectory ( -- 构造轨迹对象 ARRAY_AGG ( ROW ( traj.arrival_time :: TIMESTAMP, st_x ( traj.pts ) :: DOUBLE PRECISION, traj.lat :: DOUBLE PRECISION, traj.rowid ) ), FALSE, '{"rowid"}' :: cstring [] ), 4326 ), 40, 10, '1 minute' :: INTERVAL ) FROM ( SELECT time ST_makepoint ( lon, lat ) pts, lat, rowid FROM point_table WHERE time IS NOT NULL AND lon IS NOT NULL AND lat IS NOT NULL ORDER BY rowid ) traj INTO trajectory_table;
案例2
某无人机客户，需要将轨迹按照5分钟为分段重采样并统计出轨迹的点密度，后续打标后传递给机器学习模型作为特征输入：
SELECT ST_Density( ST_Resample(ST_OnlyST(traj), '{"add_point.period_lesser":"5 minute"}') ), 100, '30 minute' ) from table;
案例3
某货运车辆管理客户，业务中采集整个车辆的长轨迹，但分析中需要将规矩中较长的边去掉，从而生成按某段行程的短轨迹用于后续分析：
With traj as( select '{"trajectory":{"version":1,"type":"STPOINT","leafcount":19,"start_time":"2000-01-01 00:01:19.067179","end_time":"2000-01-01 03:24:25.946085","spatial":"LINESTRING(-100 -100 -100,-88.8925775739675 -86.6512698383691 -92.3767832526937,-79.6904716538265 -80.6515727923252 -84.2357598245144,-75.8435507711644 -73.7572890928326 -80.5007370118983,-70.6238425321256 -67.8213750167439 -74.5733173238113,-61.6014582272619 -61.0636760429479 -67.9874239303172,-56.1098577060426 -54.4264591250879 -64.5007972046733,-46.9800617334743 -49.4026757289345 -61.6160059720278,-41.7122942996211 -46.3224360072054 -56.5283147455193,-35.5646221285375 -38.1688933617746 -49.2775720101781,-31.7230528349367 -33.6970051738123 -44.1693710885011,-23.1585765127093 -26.5895827477798 -40.6539742602035,-16.7020264320696 -21.6133877349397 -37.3055470525287,-12.1044529232507 -14.1236051704424 -28.2295028120279,-3.77185660181567 -7.74744770256802 -24.3842111621052,0.488159407706304 -3.68223926316326 -19.9478872027248,6.33406881305078 4.54123636645575 -15.0410129944794,15.6666049417108 10.5611746329814 -11.2770220567472,14 11 -10)","timeline":["2000-01-01 00:01:19.067179","2000-01-01 00:12:36.116007","2000-01-01 00:23:53.164835","2000-01-01 00:35:10.213663","2000-01-01 00:46:27.262491","2000-01-01 00:57:44.311319","2000-01-01 01:09:01.360147","2000-01-01 01:20:18.408975","2000-01-01 01:31:35.457803","2000-01-01 01:42:52.506631","2000-01-01 01:54:09.555459","2000-01-01 02:05:26.604287","2000-01-01 02:16:43.653115","2000-01-01 02:28:00.701943","2000-01-01 02:39:17.750771","2000-01-01 02:50:34.799599","2000-01-01 03:01:51.848427","2000-01-01 03:13:08.897255","2000-01-01 03:24:25.946085"]}}'::trajectory as a ) select (ST_split(a, '{"drop_edge.spatial_distance_2d":10}')) from traj;
