このトピックでは、ST_Split関数について説明します。 ST_Split関数は、空間ジオメトリオブジェクトに基づいて軌道を複数のサブ軌道に分割します。
構文
trajectory[] ST_Split(trajectory traj, geometry geom, float8 radius_of_buffer);
trajectory[] ST_Split(trajectory traj, text config);
trajectory[] ST_Split(trajectory traj, int[] indexes);パラメーター
パラメーター | 説明 |
traj | 分割する軌道。 |
geom | 軌道を分割するために使用される空間ジオメトリオブジェクト。 PointおよびMultiPointジオメトリオブジェクトのみがサポートされます。 |
radius_of_バッファ | Pointジオメトリオブジェクトに基づいてビルドされるバッファーの半径。 単位: メートル。 |
config | 軌道を分割する方法を指定するルール。 |
indexes | 分割する軌道ポイントのインデックス。 インデックスは0から始まります。 |
説明
設定1:
ST_Split(trajectory traj, geometry geom, float8 radius_of_buffer)この関数は、指定されたジオメトリオブジェクトに基づいて軌道を分割し、サブ軌道の配列を返します。 マルチポイント空間ジオメトリオブジェクトに基づいて軌道が分割される場合、軌道は複数のセグメントに分割され得る。
設定2:
ST_Split(trajectory traj, text config)この関数は、指定されたルールに基づいて軌道を分割し、サブ軌道の配列を返します。 configパラメーターで指定できるルールは1つだけです。 次の表に、configパラメーターを使用して指定できるルールを示します。
パラメーター
データ型
説明
cut_point.max_point
正の整数
このルールは、サンプリングポイント間の数量間隔を指定します。 この関数は、指定した数量間隔に基づいて、軌道をサブ軌道に分割します。
cut_point.even_divide
正の整数
このルールは、軌道が分割される部分の数を指定します。 この関数は、指定された数のサブ軌道に軌道を均等に分割します。 軌道のエッジの数がルールによって指定された値よりも小さい場合、関数は軌道の各エッジをサブ軌道と見なします。
cut_edge.time_interval
正の時間範囲
このルールは、時間間隔を指定します。 この関数は、指定した時間間隔に基づいて、軌道をサブ軌道に分割します。
cut_edge.geohash
正の偶数
このルールはGeohashを指定します。 この関数は、ルールで指定したGeohashに基づいて軌道を分割し、各サブ軌道が1つのGeohashグリッドに含まれるようにします。 このルールを指定する場合は、軌道のデータが緯度と経度で表されていることを確認してください。
drop_edge.temporal_length
時間範囲
このルールは、時間間隔を指定します。 この関数は、指定された時間間隔の間の部分を軌道から削除します。
drop_edge.spatial_distance_2d
浮動小数点
このルールは、空間距離を指定する。 この関数は、空間距離で指定された部分を軌跡から削除します。 空間距離は、2次元ユークリッド距離である。
設定3:
ST_Split(trajectory traj, int[] indexes)この関数は、指定されたインデックスに基づいて軌道を分割します。 n個のインデックスが与えられた場合、関数は軌道をn-1の部分に分割します。 軌跡の始点および終点は、インデックスに含まれる必要はない。
図
ジオメトリオブジェクトに基づいて軌道を分割します。
configパラメーターにcut_point.max_pointまたはcut_point.even_divideルールを指定した場合、関数は指定されたサンプリングポイントに基づいて軌道を分割します。 次の図に示すように、関数はポイントBで軌道を2つのサブ軌道に分割します。configパラメーターにcut_edge.time_intervalまたはcut_edge.geohashルールを指定すると、関数は指定したエッジに基づいて軌道を分割します。 次の図に示すように、関数はポイントCで軌道を2つのサブ軌道に分割します。configパラメーターにdrop_edge.temporal_lengthまたはdrop_edge.spatial_distance_2dルールを指定すると、指定されたサンプリングポイント間の指定されたエッジが削除されます。 次の図に示すように、この関数はポイントAとポイントBの間のエッジを削除します。
例
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)