Use ST_AsTable to convert trajectories to a table - ApsaraDB RDS

ST_AsTable converts a trajectory object into a set of rows. Each row contains the timestamp and the x and y coordinates of one trajectory point.

Syntax

trajectory ST_AsTable (trajectory traj);

Parameters

Parameter	Description
traj	The original trajectory.

Description

ST_AsTable decomposes a trajectory object into rows. The output columns match the structure defined in Syntax 5 of ST_makeTrajectory:

Column	Type	Description
`t`	`timestamp`	The timestamp of the trajectory point.
`x`	`double precision`	The x coordinate of the point.
`y`	`double precision`	The y coordinate of the point.

Examples

Return rows as tuples

Call ST_AsTable directly in a SELECT statement to return each trajectory point as an anonymous tuple.

-- Output tuple
With traj AS (
    select '{"trajectory":{"version":1,"type":"STPOINT","leafcount":2,"start_time":"2010-01-01 11:30:00","end_time":"2010-01-01 12:30:00","spatial":"SRID=4326;LINESTRING(1 1,3 5)","timeline":["2010-01-01 11:30:00","2010-01-01 12:30:00"]}}'::trajectory a
)
select ST_AsTable(a) from traj;
         st_astable
-----------------------------
 ("2010-01-01 11:30:00",1,1)
 ("2010-01-01 12:30:00",3,5)
(2 rows)

Return rows as a named table

Use the AS f(col type, ...) alias to assign column names and types, making the output directly queryable as a relational table.

-- Output table
select * from ST_AsTable('{"trajectory":{"version":1,"type":"STPOINT","leafcount":2,"start_time":"2010-01-01 11:30:00","end_time":"2010-01-01 12:30:00","spatial":"SRID=4326;LINESTRING(1 1,3 5)","timeline":["2010-01-01 11:30:00","2010-01-01 12:30:00"]}}'::trajectory) as f(t timestamp,x double precision, y double precision);
          t          | x | y
---------------------+---+---
 2010-01-01 11:30:00 | 1 | 1
 2010-01-01 12:30:00 | 3 | 5
(2 rows)

ApsaraDB RDS:ST_AsTable