2

In my script below I'm returning a distance based on latitude and longitude coordinates. The problem happens when my ui.userlat and ui.userlong are the same as the latitude(-20.31225580) and longitude passed (-40.30603010), because it returns a null distance and, therefore, no SQL results.

PS: I'm using MYSQL

SELECT ui.id, pt.token, 
        (6371 * acos(cos(radians(-20.299626)) * cos(radians(ui.userlat))
        * cos( radians( ui.userlong ) - radians(-40.292269) ) +
        sin(radians(-20.299626) ) * sin(radians(ui.userlat)))) AS distance 
FROM user_info AS ui, push_tokens AS pt
WHERE pt.user_id = ui.id AND ui.tipoConta = 2 HAVING distance < 15 ORDER BY distance

How can I make it return values EVEN if distance = 0? I tried to set de default value of distance to 0 in SQL, but I hadn't any sucess until now

gmiley
  • 6,531
  • 1
  • 13
  • 25
Dr. Joao Paulo
  • 331
  • 1
  • 3
  • 11
  • What DBMS are you using? MS SQL, Oracle, mySQL? You should be able to wrap your calculation or columns in `IsNull`, `NVL`, or some other similar function that replace the `NULL` value with a value that you specify. If all else fails, you can check with a `CASE WHEN ... IS NULL THEN ... ELSE ... END As distance` – gmiley Jun 15 '16 at 02:51
  • I don't think the Haversine formula breaks down when the start and end point are the same. – Tim Biegeleisen Jun 15 '16 at 02:55
  • Tim Biegeleisen, it doenst breaks down. It just returns me nothing =) – Dr. Joao Paulo Jun 15 '16 at 02:56
  • 4
    Then just take `COALESCE(expression, 0)`, where `expression` is your Haversine formula. I've worked with Haversine before but I don't seem to remember this problem. Perhaps I never used it for extremely small distances. – Tim Biegeleisen Jun 15 '16 at 02:57
  • IFNULL(expression, 0) will also work. – Dr. Stitch Jun 15 '16 at 02:59
  • 1
    Thank you all! I got it to work with COALESCE! – Dr. Joao Paulo Jun 15 '16 at 03:03
  • Using COALESCE to 'solve' this problem may indicate the NULLs are not accounted for :} NULL is different than 0: eg. `haversine(NULL) -> NULL`, so what feeds in `NULL`? (Out of range may yield NULL for a number of such functions, eg http://stackoverflow.com/questions/28254863/mysql-geospacial-search-using-haversine-formula-returns-null-on-same-point.) – user2864740 Jun 15 '16 at 03:27

0 Answers0