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

9
  • Do you have PostGIS extension installed into your database? Check with select postgis_version();.
    – user30184
    Oct 28 at 7:42
  • Yes, postgis was installed. select postgis_version() --> 2.1 USE_GEOS=1 USE_PROJ=1 USE_STATS=1 Oct 28 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 =) Oct 28 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
    Oct 28 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
    Oct 28 at 8:41
4

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

Thank you @geozelot

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.