根据经纬度查询最近距离，mysql查询经纬度附近范围

最新推荐文章于 2025-06-22 21:29:32 发布

tominto333

最新推荐文章于 2025-06-22 21:29:32 发布

阅读量1k

点赞数

CC 4.0 BY-SA版权

分类专栏：随笔文章标签：经度纬度 mysql 最近距离

本文链接：https://blog.youkuaiyun.com/link123link/article/details/98648285

随笔专栏收录该内容

19 篇文章

订阅专栏

该博客介绍了如何使用MySQL根据经纬度查询最近的距离。通过提供一个包含多个经纬度坐标的数据集，作者展示了如何计算这些点与给定点之间的距离，并按距离进行排序，找出最近的点。文章中包含两种不同的距离计算方法，并给出了SQL查询示例来筛选一定范围内最近的坐标。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

public class Test{

    private static List<LocalAddress> ilist = new ArrayList<LocalAddress>();
    public static void main(String[] args) {
        Test test3 = new Test();
        Double localDouble  = 0.0;

        //定义一个二维数组存放经纬度
        Double[][] doubles = {
                { 22.6036906766, 113.8793209706 }, 
                { 22.5205569549, 113.9394272419 }, 
                { 22.6494305358, 114.0411629507 }, 
                { 22.5255080247,114.0384880750 },  
                { 22.5246432654,114.0720634923 },  
                { 22.5963291708,113.9689558477 },   
                { 22.5509638661,113.9358124450 },   
                { 22.5509638661,113.9358124450 },   
                { 22.5254496086,114.0555439122 },   
                { 22.7789489191,114.3063672776 },   
                { 22.5240537775,114.0641924822 },   
                { 22.5141408858,114.0624887496 } }; 

        //门店的经纬度
        Double[] initlocal = {22.539899298946577,113.95296375395431 };


        for (int i = 0; i < doubles.length; i++) {
            Double z = test3.getDistance(doubles[i][0], doubles[i][1],initlocal[0], initlocal[1]);
            System.out.println(z);
            //获取最短距离后把经纬度和距离存放到对象中
            LocalAddress localaddress = new LocalAddress(doubles[i][0], doubles[i][1], z);
            //将对象用list保存
            ilist.add(localaddress);
        }

        List<LocalAddress> shotlocal =  getLocalList();
        Double lat=shotlocal.get(0).getLat();
        Double lon= shotlocal.get(0).getLon();
        localDouble = shotlocal.get(0).getDistance();
        System.err.println("最近的距离：" + localDouble + "。对应的经纬是：" +"(" + lat + "," + lon + ")");
    }

    /*第一种求最近距离 ===================================================*/
    /**
     * 获取最短的距离
     * @param lat
     * @param lon
     * @param initlat
     * @param initlon
     * @return
     */
    public Double GetShotLocal(Double lat, Double lon, Double initlat,Double initlon) {
        Double x = (initlat - lat) * (initlat - lat);
        Double y = (initlon - lon) * (initlon - lon);
        Double z = Math.sqrt(x + y);
        return z;
    }

    /**
     * 对List<LocalAddress> 进行排序
     * @return
     */
    public static List<LocalAddress> getLocalList()  {
        Collections.sort(ilist, new Comparator<LocalAddress>() {
            @Override
            public int compare(LocalAddress arg0, LocalAddress  arg1) {
                Double double1 = arg0.getDistance();
                Double double2 = arg1.getDistance();
                if(double1>double2){
                    return 1;
                }else if (double1 == double2) {
                    return 0;
                }else {
                    return -1;
                }
            }
        });
        return ilist;
    }

/*    第二种求最近距离                         ==================================================================*/
    private static final double EARTH_RADIUS = 6378.137;//地球半径,单位千米
    private static double rad(double d)
    {
        return d * Math.PI / 180.0;
    }

    /**
     *
     * @param lat1 第一个纬度
     * @param lng1 第一个经度
     * @param lat2 第二个纬度
     * @param lng2 第二个经度
     * @return 两个经纬度的距离
     */
    public static double getDistance(double lat1,double lng1,double lat2,double lng2)
    {
        double radLat1 = rad(lat1);
        double radLat2 = rad(lat2);
        double a = radLat1 - radLat2;
        double b = rad(lng1) - rad(lng2);

        double s = 2 * Math.asin(Math.sqrt(Math.pow(Math.sin(a/2),2) +
                Math.cos(radLat1)*Math.cos(radLat2)*Math.pow(Math.sin(b/2),2)));
        s = s * EARTH_RADIUS;
        s = Math.round(s * 10000) / 10000;
        return s;

    }



}


entity

public class LocalAddress {

    private Double lat;     //纬度
    private Double lon;     //经度
    private Double distance;    //最近距离

    public LocalAddress() {
        // TODO Auto-generated constructor stub
    }

    public LocalAddress(Double lat, Double lon, Double distance) {
        super();
        this.lat = lat;
        this.lon = lon;
        this.distance = distance;
    }
    
}

<!--根据经纬度查询最近距离范围-->
<select id="SelectDistanceByLat" parameterType="map" resultType="map">
   SELECT
   *,(
   6371 * acos (
   cos ( radians(#{lat}) )
   * cos( radians( lat ) )
   * cos( radians( lng ) - radians(#{lng}) )
   + sin ( radians(#{lat}) )
   * sin( radians( lat ) )
   )
   ) AS distance
   FROM machine
   HAVING   3 > distance
   ORDER BY distance
   LIMIT 0 , 20;
</select>