Disable wrapping in selectΒΆ

If the application wants to build queries with GeoAlchemy 2 and gets them as strings, the wrapping of geometry columns with a ST_AsEWKB() function might be annoying. In this case it is possible to disable this wrapping. This example uses SQLAlchemy ORM queries.

10 from sqlalchemy import Column
11 from sqlalchemy import Integer
12 from sqlalchemy import func
13 from sqlalchemy import select
14 from sqlalchemy.ext.declarative import declarative_base
15
16 from geoalchemy2 import Geometry
17
18
19 Base = declarative_base()
20
21
22 class RawGeometry(Geometry):
23     """This class is used to remove the 'ST_AsEWKB()'' function from select queries"""
24
25     def column_expression(self, col):
26         return col
27
28
29 class Point(Base):
30     __tablename__ = "point"
31     id = Column(Integer, primary_key=True)
32     geom = Column(Geometry(srid=4326, geometry_type="POINT"))
33     raw_geom = Column(
34         RawGeometry(srid=4326, geometry_type="POINT"))
35
36
37 def test_no_wrapping():
38     # Select all columns
39     select_query = select([Point])
40
41     # Check that the 'geom' column is wrapped by 'ST_AsEWKB()' and that the column
42     # 'raw_geom' is not.
43     assert str(select_query) == (
44         "SELECT point.id, ST_AsEWKB(point.geom) AS geom, point.raw_geom \n"
45         "FROM point"
46     )
47
48
49 def test_func_no_wrapping():
50     # Select query with function
51     select_query = select([
52         func.ST_Buffer(Point.geom),  # with wrapping (default behavior)
53         func.ST_Buffer(Point.geom, type_=Geometry),  # with wrapping
54         func.ST_Buffer(Point.geom, type_=RawGeometry)  # without wrapping
55     ])
56
57     # Check the query
58     assert str(select_query) == (
59         "SELECT "
60         "ST_AsEWKB(ST_Buffer(point.geom)) AS \"ST_Buffer_1\", "
61         "ST_AsEWKB(ST_Buffer(point.geom)) AS \"ST_Buffer_2\", "
62         "ST_Buffer(point.geom) AS \"ST_Buffer_3\" \n"
63         "FROM point"
64     )

Total running time of the script: ( 0 minutes 0.000 seconds)

Gallery generated by Sphinx-Gallery