经纬度计算距离并排序

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以上版本实现

 

使用geomfromtext()内置函数可将字符串的位置点转换为geometry格式存入到数据库中，例如：
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 != '张三';