Consider the following SQLAalchemy
/ GeoAlchemy2
ORM with a geometry field:
from geoalchemy2 import Geometry, WKTElement
class Item(Base):
__tablename__ = 'item'
id = Column(Integer, primary_key=True)
...
geom = Column(Geometry(geometry_type='POINTZ', srid=4326))
When I update an item in the PostgreSQL shell:
UPDATE item SET geom = st_geomFromText('POINT(2 3 0)', 4326) WHERE id = 5;
Fetching the field:
items = session.query(Item).\
filter(Item.id == 3)
for item in items:
print item.geom
Gives:
01e9030000000000000000004000000000000008400000000000000000
This isn't a proper WKB - at least, it does not parse with Shapely's loads
.
How do I get the lat
/lon
of the geom
field?
Fetching the lat
, lon
via ST_X and ST_Y might not be the most elegant approach, but it works:
from sqlalchemy import func
items = session.query(
Item,
func.st_y(Item.geom),
func.st_x(Item.geom)
).filter(Item.id == 3)
for item in items:
print(item.geom)
Gives:
(<Item 3>, 3.0, 2.0)