TransWikia.com

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

Geographic Information Systems Asked on July 8, 2021

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?

One Answer

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

Correct answer by CL. on July 8, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP