MySql教程

MySQL附近功能

本文主要是介绍MySQL附近功能,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

MySQL 实现附近人功能纯属学习,并不好用,推荐使用 Search,方案对比:

方案

优势

缺点

Mysql外接正方形

逻辑清晰,实现简单,支持多条件筛选

效率较低,不适合大数据量,不支持按距离排序

Mysql+Geohash

借助索引有效提高效率,支持多条件筛选

不支持按距离排序,存在数据库瓶颈

Redis+Geohash

效率高,集成便捷,支持距离排序

不适合复杂对象存储,不支持多条件查询

ElasticSearch

集成了 Redis 和 MySQL 的有点既可以条件筛选,距离排序,也可以复杂对象的存储

 

 

MySQL依然可以实现附近的人的功能,两种方案,

方案一:Mysql+外接正方形

实现思路:

以当前用户为圆心,以给定距离为半径画圆,那么在这个圆内的所有用户信息就是符合结果的信息,直接检索圆内的用户坐标难以实现,

我们可以通过获取这个圆的外接正方形。

通过外接正方形,获取经度和纬度的最大最小值,根据最大最小值可以将坐标在正方形内的用户信息搜索出来。

此时在外接正方形中不属于圆形区域的部分就属于多余的部分,这部分用户信息距离当前用户(圆心)的距离必定是大于给定半径的,

故可以将其剔除,最终获得指定范围内的附近的人

 

存储经纬度坐标用的数类型:数据类型为double(10,6)

 

将地理位置的经度、纬度 分开存储在数据库中:

 

 

 

根据定位中心和半径范围,得出经度的最大值最小值,纬度的最大值最小值,即一个正方形范围,得到范围内的对象集合,

再遍历对象集合,分别计算每个对象的距离是否满足距离小于等于半径,过滤掉不满足条件的对象,即可实现附近的人

 

 

求距离 S 半径内的对象时有一部分对象时在圆之外的四个三角区域,若换个思路,求S1 半径范围内的对象

这样得到的对象就都是范围内的对象,若 S1 范围内的对象不够再查找 S 范围的对象进行过滤

 

 

 

 

 计算给定中心坐标,半径范围内的经纬度范围(单位需要统一,用米/或者KM都可以)

 

public class GeoUtils {
   //地球半径(米)
   private static final double EARTH_RADIUS = 6371000d;
   //弧度
   public static final double RADIANS_TO_DEGREES = 180 / Math.PI;
   public static final double DEGREES_TO_RADIANS = Math.PI / 180;
 
 
   /**
    * 根据中心点经纬度,计算半径范围内的经纬度范围
    *
    * @param centerLon 中心点经度
    * @param centerLat 中心点纬度
    * @param dist      半径范围(米)
    * @return
    */
   public static Map<String, Double> findPosition(double centerLon, double centerLat, double dist) {
 
      //先计算查询点的经纬度范围
      double lonRad = 2 * Math.asin(Math.sin(dist / (2 * EARTH_RADIUS)) / Math.cos(centerLat * Math.PI / 180));
      //角度转为弧度
      lonRad = lonRad * RADIANS_TO_DEGREES;
      double latRad = dist / EARTH_RADIUS;
      latRad = latRad * 180 / Math.PI;
      //经度最小值
      double minLon = centerLon - lonRad;
      //经度最大值
      double maxLon = centerLon + lonRad;
      //纬度最小值
      double minLat = centerLat - latRad;
      //纬度最大值
      double maxLat = centerLat + latRad;
 
      HashMap<String, Double> geoMap = new HashMap<>();
      geoMap.put("minLat", minLat);
      geoMap.put("maxLat", maxLat);
      geoMap.put("minLon", minLon);
      geoMap.put("maxLon", maxLon);
      return geoMap;
   }
   
