CODEWITHSUNDEEP
mysqllatitude-longitudeclosest-pointsmysql-select-db
malinchhan
malinchhan

Reputation: 767

How to get 10 closest locations from table

I want to get 10 closest locations with longitude and latitude. I store longitude and latitude as double. I tried this:

SELECT * FROM company as dest where 3956 * 2 * ASIN(SQRT( POWER(SIN((122.4058 - abs( dest.latitude)) * pi()/180 / 2),2) + COS(122.4058 * pi()/180 ) * COS( abs (dest.latitude) * pi()/180) * POWER(SIN((37.7907 – dest.longitude) * pi()/180 / 2), 2) )) as dis <10 and ORDER BY dis limit 10;

but I get this error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '– dest.longitude) * pi()/180 / 2), 2) )) as dis <10 and ORDER BY dis limit 10 ' at line 1

How to solve this error?

Upvotes: 0

Views: 515

Answers (2)

echo_Me
echo_Me

Reputation: 37233

try this :

     POWER(SIN((37.7907 – abs(dest.longitude))

EDIT2:

     SELECT *,3956 * 2 * ASIN(SQRT( POWER(SIN((122.4058 - abs(dest.latitude)) * pi()/180 / 2),2) + COS(122.4058 * pi()/180 ) * COS( abs(dest.latitude) * pi()/180) * POWER(SIN((37.7907 - abs(dest.longitude)) * pi()/180 / 2), 2) )) as dis FROM company as dest HAVING dis <10 ORDER BY dis limit 10;

Upvotes: 1

SimonSimCity
SimonSimCity

Reputation: 6572

I copied your sql-code and put it into an editor having syntax-highlight support (Sublime2) and the character, the error points to (looking like a minus) actually is another sign ..

According to this webpage this character is called EN DASH (you see it in utf8-hex-code at the end of the link :)) http://www.utf8-character.info/#!%E2%80%93

Try to replace this character by a simple minus.

Upvotes: 1

Related Questions