一,使用mysl内置st_distance函数
注:本函数在mysql5.6以上版本才有
应用场景:
假设我当时的坐标:117.069,35.86 需要查询我附近50m内服务区,并按照距离由近及远排列
SELECT
s.id,s.name,s.lng,s.lat,
(st_distance (point (lng, lat),point(117.069,35.86) ) *111195) AS distance
FROM
road_servicearea s
HAVING distance<50
ORDER BY distance
二,自行编写sql语句1
$toollist=M("tool")->where($where)->field("title,address,".get_distance_sql('longitude','latitude',$longitude,$latitude)." as dis")
->order("dis asc")
->limit($start,$limit)
->select();
/*
$lng1:第一个经度;
$lat1:第一个纬度;
$lng2:第二个经度;
$lat2:第二个纬度;
*/
function get_distance_sql($lng1,$lat1,$lng2,$lat2){
return "round(6378.138*2*asin(sqrt(pow(sin((".$lat1."*pi()/180-".$lat2."*pi()/180)/2),2)+cos(".$lat1."*pi()/180)*cos(".$lat2."*pi()/180)*pow(sin( (".$lng1."*pi()/180-".$lng2."*pi()/180)/2),2)))*1000)";
}
二,自行编写sql语句2
SELECT
id, `name`,(
6371 * ACOS (
COS ( RADIANS (31.918776) )
* COS( RADIANS( latitude ) )
* COS( RADIANS( longitude ) - RADIANS(117.284313) ) + SIN ( RADIANS(31.918776) )
* SIN( RADIANS( latitude ) )
)
) AS distance
FROM re_tenant
HAVING distance < 20
ORDER BY distance
LIMIT 0,20 ;