I've imported a large table that has a geometry column, problem is that it is in text format, how to change this to be a point geometry in 27700 CRS?

I've tried SELECT UpdateGeometrySRID('table','geom',27700);

But got hit with

SQL Error [P0001]: ERROR: column not found in geometry_columns table Where: PL/pgSQL function updategeometrysrid(character varying,character varying,character varying,character varying,integer) line 35 at RAISE SQL statement "SELECT public.UpdateGeometrySRID('','',$1,$2,$3)" PL/pgSQL function updategeometrysrid(character varying,character varying,integer) line 5 at SQL statement

I've tried update table set geom2 = st_setsrid(ST_GeomFromText(geom),27700)

But got hit with

SQL Error [XX000]: ERROR: parse error - invalid geometry Hint: "" <-- parse error at position 1 within geometry

Is there a different way to change my column type to geometry?

  • What is the format of the values in that column? – CL. Feb 16 at 11:11
  • @CL. the data type imported by ogr2ogr is text and the top returned cell contains POINT (535440 183402) – Luffydude Feb 16 at 12:07

To change the type of a column, you need to use ALTER TABLE. Use NULLIF to convert empty strings into NULLs:

SET DATA TYPE geometry(geometry, 27700)   -- or geometry(point, 27700)
USING ST_SetSRID(ST_GeomFromText(NULLIF(geom, '')), 27700);

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.