2

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

Improve this question
9
  • Do you have PostGIS extension installed into your database? Check with select postgis_version();.
    – user30184
    Commented Oct 28, 2021 at 7:42
  • Yes, postgis was installed. select postgis_version() --> 2.1 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
    – aksh_sharma
    Commented Oct 28, 2021 at 7:50
  • what tool do you use to execute your request ? Maybe try to create your table first check if it's good then go with a insert into request ... baby steps =)
    – Maximilien jaffrès
    Commented Oct 28, 2021 at 7:53
  • Does simple select ST_SetSRID(ST_MakePoint(1,2), 4326) work? If is works then perhaps {column}[0],{column}[1] are not of correct datatype for ST_MakePoint.
    – user30184
    Commented Oct 28, 2021 at 7:58
  • 2
    FWIW: your second statement is syntactically wrong: there is no CAST to ST_SetSRID - you need to wrap the actual CAST into those functions, i.e. USING ST_SetSRID(<column>::GEOMETRY(POINT), 4326)
    – geozelot
    Commented Oct 28, 2021 at 8:41

1 Answer 1

Reset to default
4

(@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);")

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.