save geometry type data with timestamp in postgresql

Question

I want to save LineString like the below in 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] means we are in this point [50.998085021972656, 35.83528137207031] at timestamp 1682083948.0. I know postgis can save linestring like below :

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

but there is no way to save timestamp in this kind of column. is there anyway to save timestamp alongside lat and lon in postgres or anyother type of data in postgres or not ?

Jim Jones · Accepted Answer · 2023-05-23T15:46:14.717

You can add the EPOCH of your time stamp to the coordinate pairs using the Z dimension, e.g. 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)

To convert the epoch back to timestamp ..

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)

To convert the geometry to 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)

.. and this creates your 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)

Demo: db<>fiddle

thanks for the great explanation, but I don't want to save each point in a row. I guess your solution is saving each point in each row of the table, am I right?I want to save such thing `LINESTRING(50.99851989746094 35.83426284790039 10000 1682112566.0, 50.9981575012207 35.83538818359375 10000 1682112592.0 )` in each row as `path` column — MSepehr, May 24 '23 at 08:51
@MSepehr I'm glad to help. This only shows how to create `POINT Z` and `LINESTRING Z` geometries. The amount of geometries per record can be adjusted using an aggregator, such as `ST_Collect` or `ST_Union`. I can try to help you further if you provide the input data and the exact expected result - preferably in a fiddle, as I used in my demo. Cheers! — Jim Jones, May 24 '23 at 10:36

score 1 · Answer 2 · answered May 24 '23 at 10:13

1

Thanks to @jim-jones's hint I could find the more straightforward solutions :

I can have two options : CREATE TABLE geotb (location_d geometry(LINESTRINGzm)) and
CREATE TABLE geotb (location_d json)

the cost of solution 2 is saving a big Json in db but can be used directly in Kepler, solution2 saves points as binary type(with higher perfomance) and we will be able to manipulate it by means of PostGIS functions and routines

answered May 24 '23 at 10:13

MSepehr

890
2
13
36

1

Keep in mind that GeoJSON is just a representation of a geometry and it is intended to be used as data format, not data type. In other words, postgres has an internal abstract format to store geometries, which has nothing to do with GeoJSON, WKT, KML, GML, MARC21. Saving geometries as anything other than `geometry` or `geography` is almost never a good idea. + 1 for reporting your findings! – Jim Jones May 24 '23 at 10:54

save geometry type data with timestamp in postgresql

2 Answers2