0

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
0

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;
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.