MySQL之根据经纬度查询多少公里范围内的数据

MySQL之根据经纬度查询多少公里范围内的数据

根据经纬度查询多少公里范围内的数据

可根据实际业务逻辑去套用该方法

创建地址表,存储经纬度信息

//创建地址表,存储位置信息
CREATE TABLE `address` (
  `longitude` varchar(255) DEFAULT NULL COMMENT '经度',
  `latitude` varchar(255) DEFAULT NULL COMMENT '纬度'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入数据

INSERT INTO `test`.`address` (`longitude`, `latitude`) VALUES ('104.56589098034763', '28.99800125997524'),('104.05569373538', '30.562135496625'),('104.05869091152', '30.553040797531'),('104.13420930748', '30.665243528999'),('104.12867010329', '30.662528026621'),('104.04138950641', '30.513198045111'),('104.17470773022', '30.804769965679'),('104.04550959085', '30.531267527914'),('104.02694043085', '30.715366348472'),('103.97637771606', '30.68450999144'),('103.64457672727', '30.784181779703'),('104.04560400202', '30.531092341214'),('106.49030637454', '29.535946896717'),('104.08742448397', '30.680228365291'),('104.04794645727', '30.531973850008'),('106.56791030671', '29.557989036859'),('104.05338436547', '30.564006578701'),('104.02703686796', '30.729158155012'),('104.08646754473', '30.689490451485'),('104.1900486191', '30.583207989051'),('104.27089976383', '30.647392212029'),('106.43459219431', '29.553529003531'),('104.05390189852', '30.601960986681'),('104.01068560379', '30.710372251791'),('106.61270851307', '26.651207071474'),('104.71460234384', '28.685603980002'),('104.07871813555', '30.652789043723'),('104.0456659613715', '30.60152750651042'),('107.0494192165799', '33.06417317708333'),('106.6701207139757', '26.60911675347222'),('104.0254877387153', '30.7549755859375'),('104.059095', '30.676088'),('107.102656', '31.792046'),('104.06058', '30.649039'),('104.49597', '31.318481'),('103.9858797200521', '30.56755181206597'),('108.824202202691', '30.26600151909722'),('105.1634727647569', '31.62689832899305'),('106.486002061632', '29.5526744249132'),('106.6154720052083', '26.66232286241319'),('104.0255767144097', '30.62420627170139'),('106.4696183268229', '29.43423936631945'),('104.0404334852431', '30.68428927951389'),('105.8924037000868', '34.5719935438368'),('103.7285785590278', '30.58210611979167'),('104.0254996744792', '30.75497965494792'),('104.4114819335937', '29.69346788194444'),('106.5720852322049', '29.52876763237847'),('104.0061404079861', '30.62421685112847'),('106.6133222113715', '29.65951144748264'),('106.6719240993924', '26.55035183376736'),('104.0461447482639', '30.52714274088542'),('106.4714493815104', '29.4492076280382'),('104.0870776367187', '30.63785373263889'),('104.0721408420139', '30.62553683810764'),('103.8255387369792', '30.92856499565972'),('106.4056217447917', '27.79358642578125'),('103.9841859266493', '30.63812771267361'),('105.6231499565972', '28.86593994140625'),('103.7447246636285', '29.56069607204861'),('104.6236545138889', '30.07939181857639'),('106.279944390191', '29.96944634331597'),('104.1875244140625', '30.85933159722222'),('108.3942168511285', '31.170830078125'),('106.9167198350694', '27.66800971137153'),('103.9859627278646', '30.62867757161458'),('105.1614154730903', '31.616787109375'),('106.8272151692708', '32.34679578993055'),('104.2351565212674', '31.32266927083333'),('104.737516547309', '31.77503743489583'),('103.9776860894097', '30.64578260633681');

根据当前经纬度查询所有地址,按距离由近到远排序

SELECT *,
	(
		6371 * acos (
			cos ( radians('30.4138') )
			* cos( radians(latitude) )
			* cos( radians(longitude) - radians('104.234') )
			+ sin ( radians('30.4138') )
			* sin( radians(latitude) )
		)
	) AS distance 
FROM address
GROUP BY distance

查询结果(距离以km为单位)

MySQL之根据经纬度查询多少公里范围内的数据

上一篇:mysql 计算离目标点的距离


下一篇:微信小程序获取当前位置并调用微信内置地图打开