Ogr2ogr importing table with all in text type - how to change geometry accordingly?

Question

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?

Answer 1

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

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