CODEWITHSUNDEEP
postgresqlpostgis
SOK
SOK

Reputation: 595

PostGIS: Query z and m dimensions (linestringzm)

Question

I have a system with multiple linestringzm, where the data is structured the following way: [x, y, speed:int, time:int]. The data is structured this way to be able to use ST_SimplifyVW on the x, y and z dimensions, but I still want to be able to query the linestring based on the m dimension e.g. get all linestrings between a time interval.

Is this possible with PostGIS or am I structuring the data incorrectly for my use case?

Example

CREATE TABLE t (id int NOT NULL, geom geometry(LineStringZM,4326), CONSTRAINT t_pkey PRIMARY KEY (id));
INSERT INTO t VALUES (1, 'SRID=4326;LINESTRING ZM(30 10 5 1620980688, 30 15 10 1618388688, 30 20 15 1615710288, 30 25 20 1620980688)'::geometry);
INSERT INTO t VALUES (2, 'SRID=4326;LINESTRING ZM(50 10 5 1620980688, 50 15 10 1618388688, 50 20 15 1615710288, 50 25 20 1620980688)'::geometry);
INSERT INTO t VALUES (3, 'SRID=4326;LINESTRING ZM(20 10 5 1620980688, 20 15 10 1618388688, 20 20 15 1615710288, 20 25 20 1620980688)'::geometry);

Upvotes: 1

Views: 1372

Answers (1)

Jim Jones
Jim Jones

Reputation: 19643

If you want to check every single point of your LineString you could ST_DumpPoints them and get the M dimension with ST_M. After that extract the subset as a LineString containing the overlapping M values and apply ST_MakeLine with a GROUP BY:

WITH j AS (
  SELECT id,geom,(ST_DumpPoints(geom)).geom AS p 
  FROM t 
) 
SELECT id,ST_AsText(ST_MakeLine(p))
FROM j
WHERE ST_M(p) BETWEEN 1618388000 AND 1618388700
GROUP BY id;

Demo: db<>fiddle

Note: Depending on your table and LineString sizes this query may become pretty slow, as values are being parsed in query time and therefore aren't indexed. Imho a more elegant alternative would be ..

.. 1) to create a tstzrange column

ALTER TABLE t ADD COLUMN line_interval tstzrange;

.. 2) to properly index it

CREATE INDEX idx_t_line_interval ON t USING gist (line_interval);

.. and 3) to populate it with the time of geom's first and last points:

UPDATE t SET line_interval = 
  tstzrange(
    to_timestamp(ST_M(ST_PointN(geom,1))),
    to_timestamp(ST_M(ST_PointN(geom,ST_NPoints(geom)))));

After that you can speed things up by checking wether the indexed column overlaps with a given interval. This will significantly improve query time:

SELECT * FROM t
WHERE line_interval && tstzrange(
                        to_timestamp(1618138148), 
                        to_timestamp(1618388700));

Demo: db<>fiddle

Further reading:

Upvotes: 2

Related Questions