5

I have a geometry column stored as text e.g.:

'010100002091080000000000804D624041000000C066735741' 

in PostgreSQL db with PostGIS. This for a point geometry with the EPSG:2193.

How do I convert this text column to a geometry column?

I have seen this question which is essentially the same, but the answer does not work.

  • Is that an actual example or random numbers? – bugmenot123 May 6 '16 at 9:05
  • '023013040523432420' is not a wkb ... – WKT May 6 '16 at 9:31
  • My apologies, those are random numbers. I'll edit it with a proper MWE. – Phil Donovan May 6 '16 at 9:40
  • OK, rather embarrassingly I've realised that there are null geometries. Hence the parsing error. I'm not sure if I should take down the question or leave it up to serve as a warning to newbies and, someone who should have done better such as myself... – Phil Donovan May 6 '16 at 9:52
6

Simply:

ALTER TABLE opr_records ALTER COLUMN geom TYPE Geometry USING geom::Geometry;

Or this if you're using a specific geometry (the post you linked to was using point) and SRID (4326 here):

ALTER TABLE opr_records ALTER COLUMN geom TYPE Geometry(POINT, 4326) USING ST_SetSRID(geom::Geometry,4326);
  • When running the above I get the following error: ERROR: parse error - invalid geometry – Phil Donovan May 6 '16 at 9:38
  • 1
    That'll happen if you've got some rows with values in geom that can't be cast into geometries. I tried the example you've just added to your question, and it works. SRID 2193. You should try identifying which rows are giving you problems and handle them some other way. – thatharmansingh May 6 '16 at 9:48
  • After I realised this I saw this comment. Terrible! I think it is good to leave this question up as the other is not properly flagged as the right answer. – Phil Donovan May 6 '16 at 9:54
  • 2
    The solution to removing the geometries was to UPDATE table SET the_geom = NULL WHERE the_geom = '';. – Phil Donovan May 6 '16 at 10:01

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.