Use ST_AsTableFormat to get the column type definition of a trajectory - PolarDB

Returns the column definition string for a trajectory object, which you pass directly to ST_AsTable to expand the trajectory into a relational table.

Syntax

text ST_AsTableFormat(trajectory traj);

Parameters

Parameter	Description
`traj`	The trajectory object to inspect.

Description

ST_AsTableFormat reads the attribute schema of a trajectory and returns it as a text string in PostgreSQL column-definition format. The returned string lists each column name and its SQL data type, separated by commas, enclosed in parentheses — for example:

(t timestamp, x double precision, y double precision, i1 integer, ...)

The column list always starts with t (the timestamp column) and x, y (the spatial coordinates). Attribute columns follow in the order they appear in the trajectory definition. Trajectory attribute types map to SQL column types as follows:

Trajectory attribute type	Length	SQL column type
`integer`	1 byte	`integer`
`integer`	2 bytes	`smallint`
`integer`	4 bytes	`integer`
`integer`	8 bytes	`bigint`
`float`	4 bytes	`real`
`float`	8 bytes	`double precision`
`string`	any	`text`
`timestamp`	8 bytes	`timestamp`
`bool`	1 byte	`boolean`

Usage notes

ST_AsTableFormat is a helper function for ST_AsTable. The typical workflow is:

Call ST_AsTableFormat on a sample trajectory to get the column definition string.
Copy the output and use it as the column list in ST_AsTable to expand trajectories into rows.

The two functions must be used together — ST_AsTableFormat alone does not expand any data.

Examples

Expand a trajectory table using ST_AsTableFormat output

First, get the column definition string for a trajectory in your table:

SELECT ST_AsTableFormat(traj) FROM table_name LIMIT 1;

Copy the output, then use it as the column specification in ST_AsTable:

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);

Get the column definition for an inline trajectory

The following example calls ST_AsTableFormat on a trajectory value constructed inline:

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
);

Output:

(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)