计算两个经纬度之间的球面距离
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
BEGIN
DECLARE 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
?>