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.

closed as off-topic by csk, nmtoken, Ian Turton Jun 16 at 9:42

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "This problem cannot or can no longer be reproduced. Changes to the system or to the asker's circumstances may have rendered the question obsolete, or the question does not include a procedure to enable potential answerers to reproduce the same symptoms. Such questions are off-topic as they are unlikely to help future readers, but editing them to include more details can lead to re-opening." – csk, nmtoken, Ian Turton
If this question can be reworded to fit the rules in the help center, please edit the question.

  • Your question is too broad or it is a duplicate of gis.stackexchange.com/questions/162734/… – barteksch Jun 12 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 at 8:51
  • Your coordinates column has wrong format for ST_GeomFromText, it is explained in mentioned post. – barteksch Jun 12 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 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 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 at 10:14

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