PostGIS:查询 z 和 m 维度(linestringzm) [英] PostGIS: Query z and m dimensions (linestringzm)

问题描述

问题

我有一个具有多个 linestringzm 的系统，其中数据的结构如下:[x, y, speed:int, time:int].数据以这种方式构建，以便能够在 x、y 和 z 维度上使用 ST_SimplifyVW，但是我仍然希望能够根据 m 维度查询线串，例如获取时间间隔之间的所有线串.

这在 PostGIS 中是可行的，还是我为我的用例错误地构建了数据?

示例

• z = 速度，例如公里/小时
• m = Unix 纪元时间

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

• 用例 A:基于 x、y 和 z 简化几何图形>

  这可以通过例如完成ST_SimplifyVW 简化后保持m维度.

• 用例 B:基于 m 维度的查询几何

  我有一组 linestringzm，我想根据我的时间维度 (m) 查询它们.如果所有 m 都在例如 1618388000 和 1618388700 之间，则结果要么是完整几何，要么是满足谓词的几何部分.查询数据的最有效方法是什么?

解决方案

如果你想检查 LineString 的每一个点，你可以 ST_DumpPoints 它们并得到 M带有 ST_M 的维度.之后将子集提取为包含重叠 M 值的 LineString 并应用 ST_MakeLine 和 GROUP BY:

WITH j AS (SELECT id,geom,(ST_DumpPoints(geom)).geom AS p从T)SELECT id,ST_AsText(ST_MakeLine(p))从 jWHERE ST_M(p) BETWEEN 1618388000 和 1618388700按 ID 分组；

演示:db<>fiddle

注意:根据您的表和 LineString 大小，此查询可能会变得非常缓慢，因为值在查询时被解析，因此不会被编入索引.恕我直言，一个更优雅的选择是..

.. 1) 创建一个 tstzrange 列

ALTER TABLE t ADD COLUMN line_interval tstzrange;

.. 2) 正确索引它

CREATE INDEX idx_t_line_interval ON t USING gist (line_interval);

.. 和 3) 用 geom 的 first 和 last 点的时间填充它:

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

之后，您可以通过检查索引列是否与给定间隔重叠来加快速度.这将显着缩短查询时间:

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

演示:db<>fiddle

进一步阅读:

• ST_M
• ST_PointN
• ST_NPoints
• PostgreSQL 内置范围类型

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

• z = speed e.g. km/h
• m = Unix epoch time

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);

• Use case A: Simplify the geometry based on x, y and z

  This can be accomplished by e.g. ST_SimplifyVW which keep the m dimension after simplification.

• Use case B: Query geometry based on the m dimension

  I have a set of linestringzm which I want to query based on my time dimension (m). The result is either the full geometry if all m is between e.g.1618388000 and 1618388700 or the part of the geometry which satisfies the predicate. What is the most efficient way to query the data?

解决方案

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:

• ST_M
• ST_PointN
• ST_NPoints
• PostgreSQL Built-in Range Types

这篇关于PostGIS:查询 z 和 m 维度(linestringzm)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！