Transforming a linestring table to trajectories on the fly

Question

I have the following code:

tracks AS
(SELECT 
    imei,
    st_setsrid(ST_MakeLine(points_geom ORDER BY time_created),4326) AS track from 
    subset_cr  
    GROUP BY imei
)

Is there any way I can transform these linestring tracks into trajectories on the fly since I do not have write permissions in the db?

I want to use time_created as the measure.

Answer 1

Simply reconstruct measured points from your dataset and collect them into LINESTRING Ms:

SELECT
  imei,
  ST_SetSRID(
    ST_MakeLine(
      ST_MakePointM(ST_X(points_geom), ST_Y(points_geom), time_created::BIGINT)
      ORDER BY time_created
    ),
    4326
  ) AS trajectory_geom
FROM
  subset_cr
GROUP BY
  imei
;