0

I am trying to calculate and return the distances between some points.

Here is the complete MYSQL statement that I am using. I have tried multiple versions of this formula, and they all have similar results: returning zero result. Any thoughts on what I am doing wrong?

SET @origLat = 37.386337;
SET @origLon = -122.085823;
SET @dist = 20;

SELECT user_id, user_lat, user_lon, 3956 * 2 * 
          ASIN(SQRT( POWER(SIN((@origLat - abs(user_lat))*pi()/180/2),2)
          +COS(@origLat*pi()/180 )*COS(abs(user_lat)*pi()/180)
          *POWER(SIN((@origLon-(user_lon))*pi()/180/2),2))) 
          AS distance 
FROM myTable
WHERE user_lon BETWEEN (@origLon-@dist/abs(cos(radians(@origLat))*69))  AND (@origLon+@dist/abs(cos(radians(@origLat))*69))  AND 
      user_lat BETWEEN (@origLat-(@dist/69))  AND (@origLat+(@dist/69)) 
      HAVING distance < @dist ORDER BY distance LIMIT 100

Here is what is in my table.

enter image description here

Running just my SELECT clause shows that Distance is return nil.

enter image description here

user-44651
  • 3,924
  • 6
  • 41
  • 87
  • Have you verified if the distance formula is correct? You can try copying your data to a spreadsheet or some other utility and try building up the formula gradually to see which part is problematic. – Kalyan Vedala Sep 29 '15 at 02:18
  • works for me with non-null distances see [sqlfiddle](http://sqlfiddle.com/#!9/62d72/1). perhaps you didnt execute the set variables before running the select ? – amdixon Sep 29 '15 at 05:31
  • Yeah it's driving me crazy. If i plug in actual values for the variables, it still doesn't execute. – user-44651 Sep 29 '15 at 12:16

0 Answers0