[SOLVED] GeoAlchemy2 store point and query results

GeoAlchemy2 store point and query results

The documentation on GeoAlchemy2 doesn't seem fully featured (as compared to the pervious version).

I have a model:

class AddressCode(Base):
    __tablename__ = 'address_codes'
    id = Column(Integer, primary_key=True)
    code = Column(Unicode(34))
    geometry = Column(Geometry('POINT'))

And I want to store lat/long data, which I tried to save in the above model, example

"51.42553,-0.666085"

Which gives me the error:

"Parse error at position 9 within Geometry (the "," char")

Anyone able to shed some light on where I am going wrong here?

Also on the subject, how would I peform a query to say..

Show nearest 20 users:

class AddressCode(Base):
    __tablename__ = 'address_codes'
    id = Column(Integer, primary_key=True)
    name = Column(Unicode(34))
    geometry = Column(Geometry('POINT'))

Something like?

geom_var = "51.42553,-0.666085"
Session.query(User).filter(func.ST_DWithin, 20, geom_var).all()

Solution

In both GeoAlchemy and GeoAlchemy2 you need to specify the geometries in the well-known text format called WKT or Well-known text, or the Well-known binary format. For a point the syntax is 'POINT(X Y)', thus 'POINT(-0.666085 51.42553)' notice that the longitude comes first, then latitude.

The shapely module contains useful functions for handling geometries outside relational databases, along with easy conversions between Python geometry classes and WKT, WKB formats.