2

I have a table:

CREATE TABLE foo (id,lat,lon)
AS VALUES
  ( 1, 34, 45 ),
  ( 1, 45, 56 ),
  ( 2, 56, 67 ),
  ( 2, 58, 64 );

How can I get JSON like that:

{"1":[[34,45],[45,56]], "2":[[56,67],[58,64]]}
  • This has nothing to do with spatial, or geojson. – Evan Carroll Jan 11 at 23:21
  • The spatial solution is actually much easier, but then you don't store points in a table. You store either lines or MULTIPOINTs. and you can use PostGIS's ST_ToGeoJSON – Evan Carroll Jan 11 at 23:29
  • I have an ability to save them like Postgis geom::point, can u provide this solution also? – delkov Jan 11 at 23:36
  • no, it's a totally different question that requires you store a GEOGRAPHY or GEOMETRY type on the table, install the PostGIS extension, save all your points in a totally different format, and then query the table differently. It would take 25 pages for me to go over that setup and explain what's happening. I do however HIGHLY suggest the PostGIS in Action book if you're looking for direction and you want to learn GIS. – Evan Carroll Jan 11 at 23:44
4

First you do something like this,

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

That aggregates the values into an JSON Array. You get

 id |          j           
----+----------------------
  2 | [[56, 67], [58, 64]]
  1 | [[34, 45], [45, 56]]

From there you need to build an JSON Object..

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;

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.