mysql 通过经纬度查询附近的地点位置(谷歌方案)

 1 SELECT    
 2   id, `name`,(    
 3     6371 * acos (    
 4       cos ( radians(60.000000) )    
 5       * cos( radians( lat ) )    
 6       * cos( radians( lon ) - radians(70.000000) )    
 7       + sin ( radians(60.000000) )    
 8       * sin( radians( lat ) )    
 9     )    
10   ) AS distance    
11 FROM t_demo  
12 HAVING distance < 2    
13 ORDER BY distance   
14 LIMIT 0 , 20;    

说明:当前位置纬度:60.000000 经度:70.000000  以公里代替里程搜索,用6371替换3959。

英文原文:

The SQL statement that will find the closest 20 locations that are within a radius of 30 miles to the 78.3232, 65.3234 coordinate. It calculates the distance based on the latitude/longitude of that row and the target latitude/longitude, and then asks for only rows where the distance value is less than 30 miles, orders the whole query by distance, and limits it to 20 results. To search by kilometers instead of miles, replace 3959 with 6371.

 

 
posted @ 2017-10-18 15:53  张小三、  阅读(2042)  评论(0编辑  收藏  举报