ApsaraDB RDS:ST_Split

Split by geometry

Pass a Point or MultiPoint geometry and a buffer radius. The function builds a circular buffer around each point and splits the trajectory wherever it crosses a buffer boundary.

If no part of the trajectory intersects any buffer, no split occurs and the function returns the original trajectory unchanged.

Split by rule

Pass a JSON string with exactly one rule key. The behavior depends on the rule family:

• cut_point.* rules split at specific sampling points (point B in the diagram).

• cut_edge.* rules split at specific edges (point C in the diagram).

• drop_edge.* rules remove matching edges entirely. Use these rules to clean noise from trajectory data, such as removing very long segments caused by GPS signal loss or large positional jumps.

Split by index

Pass an array of point indexes. Given n indexes, the function returns n-1 sub-trajectories. The start and end points of the trajectory do not need to be included in the index array.

Set up sample data

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]}}}'
  )
);

Split by geometry

Split the trajectory wherever it passes within 23,000 meters of any point in MULTIPOINT(100 30, 105 35, 110 35). This produces 4 sub-trajectories.

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",...}}
  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",...}}
  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",...}}
  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",...}}
(4 rows)

Edge case: If radius_of_buffer is too small for the trajectory to intersect any buffer zone, no split occurs and the function returns the original trajectory as a single-element array.

Split by rule

The following examples all use the same 19-point trajectory defined inline.

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
)

cut_point.max_point — split every 4 points (produces 5 sub-trajectories)

-- append to the WITH block above
SELECT unnest(ST_split(a, '{"cut_point.max_point":4}')) FROM traj;
-- Result: 5 rows, leafcount 5/5/5/5/3

cut_point.max_point — split every 10 points (produces 2 sub-trajectories)

SELECT ST_split(a, '{"cut_point.max_point":10}') FROM traj;
-- Result: 1 row containing an array of 2 sub-trajectories, leafcount 10/10

cut_point.max_point — split every 3 points (produces 7 sub-trajectories)

SELECT ST_split(a, '{"cut_point.max_point":3}') FROM traj;
-- Result: 1 row containing an array of 7 sub-trajectories, leafcount 3/4/4/4/4/4/2

cut_point.even_divide — divide into 100 equal parts

Because the trajectory has only 18 edges (19 points), and 18 < 100, each edge becomes its own sub-trajectory. The result is an array of 18 sub-trajectories, each with leafcount 2.

SELECT ST_split(a, '{"cut_point.even_divide":100}') FROM traj;
-- Result: 1 row containing an array of 18 sub-trajectories

cut_edge.geohash — split by Geohash precision 2 (produces 4 sub-trajectories)

SELECT ST_split(a, '{"cut_edge.geohash":2}') FROM traj;
-- Result: 1 row containing an array of 4 sub-trajectories

cut_edge.geohash — split by Geohash precision 20 (produces 19 sub-trajectories)

SELECT ST_split(a, '{"cut_edge.geohash":20}') FROM traj;
-- Result: 1 row containing an array of 19 sub-trajectories

drop_edge.spatial_distance_2d — remove edges longer than 13 (produces 1 sub-trajectory)

SELECT ST_split(a, '{"drop_edge.spatial_distance_2d":13}') FROM traj;
-- Result: 1 sub-trajectory with leafcount 18 (first long edge removed)

drop_edge.spatial_distance_2d — remove edges longer than 10 (produces 7 sub-trajectories)

SELECT ST_split(a, '{"drop_edge.spatial_distance_2d":10}') FROM traj;
-- Result: array of 7 sub-trajectories

drop_edge.spatial_distance_2d — threshold of 1 (all edges removed)

When the threshold is smaller than all edge lengths, every edge is removed and the function returns an empty array.

SELECT ST_split(a, '{"drop_edge.spatial_distance_2d":1}') FROM traj;
-- Result: {}

cut_edge.time_interval — split every 50 minutes (produces 5 sub-trajectories)

SELECT ST_split(a, '{"cut_edge.time_interval":"50 minute"}') FROM traj;
-- Result: array of 5 sub-trajectories

cut_edge.time_interval — split every 1 hour anchored at 2000-01-01 (produces 4 sub-trajectories)

Use unnest() to expand the array into individual rows.

SELECT unnest(ST_split(a, '{"cut_edge.time_interval":"1 hour, 2000-01-01"}')) FROM traj;
-- Result: 4 rows covering 00:01–01:00, 01:00–02:00, 02:00–03:00, 03:00–03:24

Split by index

3 indexes — 2 sub-trajectories

SELECT unnest(ST_split(a, '{1,3,5}'::int[])) FROM traj;
-- Result: 2 rows
-- Sub-trajectory 1: points at indexes 1–3 (start_time 00:12:36, end_time 00:35:10)
-- Sub-trajectory 2: points at indexes 3–5 (start_time 00:35:10, end_time 00:57:44)

6 indexes (with duplicates and boundary points) — 4 sub-trajectories

Duplicate index values and explicit boundary indexes (0 and leafcount-1) are accepted. After deduplication and sorting, 5 unique split points produce 4 sub-trajectories.

SELECT unnest(ST_split(a, '{0}'::int[] || '{0,1,3,5}'::int[] || ST_leafcount(a) - 1)) FROM traj;
-- Result: 4 rows