在 postgresql 中保存带有时间戳的几何类型数据

发布于05月23日

我想要在PostgreSQL中保存如下所示的线串

{"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]]}}]}

[50.998085021972656, 35.83528137207031, 1682083948.0]表示我们在时间戳 1682083948.0处的点[50.998085021972656, 35.83528137207031] .我知道postgis可以保存行字符串，如下所示:

LINESTRING(77.29 29.07，77.42 29.26，77.27 29.31，77.29 29.07)

但是没有办法在这种列中保存时间戳.有没有办法将时间戳和LATH和LATH一起保存在postgres中，或者是否有任何其他类型数据保存在postgres中？

推荐答案

您可以使用Z维将时间戳的EPOCH添加到坐标对中，例如POINT Z:

SELECT 
 ST_AsText(
  ST_PointZ(
        52.0,
        6.0,
        EXTRACT(EPOCH FROM '2023-05-23 10:22:18'::timestamp),
        4326)
  );
         st_astext         
---------------------------
 POINT Z (52 6 1684837338)
(1 row)

要将纪元转换回时间戳..

WITH j (geo) AS (
  VALUES ('POINT Z (52 6 1684837338)'::geometry)
)
SELECT ST_X(geo), ST_Y(geo), 
  to_timestamp(ST_Z(geo))
FROM j;
 st_x | st_y |      to_timestamp      
------+------+------------------------
   52 |    6 | 2023-05-23 10:22:18+00
(1 row)

将几何图形转换为GeoJSON的步骤

SELECT 
  ST_AsGeoJSON(
   ST_MakeLine(
    ST_PointZ(52.0,6.0,EXTRACT(EPOCH FROM now()-'1 hour'::interval),4326),
    ST_PointZ(52.0,6.0,EXTRACT(EPOCH FROM now()),4326))
  );

                                      st_asgeojson                                       
-----------------------------------------------------------------------------------------
 {"type":"LineString","coordinates":[[52,6,1684836224.778756],[52,6,1684839824.778756]]}
(1 row)

..这将创建您的FeatureCollection:

 WITH j (geo) AS (
  VALUES
    (ST_MakeLine(
        ST_PointZ(52,6,EXTRACT(EPOCH FROM now()-'1 hour'::interval),4326),
        ST_PointZ(52,6,EXTRACT(EPOCH FROM now()),4326))),
    (ST_MakeLine(
        ST_PointZ(55,7,EXTRACT(EPOCH FROM now()-'2 hour'::interval),4326),
        ST_PointZ(55,7,EXTRACT(EPOCH FROM now()),4326)))
 )
SELECT json_build_object(
    'type', 'FeatureCollection',
    'features', json_agg(ST_AsGeoJSON(j.*)::json)
    )
FROM j;
                                                                                                                                                           json_build_object                                                                   
                                                                                        
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------
 {"type" : "FeatureCollection", "features" : [{"type": "Feature", "geometry": {"type":"LineString","coordinates":[[52,6,1684836873.929858],[52,6,1684840473.929858]]}, "properties": {}}, {"type": "Feature", "geometry": {"type":"LineString",
"coordinates":[[55,7,1684833273.929858],[55,7,1684840473.929858]]}, "properties": {}}]}
(1 row)

演示:db<>fiddle