[How to use] I'm not able to use ST_* functions during insertion

rmmariano commented 6 months ago

Describe the problem

I have a table/schema Geofence with two geometry fields, geom (normal polygon) and bbox (ST_Envelope + ST_Buffer from the polygon). I need to insert a list of "geofences" into the database, for example using bulk_save_objects (or other way). When I insert the record I need to use the ST_Envelope and ST_Buffer functions to calc the bbox and the buffer, however I receive an error: psycopg2.ProgrammingError: can't adapt type 'ST_Envelope' I've already read the documentation and the example related to insert, but I haven't been able to update my code to fix the problem. I send below an example to illustrate what I get. Thank you.

Show what you tried to do.

# you need to create a postgis database called `geofences_test` for this test

from geoalchemy2 import Geometry, WKBElement
from sqlalchemy import Integer, create_engine
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, sessionmaker
from sqlalchemy.sql import func

engine = create_engine(url="postgresql+psycopg2://postgres:postgres@localhost:15432/geofences_test")

class Base(DeclarativeBase):
    ...

class Geofence(Base):
    __tablename__ = "geofence"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, index=True)
    geom: Mapped[WKBElement] = mapped_column(
        Geometry(geometry_type="POLYGON", srid=4326), nullable=False
    )
    bbox: Mapped[WKBElement] = mapped_column(
        Geometry(geometry_type="POLYGON", srid=4326), nullable=False
    )

Base.metadata.create_all(engine)

# 

geom = Geometry(geometry_type='POLYGON ((-46.6841 -23.5913, -46.6841 -23.5915, -46.6836 -23.5912, -46.6841 -23.5913))')

geofences = [
    Geofence(
        id=1,
        geom=geom.geometry_type,
        bbox=func.ST_Envelope(geom.geometry_type)
    ),
    Geofence(
        id=2,
        geom=geom.geometry_type,
        bbox=func.ST_Envelope(func.ST_Buffer(geom.geometry_type, 20))
    )
]

#

Session = sessionmaker(bind=engine, expire_on_commit=False)

with Session() as session:
    try:
        session.bulk_save_objects(geofences)
    except Exception:
        session.rollback()
        raise
    else:
        session.commit()

Describe what you expected.

I expect to be able to use ST_* function when I insert objects into the database

Error

