参数说明:
Lat:数据库中的纬度字段名
Lon:数据库中的精度字段名
uLat:用户传递的纬度
uLon:用户传递的经度
结果单位:
千米(KM)
注意:如需要“米”只需要把最后的“/1000”删除即可
SQL语句:
方法一:
SELECT ,ROUND(6378.1382ASIN(SQRT(POW(SIN((LatPI()/180-uLatPI()/180)/2),2)+COS(LatPI()/180)COS(uLatPI()/180)POW(SIN((LonPI()/180-uLon*PI()/180)/2),2)))*1000)/1000 AS juli FROM table_name ORDER BY juli ASC
方法二:
SELECT *,(st_distance (point (Lon,Lat),point (uLon,uLat))*111195/1000 )as juli FROM table_name ORDER BY juli ASC LIMIT 0,10
例如:
方法一:
SELECT shop_id,ROUND(6378.1382ASIN(SQRT(POW(SIN((LatPI()/180-31.166349PI()/180)/2),2)+COS(LatPI()/180)COS(31.166349PI()/180)POW(SIN((LonPI()/180-121.458495PI()/180)/2),2)))*1000)/1000 AS juli FROM shop ORDER BY juli ASC
方法二:
SELECT shop_id,(st_distance (point (Lon,Lat),point (121.458495,31.166349))*111195/1000 )as juli FROM shop ORDER BY juli ASC LIMIT 0,10
结果:
方法一:
方法二:
总结:
经通过软件对比第一种方法较为准确