ST_AsTable converts a trajectory object into a table of rows, one row per trajectory point. Each row contains a timestamp and the corresponding spatial coordinates.
Syntax
trajectory ST_AsTable (trajectory traj);Parameters
| Parameter | Description |
|---|---|
traj | The original trajectory. |
Description
ST_AsTable expands a trajectory value into a set of rows. The output schema matches the point-based format defined in ST_makeTrajectory (Syntax 5). Each row contains three fields:
t(timestamp): the timestamp of the trajectory pointx(double precision): the x-coordinatey(double precision): the y-coordinate
To retrieve named, typed columns, call ST_AsTable in the FROM clause with a column alias clause: AS f(t timestamp, x double precision, y double precision).
Examples
Return trajectory points as tuples
Call ST_AsTable inside a SELECT to return each trajectory point as a composite 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;Output:
st_astable
-----------------------------
("2010-01-01 11:30:00",1,1)
("2010-01-01 12:30:00",3,5)
(2 rows)Expand trajectory points into a typed table
Call ST_AsTable with SELECT * and a column alias clause to expand trajectory points into named, typed columns. This form is useful for filtering, joining, or aggregating trajectory data with standard SQL.
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);Output:
t | x | y
---------------------+---+---
2010-01-01 11:30:00 | 1 | 1
2010-01-01 12:30:00 | 3 | 5
(2 rows)