geopandas: BUG: Spatial index error using to_postgis with GeoAlchemy2 0.11

I have checked that this issue has not already been reported.
I have confirmed this bug exists on the latest version of geopandas.
(optional) I have confirmed this bug exists on the main branch of geopandas.

Code Sample, a copy-pastable example

import geopandas
from sqlalchemy import create_engine

db_connection_url = "postgresql://postgres:postgres@localhost:5432/foo"
engine = create_engine(db_connection_url) 
gdf = geopandas.read_file(geopandas.datasets.get_path('naturalearth_lowres'))
gdf.to_postgis('naturalearth_lowres', engine, if_exists='replace')

Traceback

Traceback (most recent call last):
  File ".../lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1808, in _execute_context
    self.dialect.do_execute(
  File ".../lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.DuplicateTable: relation "idx_naturalearth_lowres_geometry" already exists


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

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File ".../lib/python3.10/site-packages/geopandas/geodataframe.py", line 1808, in to_postgis
    geopandas.io.sql._write_postgis(
  File ".../lib/python3.10/site-packages/geopandas/io/sql.py", line 431, in _write_postgis
    gdf.to_sql(
  File ".../lib/python3.10/site-packages/pandas/core/generic.py", line 2963, in to_sql
    return sql.to_sql(
  File ".../lib/python3.10/site-packages/pandas/io/sql.py", line 697, in to_sql
    return pandas_sql.to_sql(
  File ".../lib/python3.10/site-packages/pandas/io/sql.py", line 1729, in to_sql
    table = self.prep_table(
  File ".../lib/python3.10/site-packages/pandas/io/sql.py", line 1628, in prep_table
    table.create()
  File ".../lib/python3.10/site-packages/pandas/io/sql.py", line 842, in create
    self._execute_create()
  File ".../lib/python3.10/site-packages/pandas/io/sql.py", line 828, in _execute_create
    self.table.create(bind=self.pd_sql.connectable)
  File ".../lib/python3.10/site-packages/sqlalchemy/sql/schema.py", line 950, in create
    bind._run_ddl_visitor(ddl.SchemaGenerator, self, checkfirst=checkfirst)
  File ".../lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2119, in _run_ddl_visitor
    visitorcallable(self.dialect, self, **kwargs).traverse_single(element)
  File ".../lib/python3.10/site-packages/sqlalchemy/sql/visitors.py", line 524, in traverse_single
    return meth(obj, **kw)
  File ".../lib/python3.10/site-packages/sqlalchemy/sql/ddl.py", line 905, in visit_table
    self.traverse_single(index, create_ok=True)
  File ".../lib/python3.10/site-packages/sqlalchemy/sql/visitors.py", line 524, in traverse_single
    return meth(obj, **kw)
  File ".../lib/python3.10/site-packages/sqlalchemy/sql/ddl.py", line 936, in visit_index
    self.connection.execute(CreateIndex(index))
  File ".../lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1295, in execute
    return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
  File ".../lib/python3.10/site-packages/sqlalchemy/sql/ddl.py", line 80, in _execute_on_connection
    return connection._execute_ddl(
  File ".../lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1387, in _execute_ddl
    ret = self._execute_context(
  File ".../lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1851, in _execute_context
    self._handle_dbapi_exception(
  File ".../lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2032, in _handle_dbapi_exception
    util.raise_(
  File ".../lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
  File ".../lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1808, in _execute_context
    self.dialect.do_execute(
  File ".../lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.DuplicateTable) relation "idx_naturalearth_lowres_geometry" already exists

[SQL: CREATE INDEX idx_naturalearth_lowres_geometry ON public.naturalearth_lowres USING gist (geometry)]
(Background on this error at: https://sqlalche.me/e/14/f405)

Problem description

The above code works with GeoAlchemy2 0.10.2, but it seems to_postgis always fails with a duplicate spatial index error with GeoAlchemy2 0.11.

This may be related to https://github.com/geoalchemy/geoalchemy2/pull/344, added in 0.11.0, which changed how GeoAlchemy2 creates spatial indexes. I don’t know if this exception I’m seeing is a bug in GeoAlchemy2 or a breaking change which GeoPandas needs to account for.

Expected Output

The to_postgis method should successfully create a table on the PostGIS server without throwing any exceptions.

Output of `geopandas.show_versions()`

SYSTEM INFO

python : 3.10.1 (v3.10.1:2cd268a3a9, Dec 6 2021, 14:28:59) [Clang 13.0.0 (clang-1300.0.29.3)] executable : …/bin/python machine : macOS-12.2-x86_64-i386-64bit

GEOS, GDAL, PROJ INFO

GEOS : 3.10.2 GEOS lib : /usr/local/Cellar/geos/3.10.2/lib/libgeos_c.dylib GDAL : 3.4.1 GDAL data dir: …/lib/python3.10/site-packages/fiona/gdal_data PROJ : 8.2.0 PROJ data dir: …/lib/python3.10/site-packages/pyproj/proj_dir/share/proj

PYTHON DEPENDENCIES

geopandas : 0.10.2 pandas : 1.4.1 fiona : 1.8.21 numpy : 1.22.3 shapely : 1.8.1.post1 rtree : None pyproj : 3.3.0 matplotlib : 3.5.1 mapclassify: None geopy : None psycopg2 : 2.9.3 (dt dec pq3 ext lo64) geoalchemy2: 0.10.2 pyarrow : None pygeos : None

About this issue

Original URL
State: open
Created 2 years ago
Reactions: 3
Comments: 21 (5 by maintainers)

Most upvoted comments

This is a confirmed issue on geoalchemy2 side. For the time being, I recommend pinning your environments to <0.11.

martinfleis on Mar 23, 2022

When I run my code locally, to_postgis works like a charm (to the same server). When I run remote (k8s pod/Docker container) I get this error too.

geoalchemy in requirements: geoalchemy2

I will pinpoint a newer version now and see if it resolves. local version: 0.9.4 I just updated local to 0.11.1 and now it also crashes on to_postgis . When I downgrade to 0.10.2 it works again locally.

thijscobben on Mar 22, 2022

Thanks for the resonse. Ive tried to reproduce it myself today, but somehow it did work now :S. What I do know for sure is that the error did occur, the version was 0.14.2 and the table did not exist yet. If it did exist, there should also be no error, because of the “append” flag. This leaves me to think that there was probably something weird going on on our side although I do not know what. Might just be a false alarm. Sorry about that!

Ok, no problem 😃 So for now let’s consider it was a false alarm. See you next time it happens again! 😄

adrien-berchet on Nov 6, 2023

Ok, that’s a good first step then 😃 For your error, I realize that the doc is wrong… The include_object was removed and is not needed anymore. I created a PR to fix the doc: https://github.com/geoalchemy/geoalchemy2/pull/386. You can already check there the (hopefully) correct snippets for Alembic.

EDIT: The PR was merged and the doc was rebuild. Now the links I gave you should be correct.

adrien-berchet on Jun 17, 2022

Hello there, FYI, I released this morning GeoAlchemy2==0.12.0 which should solve this issue.

adrien-berchet on Jun 7, 2022