PostGIS Geometry – How to Convert Native Point Datatype into Geometry(Point,4326) Using PostGIS

geometrypostgispsycopg2sql

I have a table with "coordinate" column in it of data_type POINT (native postrgresql point). I want to convert it into Geometry(Point,4326)

I am using psycopg2 to connect to my database.

I ran this command:

cur.execute(f"ALTER TABLE {table} ALTER COLUMN {column} TYPE geometry(Point, 4326) USING ST_SetSRID(ST_MakePoint({column}[0],{column}[1]), 4326);")

But it gives this error:

server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request

and if I use

cur.execute(f"ALTER TABLE {table} ALTER COLUMN {column} TYPE geometry(Point, 4326) USING {column}::ST_SetSRID(ST_MakePoint({column}[0],{column}[1]), 4326);")

it says st_setsrid does not exist.

If I cannot solve this issue, is there any other way to convert dataType of column from native point to geometry(point,4326)?

Best Answer

(@geozelot) Right way to type cast is -

cur.execute(f"ALTER TABLE {table} ALTER COLUMN {column} TYPE geometry(Point,4326) USING ST_SetSRID({column}::GEOMETRY(POINT), 4326);")

Related Question