   /**
    * 求地球表面两点间的距离
    *
    * @param centerLon 中心点经度
    * @param centerLat 中心点纬度
    * @param targetLon 目标经度
    * @param targetLat 目标纬度
    * @return
    */
   public static double getDistance(double centerLon, double centerLat, double targetLon, double targetLat) {
 
      //计算中心点经纬度弧度
      double centerLonRad = centerLon * DEGREES_TO_RADIANS;
      double centerLatRad = centerLat * DEGREES_TO_RADIANS;
      //计算目标点经纬度弧度
      double targetLonRad = targetLon * DEGREES_TO_RADIANS;
      double targetLatRad = targetLat * DEGREES_TO_RADIANS;
      //计算经纬度的差
      double diffX = Math.abs(centerLonRad - targetLonRad);
      double diffY = Math.abs(centerLatRad - targetLatRad);
      //计算正弦
      double hsinX = Math.sin(diffX / 2);
      double hsinY = Math.sin(diffY / 2);
      //计算余弦
      double centerLatRad_cos = Math.cos(centerLatRad);
      double targetLatRad_cos = Math.cos(targetLatRad);
 
      double h = power(hsinY, 2) + (centerLatRad_cos * targetLatRad_cos * power(hsinX, 2));
      //两点的距离
      double dist = EARTH_RADIUS * 2 * Math.atan2(Math.sqrt(h), Math.sqrt(1 - h));
      return dist;
   }
 
   /**
    * 经纬度剔除小数点(直接剔除不舍入),保留6位
    *
    * @param number 经度/纬度
    * @return
    */
   public static Double geoRound6Pla(Double number) {
      BigDecimal numDigd = new BigDecimal(number);
      number = numDigd.setScale(6, BigDecimal.ROUND_DOWN).doubleValue();
      return number;
   }
 
   public static void main(String[] args) {
      //中心点经纬度
      double x1 = 102.604646;
      double y1 = 24.239485;
 
      //目标点经纬度
      double x2 = 103.634895;
      double y2 = 24.778173;
 
      Map<String, Double> position = GeoUtils.findPosition(x1, y1, 130000 );
      double distance = GeoUtils.getDistance(x1, y1, x2, y2);
      System.out.println(position);
      System.out.println(distance);
   }
}

打印:

{
  minLon=101.32248517333221, 
  maxLat=25.40860308769435, 
  minLat=23.070366912305648, 
  maxLon=103.8868068266678
}
 
120220.59030376052

 


使用示例

Long distance = NOTICE_DISTANCE;
   publishNotice.setGeoLat(null);
   publishNotice.setGeoLon(null);
   Map<String, Double> geoMap = GeoUtils.findPosition(X, Y, distance);
   Double minLat = geoMap.get("minLat");
   Double maxLat = geoMap.get("maxLat");
   Double minLon = geoMap.get("minLon");
   Double maxLon = geoMap.get("maxLon");
   //保留6位小数点(尽量取多的小数点,保证计算精度)
   minLat = GeoUtils.geoRound6Pla(minLat);
   maxLat = GeoUtils.geoRound6Pla(maxLat);
   minLon = GeoUtils.geoRound6Pla(minLon);
   maxLon = GeoUtils.geoRound6Pla(maxLon);
   //组装SQL语句
   query.between("geo_lon", minLon, maxLon)
         .between("geo_lat", minLat, maxLat);
   iPage = page(page, query);
   noticeList = iPage.getRecords();
   noticeList = this.distFilter(noticeList, X, Y, distance);
}

过滤距离超过指定距离的

/**
 * 计算距离,过滤超出距离范围的元素
 *
 * @param noticeList 通告集合
 * @param centerLon  中心位置经度
 * @param centerLat  中心位置纬度
 * @param distance   距离半径
 */
private List<PublishNotice> distFilter(List<PublishNotice> noticeList, Double centerLon, Double centerLat, Long distance) {
   noticeList = noticeList.parallelStream()
         .map(notice -> {
            Double geoLon = notice.getGeoLon();
            Double geoLat = notice.getGeoLat();
            double dist = GeoUtils.getDistance(centerLon, centerLat, geoLon, geoLat);
            notice.setDistance(Math.round(dist));
            return notice;
         }).filter(notice -> notice.getDistance() <= distance)
         .sorted(Comparator.comparing(PublishNotice::getDistance))
         .collect(Collectors.toList());
   return noticeList;
}

 


使用框架依赖不用自己写计算工具类:

在实现附近的人搜索中,需要根据位置经纬度点,进行一些距离和范围的计算,比如求球面外接正方形的坐标点,球面两坐标点的距离等,可以引入Spatial4j 库。

<dependency>
    <groupId>com.spatial4j</groupId>
    <artifactId>spatial4j</artifactId>
    <version>0.5</version>
</dependency>

建表举例:

CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL COMMENT '名称',
`longitude` double DEFAULT NULL COMMENT '经度',
`latitude` double DEFAULT NULL COMMENT '纬度',
`create_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
public class User {
    private int id;
    private String name;
    private double longitude;//经度,
    private double latitude;//纬度,
    private LocalDateTime createTime;//创建时间,
}

Controller方法

public class GeoTestController {
 
    private SpatialContext spatialContext = SpatialContext.GEO;
 
    /**
     * 获取附近x米的人
     *
     * @param distance 距离范围 单位km
     * @param userLng  当前经度
     * @param userLat  当前纬度
     * @return json
     */
    @GetMapping("/nearby")
    public String nearBySearch(@RequestParam("distance") double distance,
                               @RequestParam("userLng") double userLng,
                               @RequestParam("userLat") double userLat) {
        //1.获取外接正方形
        Rectangle rectangle = getRectangle(distance, userLng, userLat);
        //2.获取位置在正方形内的所有用户
        List<User> users = userMapper.selectUser(rectangle.getMinX(), rectangle.getMaxX(), rectangle.getMinY(), rectangle.getMaxY());
        //3.剔除半径超过指定距离的多余用户
        users = users.stream()
                .filter(a -> getDistance(a.getLongitude(), a.getLatitude(), userLng, userLat) <= distance)
                .collect(Collectors.toList());
        return JSON.toJSONString(users);
    }
 
    /**
     * 中心坐标和半径距离,获取方形经纬度范围
     *
     * @param distance 距离半径
     * @param lon      经度
     * @param lat      纬度
     * @return
     */
    private Rectangle getRectangle(double distance, double lon, double lat) {
        Rectangle rectangle = spatialContext.getDistCalc()
                .calcBoxByDistFromPt(spatialContext.makePoint(lon, lat),
                        distance * DistanceUtils.KM_TO_DEG, spatialContext, null);
        return rectangle;
    }
}

 

 

 Mapper文件的SQL语句:

<select id="selectUser" resultMap="BaseResultMap">
    SELECT * FROM user
    WHERE 1=1
    and (longitude BETWEEN ${minlng} AND ${maxlng})
    and (latitude BETWEEN ${minlat} AND ${maxlat})
</select>

 


方案二:Mysql+geohash

引入了geohash算法,同时在查询上借助索引

geohash被广泛应用于位置搜索类的业务中,本文不对它进行展开说明,有兴趣的同学可以看一下这篇博客:  https://www.cnblogs.com/LBSer/p/3310455.html

GeoHash算法将经纬度坐标点编码成一个字符串,距离越近的坐标,转换后的geohash字符串越相似,例如下表数据:

用户

经纬度

Geohash字符串

小明

116.402843,39.999375

wx4g8c9v

小华

116.3967,39.99932

wx4g89tk

小张

116.40382,39.918118

wx4g0ffe

其中根据经纬度计算得到的geohash字符串,不同精度(字符串长度)代表了不同的距离误差。具体的不同精度的距离误差可参考下表:

geohash码长度

宽度

高度

1

5,009.4km

4,992.6km

2

1,252.3km

624.1km

3

156.5km

156km

4

39.1km

19.5km

5

4.9km

4.9km

6

1.2km

609.4m

7

152.9m

152.4m

8

38.2m

19m

9

4.8m

4.8m

10

1.2m

59.5cm

11

14.9cm

14.9cm

12

3.7cm

1.9cm

实现思路:

 

  • 使用Mysql存储用户信息,其中包括用户的经纬度信息和geohash字符串。
  • 添加新用户时计算该用户的geohash字符串,并存储到用户表中
  • 当要查询某一gps附近指定距离的用户信息时,通过比对geohash误差表确定需要的geohash字符串精度
  • 计算获得某一精度的当前坐标的geohash字符串,通过WHERE geohash Like 'geohashcode%'来查询数据集
  • 如果geohash字符串的精度远大于给定的距离范围时,查询出的结果集中必然存在在范围之外的数据

计算两点之间距离,对于超出距离的数据进行剔除。

建表结构:

CREATE TABLE `user_geohash` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL COMMENT '名称',
  `longitude` double DEFAULT NULL COMMENT '经度',
  `latitude` double DEFAULT NULL COMMENT '纬度',
  `geo_code` varchar(64) DEFAULT NULL COMMENT '经纬度所计算的geohash码',
  `create_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `index_geo_hash` (`geo_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Java Bean

public class UserGeohash {
 
    private int id;
    private String name;
    private double longitude;//经度,
    private double latitude;//纬度,
    private String geoCode;
    private LocalDateTime createTime;//创建时间,
 }

控制层

public class GeoHashController {
 
    private SpatialContext spatialContext = SpatialContext.GEO;
 
    /***
     * 添加用户
     * @return
     */
    @PostMapping("/addUser")
    public boolean add(@RequestBody UserGeohash user) {
        //默认精度12位
        String geoHashCode = GeohashUtils.encodeLatLon(user.getLatitude(), user.getLongitude());
        return userGeohashService.save(user.setGeoCode(geoHashCode), user.setCreateTime(LocalDateTime.now()));
    }
 
 
    /**
     * 获取附近指定范围的人
     *
     * @param distance 距离范围 单位km
     * @param len      geoHash的精度
     * @param userLng  当前经度
     * @param userLat  当前纬度
     * @return json
     */
    @GetMapping("/nearby")
    public String nearBySearch(@RequestParam("distance") double distance,
                               @RequestParam("len") int len,
                               @RequestParam("userLng") double userLng,
                               @RequestParam("userLat") double userLat) {
        //1.根据要求的范围,确定geoHash码的精度,获取到当前用户坐标的geoHash码
        String geoHashCode = GeohashUtils.encodeLatLon(userLat, userLng, len);
        QueryWrapper<UserGeohash> queryWrapper = new QueryWrapper<UserGeohash>()
                .likeRight("geo_code", geoHashCode);
        //2.匹配指定精度的geoHash码
        List<UserGeohash> users = userGeohashService.list(queryWrapper);
        //3.过滤超出距离的
        users = users.stream()
                .filter(a -> getDistance(a.getLongitude(), a.getLatitude(), userLng, userLat) <= distance)
                .collect(Collectors.toList());
        return JSON.toJSONString(users);
    }
 
    /***
     * 球面中,两点间的距离
     * @param longitude 经度1
     * @param latitude  纬度1
     * @param userLng   经度2
     * @param userLat   纬度2
     * @return 返回距离,单位km
     */
    private double getDistance(Double longitude, Double latitude, double userLng, double userLat) {
        double distance = spatialContext.calcDistance(spatialContext.makePoint(userLng, userLat),
                spatialContext.makePoint(longitude, latitude)) * DistanceUtils.DEG_TO_KM;
        return distance;
    }
}

通过上面几步,就可以实现这一业务场景,不仅提高了查询效率,并且保护了用户的隐私,不对外暴露坐标位置。并且对于同一位置的频繁请求,如果是同一个geohash字符串,可以加上缓存,减缓数据库的压力。

 

边界优化问题:

geohash算法将地图分为一个个矩形,对每个矩形进行编码,得到geohash码,但是当前点与待搜索点距离很近但是恰好在两个区域,用上面的方法则就不适用了。解决这一问题的办法:获取当前点所在区域附近的8个区域的geohash码,一并进行筛选。

 

如何求解附近的8个区域的geohash码可参考 : https://blog.csdn.net/dokd229933/article/details/47021515  了解了思路,这里我们可以使用第三方开源库ch.hsr.geohash来计算,通过maven引入

<dependency>
    <groupId>ch.hsr</groupId>
    <artifactId>geohash</artifactId>
    <version>1.0.10</version>
</dependency>
/**
 * 获取附近指定范围的人
 *
 * @param distance 距离范围 单位km
 * @param len      geoHash的精度
 * @param userLng  当前经度
 * @param userLat  当前纬度
 * @return json
 */
@GetMapping("/nearby")
public String nearBySearch(@RequestParam("distance") double distance,
                           @RequestParam("len") int len,
                           @RequestParam("userLng") double userLng,
                           @RequestParam("userLat") double userLat) {
 
    //1.根据要求的范围,确定geoHash码的精度,获取到当前用户坐标的geoHash码
    GeoHash geoHash = GeoHash.withCharacterPrecision(userLat, userLng, len);
    //2.获取到用户周边8个方位的geoHash码
    GeoHash[] adjacent = geoHash.getAdjacent();
 
    QueryWrapper<UserGeohash> queryWrapper = new QueryWrapper<UserGeohash>()
            .likeRight("geo_code",geoHash.toBase32());
    Stream.of(adjacent).forEach(a -> queryWrapper.or().likeRight("geo_code",a.toBase32()));
 
    //3.匹配指定精度的geoHash码
    List<UserGeohash> users = userGeohashService.list(queryWrapper);
    //4.过滤超出距离的
    users = users.stream()
            .filter(a ->getDistance(a.getLongitude(),a.getLatitude(),userLng,userLat)<= distance)
            .collect(Collectors.toList());
    return JSON.toJSONString(users);
}

 

这篇关于MySQL附近功能的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!