GeoPandas.GeoDataFrame: type "geometry" does not exist

Question

Ubuntu 20.04.3 LTS (WSL2)

fiona 1.8.21
geopandas 0.10.2
geopandas-base 0.10.2
geopy 2.2.0
numpy 1.22.3
pandas 1.4.1
psycopg2 2.9.3
pyproj 3.3.0
rtree 0.9.7
shapely 1.8.0

postgresql-14 14.2-1.pgdg20.04+1
postgresql-14-postgis-3 3.2.1+dfsg-1.pgdg20.04+1

trying to send a couple of polygons to postgresql database (postgis). Having no luck -.-' Always getting the same error message. Can someone help me?

Exception in thread Thread-2 (_create_searchwindows):
Traceback (most recent call last):
  File "/home/user/miniconda3/envs/geo/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1808, in _execute_context
    self.dialect.do_execute(
  File "/home/user/miniconda3/envs/geo/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.UndefinedObject: type "geometry" does not exist
LINE 4:  geometry geometry(POLYGON,25832)
                  ^


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

Traceback (most recent call last):
  File "/home/user/miniconda3/envs/geo/lib/python3.10/threading.py", line 1009, in _bootstrap_inner
    self.run()
  File "/home/user/miniconda3/envs/geo/lib/python3.10/threading.py", line 946, in run
    self._target(*self._args, **self._kwargs)
  File "/home/user/github/create_searchwindows/searchwindow_creator.py", line 143, in _create_searchwindows
    grid.to_postgis(self.args.searchwindows_tablename,self.engine,if_exists="append",index=True,index_label="Index")
  File "/home/user/miniconda3/envs/geo/lib/python3.10/site-packages/geopandas/geodataframe.py", line 1808, in to_postgis
    geopandas.io.sql._write_postgis(
  File "/home/user/miniconda3/envs/geo/lib/python3.10/site-packages/geopandas/io/sql.py", line 431, in _write_postgis
    gdf.to_sql(
  File "/home/user/miniconda3/envs/geo/lib/python3.10/site-packages/pandas/core/generic.py", line 2963, in to_sql
    return sql.to_sql(
  File "/home/user/miniconda3/envs/geo/lib/python3.10/site-packages/pandas/io/sql.py", line 697, in to_sql
    return pandas_sql.to_sql(
  File "/home/user/miniconda3/envs/geo/lib/python3.10/site-packages/pandas/io/sql.py", line 1729, in to_sql
    table = self.prep_table(
  File "/home/user/miniconda3/envs/geo/lib/python3.10/site-packages/pandas/io/sql.py", line 1628, in prep_table
    table.create()
  File "/home/user/miniconda3/envs/geo/lib/python3.10/site-packages/pandas/io/sql.py", line 842, in create
    self._execute_create()
  File "/home/user/miniconda3/envs/geo/lib/python3.10/site-packages/pandas/io/sql.py", line 828, in _execute_create
    self.table.create(bind=self.pd_sql.connectable)
  File "/home/user/miniconda3/envs/geo/lib/python3.10/site-packages/sqlalchemy/sql/schema.py", line 950, in create
    bind._run_ddl_visitor(ddl.SchemaGenerator, self, checkfirst=checkfirst)
  File "/home/user/miniconda3/envs/geo/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2119, in _run_ddl_visitor
    visitorcallable(self.dialect, self, **kwargs).traverse_single(element)
  File "/home/user/miniconda3/envs/geo/lib/python3.10/site-packages/sqlalchemy/sql/visitors.py", line 524, in traverse_single
    return meth(obj, **kw)
  File "/home/user/miniconda3/envs/geo/lib/python3.10/site-packages/sqlalchemy/sql/ddl.py", line 893, in visit_table
    self.connection.execute(
  File "/home/user/miniconda3/envs/geo/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1295, in execute
    return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
  File "/home/user/miniconda3/envs/geo/lib/python3.10/site-packages/sqlalchemy/sql/ddl.py", line 80, in _execute_on_connection
    return connection._execute_ddl(
  File "/home/user/miniconda3/envs/geo/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1387, in _execute_ddl
    ret = self._execute_context(
  File "/home/user/miniconda3/envs/geo/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1851, in _execute_context
    self._handle_dbapi_exception(
  File "/home/user/miniconda3/envs/geo/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2032, in _handle_dbapi_exception
    util.raise_(
  File "/home/user/miniconda3/envs/geo/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
  File "/home/user/miniconda3/envs/geo/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1808, in _execute_context
    self.dialect.do_execute(
  File "/home/user/miniconda3/envs/geo/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedObject) type "geometry" does not exist
LINE 4:  geometry geometry(POLYGON,25832)
                  ^

[SQL:
CREATE TABLE public.sw32 (
        "Index" BIGINT,
        geometry geometry(POLYGON,25832)
)

]
(Background on this error at: https://sqlalche.me/e/14/f405)

I made sure, that the postgis extension is activated in the database. If I create the table with a geometry column in the database using psql its working perfectly fine. Something seems to be not correct with the python packages installed. Did someone came up with the same trouble?

code looks like this:

import geopandas as gpd
from sqlalchemy import create_engine
engine = create_engine(f'postgresql://{db_user}:{db_password}@{db_host}:{db_port}/')
grid = gpd.GeoDataFrame(polygons,geometry="geometry",crs="EPSG:25832")
grid.to_postgis("testtable",engine,if_exists="append",index=True,index_label="Index") 

EDIT 1:

My current workaround is to store the polygons in a *.shp file and execute shp2psql to send the polygons to the database. The code looks as follows:

grid.to_file("tmp_name.shp")
cmd = f"shp2pgsql -s 25832 tmp_name.shp public.testtable |psql postgresql://{db_user}:{db_password}@{db_host}:{db_port}/{database}"
os.system(cmd)

Answer 1

Indeed my connection string was not correct. forgot to mention the database. with the adjustment of the connection string as follows:

engine = create_engine(f'postgresql://{db_user}:{db_password}@{db_host}:{db_port}/{database}')

everything works fine!

Answer 2

Probably it is related to your "geoalchemy2" version. Your geoalchemy2 version should be "0.9.2".