Traceback (most recent call last):
  File "/home/user/.pyenv/versions/test/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2119, in _exec_insertmany_context
    dialect.do_execute(
  File "/home/user/.pyenv/versions/test/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 924, in do_execute
    cursor.execute(statement, parameters)
psycopg2.ProgrammingError: can't adapt type 'ST_Envelope'

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

Traceback (most recent call last):
  File "/home/user/data/projects/testes/geofence_example.py", line 48, in <module>
    session.bulk_save_objects(geofences)
  File "/home/user/.pyenv/versions/test/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 4529, in bulk_save_objects
    self._bulk_save_mappings(
  File "/home/user/.pyenv/versions/test/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 4700, in _bulk_save_mappings
    transaction.rollback(_capture_exception=True)
  File "/home/user/.pyenv/versions/test/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py", line 146, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "/home/user/.pyenv/versions/test/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 4688, in _bulk_save_mappings
    bulk_persistence._bulk_insert(
  File "/home/user/.pyenv/versions/test/lib/python3.9/site-packages/sqlalchemy/orm/bulk_persistence.py", line 197, in _bulk_insert
    result = persistence._emit_insert_statements(
  File "/home/user/.pyenv/versions/test/lib/python3.9/site-packages/sqlalchemy/orm/persistence.py", line 1048, in _emit_insert_statements
    result = connection.execute(
  File "/home/user/.pyenv/versions/test/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1421, in execute
    return meth(
  File "/home/user/.pyenv/versions/test/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", line 514, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/home/user/.pyenv/versions/test/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1643, in _execute_clauseelement
    ret = self._execute_context(
  File "/home/user/.pyenv/versions/test/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1847, in _execute_context
    return self._exec_insertmany_context(dialect, context)
  File "/home/user/.pyenv/versions/test/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2127, in _exec_insertmany_context
    self._handle_dbapi_exception(
  File "/home/user/.pyenv/versions/test/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2356, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/home/user/.pyenv/versions/test/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2119, in _exec_insertmany_context
    dialect.do_execute(
  File "/home/user/.pyenv/versions/test/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 924, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) can't adapt type 'ST_Envelope'
[SQL: INSERT INTO geofence (id, geom, bbox) VALUES (%(id__0)s, ST_GeomFromEWKT(%(geom__0)s), ST_GeomFromEWKT(%(bbox__0)s)), (%(id__1)s, ST_GeomFromEWKT(%(geom__1)s), ST_GeomFromEWKT(%(bbox__1)s))]
[parameters: {'id__0': 1, 'geom__0': 'POLYGON ((-46.6841 -23.5913, -46.6841 -23.5915, -46.6836 -23.5912, -46.6841 -23.5913))', 'bbox__0': <geoalchemy2.functions.ST_Envelope at 0x7f0fb269ce20; ST_Envelope>, 'id__1': 2, 'geom__1': 'POLYGON ((-46.6841 -23.5913, -46.6841 -23.5915, -46.6836 -23.5912, -46.6841 -23.5913))', 'bbox__1': <geoalchemy2.functions.ST_Envelope at 0x7f0fb26c6d00; ST_Envelope>}]
(Background on this error at: https://sqlalche.me/e/20/f405)

Additional context

No response

GeoAlchemy 2 Version in Use

GeoAlchemy2==0.14.6

adrien-berchet commented 6 months ago

Hi @rmmariano I just took a quick look but as far I can see you mixed core and ORM queries. If you want to use core queries, you can look at https://geoalchemy-2.readthedocs.io/en/latest/gallery/test_length_at_insert.html#sphx-glr-gallery-test-length-at-insert-py, while if you want to use ORM queries you can look at https://geoalchemy-2.readthedocs.io/en/latest/gallery/test_type_decorator.html#sphx-glr-gallery-test-type-decorator-py. Basically, with core queries you should use func.ST_* and bindparam in the insert queries while with ORM queries you should create a specific type that will insert the proper func.ST_* automatically at insert.

rmmariano commented 6 months ago

Hi @adrien-berchet Thank you for you awnswer. I've read the example, but it's not clear to me how I could update my example to use ORM. I'm newbie on geoalchemy. Could you please send my an example based on the one that I made? Using ORM, when you have free time. Thanks a lot.

adrien-berchet commented 6 months ago

Hi @rmmariano Here is an example of what I spoke about. As I said, you have to create a specific type for your bbox column that automatically use ST_Envelope at insert. I also added an example with core inserts in the end. I hope it will solve your issue.

from geoalchemy2 import Geometry, WKBElement, WKTElement
from sqlalchemy import Integer, create_engine, bindparam, select
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, sessionmaker
from sqlalchemy.sql import func
from sqlalchemy.types import TypeDecorator

db_connection_url = "postgresql://gis:gis@localhost/gis"
engine = create_engine(url=db_connection_url, echo=True)

class BBoxGeometry(TypeDecorator):
    """This class is used to insert a ST_Force3D() in each insert."""

    impl = Geometry

    cache_ok = True

    def bind_expression(self, bindvalue):
        return func.ST_Envelope(
            self.impl.bind_expression(bindvalue),
            type=self,
        )

class Base(DeclarativeBase):
    ...

class Geofence(Base):
    __tablename__ = "geofence"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, index=True)
    geom: Mapped[WKBElement] = mapped_column(
        Geometry(geometry_type="POLYGON", srid=4326), nullable=False
    )
    bbox: Mapped[WKBElement] = mapped_column(
        BBoxGeometry(geometry_type="POLYGON", srid=4326), nullable=False
    )

Base.metadata.drop_all(engine, checkfirst=True)
Base.metadata.create_all(engine)

# 

raw_geom = 'POLYGON ((0 0, 1 1, 2 0, 1 -1, 0 0))'
geom = WKTElement(raw_geom)

geofences = [
    Geofence(
        id=1,
        geom=geom,
        bbox=geom,
    ),
    Geofence(
        id=2,
        geom=geom,
        bbox=geom,
    )
]

Session = sessionmaker(bind=engine, expire_on_commit=False)

with Session() as session:
    # With ORM
    try:
        session.bulk_save_objects(geofences)
    except Exception:
        session.rollback()
        raise
    else:
        session.commit()

    # With core
    conn = session.connection()
    i = Geofence.__table__.insert()
    i = i.values(id=bindparam("id"), geom=bindparam("geom"), bbox=func.ST_Envelope(func.ST_GeomFromEWKT(bindparam("geom"))))
    conn.execute(i, [{"id": j.id + 10, "geom": j.geom, "bbpx": j.bbox} for j in geofences])

    # Check the inserted results
    res = conn.execute(select(Geofence.__table__.c.id, Geofence.__table__.c.geom.ST_AsText(), Geofence.__table__.c.bbox.ST_AsText())).all()
    for j in res:
        print(j)

adrien-berchet commented 4 months ago

Hi @rmmariano Were you able to solve your problem? Can I close this issue?

adrien-berchet commented 1 week ago

Closing this issue due to a long time without any answer.

geoalchemy / geoalchemy2