mysql实现地理位置搜索

转载： https://blog.csdn.net/wang_quan_li/article/details/33292863

随着LBS应用的遍地开花,在数据库中实现基于地理位置的搜索显得尤为重要.今天研究了下,顺便做个小结.

首先设计好一个简单的数据表,用来存放经纬度信息:

CREATE TABLE `t_merch_info` (
  `mid` bigint(20) NOT NULL AUTO_INCREMENT,
  `mname` varchar(30) NOT NULL DEFAULT '',
  `lng` double(20,10) NOT NULL,
  `lat` double(20,10) NOT NULL,
  `mtype` tinyint(4) NOT NULL,
  `time` bigint(20) NOT NULL,
  PRIMARY KEY (`mid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

创建完成后我们可以查看一下,应该是这个样子

mysql> DESC `t_merch_info` 

我用java插入了200万的测试数据

public String insertData() {
    double lng = 0;
    double lat = 0;
    int mtype = 0;
    for (int i = 1; i < 2000000; i++) {
        lat = RandomUtils.nextDouble(21.1254976366, 41.2778064674);
        lng = RandomUtils.nextDouble(93.9111328125, 122.1240234375);
        mtype = RandomUtils.nextInt(1, 10);
        merchinfoService.insertMerchant("商家" + i, mtype, lng, lat);
    }
}

先测试没有添加索引的数据表， 

地理空间距离计算方法较多，目前我们使用的可以分为两类： 

1）球面模型，这种模型将地球看成一个标准球体，球面上两点之间的最短距离即大圆弧长，这种方法使用较广，在我们服务端被广泛使用； 

2）椭球模型，该模型最贴近真实地球，精度也最高，但计算较为复杂，目前客户端有在使用，但实际上我们的应用对精度的要求没有那么高。

我们最常用的基于球面模型的地理空间距离计算公式

R = earth’s radius
Δlat = lat2− lat1
Δlng = lng2− lng1
a = sin²(Δlat/2) + cos(lat1) * cos(lat2) * sin²(Δlng/2)
c = 2*atan2(√a, √(1−a))
dist = R*c

注：double atan2(double y,double x) 返回的是原点至点(x,y)的方位角，即与 x 轴的夹角。也可以理解为复数 x+yi 的辐角。返回值的单位为弧度，取值范围为 ；
    1度 = π/180≈0.01745弧度，1弧度=180/π≈57.29578度
    弧长 = 半径 X 圆心角(弧度制) 

 

根据公式编写Sql查询语句:

set @er=6366.564864;#earth’s radius (km)
set @lat=56.14262; #Search origin lat
set @lng=37.605853; #Search origin lng
set @dist=20;#Search radius (km)
SELECT mid,mname,lat,lng,
　　 @er*2*ASIN(SQRT(POWER(SIN(RADIANS(@lat - lat)/ 2), 2) +  COS(RADIANS(@lat)) * COS(RADIANS(lat)) *  POWER(SIN(RADIANS(@lng - lng)/ 2), 2) )) as dist
FROM t_merch_info 
having dist < @dist
ORDER BY dist; 

计算所有的数据，200w数据比较慢， 所以可以添加一些范围的搜索条件，圈出大致范围后进行查询.

首先要计算出经纬度范围,需要进行三角函数计算:  

SET @lat_offset = DEGREES(@dist / @earth_radius);-- 纬度最大偏离
SET @lng_offset = DEGREES(@dist / (@earth_radius * COS(RADIANS(@lat))));-- 经度最大偏离
SET @lat_left = @lat - @lat_offset;
SET @lat_right = @lat + @lat_offset;
SET @lng_left = @lng - @lng_offset;
SET @lng_right = @lng + @lng_offset;

完整的查询语句：  

SET @earth_radius = 6366.564864;-- 地球半径，单位km
SET @lat = 41.2549101472;-- 查找中心距离
SET @lng = 122.1261217562;
SET @dist = 20;-- 查找的最大距离 ，km
SET @lat_offset = DEGREES(@dist / @earth_radius);-- 纬度最大偏离
SET @lng_offset = DEGREES(@dist / (@earth_radius * COS(RADIANS(@lat))));-- 经度最大偏离
SET @lat_left = @lat - @lat_offset;
SET @lat_right = @lat + @lat_offset;
SET @lng_left = @lng - @lng_offset;
SET @lng_right = @lng + @lng_offset;

SELECT mid, lat, lng, mtype
    , @earth_radius * 2 * ASIN(SQRT(POWER(SIN(RADIANS(@lat - lat) / 2), 2) + COS(RADIANS(@lat)) * COS(RADIANS(lat)) * POWER(SIN(RADIANS(@lng - lng) / 2), 2))) AS dist
FROM t_merch_info
WHERE lat BETWEEN @lat_left AND @lat_right
    AND lng BETWEEN @lng_left AND @lng_right

HAVING dist <@dist
ORDER BY dist
LIMIT 100

 

通过结果可以看出查询结果有很大的改善,但是事实上我们还可以进行优化,因为我们现在所操作的是没有建立索引的数据表 

对t_merch_info表的lat,lng字段 设置一个B-tree索引: 

ALTER TABLE `t_merch_info` ADD INDEX `lat_lng` USING BTREE (`lat`, `lng`) ;   

添加索引后，查询的速度会更快。