ApsaraDB RDS:ST_AsTableFormat

Get the column definition of a trajectory

The following example retrieves the column type definition from a trajectory object.

SELECT ST_AsTableFormat(
  '{"trajectory":{"version":1,"type":"STPOINT","leafcount":1,
    "start_time":"2010-01-01 11:30:00","end_time":"2010-01-01 11:30:00",
    "spatial":"SRID=4326;POINT(1 1)","timeline":["2010-01-01 11:30:00"],
    "attributes":{"leafcount":1,
      "i1":{"type":"integer","length":1,"nullable":true,"value":[1]},
      "i2":{"type":"integer","length":2,"nullable":true,"value":[1]},
      "i4":{"type":"integer","length":4,"nullable":true,"value":[1]},
      "i8":{"type":"integer","length":8,"nullable":true,"value":[1]},
      "f4":{"type":"float","length":4,"nullable":true,"value":[1]},
      "f8":{"type":"float","length":8,"nullable":true,"value":[1]},
      "string":{"type":"string","length":10,"nullable":true,"value":["fat"]},
      "timestamp":{"type":"timestamp","length":8,"nullable":true,"value":["2010-01-01 11:30:00"]},
      "bool":{"type":"bool","length":1,"nullable":true,"value":["true"]}}}}'::trajectory
);
-- Result:
-- (t timestamp, x double precision, y double precision, i1 integer, i2 smallint,
--  i4 integer, i8 bigint, f4 real, f8 double precision, string text,
--  timestamp timestamp, bool boolean)

The output maps each trajectory attribute to its corresponding PostgreSQL column type:

Expand a trajectory into a table

The following example shows the complete two-step workflow: call ST_AsTableFormat to get the column definition, then pass it to ST_AsTable to expand the trajectory into a queryable table.

Step 1. Get the column definition.

SELECT ST_AsTableFormat(traj) FROM table_name LIMIT 1;
-- Result:
-- (t timestamp, x double precision, y double precision, sog real, cog integer,
--  hdg integer, rot integer, status integer, is_satelite smallint, statictime integer)

Step 2. Use the definition in ST_AsTable to expand the trajectory.

SELECT f.*
FROM table_name,
     ST_AsTable(traj) AS f(
       t timestamp, x double precision, y double precision,
       sog real, cog integer, hdg integer, rot integer,
       status integer, is_satelite smallint, statictime integer
     );