网站功能部分
用户
权限
敏感词过滤
网站配置
访问统计
定时任务

#lat为纬度, lng为经度, 一定不要弄错
drop function getDistance;
DELIMITER $$  
CREATE DEFINER=`root`@`localhost` FUNCTION `getDistance`(
     lon1 float(10,7)
    ,lat1 float(10,7)
    ,lon2 float(10,7)
    ,lat2 float(10,7)
) RETURNS double
begin
    declare d double;
    declare radius int;
    set radius = 6378140; #假设地球为正球形，直径为6378140米
    set d = (2*ATAN2(SQRT(SIN((lat1-lat2)*PI()/180/2)  
        *SIN((lat1-lat2)*PI()/180/2)+  
        COS(lat2*PI()/180)*COS(lat1*PI()/180)  
        *SIN((lon1-lon2)*PI()/180/2)  
        *SIN((lon1-lon2)*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((lon1-lon2)*PI()/180/2)  
        *SIN((lon1-lon2)*PI()/180/2))))*radius;
    return d;
end
$$
DELIMITER ;
select getDistance(116.3899,39.91578,116.3904,39.91576); #调用函数






SELECT areaid,earth_distance( ll_to_earth(29.5081729888916,106.494911193848),ll_to_earth(latitude, longitude))  as distancefromcurretnlocation
,id, adddate, address, gps, lastdate, latitude, longitude, name, 
       phone, website, userid
  FROM shop  where shop.areaid=403
ORDER BY distancefromcurretnlocation asc limit 10;

CREATE INDEX gisindex on shop USING gist(ll_to_earth(latitude, longitude));



select getDistance(108.95678,34.2775,shop.longitude,shop.latitude) as l,  shop.* from shop  order by l desc limit 100