ApsaraDB RDS:Use vertex tables

In this example, a vertex table is used to store a user trajectory on a travel app. The following data is collected at each sampling point: user ID, date and time, longitude, latitude, altitude, and signal intensity of the sampling device.

1. Create a 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
   );

2. Insert test data into the table.

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

3. Create an index for the userid column.

   CREATE INDEX on sample_points USING btree(userid);

4. Extract the trajectory points of the user whose userid is 1 at all sampling points. The trajectory points are arranged based on the chronological order of sampling points.

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

You can process the extracted trajectory points. For example, you can delete drift points that are not accurate.

SELECT ST_removeDriftPoints( -- Deletes inaccurate drift points.
    ST_SetSRID( -- Changes the spatial reference identifier (SRID) of a trajectory.
        ST_MakeTrajectory( -- Constructs a trajectory.
            array_agg(
                ROW(traj.sample_time, traj.x, traj.y, traj.z, traj.intensity) -- Selects the columns that you want to use to construct a trajectory.
            ), true, '{"intensity"}'::cstring[]),4326), 40, 10, '1 minute'::interval
) FROM (SELECT * FROM sample_points WHERE userid = 1 ORDER BY sample_time) traj;

The ST_removeDriftPoint function returns trajectories. If you want to convert trajectories into vertex tables, use the ST_AsTable function.

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 -- Converts a trajectory into a vertex table.
f(sample_time timestamp,x double precision, y double precision, z double precision, intensity int);