How to get lng lat value from query results of geoalchemy2

Question

For exammple,

class Lake(Base):
     __tablename__ = 'lake'
     id = Column(Integer, primary_key=True)
     name = Column(String)
     geom = Column(Geometry('POLYGON'))
     point = Column(Geometry('Point'))


lake = Lake(name='Orta', geom='POLYGON((3 0,6 0,6 3,3 3,3 0))', point="POINT(2 9)")
query = session.query(Lake).filter(Lake.geom.ST_Contains('POINT(4 1)'))
for lake in query:
     print lake.point

it returned <WKBElement at 0x2720ed0; '010100000000000000000000400000000000002240'>

I also tried to do lake.point.ST_X() but it didn't give the expected latitude neither

What is the correct way to transform the value from WKBElement to readable and useful format, say (lng, lat)?

Thanks

not an answer, but `ST_X()` is for longitude—not latitude, which is from `ST_Y()` — Mike T, Jun 09 '14 at 11:18

score 13 · Answer 1 · edited Jun 05 '15 at 14:06

13

You can parse WKB (well-known binary) points, and even other geometry shapes, using shapely.

from shapely import wkb
for lake in query:
    point = wkb.loads(bytes(lake.point.data))
    print point.x, point.y

edited Jun 05 '15 at 14:06

Wichert Akkerman

4,918
2
23
30

answered May 13 '15 at 00:39

Enrico Tiongson

400
4
5

score 5 · Answer 2 · answered Jun 07 '14 at 09:20

5

http://geoalchemy-2.readthedocs.org/en/0.2.4/spatial_functions.html#geoalchemy2.functions.ST_AsText is what you are looking for. This will return 'POINT (lng, lat)'. ST_X ought to work, though, so you may have another issue if it isn't returning the correct value.

answered Jun 07 '14 at 09:20

John Powell

12,253
6
59
67

Do you mean lake.point.ST_AsText()? It returned , but not a value. thanks – Chung Jun 07 '14 at 11:01
2

Just tried, it returned the same thing. I found session.scalar(lake.point.ST_AsText()) will return the expect result, however, it asked DB for the conversion, is it the expected behaviour? – Chung Jun 07 '14 at 11:41
Sorry, what do you mean by it asked DB for the conversion? – John Powell Jun 07 '14 at 11:58
ST_AsText is a Postgis function, so if that is what you mean by your question, then, yes, it is expected behaviour. – John Powell Jun 07 '14 at 13:07

Atul Kumar · Answer 3 · 2021-06-10T16:59:30.983

Extending John's answer, you can use ST_AsText() while querying like this -

import sqlalchemy as db
from geoalchemy2 import Geometry
from geoalchemy2.functions import ST_AsText

# connection, table, and stuff here...

query = db.select(
    [
        mytable.columns.id,
        mytable.columns.name,
        ST_AsText(mytable.columns.geolocation),
    ]
)

Find more details on using functions here - https://geoalchemy-2.readthedocs.io/en/0.2.6/spatial_functions.html#module-geoalchemy2.functions

score 0 · Answer 4 · answered Jul 08 '23 at 06:53

0

Using shapely.

from shapely import wkb
for lake in query:
    point = wkb.loads(lake.point.data.tobytes())

    latitude = point.y
    longitude = point.x

Source https://stackoverflow.com/a/30203761/5806017

answered Jul 08 '23 at 06:53

Garvit Jain

1,862
2
19
27

How to get lng lat value from query results of geoalchemy2

4 Answers4

Linked