Geopandas to PostGIS – Fix Geometry SRID Mismatch Error in Azure Postgres

geopandaspostgispython

I am trying to import a GeoDataFrame to PostGIS using the "to_postgis" method. I have multiple geometry columns. It works in my local machine and docker with Postgres version 14 and PostGIS version 3.2.2 but when I try to import it into Azure Postgres version 11 PostGIS version 2.5.1, it fails with this error:
"psycopg2.errors.InvalidParameterValue: Geometry SRID (0) does not match column SRID (4326)"

This is a part of my code:

gdf.to_postgis(
        name=filename,
        con=engine,
        schema=schema,
        if_exists="append",
        dtype={
            "geometry": Geometry("GEOMETRY", srid=4326),
            "start_coord": Geometry("POINT", srid= 4326),
            "end_coord": Geometry("POINT", srid= 4326),
        },
        index=False,
    )

Best Answer

I figured it out if anybody is interested. Just changed the code like this:

gdf.to_postgis(
        name=filename,
        con=engine,
        schema=schema,
        if_exists="append",
        dtype={
            "geometry": Geometry("GEOMETRY", srid=4326),
            "start_coord": Geometry("POINT", srid=0),
            "end_coord": Geometry("POINT", srid=0),
        },
        index=False,
    )

Then in Postgres:

ALTER TABLE my_schema.my_table
ALTER COLUMN start_coord TYPE geometry(POINT, my_srid)
USING ST_SetSRID(start_coord,my_srid);

ALTER TABLE my_schema.my_table
ALTER COLUMN end_coord TYPE geometry(POINT, my_srid)
USING ST_SetSRID(end_coord,my_srid);
Related Question