[How to use] I'm not able to use ST_* functions during insertion #508
Comments
rmmariano
changed the title
|
Hi @rmmariano |
|
Hi @adrien-berchet |
|
Hi @rmmariano 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) |
Describe the problem
I have a table/schema Geofence with two geometry fields,
geom(normal polygon) andbbox(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_EnvelopeandST_Bufferfunctions 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.
Describe what you expected.
I expect to be able to use ST_* function when I insert objects into the database
Error
Additional context
No response
GeoAlchemy 2 Version in Use
GeoAlchemy2==0.14.6
The text was updated successfully, but these errors were encountered: