0

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!

  • 1
    ...just as in the link you provided, a WKT representation of a line geometry has to be e.g. 'LINESTRING(0 0, 1 0, 2 0, 3 0)'. – ThingumaBob Feb 12 at 21:54
2

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.

  • I'm trying to do above for POLYGON and its giving parsing error: ALTER TABLE area_grid ALTER COLUMN grid_coordinates TYPE Geometry(POLYGON, 4326) USING ST_SetSRID(ST_GeomFromText(concat('POLYGON', regexp_replace(grid_coordinates, '(\[|\])','', 'g'))), 4326); "POLYGON(43.672545 -" <-- parse error at position 19 within geometry – Saim Mehmood Feb 20 at 19:29
  • Please, share your data, this is a different error. Without knowing what grid_coordinates containsit is impossible to tell – John Powell Feb 20 at 19:42
  • The data is same, the one shown above. I'm just storing it in a different table with different name. – Saim Mehmood Feb 20 at 19:45
  • Any advice regarding above issue? – Saim Mehmood Feb 20 at 22:00
1
  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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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