经纬度计算距离并排序
1.MySQL实现
公式：
- Lng1表示A点纬度和经度，Lat2 Lng2 表示B点纬度和经度
- a = Lat1 – Lat2为两点纬度之差 b = Lng1 -Lng2 为两点经度之差
- 6378.137为地球半径，单位为公里
- 计算出来的结果单位为公里
sql语句：
SELECT
*, (
2 * 6378.137 * ASIN(
SQRT(
POW(
SIN(
PI() * (108.901759 - lng) / 360
),
2
) + COS(PI() * 34.233037 / 180) * COS(lat * PI() / 180) * POW(
SIN(
PI() * (34.233037 - lat) / 360
),
2
)
)
)
) AS juli
FROM
`z_gis`
ORDER BY
juli ASC
LIMIT 0,
20;
2.Redis实现
-
geoadd 集合 经度 纬度 名称: 添加地理位置坐标
geoadd geo:city 118.8921 31.32751 nanjing
-
geopos: 获取地理位置的坐标
geopos geo:city nanjing
-
geodist: 返回两个给定位置之间的距离
geodist geo:city nanjing hangzhou km
- 基于经纬度坐标范围查询
georadius city 116.405285 39.904989 100 km WITHDIST WITHCOORD ASC COUNT 5
WITHDIST返回距离，WITHCOORD返回经纬度
3.MySQL5.7以上版本实现
insert into XXTABLE values (XX,XX,XX, st_geomfromtext('point(108.949871515 34.25416521),XX,XX);计算两点之间的距离：
SELECT floor(
st_distance_sphere (
( SELECT geom FROM nodestest WHERE id = '151024809' ),
( SELECT geom FROM nodestest WHERE id = '151027929' )
)
);
查找距离某点【POINT(118.9515 34.4271)】2km范围内的点（ST_Distance_Sphere精确查询）
SELECT
name,
ST_ASTEXT(gis),
FLOOR(ST_DISTANCE_SPHERE(POINT(108.901759, 34.233037), gis)) AS distance
FROM
z_gis
WHERE
ST_CONTAINS(ST_MAKEENVELOPE(POINT((108.901759 + (200 / 111)),
(34.233037 + (200 / 111))),
POINT((108.901759 - (200 / 111)),
(34.233037 - (200 / 111)))),
gis)
ORDER BY distance ASC
通过几何关系函数查找距离某点【POINT(118.9515 34.4271)】500米范围内的点
SELECT
id,
ST_ASTEXT(geom),
FLOOR(ST_DISTANCE_SPHERE(POINT(118.9515, 34.4271), geom)) AS distance
FROM
nodestest
WHERE
ST_CONTAINS(ST_MAKEENVELOPE(POINT((118.9515 + (0.5 / 111)),
(34.4271 + (0.5 / 111))),
POINT((118.9515 - (0.5 / 111)),
(34.4271 - (0.5 / 111)))),
geom)
ORDER BY distance
---------实战------------
CREATE TABLE `z_gis` ( `id` varchar(45) NOT NULL, `name` varchar(10) NOT NULL COMMENT '姓名', `gis` geometry NOT NULL COMMENT '空间位置信息', `geohash` varchar(20) GENERATED ALWAYS AS (st_geohash(`gis`,8)) VIRTUAL, PRIMARY KEY (`id`), UNIQUE KEY `id` (`id`), SPATIAL KEY `idx_gis` (`gis`), KEY `idx_geohash` (`geohash`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='空间位置信息'
insert into z_gis(id,name,lat,lng,gis) values
(replace(uuid(),'-',''),'张三','34.2588125935','108.9498710632',ST_geomfromtext('point(108.9498710632 34.2588125935)')),
(replace(uuid(),'-',''),'李四','34.2598766768','108.9465236664',ST_geomfromtext('point(108.9465236664 34.2598766768)')),
(replace(uuid(),'-',''),'王五','34.2590342786','108.9477252960',ST_geomfromtext('point(108.9477252960 34.2590342786)')),
(replace(uuid(),'-',''),'赵六','34.2553719653','108.9437770844',ST_geomfromtext('point(108.9437770844 34.2553719653)')),
(replace(uuid(),'-',''),'小七','34.2595663206','108.9443349838',ST_geomfromtext('point(108.9443349838 34.2595663206)')),
(replace(uuid(),'-',''),'孙八','34.2643456798','108.9473497868',ST_geomfromtext('point(108.9473497868 34.2643456798)')),
(replace(uuid(),'-',''),'十九','34.2599476152','108.9530360699',ST_geomfromtext('point(108.9530360699 34.2599476152)'));
查询张三的经纬度信息
select name, astext(gis) gis from z_gis where name = '张三';
修改张三的位置信息
update z_gis set gis = geomfromtext('point(108.9465236664 34.2598766768)') where name = '张三';
查询张三和李四之间的距离
select floor(st_distance_sphere( (select gis from z_gis where name= '张三'), gis )) distance from z_gis where name= '李四';
查询距离张三500米内的所有人
SELECT
name,
FLOOR(ST_DISTANCE_SPHERE((SELECT
gis
FROM
z_gis
WHERE
name = '张三'),
gis)) distance,
ST_astext(gis) point
FROM
z_gis
WHERE
ST_DISTANCE_SPHERE((SELECT
gis
FROM
z_gis
WHERE
name = '张三'),
gis) < 500
AND name != '张三';
如果表中数据非常多时，这样查效率会非常低，这时就会用到geohash字段查询
SELECT
name,
floor(ST_DISTANCE_SPHERE((SELECT
gis
FROM
z_gis
WHERE
name = '张三'),
gis)) distance,
ST_astext(gis) point
FROM
z_gis
WHERE
geohash like concat(left((select geohash from z_gis where name = '张三'),5),'%')
AND ST_DISTANCE_SPHERE((SELECT
gis
FROM
z_gis
WHERE
name = '张三'),
gis) < 500
AND name != '张三';
