Complex JSON query

Question

I have a table like that:

id | lat | lon | date       | speed
1  | 45  | 66  | 2018-02-01 | 139
1  | 44  | 63  | 2018-04-01 | 129
2  | 41  | 56  | 2018-01-01 | 109
2  | 44  | 45  | 2018-04-01 | 99

I wanna make a JSON like that:

{1:{path:[[45,66],[44,63]],speed:129}, 2:{path:[[41,56],[44,45]], speed:99}};

Where speed is taken at last date;

Without the speed parameter, the solution is

SELECT jsonb_object_agg(id,j)
FROM (
  SELECT id, jsonb_agg(jsonb_build_array(lat,lon)) AS j
  FROM foo
  GROUP BY id
) AS t; 

How can it be modified to include last speed?

• Postgresql 9.6

Answer 1

Working one, probably there is something better.

SELECT json_agg(json_build_object(id, to_jsonb(t) - 'id'))
FROM (
WITH last_time AS (SELECT DISTINCT ON (id) id, time_id, speed FROM foo ORDER BY id, time_id DESC),
             t AS (SELECT id, jsonb_agg(jsonb_build_array(latitude,longitude)) AS path FROM foo GROUP BY id)
             SELECT t.id as id, t.path as path, last_time.speed as speed FROM t LEFT JOIN last_time ON t.id=last_time.id
) AS t;

Answer 2

Something like this should work,

SELECT t1.id, ST_AsGeoJSON(line), t2.speed;
FROM (
    SELECT id,
      ST_MakeLine( ST_MakePoint(lon,lat)::geography ORDER BY date ) AS line
    FROM tbl
    GROUP BY id
) AS t1
CROSS JOIN LATERAL (
  SELECT speed
  FROM tbl AS t2
  WHERE t2.id = t1.id
  ORDER BY date DESC
  FETCH FIRST ROW ONLY
) AS t2;

Work a lot faster if you stored ST_MakePoint(lon,lat) on the table instead of as separate columns.

Note ST_AsGeoJSON only implements the geometry part of the geojson spec. You'll have to finish off the doc. The speed would be a properties under the document.