ApsaraDB RDS:Use trajectory tables

In this example, data on a travel app is used.

CREATE TABLE trajectory_table(
    userid numeric PRIMARY KEY, 
    traj trajectory
);

You can create a trajectory by using one of following methods:

• Convert a vertex table into a trajectory. For more information, see Use vertex tables.

  The GROUP BY clause may not sort data based on the chronological order of sampling points. You must call the ST_Sort() function to sort the data based on the chronological order of sampling points.

  INSERT INTO trajectory_table
  SELECT userid, ST_Sort(ST_MakeTrajectory(pnts.tjraw, true, '{"intensity"}'::cstring[]))
  FROM
  (SELECT sample_points.userid, 
          array_agg(
              ROW(sample_points.sample_time, 
                  sample_points.x, 
                  sample_points.y, 
                  sample_points.z, 
                  sample_points.intensity)) 
          AS tjraw FROM sample_points GROUP BY userid
  ) pnts;

• Call the ST_MakeTrajectory function to construct trajectories. For more information, see ST_makeTrajectory.

  Note

  In this example, the trajectory of the user whose userid is 1 is displayed. The trajectory consists of GEOMETRY data, a TIMESTAMP array, and a JSON string.

  INSERT INTO trajectory_table 
  SELECT 3, ST_MakeTrajectory(
              'STPOINT'::leaftype, 
              st_geomfromtext('LINESTRING(114.35 39.28 4,114.36 39.28 4,114.35 39.29 4)', 4326), 
              ARRAY['2020-04-11 17:42:30'::timestamp,'2020-04-11 17:43:30'::timestamp,'2020-04-11 17:45:00'::timestamp], 
              '{"leafcount":3,"attributes":{"intensity":{"type":"integer","length":4,"nullable":true,"value":[80,30,50]}}}'
  );           

  Note

  The ST_MakeTrajectory function provides multiple methods to construct trajectories. Some methods use GanosBase geometries. For more information, see ST_makeTrajectory and Geometries in GanosBase.

• Use JSON strings to construct trajectories.

  INSERT INTO trajectory_table
  SELECT 4, 
  '{"trajectory":{"version":1,"type":"STPOINT","leafcount":3,"start_time":"2020-04-11 17:42:30","end_time":"2020-04-11 17:45:00","spatial":"SRID=4326;LINESTRING(114.35 39.28 4,114.36 39.28 4,114.35 39.29 4)","timeline":["2020-04-11 17:42:30","2020-04-11 17:43:30","2020-04-11 17:45:00"],"attributes":{"leafcount":3,"intensity":{"type":"integer","length":4,"nullable":true,"value":[80,30,50]}}}}'
  ::trajectory;

You can perform spatio-temporal operations on a trajectory. For example, you can check whether the trajectory intersects or contains another object. You can create an R-tree index to accelerate spatial queries.

-- Create an index. 
CREATE INDEX ON trajectory_table USING gist(traj);

-- Query the trajectories that intersect a two-dimensional rectangle and return the value of userid for the queried trajectories. 
SELECT userid FROM trajectory_table WHERE ST_2DIntersects(traj, ST_MakeEnvelope(114.33,39.28,14.331,39.282));