Return to 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 geometry, 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 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 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.

For example, using a simplified geometry, 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 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 geometry, 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 have no idea how you got the [] round what appears to be a textual representation of a LINESTRING, but, to remove them, you can use regexp_replace. Then use concat to add a leading LINESTRING so that you have a valid textual representation of a geometry and finally use ST_GeomFromText to convert to a geometry.

For example, using a simplified geometry, 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 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.

For example, using a simplified geometry, 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.