PostGIS: Transform Linestring to LineString ZM

Problem:

I have a series of geometries (LineString) saved in a Postgres database with additional information saved next to the geometry (a column for speed and time). I want to combine the three columns into a LineString ZM type so the geometry goes from a 2d to a 4d vector.

id	geometry	speed	time
1	LineString_A	Int[]	Float[]
2	LineString_B	Int[]	Float[]

Question:

How do I most easily combine the geometry (x, y), speed, and time into a LineString ZM?

Answer

You’re looking for ST_Force4D.

All you need to do is to ALTER TABLE using the values you already have in the columns speed and time, e.g.

ALTER TABLE your_table ALTER COLUMN geom TYPE geometry (linestringzm, 4326) 
USING ST_Force4D(geom,speed,time);

Demo (PostGIS 3.1)

CREATE TABLE t (geom geometry(linestring,4326), speed int, time numeric);
INSERT INTO t VALUES ('SRID=4326;LINESTRING(30 10,10 30,40 40)'::geometry,42,1230);

ALTER TABLE t ALTER COLUMN geom type geometry (linestringzm, 4326) 
USING ST_Force4D(geom,speed,time);

SELECT ST_AsText(geom) FROM t;

                         st_astext                         
-----------------------------------------------------------
 LINESTRING ZM (30 10 42 1230,10 30 42 1230,40 40 42 1230)

Different scenario (see comments): speed and time are arrays that overlap 1:1 with the points in the LineString.

CREATE TABLE t
(id int, geom geometry(linestring,4326), speed int[], time numeric[]);
INSERT INTO t VALUES
(1,'SRID=4326;LINESTRING(30 10, 10 30, 40 40)'::geometry,ARRAY[1,2,3],ARRAY[1230,1231,1232]),
(2,'SRID=4326;LINESTRING(50 60, 70 80, 90 95)'::geometry,ARRAY[4,5,6],ARRAY[1458,1459,1500]);

Since you cannot unnest an array in a ALTER TABLE statement, you could just change the data type from LineString to LineStringZM and set the extra dimensions temporarily to 0:

ALTER TABLE t ALTER COLUMN geom TYPE geometry (linestringzm, 4326) 
USING ST_Force4D(geom,0,0);

The following CTE dumps all points from your LineStrings, create new ones with Z and M dimensions, and finally creates the LineStringZM for the UPDATE query.

WITH j AS (
  SELECT id, ST_MakeLine(j.pointzm) AS linestringzm
  FROM (SELECT id, geom AS linestring,
          (ST_DumpPoints(geom)).path AS path,
           ST_MakePoint(ST_X((ST_DumpPoints(geom)).geom),
                       ST_Y((ST_DumpPoints(geom)).geom),
                       unnest(speed),unnest(time)) AS pointzm
  FROM t ORDER BY id,path) j
  GROUP BY id 
)
UPDATE t SET geom = j.linestringzm
FROM j WHERE t.id = j.id;

SELECT id, ST_AsText(geom) FROM t;

 id |                       st_astext                        
----+--------------------------------------------------------
  1 | LINESTRING ZM (30 10 1 1230,10 30 2 1231,40 40 3 1232)
  2 | LINESTRING ZM (50 60 4 1458,70 80 5 1459,90 95 6 1500)
(2 rows)

Further reading:

• ST_DumpPoints
• ST_MakeLine