Geoalchemy2 query all users within X meteres

[Luke Crooks]

I have an app that takes an address string, sends it to Google Maps API and gets lat/long co-ordinates, I then want to show the all users within X meteres of this point (there lat/long is stored in my database), I then want to filter the result to only show users with certain pets

So first off, I have my Models

class User(UserMixin, Base):
    first_name = Column(Unicode)

    address = Column(Unicode)
    location = Column(Geometry('POINT'))

    pets = relationship('Pet', secondary=user_pets, backref='pets') 

class Pet(Base):
    __tablename__ = 'pets'   
    id = Column(Integer, primary_key=True)
    name = Column(Unicode)

user_pets = Table('user_pets', Base.metadata,
    Column('user_id', Integer, ForeignKey('users.id')),
    Column('pet_id', Integer, ForeignKey('pets.id'))
)

I get my lat/long from Google API and store it in my database, so from the address string "London England" I get

POINT (-0.1198244000000000 51.5112138999999871)

this stores in my database like:

0101000000544843D7CFACBEBF5AE102756FC14940

Now that all works fine, now reading the Geoalchemy2 docs I cant seem to find an exmaple query to resolve my problem.

What I want to pass is another set of lat/long co-ordinates to Geoalchemy2 and then return the nearest say 10 users. Whilst querying this I will also filter only users that have certain pets (this isn't essential for my query to work, but I wanted to show what the query will actually do in its entirety).

I don't really like to answer a question without providing a sample query, but I really don't know what functions I should be using to achieve my required result.

I am guessing I will need to use "ST_DWithin" or "ST_DFullyWithin" but I cannot find a full example of either function. Thank's.

Copied from my ask on StackOverflow

[Eric Lemoine]

Something like this maybe:

users = session.query(User).filter(
func.ST_DWithin(
WKTElement('POINT(-0.1198244000000000 51.5112138999999871)', srid=4326),
User.location,
3)).all()


As indicated in the PostGIS documentation the distance is specified in
units defined by the spatial reference system (degrees if the srid is
4326).

For the join you may want to look at the SQLAlchemy doc:
<http://docs.sqlalchemy.org/en/rel_0_9/orm/tutorial.html#using-exists>.

Hope it helps,

--
Eric Lemoine

Camptocamp France SAS
Savoie Technolac, BP 352
73377 Le Bourget du Lac, Cedex

Tel : 00 33 4 79 44 44 94
Mail : eric.l...@camptocamp.com
http://www.camptocamp.com