根据经纬度，计算亮点之间的距离

最新推荐文章于 2022-06-21 16:41:31 发布

jee4a

最新推荐文章于 2022-06-21 16:41:31 发布

阅读量1.5k

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/zztp01/article/details/39316705

版权

mysql 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

代码片段

BEGIN
	#根据两点的经、纬度值获取两点距离
	#返回值为米
	#eg.28.199607, 112.983135 28.199559, 112.98556 两点距离大约为237米
	DECLARE R,distance,dLat,dLon,a double;
	SET R = 6371.0,distance = 0.0,dLat = (lat2 - lat1)*PI() /180,dLon = (lon2 - lon1)*PI() /180;

	SET a = SIN(dLat/2)*SIN(dLat/2) + COS(lat1*PI()/180) *COS(lat2*PI()/180) *sin(dLon/2)*SIN(dLon/2);
	SET distance = floor((2*ATAN(SQRT(a),SQRT(1-a)))*R*1000);
	RETURN distance;
END

根据距离限制进行排序

		SELECT
			id,
			name,
			lat,
			lon,
			szm,
			kpy,
			type,
			getDistatce(lat,:lat,lon,:lon) AS meters
		FROM
			region
		WHERE 
			status = '0' AND 
			getDistatce(
				lat,
				:lat,
				lon,
				:lon
			) < :meters 
			 
		ORDER BY 
			meters