How to get 10 closest locations from table

Question

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?

I add already, now I get this error: #1582 - Incorrect parameter count in the call to native function 'POWER' — malinchhan, May 22 '13 at 10:07
I delete 1 close bracket: 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 - (dest.longitude)) * pi()/180 / 2), 2) )) as dis FROM company as dest where dis <10 ORDER BY dis limit 10; — malinchhan, May 22 '13 at 10:26
1. Latitudes are in the range +-90 degrees. You are doing 122.4058 - dest.latitude. 122.4058 is not a valid latitude. 2. You are using round earth equations for distance. Are the lat/lon pairs really 1,000 of miles apart? If not, you can use the much simpler flat earth equations to get distance. — TreyA, May 22 '13 at 13:06

score 1 · Answer 1 · answered May 22 '13 at 09:49

1

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.

answered May 22 '13 at 09:49

SimonSimCity

6,415
3
39
52

#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 'as dis <10 ORDER BY dis limit 10' at line 1 – malinchhan May 22 '13 at 09:56
@smartgirl, remove the "as dis" ... I think you can't use aliases there. If you need this as a result, you'd have to write this heavy calculation in the select-part of the query. – SimonSimCity May 22 '13 at 10:00
I change to like this: 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 - dest.longitude) * pi()/180 / 2)), 2) )) as dis FROM company as dest where dis <10 ORDER BY dis limit 10; – malinchhan May 22 '13 at 10:10
but I get this error: #1582 - Incorrect parameter count in the call to native function 'POWER' – malinchhan May 22 '13 at 10:10

echo_Me · Accepted Answer · 2013-05-22T10:32:43.727

1

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;

edited May 22 '13 at 10:32

answered May 22 '13 at 10:10

echo_Me

37,078
5
58
78

I add already, but I get this error: #1582 - Incorrect parameter count in the call to native function 'POWER' – malinchhan May 22 '13 at 10:12
I change to like this : 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 - (dest.longitude)) * pi()/180 / 2)), 2) )) as dis FROM company as dest where dis <10 ORDER BY dis limit 10; – malinchhan May 22 '13 at 10:15
i think maybe you mean `abs(dest.longitude)` – echo_Me May 22 '13 at 10:23
I store as double , but if I select , so it may be string ! – malinchhan May 22 '13 at 10:23
now I change to this: 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 - (dest.longitude)) * pi()/180 / 2), 2) )) as dis FROM company as dest where dis <10 ORDER BY dis limit 10; – malinchhan May 22 '13 at 10:24
and I get this error : #1054 - Unknown column 'dis' in 'where clause' – malinchhan May 22 '13 at 10:24
in your previous error unkown dis in where clause try to replace WHERE by HAVING – echo_Me May 22 '13 at 10:28
MySQL returned an empty result set (i.e. zero rows). ( Query took 0.0006 sec ) – malinchhan May 22 '13 at 10:31
I have 24 rows in company table, but why it is empty ? – malinchhan May 22 '13 at 10:32
still empty result, I don't know why ! – malinchhan May 22 '13 at 10:36
did the result returns if you remove this `HAVING dis <10` ? – echo_Me May 22 '13 at 10:38
nice :) , then your results was greater then 10 :) – echo_Me May 22 '13 at 10:40
but I also get 2 rows that doesn't have latitude and longitude. – malinchhan May 22 '13 at 10:42
what condition I have to set in order to not get rows without lat or long ? – malinchhan May 22 '13 at 10:42
I want to get only companies that have latitude and longitude ! – malinchhan May 22 '13 at 10:44
i guess something like : where lat <> 0 or long <> 0 – echo_Me May 22 '13 at 10:44
because i didnt see your table structure thats why i guessed – echo_Me May 22 '13 at 10:45
or like that if empty , where lat is not null – echo_Me May 22 '13 at 10:46
it shows 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 'where dest.longitude is not null and dest.latitude is not null' at line 1 – malinchhan May 22 '13 at 10:50
when I use : 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 ORDER BY dis limit 10 where dest.longitude is not null and dest.latitude is not null; – malinchhan May 22 '13 at 10:50
and if you want make dis < 10 , then should be like that : from ... where ... having dis < 10 order by ... – echo_Me May 22 '13 at 10:59
I don't know why I use having dis < 10, I take this formular from others ! \ – malinchhan May 22 '13 at 11:00
this because you will get all results < 10 , but your results are all > 10 then maybe you will change 10 to 15 or 20 or something you want get results < then it . if you dont want filter your result then you dont need having dis < 10 – echo_Me May 22 '13 at 11:03
where should I put having dis < 20 ? I have errors when I use it ! the result I get before seems not right because I put the nearest place from companyID 1 to 10, I get random companyID like 1, 4, 20 , ... – malinchhan May 23 '13 at 02:06
now I can get the nearest places to my current locations by using this query: SELECT companyID,relevantTextName as companyName, ACOS( SIN( RADIANS( dest.latitude ) ) * SIN( RADIANS( 11.576150037278605 ) ) + COS( RADIANS( dest.latitude ) ) * COS( RADIANS( 11.576150037278605 )) * COS( RADIANS( dest.longitude ) - RADIANS( 104.89529371261597 )) ) * 6380 AS distance FROM company as dest having distance < 10 ORDER BY distance limit 10; – malinchhan May 23 '13 at 04:18
But it is not dynamically ! – malinchhan May 23 '13 at 05:40
http://stackoverflow.com/questions/3922404/how-to-efficiently-find-the-closest-locations-nearby-a-given-location this may help you about location , i dont understand what you mean by its not dinamically – echo_Me May 23 '13 at 15:49

How to get 10 closest locations from table

2 Answers2