using a column's value in calculation in mysql query

Question

I have this code which should (hopefully) get the closest results to a given location:

$sql = "SELECT *,
    lat as LAT,
    long as LONG
FROM mellow_listings
ORDER BY SQRT(((".$long."-LONG)*(".$long."-LONG))
    +((".$lat."-LAT)*(".$lat."-LAT))) ASC
LIMIT 0,10";

for some reason it isn't working at all. I think its because I'm incorrectly using the column names in the calculation...

Any ideas?

Thank you

Answer 1

I bet you a Euro you're in North America, in which hemisphere longitudes are negative.

Try sticking your values for lat and long in parentheses.

Also, SQRT doesn't get you anything except extra computation here. You can order on the squared value just as easily, with the same result.

ORDER BY ((((".$long.")-LONG) * ((".$long.")-LONG))+
          (((".$lat. ")-LAT ) * ((".$lat. ")-LAT )))

This typed-in sample worked from my local US zipcode file very nicely.

SELECT * 
FROM ZIP 
ORDER BY ((latitude-(42.811261)) * (latitude-(42.811261)))+
         ((longitude-(-70.877264))*(longitude-(-70.877264)))

Also, your locations better not be near either pole or more than about 15 megameters apart, or this rectangular approximation you're using is going to fall apart pretty spectacularly. If they are that far apart you're going to need to use a great-circle distance computation formula.

Answer 2

It seems that mysql may be getting confused by the long AS LONG ... I am wondering if maybe mysql thinks it should be part of a Dim statement in a stored procedure or something. In any case, mysql would not allow me to alias a column as LONG... I tried changing it to LONGX, and it worked.

btw, I am using mysql 5.1.4 - not sure if this is sensitive to version