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?
Advertisement
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: