mysql 计算 给定座标 n米范围内的 公司地址

2019独角兽企业重金招聘Python工程师标准>>>

select * from (

SELECT id,title,addr,pointX,pointY,(2*ATAN2(SQRT(SIN((24.488935-pointY)*PI()/180/2)   
        *SIN((24.488935-pointY)*PI()/180/2)+   
        COS(pointY*PI()/180)*COS(24.488935*PI()/180)   
        *SIN((118.192988-pointX)*PI()/180/2)   
        *SIN((118.192988-pointX)*PI()/180/2)),   
        SQRT(1-SIN((24.488935-pointY)*PI()/180/2)   
        *SIN((24.488935-pointY)*PI()/180/2)   
        +COS(pointY*PI()/180)*COS(24.488935*PI()/180)   
        *SIN((118.192988-pointX)*PI()/180/2)   
        *SIN((118.192988-pointX)*PI()/180/2))))*6378140 juli
FROM jh_company

) a where a.juli < 5000 order by a.juli asc

 

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

#lat为纬度, lng为经度, 一定不要弄错 declare @lng1 float; declare @lat1 float; declare @lng2 float; declare @lat2 float; set @lng1=116.3899; set @lat1=39.91578; set @lng2=116.3904; set @lat2=39.91576;   select (2*ATAN2(SQRT(SIN((@lat1-@lat2)*PI()/180/2)           *SIN((@lat1-@lat2)*PI()/180/2)+           COS(@lat2*PI()/180)*COS(@lat1*PI()/180)           *SIN((@lng1-@lng2)*PI()/180/2)           *SIN((@lng1-@lng2)*PI()/180/2)),           SQRT(1-SIN((@lat1-@lat2)*PI()/180/2)           *SIN((@lat1-@lat2)*PI()/180/2)           +COS(@lat2*PI()/180)*COS(@lat1*PI()/180)           *SIN((@lng1-@lng2)*PI()/180/2)           *SIN((@lng1-@lng2)*PI()/180/2))))*6378140;

转载于:https://my.oschina.net/u/1045177/blog/755128