如何在PostgreSQL中存储带时间戳的LineString几何数据？

你可以通过以下几种方式实现同时存储经纬度和时间戳的需求：

1. 利用PostGIS的M维度（Measure维度）存储时间戳 #

PostGIS的LINESTRINGM类型支持在坐标中添加额外的M维度，专门用来存储测量值（比如时间戳），刚好匹配你的坐标结构：

• 创建表时定义字段类型为LINESTRINGM：

CREATE TABLE tracked_routes (
    id SERIAL PRIMARY KEY,
    route LINESTRINGM,
    style JSONB -- 存储样式信息，比如颜色、线宽
);

• 插入数据时把时间戳放到M维度位置：

INSERT INTO tracked_routes (route, style)
VALUES (
    ST_GeomFromText('LINESTRINGM(50.998085 35.835281 1682083948, 50.998100 35.835274 1682083948, 50.998024 35.835289 1682083948)', 4326),
    '{"color": "#19f3e7", "weight": 2}'
);

• 查询时提取每个点的时间戳：

SELECT 
    ST_X((ST_DumpPoints(route)).geom) AS lon,
    ST_Y((ST_DumpPoints(route)).geom) AS lat,
    ST_M((ST_DumpPoints(route)).geom) AS timestamp
FROM tracked_routes;

这种方案既能保留LineString的空间特性，又能支持PostGIS的所有空间查询（比如距离计算、相交检测）。

2. 拆分存储：LineString + 时间戳数组 #

如果不想用M维度，可以单独存储LineString和对应的时间戳数组，通过索引关联：

• 创建表：

CREATE TABLE tracked_routes (
    id SERIAL PRIMARY KEY,
    route LINESTRING,
    timestamps DOUBLE PRECISION[], -- 也可以用TIMESTAMP[]，按需转换
    style JSONB
);

• 插入数据时确保时间戳数组长度和LineString点数一致：

INSERT INTO tracked_routes (route, timestamps, style)
VALUES (
    ST_GeomFromText('LINESTRING(50.998085 35.835281, 50.998100 35.835274, 50.998024 35.835289)', 4326),
    ARRAY[1682083948.0, 1682083948.0, 1682083948.0]::DOUBLE PRECISION[],
    '{"color": "#19f3e7", "weight": 2}'
);

• 查询时通过generate_subscripts关联点和时间戳：

SELECT
    ST_X(ST_PointN(route, i)) AS lon,
    ST_Y(ST_PointN(route, i)) AS lat,
    timestamps[i] AS timestamp
FROM tracked_routes,
     generate_subscripts(timestamps, 1) AS i;

这种方案灵活性高，但需要自己维护点和时间戳的对应关系，避免出现长度不匹配的问题。

3. 直接存储GeoJSON（JSONB类型） #

如果业务不需要频繁进行空间查询，只是需要存储和读取完整的GeoJSON数据，可以直接用PostgreSQL的JSONB类型存储：

• 创建表：

CREATE TABLE tracked_routes (
    id SERIAL PRIMARY KEY,
    feature_collection JSONB
);

• 插入原始GeoJSON数据：

INSERT INTO tracked_routes (feature_collection)
VALUES (
    '{
        "type": "FeatureCollection",
        "features": [
            {
                "type": "Feature",
                "properties": {"style": {"color": "#19f3e7", "weight": 2}},
                "geometry": {
                    "type": "LineString",
                    "coordinates": [
                        [50.998085021972656, 35.83528137207031, 1000.0, 1682083948.0],
                        [50.99810028076172, 35.83527374267578, 1000.0, 1682083948.0],
                        [50.998023986816406, 35.835289001464844, 1000.0, 1682083948.0]
                    ]
                }
            }
        ]
    }'::JSONB
);

• 查询时提取坐标和时间戳：

SELECT
    coord->>0 AS lon,
    coord->>1 AS lat,
    coord->>3 AS timestamp
FROM tracked_routes,
     jsonb_path_query(feature_collection, '$.features[0].geometry.coordinates[*]') AS coord;

这种方案最直接，但无法利用PostGIS的空间索引和空间函数，适合只做数据存储和简单提取的场景。

4. 存储带时间戳的点集合（归一化表） #

如果需要更细粒度的查询（比如按时间范围过滤点），可以把每个带时间戳的点单独存储，再通过关联字段组成路线：

• 创建表：

CREATE TABLE tracked_routes (
    id SERIAL PRIMARY KEY,
    style JSONB
);

CREATE TABLE route_points (
    id SERIAL PRIMARY KEY,
    route_id INTEGER REFERENCES tracked_routes(id),
    point GEOGRAPHY,
    timestamp DOUBLE PRECISION,
    sequence INTEGER -- 标记点在路线中的顺序
);

• 插入数据：

-- 先插入路线主记录
INSERT INTO tracked_routes (style) VALUES ('{"color": "#19f3e7", "weight": 2}');
-- 获取刚插入的route_id并插入点数据
WITH route AS (SELECT id FROM tracked_routes ORDER BY id DESC LIMIT 1)
INSERT INTO route_points (route_id, point, timestamp, sequence)
SELECT
    route.id,
    ST_SetSRID(ST_MakePoint(50.998085021972656, 35.83528137207031), 4326),
    1682083948.0,
    1
FROM route
UNION ALL
SELECT
    route.id,
    ST_SetSRID(ST_MakePoint(50.99810028076172, 35.83527374267578), 4326),
    1682083948.0,
    2
FROM route
UNION ALL
SELECT
    route.id,
    ST_SetSRID(ST_MakePoint(50.998023986816406, 35.835289001464844), 4326),
    1682083948.0,
    3
FROM route;

• 重建LineString：

SELECT
    route_id,
    ST_MakeLine(point ORDER BY sequence) AS route
FROM route_points
GROUP BY route_id;

这种方案适合需要按时间或单个点进行复杂查询的场景，空间和时间查询都很灵活，但需要额外维护点的顺序。

内容的提问来源于stack exchange，提问作者MarziehSepehr