python - psycopg2.errors.RaiseException find_srid() - could not find the corresponding SRID

Question

I'm trying to import a layer into a PostGIS database with

gdf.to_postgis('test_polygons', engine, if_exists='append', index=False, dtype={'geom': Geometry(geometry_type='POLYGON', srid= 4326)})

And I'm getting the error:

Traceback (most recent call last): File "C:\Users\Александр\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\engine\base.py", line 1276, in _execute_context self.dialect.do_execute( File "C:\Users\Александр\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\engine\default.py", line 609, in do_execute cursor.execute(statement, parameters) psycopg2.errors.RaiseException: find_srid() - could not find the corresponding SRID - is the geometry registered in the GEOMETRY_COLUMNS table? Is there an uppercase/lowercase mismatch? CONTEXT: PL/pgSQL function find_srid(character varying,character varying,character varying) line 17 at RAISE

I have a custom projection, which i set to the layer Any ideas, what's wrong with geometry and srid?

Full error message:

Traceback (most recent call last): File "C:\Users\Александр\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\engine\base.py", line 1276, in _execute_context self.dialect.do_execute( File "C:\Users\Александр\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\engine\default.py", line 609, in do_execute cursor.execute(statement, parameters) psycopg2.errors.RaiseException: find_srid() - could not find the corresponding SRID - is the geometry registered in the GEOMETRY_COLUMNS table? Is there an uppercase/lowercase mismatch? CONTEXT: PL/pgSQL function find_srid(character varying,character varying,character varying) line 17 at RAISE

The above exception was the direct cause of the following exception:

Traceback (most recent call last): File "<pyshell#84>", line 1, in gdf.to_postgis('test_polygons', engine, if_exists='append', index=False, dtype={'geom': Geometry(geometry_type='POLYGON', srid= 4326)}) File "C:\Users\Александр\AppData\Local\Programs\Python\Python38\lib\site-packages\geopandas\geodataframe.py", line 1097, in to_postgis geopandas.io.sql._write_postgis( File "C:\Users\Александр\AppData\Local\Programs\Python\Python38\lib\site-packages\geopandas\io\sql.py", line 375, in _write_postgis target_srid = connection.execute( File "C:\Users\Александр\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\engine\base.py", line 1003, in execute return self.execute_text(object, multiparams, params) File "C:\Users\Александр\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\engine\base.py", line 1172, in _execute_text ret = self._execute_context( File "C:\Users\Александр\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\engine\base.py", line 1316, in _execute_context self.handle_dbapi_exception( File "C:\Users\Александр\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\engine\base.py", line 1510, in handle_dbapi_exception util.raise( File "C:\Users\Александр\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\util\compat.py", line 182, in raise raise exception File "C:\Users\Александр\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\engine\base.py", line 1276, in _execute_context self.dialect.do_execute( File "C:\Users\Александр\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\engine\default.py", line 609, in do_execute cursor.execute(statement, parameters) sqlalchemy.exc.InternalError: (psycopg2.errors.RaiseException) find_srid() - could not find the corresponding SRID - is the geometry registered in the GEOMETRY_COLUMNS table? Is there an uppercase/lowercase mismatch? CONTEXT: PL/pgSQL function find_srid(character varying,character varying,character varying) line 17 at RAISE

[SQL: SELECT Find_SRID('public', 'test_polygons', 'geometry');] (Background on this error at: http://sqlalche.me/e/13/2j85)

Answer 1

The way the function to_postgis works seems to look for the srid in your geometry, not the dtype argument (see _write_postgis here), so maybe it's the problem.

Does your input geometry have a correct srid? Did you created it for example like that:

# Create a GeoDataFrame specifying 'the_point' as the column with the 
# geometry data
crs = {'init': 'epsg:4326'}
geo_df = gpd.GeoDataFrame(df.copy(), crs=crs, geometry=the_point)

Maybe look here for a working example (I took the above code there)

Answer 2

I had this same problem, which was from a bug in geopandas where it only looks at the geometry_columns and not the geography_columns table for the SRID.

See: https://github.com/geopandas/geopandas/pull/2096#issuecomment-1814923034

The (very very hacky) way I fixed this in my upload script was to insert rows manually to get around the fix. You can also use 'replace' instead of 'append', but I wanted to preserve the table.

conn.exec_driver_sql(f"INSERT INTO public.geometry_columns (f_table_catalog,f_table_schema,f_table_name,f_geometry_column,coord_dimension,srid,type) "
+ f"VALUES ('postgres','{schema}','{table_name}','geometry',2,4326,'GEOMETRY');"
            )
gdf_reprojected.to_postgis(
                table_name,
                conn,
                schema=schema,
                if_exists="replace",
                index=False,
                dtype={"geometry": Geography("GEOMETRY", srid=4326)})
conn.exec_driver_sql(f"delete from public.geometry_columns where f_table_schema = '{schema}' and f_table_name = '{table_name}';")