mysql 实现按经纬度进行距离排序

客官°小女子只卖身不卖艺 2021-10-19 15:00 1698阅读 0赞

今天，日月教大家如何使用mysql根据经纬度实现距离排序搜索查询，话不多说，往下看。  
为了节省时间，我们在[SpringBoot 集成 mybatis的四种方式][SpringBoot _ mybatis] 中方式二的项目基础上修改。

**一、创建数据表，插入测试数据**

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDYyMzczNg_size_16_color_FFFFFF_t_70]

经纬度获取方式：[https://lbs.amap.com/console/show/picker][https_lbs.amap.com_console_show_picker]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDYyMzczNg_size_16_color_FFFFFF_t_70 1]

根据上图，我们可以明显的看出，根据当前所在地按距离排序的话，应该是  
武汉–>黄冈–>咸宁–>九江 这样的顺序。

**二、创建mysql函数**  
计算两个经纬度之间距离的 数据库函数  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDYyMzczNg_size_16_color_FFFFFF_t_70 2]

CREATE FUNCTION `lat_lng_distance` (lat1 FLOAT, lng1 FLOAT, lat2 FLOAT, lng2 FLOAT)
    RETURNS FLOAT
    DETERMINISTIC
    BEGIN
        RETURN 6371 * 2 * ASIN(SQRT(
            POWER(SIN((lat1 - abs(lat2)) * pi()/180 / 2),
            2) + COS(lat1 * pi()/180 ) * COS(abs(lat2) *
            pi()/180) * POWER(SIN((lng1 - lng2) *
            pi()/180 / 2), 2) ));
    END

**三、编写测试代码**

**SpringBootController.java**

package com.chenqi.springboot.controller;
    import com.chenqi.springboot.entity.Store;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.GetMapping;
    import org.springframework.web.bind.annotation.RequestParam;
    import org.springframework.web.bind.annotation.RestController;
    import com.chenqi.springboot.service.StoreService;
    import java.util.List;
    @RestController
    public class SpringBootController {
        
        @Autowired
        StoreService storeService;
        @GetMapping("/getStores")
        public List<Store> getStores(@RequestParam("longitude") String longitude,
                                     @RequestParam("latitude") String latitude){
            return storeService.getStores(longitude,latitude);
        }
    }

**StoreService.java**

package com.chenqi.springboot.service;
    import com.chenqi.springboot.entity.Store;
    import java.util.List;
    public interface StoreService {
        List<Store> getStores(String longitude, String latitude);
    }

**StoreServiceImpl.java**

package com.chenqi.springboot.service.impl;
    import com.chenqi.springboot.entity.Store;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;
    import com.chenqi.springboot.dao.StoreMapper;
    import com.chenqi.springboot.service.StoreService;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    @Service
    public class StoreServiceImpl implements StoreService {
        
        @Autowired
        StoreMapper storeMapper;
        @Override
        public List<Store> getStores(String longitude, String latitude) {
            //距离排序 范围500公里
            Map<String,String> map = new HashMap<String,String>();
            map = lonLatCalculation(Double.valueOf(longitude),Double.valueOf(latitude),500000);
            map.put("longitude",longitude);
            map.put("latitude",latitude);
            return storeMapper.getStores(map);
        }
        /**
         * @Author chenqi
         * @Description 根据经纬度和半径 计算
         * @Date 14:19 2019/1/2
         * @Param [longitude 经度, latitude 纬度, raidus 搜索半径 m]
         * @return java.util.Map<java.lang.String,java.lang.String>
         **/
        public static Map<String,String> lonLatCalculation(Double longitude, Double latitude, Integer raidus){
            Map<String,String> map = new HashMap<String,String>();
            // 赤道周长24901英里 1609是转换成米的系数
            Double degree = (24901 * 1609) / 360.0;
            double raidusMile = raidus;
            Double dpmLat = 1 / degree;
            Double radiusLat = dpmLat * raidusMile;
            Double minLat = latitude - radiusLat;
            Double maxLat = latitude + radiusLat;
            Double mpdLng = degree * Math.cos(latitude * (Math.PI / 180));
            Double dpmLng = 1 / mpdLng;
            Double radiusLng = dpmLng * raidusMile;
            Double minLng = longitude - radiusLng;
            Double maxLng = longitude + radiusLng;
            //return new double[] { minLat, minLng, maxLat, maxLng };
            map.put("minLat",minLat.toString());
            map.put("maxLat",maxLat.toString());
            map.put("minLng",minLng.toString());
            map.put("maxLng",maxLng.toString());
            return map;
        }
    }

