Point tables-ApsaraDB RDS(RDS)-阿里云帮助中心

In the vertex table approach, each sampling point is stored as a separate row. To perform trajectory operations, aggregate the rows into a trajectory object at query time using ST_MakeTrajectory. This trades higher storage usage for flexibility — you can filter or modify individual points before aggregation.

Use vertex tables when:

Queries are primarily filtered by trajectory ID or user ID
Storage cost is not a primary constraint
You need to process or filter individual points before aggregating them into a trajectory

After querying a vertex table, the query results must be aggregated into trajectories to perform the required operations.

The following example stores user trajectory data from a travel app. Each row captures one sampling point with columns for user ID, timestamp, coordinates (longitude, latitude, altitude), and signal intensity.

Set up the table

Create the table.

CREATE TABLE sample_points(
    userid numeric,          -- User ID
    sample_time timestamp,   -- Sampling point
    x double precision,      -- Longitude
    y double precision,      -- Latitude
    z double precision,      -- Altitude
    intensity int            -- Signal intensity of the sampling device
);

Insert test data.

INSERT INTO sample_points VALUES
(1,'2020-04-11 17:42:30',114.35, 39.28, 4, 80),
(1,'2020-04-11 17:43:30',114.36, 39.28, 4, 30),
(1,'2020-04-11 17:45:00',114.35, 39.29, 4, 50),
(2,'2020-04-11 17:42:30',114.3, 39, 34, 60),
(2,'2020-04-11 17:43:30',114.3, 39, 38, 58);

Create a btree index on userid to speed up ID-based queries.
```
CREATE INDEX on sample_points USING btree(userid);
```

Query and aggregate trajectory points

Retrieve sampling points for a specific user in chronological order. ORDER BY sample_time is required — trajectory construction depends on correctly ordered input.

SELECT * FROM sample_points WHERE userid = 1 ORDER BY sample_time;

To perform trajectory operations on the result, aggregate the rows into a trajectory object using ST_MakeTrajectory:

SELECT ST_MakeTrajectory(
    array_agg(
        ROW(traj.sample_time, traj.x, traj.y, traj.z, traj.intensity)
    ),
    true,
    '{"intensity"}'::cstring[]
)
FROM (SELECT * FROM sample_points WHERE userid = 1 ORDER BY sample_time) traj;

Note

In the ROW function, column order and data types are fixed:

First column: timestamp
Second and third columns: double precision (longitude and latitude)
Fourth column: double precision (altitude) — required when the second parameter of ST_MakeTrajectory is true. The value true indicates that trajectory points contain altitudes. If a column is not double precision, cast it explicitly: column_name::double precision or cast(column_name as double precision). For details on time value data types, see Data types for time values in PostgreSQL.

Remove drift points

After aggregating rows into a trajectory, apply ST_removeDriftPoints to remove inaccurate sampling points. The function requires the trajectory's spatial reference identifier (SRID) to be 4326, which specifies the World Geodetic System 1984 (WGS84). Use ST_SetSRID to set the SRID before passing the trajectory to ST_removeDriftPoints.

SELECT ST_removeDriftPoints(
    ST_SetSRID(
        ST_MakeTrajectory(
            array_agg(
                ROW(traj.sample_time, traj.x, traj.y, traj.z, traj.intensity)
            ),
            true,
            '{"intensity"}'::cstring[]
        ),
        4326
    ),
    40, 10, '1 minute'::interval
)
FROM (SELECT * FROM sample_points WHERE userid = 1 ORDER BY sample_time) traj;

Note If the input trajectory SRID is not 4326, ST_removeDriftPoints returns a trajectory with SRID 0, which indicates an unknown spatial reference.

For parameter details, see ST_MakeTrajectory, ST_SetSRID, and ST_removeDriftPoints.

Convert a trajectory back to rows

ST_removeDriftPoints returns a trajectory object. To convert it back to a vertex table, use ST_AsTable:

WITH removed AS (
    SELECT ST_removeDriftPoints(
        ST_SetSRID(
            ST_MakeTrajectory(
                array_agg(
                    ROW(traj.sample_time, traj.x, traj.y, traj.z, traj.intensity)
                ),
                true,
                '{"intensity"}'::cstring[]
            ),
            4326
        ),
        40, 10, '1 minute'::interval
    ) AS trajcol
    FROM (SELECT * FROM sample_points WHERE userid = 1 ORDER BY sample_time) traj
)
SELECT f.*
FROM removed,
ST_AsTable(removed.trajcol) AS f(
    sample_time timestamp,
    x double precision,
    y double precision,
    z double precision,
    intensity int
);

Note If you connect to your ApsaraDB RDS instance using Data Management (DMS) and run this statement, compatibility issues may occur. If you see a message indicating that unexpected results are returned, use a different client.