Converting lat long strings to xy linestrings [closed]

Question

In PostGIS, I have a dataset with road segments including a column with coordinates for the various segments. The coordinates are in a lat-long format and the data type of the column is character varying, plus it contains a varying number of coordinates in each row (see attached image).

What I wish to do is to be able to show the segments as geometric linestrings in order to later match them to a road network and/or a map.

I've already tried using ST_GeomFromText (see below) and ST_DumpPoint, but without any luck.

SELECT ST_GeomFromText('LINESTRING('||coordinates||')') as geom FROM vejnet.segmentnetdatex2format;

Which results in the following error code

ERROR: parse error - invalid geometry HINT: "LINESTRING(55.67258,1" <-- parse error at position 21 within geometry SQL state: XX000

I'm still fairly new to PostGIS and working with GIS/spatial data in general.

Answer 1

ST_GeomFromText demands the coordinates in the x1 y1,x2 y2, ... ,xn yn format, as said in the comments. Consider using something like this to adjust the format of your coordinates column:

select coordinates, replace(replace(replace(coordinates, ' ', ';'), ',', ' '), ';', ',') as adjusted, ST_GeomFromText('LINESTRING('||adjusted||')') as geom from vejnet.segmentnetdatex2format