As you know, redshift does not support geometry types. There is a column in a dataset on my redshift db that has varchar column and the format looks like:
on and on for 765 characters. Since it looks like at some point it was a geometry type, I want to convert this into WKT with hopes of then converting to geom using 'ST_GeomFromText'. I feel like I may be complicating this more than it has to be, I've never worked with WKT so if you have suggestions on how to achieve the same result with a more simple approach I'd prefer that.
I've added a new column using:
SELECT AddGeometryColumn ('public','wkt_regions','geomm',4326,'Polygon',2);
ALTER TABLE wkt_regions ALTER COLUMN geom TYPE geometry(Polygon) USING st_astext(left(geometry, 764));
Without the left function to trim the geometry I get an error that says " Invalid hex string, length (765) has to be a multiple of two!" But with it, I get an error that says "WKB structure does not match expected size!"