CODEWITHSUNDEEP
postgresqlgispostgis
Muhammad Zahid
Muhammad Zahid

Reputation: 345

Convert lon/lat collection to geom/linestring

I have a column in postgis like:

[{"lang":-122.39726983952495,"lat":37.789519907547806},{"lang":-122.39703479547161,"lat":37.78933265166566}]

I need to draw the line on OpenLayers Map by converting this column to geometry.

Upvotes: 1

Views: 686

Answers (1)

S-Man
S-Man

Reputation: 23686

step-by-step demo:db<>fiddle (From JSON to line as geometry)

SELECT
    st_makeline(                              -- 5
        array_agg(point.point)                -- 4
    )
FROM
    my_table,
    json_array_elements(my_points) AS elems,  -- 1
    st_makepoint(                             -- 3
        (elems ->> 'lang')::numeric,          -- 2
        (elems ->> 'lat')::numeric
    ) AS point
  1. Creates one row for each array element
  2. Getting the numeric values from the json object
  3. Create point geometries for every row
  4. Aggregate all point geometries into one geometry array
  5. Create line geomatries out of geometry array

Upvotes: 2

Related Questions