How to Use Mathematic Equations as Filters in SQLAlchemy

Question

I'm using the SQLAlchemy ORM to construct the MySQL queries in my application, and am perfectly able to add basic filters to the query, like so:

query = meta.Session.query(User).filter(User.user_id==1)

Which gives me something basically equivalent to this:

SELECT * FROM users WHERE user_id = 1

My question is how I would integrate some basic MySQL math functions into my query. So, say for instance I wanted to get users near a certain latitude and longitude. So I need to generate this SQL ($mylatitude and $mylongitude are the static latitude and longitude I'm comparing against):

SELECT * FROM users 
WHERE SQRT(POW(69.1 * (latitude - $mylatitude),2) + POW(53.0 * (longitude - $mylongitude),2)) < 5

Is there a way I can incorporate these functions into a query using the SQLAlchemy ORM?

Answer 1

You can use literal SQL in your filter, see here: http://www.sqlalchemy.org/docs/05/ormtutorial.html?highlight=text#using-literal-sql

For example:

clause = "SQRT(POW(69.1 * (latitude - :lat),2) + POW(53.0 * (longitude - :long),2)) < 5"
query = meta.Session.query(User).filter(clause).params(lat=my_latitude, long=my_longitude)

Answer 2

I'd use the query builder interface and the func SQL function constructor to abstract the calculation as a function. This way you can use it freely with aliases or joins.

User.coords = classmethod(lambda s: (s.latitude, s.longitude))

def calc_distance(latlong1, latlong2):
    return func.sqrt(func.pow(69.1 * (latlong1[0] - latlong2[0]),2)
                   + func.pow(53.0 * (latlong1[1] - latlong2[1]),2))

meta.Session.query(User).filter(calc_distance(User.coords(), (my_lat, my_long)) < 5)