MySQL 坐标批量计算及优化

分手后的思念是犯贱 2023-10-13 20:09 58阅读 0赞

#### 文章目录 ####

*   *   *  1、坐标计算
         *  2、优化

现在有一个需求，就是找出距离某用户最近的一些点，一种实现方法就是调用地图的api来计算筛选，另外一种就是在数据库中计算，考虑到地图api有并发量限制，所以选用数据库计算的方式。

#### 1、坐标计算 ####

在MySQL中计算坐标点距离，mysql5.6 提供了st\_distance函数，mysql5.7及以上提供了 **st\_distance\_sphere** 函数，可以直接查询两个经纬度之间相距多少米。

st\_distance\_sphere 函数的计算结果要比 st\_distance 函数结果更精确。

现数据库表中数据如下：

![在这里插入图片描述][211e25ecd41044f29fec93c0fe66c2f8.png]

例：计算出坐标点（102.683894, 25.038415）与表中所有坐标的距离。

select latitude,longitude,
      TRUNCATE(st_distance_sphere(point(102.683894, 25.038415),point(longitude,latitude)),2) as distance
    from h_info_coordinate
    where state=1
    ORDER BY distance;

这里用 TRUNCATE(x,2）保留了两位小数，距离单位米，结果如下

![在这里插入图片描述][25bc49ef860b4650937dc8bbc6769eff.png]

如果要筛选3000米以内，则这样写

#### 2、优化 ####

上面对于一般的计算量来说没什么问题，但是当数据量达到一定量级以后效率就会下降，对于这种情况，我们其实可以预先在坐标上下功夫，减少不必要的计算，因为实际开发中最大的应用场景是找出一定范围内的点。

我们首先来看经纬度，经度划分为360度，纬度划分为180度。

![在这里插入图片描述][479ccc0cfcd0472bb04cbf4cd093d825.png]

1度纬度之间的距离是多少公里？

纬度是平行的，因此理论上，不管在地球哪个地方，1度纬度的距离都是固定的不变的。然而地球的是略呈椭圆形，当我们从赤道到北极和南极时，度数之间产生微小的变化。  
在赤道，1度纬度的距离为110.567公里，在南北极点，1度纬度的距离为111.699公里。

所以我们可以看做是：`1度纬度为111公里`。

1度经度之间的距离是多少公里？

与纬度不同，经度之间的距离因你在地球上的位置而异。因为地球是个球体，所以肯定赤道相距最远，两极会聚成一点。

在两极极点地区，1度经度之间的距离为0，它们已经变成了一个点；在北纬40度或南纬40度处，1度经度之间的距离为85公里；赤道最长为111.321公里；

![在这里插入图片描述][8f9ba4b9207b4b7e89abd92e43b0c940.png]

综上所述，我们可以大致筛选我们需要计算的坐标，纬度一度111公里，而经度看所处地区，也可以直接按最大值111公里来筛选，所以比如我们要计算50公里范围内的坐标，则经纬度可以上下浮动大致0.5度（实际度数50/111=0.45），如下

select latitude,longitude,
      TRUNCATE(st_distance_sphere(point(102.683894, 25.038415),point(longitude,latitude)),2) as distance
    from h_info_coordinate
    where state=1
    and latitude > '25.038415' - 0.5 
    and latitude < '25.038415' + 0.5
    and longitude > '102.683894' - 0.5 
    and longitude < '102.683894' + 0.5
    ORDER BY distance;

这样，需要计算的坐标变少了，效率自然提高了。

--------------------

更多技术干货，请持续关注程序员大佬超。  
原创不易，转载请注明出处。

[211e25ecd41044f29fec93c0fe66c2f8.png]: https://img-blog.csdnimg.cn/211e25ecd41044f29fec93c0fe66c2f8.png
[25bc49ef860b4650937dc8bbc6769eff.png]: https://img-blog.csdnimg.cn/25bc49ef860b4650937dc8bbc6769eff.png
[479ccc0cfcd0472bb04cbf4cd093d825.png]: https://img-blog.csdnimg.cn/479ccc0cfcd0472bb04cbf4cd093d825.png
[8f9ba4b9207b4b7e89abd92e43b0c940.png]: https://img-blog.csdnimg.cn/8f9ba4b9207b4b7e89abd92e43b0c940.png