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?

Sorry if i am missing somethig but how calculated value is connected to message db table (i mean all values for calculation you are passing outside as well as expected value for calculation? — Artsiom Rudzenka, Jun 09 '11 at 21:20

score 5 · Accepted Answer · edited May 23 '17 at 11:58

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 <= ...

Artsiom Rudzenka · Answer 2 · 2011-06-09T21:51:20.340

Not related to your question but you can increase readability of your query if start using """ For example your query could be modified the following way:

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)}

Also just for me is this correctly to use " AS 'distance'" instead of " AS distance" as alias i mean is it allowed to use " ' "?

Haversine's formula in sqlalchemy

2 Answers2