Mysql经纬度查询

最新推荐文章于 2024-08-06 18:14:18 发布

dud1ao

最新推荐文章于 2024-08-06 18:14:18 发布

阅读量1.8k

点赞数

分类专栏： PHP

PHP 专栏收录该内容

0 篇文章 0 订阅

订阅专栏

1.创建MySQL存储函数,并对经纬度字段建立索引

DELIMITER $$
CREATE DEFINER=`root`@`%` FUNCTION `GETDISTANCE`(lat1 DOUBLE, lng1 DOUBLE, lat2 DOUBLE, lng2 DOUBLE) RETURNS double
READS SQL DATA
DETERMINISTIC
BEGIN
DECLARE RAD DOUBLE;
DECLARE EARTH_RADIUS DOUBLE DEFAULT 6378137;
DECLARE radLat1 DOUBLE;
DECLARE radLat2 DOUBLE;
DECLARE radLng1 DOUBLE;
DECLARE radLng2 DOUBLE;
DECLARE s DOUBLE;
SET RAD = PI() / 180.0;
SET radLat1 = lat1 * RAD;
SET radLat2 = lat2 * RAD;
SET radLng1 = lng1 * RAD;
SET radLng2 = lng2 * RAD;
SET s = ACOS(COS(radLat1)*COS(radLat2)*COS(radLng1-radLng2)+SIN(radLat1)*SIN(radLat2))*EARTH_RADIUS;
SET s = ROUND(s * 10000) / 10000;
RETURN s;
END$$
DELIMITER ;

2.查询SQL

SELECT *,lng,lat,GETDISTANCE(lat,lng,27.985417,120.69683) AS distance FROM  et_merchant where 1 HAVING distance<100000 ORDER BY distance ASC LIMIT 0,10

返回的单位为:米

在PHP中可以使用下面的函数来计算距离。返回单位:米

<?php
    //根据经纬度计算距离 其中A($lat1,$lng1)、B($lat2,$lng2)
    public static function getDistance($lat1,$lng1,$lat2,$lng2) {
        //地球半径
        $R = 6378137;
        //将角度转为狐度
        $radLat1 = deg2rad($lat1);
        $radLat2 = deg2rad($lat2);
        $radLng1 = deg2rad($lng1);
        $radLng2 = deg2rad($lng2);
        //结果
        $s = acos(cos($radLat1)*cos($radLat2)*cos($radLng1-$radLng2)+sin($radLat1)*sin($radLat2))*$R;
        //精度
        $s = round($s* 10000)/10000;
        return  round($s);
    }
?>