1

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)

2 Answers 2

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)

1
  • Yes, the problem was that srid was not 4326. so, when i changed it to 4326, .to_postgis() method worked fine. Thank you. Jan 29, 2021 at 7:06
0

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}';")

Your Answer

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

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