Haversine's formula in sqlalchemy

Question

I'm having the following lines in my code

    query = "SELECT id, " \
        "( 3959 * acos( cos( radians(37) ) * cos( radians( %(lat)i ) ) * " \
        "cos( radians( %(lng)i ) - radians(-122) ) + sin( radians(37) ) * " \
        "sin( radians( %(lat)i ) ) ) ) AS `distance` from message where" \
        " `distance` <= %(drange)d" % {'lat': float(lat), 'lng': float(lng), 'drange': int(drange)}
    print query
    messages = db.session.query(Message).from_statement(query).all()

I get the following error when I use this

OperationalError: (OperationalError) (1054, "Unknown column 'distance' in 'where clause'") 'SELECT id, ( 3959 * acos( cos( radians(37) ) * cos( radians( 0 ) ) * cos( radians( 0 ) - radians(-122) ) + sin( radians(37) ) * sin( radians( 0 ) ) ) ) AS `distance` from message where `distance` <= 50' ()

What is the correct way to fix this?

Answer 1

You cannot reference the named expression (distance) in the WHERE clause (I don't know if this is generally true for all database systems, but it is at least so for MySQL). You can use HAVING instead (see option C).

Options:

A. Repeat the expression again in the where clause:

SELECT id, (long_formula) as distance FROM message WHERE (long_formula) <= ...

B. Use a nested query:

SELECT * FROM 
(SELECT id, (long_formula) AS distance FROM message) inner_query 
WHERE distance <= ...

C. Use the HAVING clause (I have used SQL for years but was not aware of HAVING until I read this):

SELECT id, (long_formula) as distance FROM message HAVING distance <= ...