ERROR: parse error - invalid geometry (Postgis)

Question

Initially I was storing my column as path and then following this question I changed it to text.

I'm trying to convert my column from text to geometry type by running this command:

ALTER TABLE area_grid_01 ALTER COLUMN coordinates TYPE Geometry(point, 4326) USING ST_SetSRID(coordinates::Geometry, 4326);

And facing this error:

ERROR:  parse error - invalid geometry
HINT:  "[(" <-- parse error at position 2 within geometry
SQL state: XX000

Following this question, I changed my data formatting and column looks like this:

The reason I am converting it into geometry in the first place is to be able to perform postgis functions on data.

I have explored many other ways trying to figure out a way around it. Seemingly, its not working yet!

Answer 1

I have no idea how you got the [] round what appears to be a textual representation of a LINESTRING, and there is a fairly strong clue in the error message. To remove them:

1. use regexp_replace to remove the superfluous [ and ] around your text string.
2. Use the Postgres concat operator to add a leading LINESTRING, so that you have a valid textual representation of a geometry.
3. Use ST_GeomFromText to convert to a geometry. You can either wrap this with ST_SetSRID, as you have, or use the second form, adding in the SRID to the text string -- it makes no practical difference.
4. Combine the ALTER TABLE statement with USING, as you have. This basically says change the data type in place, using, the updated geometry type in place of the existing text type.

For example, using a simplified example of what you have above, [(1 1, 2 2, 3 3)], as I am too lazy to type out yours:

SELECT ST_GeomFromText(concat('LINESTRING',
            regexp_replace('[(1 1, 2 2, 3 3)]', '(\[|\])','', 'g')));

returns a linestring -- if you wrap this in ST_AsText, you get,

LINESTRING(1 1,2 2,3 3)

Note, the (a|b) syntax means match a and/or b the \ before the [ and ] is an escape character, as [ and ] have special a meaning in regular expressions, and g is a global flag, meaning replace everywhere.

Putting this altogether:

ALTER TABLE area_grid_01 ALTER COLUMN coordinates TYPE Geometry(LINESTRING, 4326) 
USING ST_SetSRID(ST_GeomFromText(concat('LINESTRING', 
           regexp_replace(coordinates, '(\[|\])','', 'g'))), 4326);

ought to do what you want, using your coordinates column instead of hard-coded numbers as above. The last bit is untested, so there may be an extra/missing ( or ) somewhere.

Answer 2

1. It looks like you're getting that error because you're attempting to change the data type on a column that is already populated with a different data type.
2. The data you have doesn't look like any geographic text format that I'm familiar with -- you may need to use some SQL string functions to get this into, for example, WKT
3. what kind of geometry type you have (polygon? lines?). You'll need to add this to the text with your string functions.
4. Once the data is in geographic text format (and you should also know the coordinate reference system, let's say it's EPSG:4326, looks that way) you should create a new geometry type field with AddGeometryColumn
5. Finally you should use Update to populate the field with data converted from the field containing your cleaned up geographic text using ST_GeomFromText