PolarDB:ST_LeafN

Syntax

trajectory ST_LeafN(trajectory traj, integer pos);

Parameters

Return value

Returns a single-point trajectory object containing the spatial location, timestamp, and all user-defined attributes of the specified point.

The returned trajectory JSON includes the following fields:

• spatial: The point geometry (e.g., SRID=4326;POINT(114 35))

• timeline: The timestamp array for the point

• attributes: The user-defined attribute values at that position

Usage notes

ST_LeafN uses 0-based indexing. The first point is at position 0, and the last point is at position ST_LeafCount(traj) - 1.

Examples

Retrieve the point at position 1 from a trajectory that includes velocity, accuracy, bearing, acceleration, and active-time attributes:

WITH traj_table AS (
  SELECT ST_MakeTrajectory(
    'STPOINT'::leaftype,
    ST_GeomFromText('LINESTRING (114 35, 114 35, 116 37)', 4326),
    ARRAY[
      '2010-1-11 14:30'::timestamp,
      '2010-1-11 14:30',
      '2010-1-11 15:00'
    ],
    '{"leafcount":3,"attributes":{"velocity":{"type":"integer","length":2,"nullable":true,"value":[120,130,140]},"accuracy":{"type":"float","length":4,"nullable":false,"value":[120,130,140]},"bearing":{"type":"float","length":8,"nullable":false,"value":[120,130,140]},"acceleration":{"type":"string","length":20,"nullable":true,"value":["120","130","140"]},"active":{"type":"timestamp","nullable":false,"value":["Fri Jan 01 14:30:00 2010","Fri Jan 01 15:00:00 2010","Fri Jan 01 15:30:00 2010"]}},"events":[{"1":"Fri Jan 01 14:30:00 2010"},{"2":"Fri Jan 01 15:00:00 2010"},{"3":"Fri Jan 01 15:30:00 2010"}]}'
  ) traj
)
SELECT ST_LeafN(traj, 1) FROM traj_table;

Expected output:

{"trajectory":{"version":1,"type":"STPOINT","leafcount":1,"start_time":"2010-01-11 14:30:00","end_time":"2010-01-11 14:30:00","spatial":"SRID=4326;POINT(114 35)","timeline":["2010-01-11 14:30:00"],"attributes":{"leafcount":1,"velocity":{"type":"integer","length":2,"nullable":true,"value":[130]},"accuracy":{"type":"float","length":4,"nullable":false,"value":[130.0]},"bearing":{"type":"float","length":8,"nullable":false,"value":[130.0]},"acceleration":{"type":"string","length":20,"nullable":true,"value":["130"]},"active":{"type":"timestamp","length":8,"nullable":false,"value":["2010-01-01 15:00:00"]}},"events":[{"1":"2010-01-01 14:30:00"},{"2":"2010-01-01 15:00:00"},{"3":"2010-01-01 15:30:00"}]}}

Related functions

• ST_LeafCount — Returns the total number of points in a trajectory object. Use this to determine the valid index range for ST_LeafN.