Question 174830 in PostgreSQL Question posted in
The official documentation can be found here.

save geometry type data with timestamp in postgresql

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 ?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

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

    1. I can have two options : CREATE TABLE geotb (location_d geometry(LINESTRINGzm)) and
    2. 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


  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search