PostGIS – ERROR: Parse Error Invalid Geometry

pgadmin-4postgispostgresql

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:

enter image description here

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!

Best Answer

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.

Related Question