How to use GeoAlchemy2 - 10 common examples

def __init__(self, data, srid=-1, extended=False):
        if extended and srid == -1:
            # read srid from the EWKB
            #
            # WKB struct {
            #    byte    byteOrder;
            #    uint32  wkbType;
            #    uint32  SRID;
            #    struct  geometry;
            # }
            # byteOrder enum {
            #     WKB_XDR = 0,  // Most Significant Byte First
            #     WKB_NDR = 1,  // Least Significant Byte First
            # }
            if isinstance(data, str_):
                # SpatiaLite case
                # assume that the string is an hex value
                header = binascii.unhexlify(data[:18])
            else:
                header = data[:9]
            if not PY3:
                header = bytearray(header)
            byte_order, srid = header[0], header[5:]
            srid = struct.unpack('I', srid)[0]
        _SpatialElement.__init__(self, data, srid, extended)

id=1,
                parent_id=1337,
                osm_id=88267051,
                normalized_street="citepreville19",
                geometry_center=WKTElement("POINT(653642.999259018 6623211.41263188)", srid=3857)
                )

            )

    session.add(
            tables.osm_linestring(
                id=2,
                osm_id=42,
                parent_id=1337,
                normalized_name="citepreville",
                geometry=WKTElement("""LINESTRING(653664.707113796
                                       6623110.56066385,653621.291404239
                                       6623312.26459991)""", srid=3857)
                )
            )

    session.commit()

    set_street_ids_by_street_name()

    assert session.query(tables.osm_housenumber).get(1).street_id == 42

class Lake(Base):
    __tablename__ = 'lake'
    __table_args__ = {'schema': 'gis'}
    id = Column(Integer, primary_key=True)
    geom = Column(Geometry(geometry_type='LINESTRING', srid=4326))

    def __init__(self, geom):
        self.geom = geom


class Poi(Base):
    __tablename__ = 'poi'
    __table_args__ = {'schema': 'gis'}
    id = Column(Integer, primary_key=True)
    geom = Column(Geometry(geometry_type='POINT', srid=4326))
    geog = Column(Geography(geometry_type='POINT', srid=4326))

    def __init__(self, geog):
        self.geog = geog


class Summit(Base):
    __tablename__ = 'summit'
    __table_args__ = {'schema': 'gis'}
    id = Column(Integer, primary_key=True)
    geom = Column(Geometry(
        geometry_type='POINT', srid=4326, management=True))

    def __init__(self, geom):
        self.geom = geom

def test_insert(self):
        conn = self.conn

        # Issue two inserts using DBAPI's executemany() method. This tests
        # the Geometry type's bind_processor and bind_expression functions.
        conn.execute(Lake.__table__.insert(), [
            {'geom': 'SRID=4326;LINESTRING(0 0,1 1)'},
            {'geom': WKTElement('LINESTRING(0 0,2 2)', srid=4326)},
            {'geom': from_shape(LineString([[0, 0], [3, 3]]), srid=4326)}
        ])

        results = conn.execute(Lake.__table__.select())
        rows = results.fetchall()

        row = rows[0]
        assert isinstance(row[1], WKBElement)
        wkt = session.execute(row[1].ST_AsText()).scalar()
        assert wkt == 'LINESTRING(0 0, 1 1)'
        srid = session.execute(row[1].ST_SRID()).scalar()
        assert srid == 4326

        row = rows[1]
        assert isinstance(row[1], WKBElement)
        wkt = session.execute(row[1].ST_AsText()).scalar()

osm_id=195916994,
                    housenumber=89,
                    parent_id=9999,
                    geometry=WKTElement("""POLYGON((835220.293672307 5939566.85419046,835223.11154127
                    5939588.79287532,835237.163563318 5939586.97376781,835234.345694356
                    5939565.06244245,835220.293672307 5939566.85419046))""", srid=3857)
                )
            )

    session.add(
            tables.osm_linestring(
                    id=2,
                    osm_id=25736914,
                    name="Dorfstrasse",
                    parent_id=9999,
                    geometry=WKTElement("""LINESTRING(835569.625447532 5939578.46636778,835353.853196615
                        5939540.30626715,835189.782309692 5939547.66472518,834976.622652515
                        5939499.47924756,834903.040815942 5939518.99686424,834783.579967145
                        5939588.61506781,834755.821158649 5939590.92656582)""", srid=3857)
                )
            )

    session.add(
            tables.osm_linestring(
                    id=3,
                    osm_id=26162329,
                    name="Zaelgli",
                    parent_id=9999,
                    geometry=WKTElement("""LINESTRING(835139.891099933 5939534.73955675,835080.034711193
                        5939629.44250458,835054.655229139 5939807.33473233)""", srid=3857)
                )
            )

name="Dorfstrasse",
                parent_id=1337,
                geometry=WKTElement("""LINESTRING(945262.014242162 5985606.22988835,
                    945125.963423109 5985669.20516832,944921.48130943 5985680.63151807,
                    944732.478813664 5985815.76883825,
                    944577.598658291 5985883.07702847)""", srid=3857)
            )
        )

    session.add(
            tables.osm_linestring(
                id=2,
                osm_id=2222,
                name="Dorfstrasse",
                parent_id=1337,
                geometry=WKTElement("""LINESTRING(944410.8312014 5985761.48265348,
                    944216.360920161 5985861.25509228)""", srid=3857)
            )
        )

    session.add(
            tables.osm_linestring(
                id=3,
                osm_id=3333,
                name="Dorfstrasse",
                parent_id=1337,
                geometry=WKTElement("""LINESTRING(944410.8312014 5985761.48265348,
                    944577.598658291 5985883.07702847)""", srid=3857)
            )
        )

    session.add(

def _create_one_lake(self):
        lake = Lake(WKTElement('LINESTRING(0 0,1 1)', srid=4326))
        session.add(lake)
        session.flush()
        return lake.id

def test_when_street_with_same_parent_id_but_almost_same_name_exists(session, tables):
    session.add(
            tables.osm_housenumber(
                id=1,
                parent_id=1337,
                osm_id=88267051,
                normalized_street="bochslenrasse",
                geometry_center=WKTElement("POINT(653642.999259018 6623211.41263188)", srid=3857)
                )

            )

    session.add(
            tables.osm_linestring(
                id=2,
                osm_id=42,
                parent_id=1337,
                normalized_name="bochslenstrasse",
                geometry=WKTElement("""LINESTRING(653664.707113796
                                       6623110.56066385,653621.291404239
                                       6623312.26459991)""", srid=3857)
                )
            )

def test_function_call(self):
        e = WKTElement('POINT(1 2)')
        f = e.ST_Buffer(2)
        eq_sql(f, 'ST_Buffer('
               'ST_GeomFromText(:ST_GeomFromText_1, :ST_GeomFromText_2), '
               ':ST_Buffer_1)')
        assert f.compile().params == {
            u'ST_Buffer_1': 2,
            u'ST_GeomFromText_1': 'POINT(1 2)',
            u'ST_GeomFromText_2': -1
        }

def test_func_no_wrapping():
    # Select query with function
    select_query = select([
        func.ST_Buffer(Point.geom),  # with wrapping (default behavior)
        func.ST_Buffer(Point.geom, type_=Geometry),  # with wrapping
        func.ST_Buffer(Point.geom, type_=RawGeometry)  # without wrapping
    ])

    # Check the query
    assert str(select_query) == (
        "SELECT "
        "ST_AsEWKB(ST_Buffer(point.geom)) AS \"ST_Buffer_1\", "