This topic describes the ST_Split function. The ST_Split function splits a trajectory into multiple sub-trajectories based on a spatial geometry object.
Syntax
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);Parameters
| Parameter | Description |
|---|---|
| traj | The trajectory that you want to split. |
| geom | The spatial geometry object that is used to split the trajectory. Only Point and MultiPoint geometry objects are supported. |
| radius_of_buffer | The radius of the buffer that is built based on the Point geometry object. Unit: meter. |
| config | The rule that specifies how to split the trajectory. |
| indexes | The index of the trajectory point that you want to split. The index starts from 0. |
Description
- Configuration 1:
ST_Split(trajectory traj, geometry geom, float8 radius_of_buffer)This function splits a trajectory based on the specified geometry object and returns an array of sub-trajectories. If the trajectory is split based on a MultiPoint spatial geometry object, the trajectory may be split into multiple segments.
- Configuration 2:
ST_Split(trajectory traj, text config)This function splits a trajectory based on the specified rule and returns an array of sub-trajectories. Only one rule can be specified by the config parameter. The following table describes the rules that you can specify by using the config parameter.Parameter Type Description cut_point.max_point Positive integer This rule specifies a quantity interval between sampling points. The function splits the trajectory into sub-trajectories based on the quantity interval that you specify. cut_point.even_divide Positive integer This rule specifies the number of parts into which the trajectory is divided. The function evenly splits the trajectory into the specified number of sub-trajectories. If the number of edges of the trajectory is smaller than the value that is specified by the rule, the function considers each edge of the trajectory as a sub-trajectory. cut_edge.time_interval Positive time range This rule specifies a time interval. The function splits the trajectory into sub-trajectories based on the time interval that you specify. cut_edge.geohash Positive even number This rule specifies a Geohash. The function splits the trajectory based on the Geohash that you specify in the rule and ensures that each sub-trajectory is included in one Geohash grid. If you specify this rule, make sure that the data of the trajectory is represented by a latitude and a longitude. drop_edge.temporal_length Time range This rule specifies a time interval. The function deletes the part between the specified time interval from the trajectory. drop_edge.spatial_distance_2d Floating point This rule specifies a spatial distance. The function deletes the part specified by the spatial distance from the trajectory. The spatial distance is a two-dimensional Euclidean distance. - Configuration 3:
ST_Split(trajectory traj, int[] indexes)This function splits a trajectory based on the specified index. If n indexes are given, the function splits the trajectory into n-1 parts. The start and end points of the trajectory are not required to be included in the indexes.
Diagrams
- Split a trajectory based on a geometry object.
- If you specify the cut_point.max_point or the cut_point.even_divide rule for the config
parameter, the function splits the trajectory based on the specified sampling points.
As shown in the following diagram, the function splits the trajectory into two sub-trajectories
at Point B. If you specify the cut_edge.time_interval or the cut_edge.geohash rule
for the config parameter, the function splits the trajectory based on the specified
edges. As shown in the following diagram, the function splits the trajectory into
two sub-trajectories at Point C. If you specify the drop_edge.temporal_length or the
drop_edge.spatial_distance_2d rule for the config parameter, the function deletes
the specified edge between the specified sampling points. As shown in the following
diagram, the function deletes the edge between Point A and Point B.
Examples
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)