sql经纬度计算距离

##lat 纬度 lng 经度 distance 距离  用户经纬度：36.738024 116.597805  赤道半径：6378.173千米
SELECT
	lat,
	lng,
	(
		round(
			6378137 * 2 * asin(
				sqrt(
					pow(
						sin(
							(
								(lat * pi()) / 180 - (116.597805 * pi()) / 180
							) / 2
						),
						2
					) + cos((116.597805 * pi()) / 180) * cos((lat * pi()) / 180) * pow(
						sin(
							(
								(lng * pi()) / 180 - (36.738024 * pi()) / 180
							) / 2
						),
						2
					)
				)
			)
		)
	) AS distance
FROM
	users_location
ORDER BY
	distance ASC;

 

##lat 纬度 lng 经度 distance 距离  用户经纬度：36.738024 116.597805  赤道半径：6378.173千米
SELECT
	lat,
	lng,
	(
		round(
			6378137 * 2 * asin(
				sqrt(
					power(
						sin(
							(
								(lat * 3.14159265358979323846) / 180 - (116.597805 * 3.14159265358979323846) / 180
							) / 2
						),
						2
					) + cos((116.597805 * 3.14159265358979323846) / 180) * cos((lat * 3.14159265358979323846) / 180) * power(
						sin(
							(
								(lng * 3.14159265358979323846) / 180 - (36.738024 * 3.14159265358979323846) / 180
							) / 2
						),
						2
					)
				)
			)
		)
	) AS distance
FROM
	users_location
ORDER BY
	distance ASC;