0

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).

Screenshot of coordinates column in PostGIS

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.

  • Your question is too broad or it is a duplicate of gis.stackexchange.com/questions/162734/… – barteksch Jun 12 '19 at 8:48
  • I've seen this post, but it doesn't seem to answer the questions when all my coordinate pairs are in a column, and I don't manually type them. Or what am I missing here? – SuperPirat0s Jun 12 '19 at 8:51
  • Your coordinates column has wrong format for ST_GeomFromText, it is explained in mentioned post. – barteksch Jun 12 '19 at 9:22
  • Well-Known Text is a specific format. It does not have commas between the X/Y/Z/M values for each ordinate. – Vince Jun 12 '19 at 9:59
  • You are missing the fact that coorindates are arranged as x1 y1, x2 y2, etc, ie, there is a space between them. Also, don't use ||, as it is hard to read, used the Postgres concat operator. – John Powell Jun 12 '19 at 10:00
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
  • 1
    Yes thank you, that is how I came about it with the input from barteksch – SuperPirat0s Jun 13 '19 at 10:14

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