Creating linestring geometry from jsonb column

Question

I am trying to create a line from an array of x,y coordinates inside of a jsonb field. Here is the format of the field:

enter image description here

I created a linestring geometry column, but having difficulty with the syntax. Do I need to make points from the x,y first? The j.line part is where I think i need to make points?

UPDATE waze.wazejams SET geom = ST_SetSRID(ST_MakeLine(**j.line**),4326)

I'm very green with PostGIS so go easy :)

UPDATE: I'm thinking I need to create a multipoint geometry to house the multiple vertexes along the line.

Answer 1

jsonb support is great, but handling them as rows is a little tricky. The key is to get familiar with the operators and functions to properly access json/jsonb objects.

To update <your_table>'s geom column with row-based LINESTRINGs from your jsonb data, run

WITH
 lines AS (
 SELECT <id>,
 ST_MakeLine(
 ST_MakePoint(
 (line -> n ->> 'x')::NUMERIC,
 (line -> n ->> 'y')::NUMERIC
 )
 ) AS geom
 FROM <your_table>
 CROSS JOIN generate_series(0, jsonb_array_length(line)) AS n
 GROUP BY <id>
 )
UPDATE <your_table> AS a
SET geom = ST_SetSRID(b.geom, <your_srid>)
FROM lines AS b
WHERE a.<id> = b.<id>

What's happening:

• in the CTE (i.e. WITH ...), for the each row in <your_table>

  • generate a series of numbers as index n, according to the length of the jsonb array
    (... CROSS JOIN generate_series(...) ...)
  • access the objects based on that index in numeric order (->) and extract the value of key 'x' and 'y' (->>) into ST_MakePoint()
  • pass those points to ST_MakeLine() in consequtive order (that is done implicitly here), grouped by the tables <id> column
    
    

  and create a temporary table lines with the geometries and the tables <id> column
  
  

• in the UPDATE, for each row in <your_table>

  • select that geometry from lines with matching <id>

  • set SRID to <your_srid> and copy into column geom

All parameters in <> are to be replaced with the names of your table/columns/SRID, of course.


Hope that made sense to you?

• MjonesGEO: Awesome! That worked. Thank you for the explanation.