Ganos轨迹采样点常用处理方法解析

本文涉及的产品
云数据库 RDS SQL Server,基础系列 2核4GB
RDS PostgreSQL Serverless,0.5-4RCU 50GB 3个月
推荐场景:
对影评进行热评分析
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
简介: 本文介绍了Ganos轨迹引擎的采样点处理能力,包含轨迹过滤、轨迹切分、轨迹重采样和轨迹简化等,帮助用户快速掌握移动对象的预处理方法,提升业务开发的便捷性。

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,同步时间欧式距离)取代上述的垂直欧式距离,确保轨迹的时空压缩更加合理有效。

函数

  1. 基于道格拉斯-普克算法的二维轨迹简化

函数名

函数语法

函数功能

ST_Compress(参考手册)

trajectory ST_Compress (trajectory traj, float8 dist);

将trajectory对象按一定规则进行压缩

  1. 基于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;


相关文章
|
5月前
|
SQL 存储 数据可视化
Ganos H3地理网格能力解析与最佳实践
本文介绍了Ganos H3的相关功能,帮助读者快速了解Ganos地理网格的重要特性与应用实践。H3是Uber研发的一种覆盖全球表面的二维地理网格,采用了一种全球统一的、多层次的六边形网格体系来表示地球表面,这种地理网格技术在诸多业务场景中得到广泛应用。Ganos不仅提供了H3网格的全套功能,还支持与其它Ganos时空数据类型进行跨模联合分析,极大程度提升了客户对于时空数据的挖掘分析能力。
|
10月前
|
存储 关系型数据库 数据库
Ganos全空间数据多态分层存储能力解析与最佳实践
本文介绍了Ganos与云原生关系型数据库PolarDB-PG联合打造的全空间数据多态分层存储能力与最佳实践。多态分层存储将OSS对象存储直接作为PolarDB-PG数据库的一种存储介质,可以与块存储联合使用,它支持用户将整库、单表、子分区表甚至是表内的一个LOB字段存储在OSS上,达到存储降本的目的,同时保持了增删改查的全部透明,并通过多级缓存保障了性能的最少衰减。多态分层存储是一种兼顾成本、性能与易用性的全空间数据管理方案,可极大程度降低业务开发的复杂度与云资源使用成本。
|
11天前
|
存储 Cloud Native 关系型数据库
Ganos实时热力聚合查询能力解析与最佳实践
Ganos是由阿里云数据库产品事业部与飞天实验室共同研发的新一代云原生位置智能引擎,集成于PolarDB-PG、Lindorm、AnalyticDB-PG和RDS-PG等核心产品中。Ganos拥有十大核心引擎,涵盖几何、栅格、轨迹等多种数据处理能力,实现了多模多态数据的一体化存储、查询与分析。本文重点介绍了Ganos的热力瓦片(HMT)技术,通过实时热力聚合查询与动态输出热力瓦片,无需预处理即可实现大规模数据秒级聚合与渲染,适用于交通、城市管理、共享出行等多个领域。HMT相比传统网格聚合技术具有高效、易用的优势,并已在多个真实场景中验证其卓越性能。
25 0
|
11天前
|
SQL 存储 数据可视化
Ganos H3地理网格能力解析与最佳实践
Ganos H3地理网格是一种基于六边形结构的高效地理空间数据处理技术,适用于物流、社交网络、数据分析及应急响应等多种场景。Ganos H3利用独特的六边形网格体系实现更均匀的数据分布和固定邻居关系,优化了空间数据分析、路径规划等功能。Ganos地理网格引擎支持GeoSOT和H3两种网格,具备丰富的打码方式、高性能查询及聚合分析能力,并能与几何和栅格数据融合,大幅提升了数据处理效率和存储成本效益。借助Ganos H3,企业和开发者可以更好地管理和利用地理空间数据,提高位置相关决策的准确性和效率。
27 0
|
4月前
|
存储 Cloud Native 关系型数据库
《阿里云产品四月刊》—Ganos H3 地理网格能力解析与最佳实践(1)
阿里云瑶池数据库云原生化和一体化产品能力升级,多款产品更新迭代
128 3
《阿里云产品四月刊》—Ganos H3 地理网格能力解析与最佳实践(1)
|
4月前
|
存储 数据库 数据可视化
《阿里云产品四月刊》—Ganos H3 地理网格能力解析与最佳实践(2)
阿里云瑶池数据库云原生化和一体化产品能力升级,多款产品更新迭代
《阿里云产品四月刊》—Ganos H3 地理网格能力解析与最佳实践(2)
|
4月前
|
Cloud Native 数据库 数据可视化
《阿里云产品四月刊》—Ganos H3 地理网格能力解析与最佳实践(3)
阿里云瑶池数据库云原生化和一体化产品能力升级,多款产品更新迭代
|
5月前
|
Cloud Native 前端开发 关系型数据库
Ganos实时热力聚合查询能力解析与最佳实践
本文主要介绍Ganos实时热力聚合查询并动态输出热力瓦片能力,依托阿里云PolarDB PostgreSQL产品、ADB PostgreSQL和RDS PostgreSQL 三款数据库建设输出。
|
12月前
|
存储 数据可视化 数据管理
Ganos三维引擎系列(三):BIM数据管理与可视化功能解析
本文介绍了阿里云多模态时空数据库Ganos三维引擎在BIM数据管理中的应用。Ganos三维引擎支持三大类存储结构:表面网格模型、体网格模型与3D实景模型,其中表面网格模型(SFMesh)用于存储带有语义的类BIM精细化三维模型,同时提供了空间索引、分析算子、导入工具、可视化支撑等功能,本文详细介绍表面网格模型应用于IFC格式的BIM数据管理与可视化等功能。
|
存储 SQL 数据可视化
Ganos三维引擎系列(一):倾斜摄影数据管理与可视化功能解析
本文介绍了阿里云多模态时空数据库Ganos三维引擎在倾斜摄影数据管理中的应用。Ganos三维引擎支持三大类存储结构:表面网格模型、体网格模型与3D实景模型,其中表面网格模型用于存储带有语义的类BIM精细化三维模型,体网格模型用于存储地质体等非匀质“场”类三维模型,3D实景模型用于存储倾斜摄影、精白模等用于渲染的三维模型,三种存储结构都提供了原生数据类型、空间索引、分析算子、导入导出工具、可视化支撑等功能,为数字孪生类应用提供闭环的存算显能力,本文重点介绍基于3D实景模型开展倾斜摄影数据管理与可视化等功能。

推荐镜像

更多