PostGIS/CartoDB: Store Timestamp for each point in a MultiLineString

Question

I'm using CartoDB as a PostGIS server for my app.

I need to store tracks and associate a timestamp for each coordinate pair of track points, in order to make queries like 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'm using a MULTILINESTRING table.

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

How is this achieved in a "normal" PostGIS database and also how can I achieve it in CartoDB PostGIS implementation?

UPDATE 1:

So after jatorre answer I created a JSFiddle example as a practicing exercise. However I'm getting incoherent results.

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

Then I query these two tables to get the total distance of segments according to transport mode. I think I got jattore's idead pretty clearly, however I don't understand why I get different results for Car and Walk total distance. Any hints?

SOLUTION:

The small diferences in distances were because I needed to sort the points table before grouping the segment. This is my current query to get the total distance and time traveled according to transport mode:

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

Answer 1

Well, it depends a bit.

Imagine you have an structure of GPS traces. In this case you will convert those GPS traces into Segments, lines of just two coordinates, and you can have start_time and end_time separated columns.

Then with that structure you can do a SELECT with a WHERE clause between those dates and summing the ST_Length_Spheroid of the segments.

If you want to do all in one single query, considering that you have a table with:

the_geom(point), timestamp, cartodb_id

The you can do something like

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)

This is totally our of my head... but i hope you get the idea.

You dont need to use Z dimension and actually we dont well support that.