**StoreMapper.java**

package com.chenqi.springboot.dao;
    import com.chenqi.springboot.entity.Store;
    import org.apache.ibatis.annotations.Mapper;
    import java.util.List;
    import java.util.Map;
    @Mapper
    public interface StoreMapper {
        List<Store> getStores(Map<String,String> map);
    }

**StoreMapper.xml**

<?xml version="1.0" encoding="UTF-8" ?>
    <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
    <mapper namespace="com.chenqi.springboot.dao.StoreMapper" >
          <select id="getStores" parameterType="java.util.Map" resultType="com.chenqi.springboot.entity.Store">
            select s.store_id as storeId,
            s.store_name as storeName,
            s.longitude as longitude,
            s.latitude as latitude
            <if test="longitude != null and longitude != ''">
                , ROUND(lat_lng_distance(#{latitude}, #{longitude}, latitude, longitude), 2) as distance
            </if>
            from store s where 1=1
            <if test="longitude != null and longitude != ''">
                and s.latitude > #{minLat} and s.latitude < #{maxLat}
                and s.longitude > #{minLng} and s.longitude < #{maxLng}
                order by distance asc
            </if>
        </select>
    </mapper>

**Store.java**

package com.chenqi.springboot.entity;
    import lombok.Data;
    import java.io.Serializable;
    /**
     * @author chenqi
     * @version V1.0
     * @ClassName: Store
     * @Description: 店铺表
     * @Date 2019/1/13 12:37
     */
    @Data
    public class Store implements Serializable {
        private static final long serialVersionUID = -7541432682774092989L;
        /** 店铺id */
        private Long storeId;
        /** 店铺名称 */
        private String storeName;
        /** 经度 */
        private String longitude;
        /** 纬度 */
        private String latitude;
        /** 距离 */
        private String distance;
    }

**四、启动项目，测试接口**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDYyMzczNg_size_16_color_FFFFFF_t_70 3]

浏览器或者postman请求：[http://localhost:8080/getStores?longitude=114.304327&latitude=30.594048][http_localhost_8080_getStores_longitude_114.304327_latitude_30.594048]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDYyMzczNg_size_16_color_FFFFFF_t_70 4]

我们以武汉市政府的经纬度作为当前位置进行查询搜索。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDYyMzczNg_size_16_color_FFFFFF_t_70 5]  
可以看到，数据成功按照距离的远近进行排序返回了，distance是距离，单位km。  
至此，我们就简单的根据mysql函数实现了距离排序查询搜索的功能。

如果该文章有帮助到您，就留言点个赞吧！您的支持与肯定是我持续更新最大的动力。

[SpringBoot _ mybatis]: https://blog.csdn.net/weixin_40623736/article/details/97677324
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDYyMzczNg_size_16_color_FFFFFF_t_70]: /images/20211019/9e023c5c63824fec9d6a17ab126c9959.png
[https_lbs.amap.com_console_show_picker]: https://lbs.amap.com/console/show/picker
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDYyMzczNg_size_16_color_FFFFFF_t_70 1]: /images/20211019/749b65be15d6416eb3f785c6edacc3b4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDYyMzczNg_size_16_color_FFFFFF_t_70 2]: /images/20211019/952cd54bcf2549318ebe979e44fa6fc5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDYyMzczNg_size_16_color_FFFFFF_t_70 3]: /images/20211019/2d208893b31b42fab60b5d6899582d03.png
[http_localhost_8080_getStores_longitude_114.304327_latitude_30.594048]: http://localhost:8080/getStores?longitude=114.304327&latitude=30.594048
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDYyMzczNg_size_16_color_FFFFFF_t_70 4]: /images/20211019/13dd420031134d3b9ebe3362295d4fe3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDYyMzczNg_size_16_color_FFFFFF_t_70 5]: /images/20211019/6e88ac9544e6423b9513800e139e3170.png