How to get 5km distance surroundings when im giving current latitude and longitude in SQL

Question

Is this query working fine or not ?? any one help to execute this query, i want 5km surrounding values when i giving curent latittide and longitude

SELECT
    id, (
      3959 * acos (
      cos ( radians(12.966958) )
      * cos( radians( latitude ) )
      * cos( radians( longitude ) - radians(80.1525835) )
      + sin ( radians(12.966958) )
      * sin( radians( latitude ) )
    )
) AS distance
FROM place
HAVING distance < 5 ORDER BY distance

have you tried to use "where" instead of "having" in your query? — h.m.i.13, Jan 21 '17 at 10:50
@arul-dass-d the way you have formed the query is not correct. Checkout http://dev.mysql.com/doc/refman/5.5/en/problems-with-alias.html. I've written an answer to explain — Karan Shah, Jan 21 '17 at 11:49

score 1 · Accepted Answer · answered Jan 21 '17 at 11:41

1

HAVING is used only when you want to filter results after an aggregation function - You have to use WHERE here.

Also,

Standard SQL disallows references to column aliases in a WHERE clause. This restriction is imposed because when the WHERE clause is evaluated, the column value may not yet have been determined.

SELECT id, distance FROM(
SELECT
    id, (
      3959 * acos (
      cos ( radians(12.966958) )
      * cos( radians( latitude ) )
      * cos( radians( longitude ) - radians(80.1525835) )
      + sin ( radians(12.966958) )
      * sin( radians( latitude ) )
    )
) AS distance
FROM place) tmp
WHERE tmp.distance >5
ORDER by distance

answered Jan 21 '17 at 11:41

Karan Shah

1,304
11
15

"Standard SQL..." - is that a qoute? because it is logically totally wrong. – David דודו Markovitz Jan 21 '17 at 11:55
@DuduMarkovitz Check this:http://dev.mysql.com/doc/refman/5.5/en/problems-with-alias.html – Karan Shah Jan 21 '17 at 11:56
its working...How can i write this in laravel ?? do u have any ideas ?? – user7346035 Jan 21 '17 at 11:59
@AruldassD You can refer this: http://stackoverflow.com/questions/14687993/laravel-4-how-to-run-a-raw-sql – Karan Shah Jan 21 '17 at 12:01

How to get 5km distance surroundings when im giving current latitude and longitude in SQL

1 Answers1