计算两个经纬度之间的球面距离（基于Mysql和PHP实现）

计算两个经纬度之间的球面距离

1、MySQL实现方式 - 基于空间函数(ST_Distance_Sphere)实现

前置条件：确保您使用的是 MySQL 8.0 或更高版本，因为较早的版本对地理空间的支持有限。

1.1 创建表和索引

说明：设置 location 为 point 类型

# 建表
CREATE TABLE `test` (`id` int(11) unsigned NOT NULL AUTO_INCREMENT,`location` point NOT NULL,`name` varchar(30) NOT NULL,PRIMARY KEY (`id`),SPATIAL KEY `sp_index` (`location`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4;# 创建空间索引
CREATE SPATIAL INDEX sp_index ON `test` (location);

1.2 添加模拟数据

方式一:

INSERT INTO test (location,name) VALUES ( ST_GeomFromText('POINT(121.675702 31.281530)'),'恒越荣新广场');

方式二:

INSERT INTO test (location,name) VALUES ( POINT(121.675702,31.281530),'恒越荣新广场');

1.3 根据定位查询与目标位置的距离并排序

定位:121.658889,31.26485

SELECT *, ST_Distance_Sphere(location, Point(121.658889,31.26485)) AS distance
FROM test
ORDER BY distance asc;

查询结果：
说明: distance的单位: 米
在这里插入图片描述

2、MySQL实现方式 - 基于自定义函数实现

2.1 创建表和索引

说明：设置 location 为 varchar 类型，格式: 经度,纬度

# 建表
CREATE TABLE `test1` (`id` int(11) unsigned NOT NULL AUTO_INCREMENT,`location` varchar(30) NOT NULL,`name` varchar(30) NOT NULL,PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4;# 创建普通索引
ALTER TABLE `test1` ADD INDEX(`location`);

2.2 添加模拟数据

INSERT INTO test1 (location,name) VALUES ('121.675702,31.28153','恒越荣新广场');
INSERT INTO test1 (location,name) VALUES ('121.673772,31.2799','华美新苑');
INSERT INTO test1 (location,name) VALUES ('121.658889,31.26485','金泰广场');

2.3 封装计算函数

CREATE DEFINER = CURRENT_USER FUNCTION `calculate_distance_from_comma_separated`(`loc1` VARCHAR(50), `loc2` VARCHAR(50))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGINDECLARE lon1 DECIMAL(10, 7);DECLARE lat1 DECIMAL(9, 6);DECLARE lon2 DECIMAL(10, 7);DECLARE lat2 DECIMAL(9, 6);DECLARE earth_radius DECIMAL(10, 2) DEFAULT 6371.0; -- 地球平均半径，单位：千米DECLARE lat1_rad DECIMAL(11, 7);DECLARE lon1_rad DECIMAL(11, 7);DECLARE lat2_rad DECIMAL(11, 7);DECLARE lon2_rad DECIMAL(11, 7);DECLARE distance DECIMAL(10, 2);SET lon1 = CAST(SUBSTRING_INDEX(loc1, ',', 1) AS DECIMAL(10, 7));SET lat1 = CAST(SUBSTRING_INDEX(loc1, ',', -1) AS DECIMAL(9, 6));SET lon2 = CAST(SUBSTRING_INDEX(loc2, ',', 1) AS DECIMAL(10, 7));SET lat2 = CAST(SUBSTRING_INDEX(loc2, ',', -1) AS DECIMAL(9, 6));SET lon1_rad = RADIANS(lon1);SET lat1_rad = RADIANS(lat1);SET lon2_rad = RADIANS(lon2);SET lat2_rad = RADIANS(lat2);SET distance = earth_radius * ACOS(SIN(lat1_rad) * SIN(lat2_rad) + COS(lat1_rad) * COS(lat2_rad) * COS(lon2_rad - lon1_rad));RETURN distance;
END;

2.4 根据定位查询与目标位置的距离并排序

定位:121.658889,31.26485

SELECT *,(calculate_distance_from_comma_separated(location,'121.658889,31.26485')) AS distance
FROM test1
ORDER BY distance asc;

查询结果：
说明: distance的单位: 千米
在这里插入图片描述

3、PHP实现方式

3.1 函数封装

说明: 单位: 千米

<?php/*** 计算两个定位的球面距离* @param $longitude1 string 经度1* @param $latitude1 string 纬度1* @param $longitude2 string 经度2* @param $latitude2 string 纬度2* @return float|int*/
function calculateDistance($latitude1, $longitude1, $latitude2, $longitude2)
{$earthRadius = 6371; // 地球平均半径，单位：千米$lat1 = deg2rad($latitude1);$lon1 = deg2rad($longitude1);$lat2 = deg2rad($latitude2);$lon2 = deg2rad($longitude2);$distance = acos(sin($lat1) * sin($lat2) + cos($lat1) * cos($lat2) * cos($lon2 - $lon1)) * $earthRadius;return $distance;
}$longitude1 = 121.658889;
$latitude1 = 31.26485;
$longitude2 = 121.675702;
$latitude2 = 31.28153;$distance = calculateDistance($latitude1, $longitude1, $latitude2, $longitude2);
echo sprintf('%.2f', $distance); # 输出: 2.45
?>