0

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:

01060000000000000000___________________________

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);

and later:

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!"

  • That example would describe a MultiPolygon containing zero polygons, which should have 18 characters (9 bytes). Are you really sure this is geometry data? – CL. Jul 30 '18 at 6:35
  • @CL. I can't be entirely sure, I just thought it might be because of the format. Currently, it's stored in a redshift db which does not support geometry but I was hoping I could grab the varchar and turn it into a geometry type. Perhaps my way of explaining the problem is not accurate, I'm still somewhat new to GIS on the databases side – atlasofcoffee Jul 31 '18 at 16:18
  • as @CL. mentioned, the WKT geometry structure is of type MultiPolygon and is parsed without error with the first 764 characters...with the example being an empty geometry, though. it might just work with the column type set to MultiPolygon. if that actually yield any useful results...well, that remains to be seen. btw., if you have created the column accordingly using AddGeometryColumn you can simply UPDATE wkt_table SET geom = ST_Astext(left(geometry, 764));. – ThingumaBob Aug 1 '18 at 9:22

Your Answer

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

Browse other questions tagged or ask your own question.