Geek asks and answers

PostGIS / CartoDB: save timestamp for each point in MultiLineString

I am using CartoDB as a PostGIS server for my application.

I need to save the tracks and associate a time stamp for each pair of coordinate points of the track in order to make queries such as give me the distance traveled between 12AM and 12PM on day X.

In CartoDB, I can only create 3 types of tables: MULTIPOINT, MULTILINESTRING and MULTIPOLYGON. To insert tracks, I use the MULTILINESTRING table.

My first attemp was to insert a timestamp as the Z index for each point in MULTILINESTRING, however I always get ERROR: Geometry has Z dimension but column does not.

How is this achieved in the "normal" PostGIS database, and also how can I achieve this in the implementation of PostGIS CartoDB?

UPDATE 1:

So, after jatorre's answer, I created a JSFiddle example as an exercise for exercises. However, I get incoherent results.

In the above example, I have two tables with the same two datasets. However, one of them is a table MULTILINESTRINGin which each row represents segment, and the other is a table MULTIPOINTin which I store each set of coordinates segment.

Then I query these two tables to get total distance of segments according to transport mode. I think I understood jattore idead very well, but I don’t understand why I have different results for Carand Walkoverall distance. Any clues?

DECISION:

, points segment. , , :

WITH segments AS 
  (SELECT ST_Makeline(pts.the_geom_webmercator) as the_geom, (MAX(pts.timestamp) - MIN(pts.timestamp)) AS time, paths.transport_mode, paths.cartodb_id AS id
   FROM (SELECT * FROM points ORDER BY track_id, path_id, timestamp ASC) AS pts JOIN paths ON pts.path_id=paths.cartodb_id
   WHERE paths.user_id=1
   GROUP BY id, transport_mode)
SELECT SUM(ST_Length(segments.the_geom)) AS distance, SUM(segments.time), segments.transport_mode
FROM segments
GROUP BY segments.transport_mode
ORDER BY distance
+5
1

, .

, GPS. GPS , , , start_time end_time.

SELECT WHERE ST_Length_Spheroid .

, , :

the_geom(point), timestamp, cartodb_id

-

WITH segments AS 
(SELECT ST_MakeLine(
   the_geom,
   (SELECT the_geom FROM tracks as st WHERE st.cartodb_id = t.cartodb_id+1)
) as the_geom
FROM traces as t WHERE timestamp BETWEEN ...)
SELECT sum(ST_Length_Spheroid(segments.the_geom)

... , .

Z, .

+3

More articles:


All Articles