pythonsqlalchemyflask-sqlalchemypostgisgeoalchemy2

GeoAlchemy ST_DWithin implementation


I need to implement a query using GeoAlchemy to get all the Points that are near a given Point (for example, within a 10 meter radius). For storing points, i am using Geography fields in my PostGIS database. From the SQLAlchemy documentation I have figured out that i would need to use the ST_DWithin function, but i'm not exactly sure how to implement this function in my Flask application.

Here are the relevant code snippets:

# models.py
class Post(db.Model):
    __tablename__ = 'posts'
    id = db.Column(db.Integer, primary_key=True)
    location = db.Column(Geography(geometry_type='POINT', srid=4326))

#routes.py
from models import Post

@app.route('/get_coordinates')
    lat = request.args.get('lat', None)
    lng = request.args.get('lng', None)

    if lat and lng:
        point = WKTElement('POINT({0} {1})'.format(lng, lat), srid=4326)
        # Here i should make the query to get all Posts that are near the Point 

Solution

  • After a bit of research, I was able to implement the following query successfully :)

    posts = db.session.query(Post).filter(func.ST_DWithin(Post.location, point, 10))