mysql 距离计算排序

应用场景为已知某用户的地理位置，即经纬度，获取他附近的商家信息按距离远近排序

从mysql5.6.1以后开始支持 ST_Distance 函数用来计算对象的空间关系，并且支持地理参数类型为 Point和Point，或point和MultiPoint（不限制顺序）

从mysql8.0.18开始支持所有几何类型的地理SRS自变量的距离计算

而从mysql5.7.6以后新增加一个方法用来测球面上两点距离

ST_Distance_Sphere(g1, g2 [, radius]) radius指球体半径，缺省值为地球半径

SRS： 用来描述几何的定义空间被称为空间参照系

废话说了这么多直接上干货

例如：

 沈丘县周口市中国466300
         "geometry" : {
            "location" : {
               "lat" : 33.4096812,
               "lng" : 115.0984158
            }

拱墅区杭州市中国310000
         "geometry" : {
            "location" : {
               "lat" : 30.2734437,
               "lng" : 120.155262
            }

计算两地之间的距离单位米

select ST_Distance_sphere(point(120.155262, 30.2734437), point(115.0984158, 33.4096812));

与工具测算的相差27米，误差也算可以接收，误差的原因猜测是半径取值问题，根据公式计算

select ST_Distance_sphere(point(0, 0), point(180, 0)) /pi(); 得出ST_Distance_sphere 函数的地球半径取值为 6370986 米

如果是从程序中处理也可以使用字符串的形式

select ST_Distance_sphere(PointFromText('point(120.155262 30.2734437)'),PointFromText('point(115.0984158 33.4096812)'));

注意：point坐标的第一个参数是经度（-180,180），第二个参数是维度（-90,90）实际此值是(-85.05112878,85.05112878)，mysql中命令中point坐标用 逗号 分隔，字符串的point 用 空格 分隔

另外一点是关于经纬的存储精度问题

维基百科中精确到固定位数可辨识的程度表

经度取值范围为[0,180]，纬度的取值范围为[0,90]，一般情况下辨识度达到人 这一级别就可以，即6位小数，因此选择为

longitudes（经度）: decimal(9,6)
latitudes （纬度）: decimal(8,6)

经度应该比纬度是要多存一位。

另外如果需要达到专业的测量结果，可以选用最高精度：

longitudes（经度）: decimal(11,8)
latitudes （纬度）: decimal(10,8)

维基百科说明decimal就是用于存储地理位置信息的，所以选择 decimal 作为存储类型也是可以经得住推敲的；但实际情况使用decimal会比float字段查询耗费更多时间

经过1000万条的数据库对点（20.000006, 10.000005）获取某长度范围的数据耗时对比测试

select t.* from(
select id,lat,lng,ST_Distance_sphere(point(lng,lat), point(20.000006, 10.000005)) 'dis' from partner_store_song1000m
)t 
where t.dis < 1000
order by t.dis desc limit 10;

mysql字段类型	1公里内数据耗时	10公里内数据耗时	100公里内数据耗时
decimal(11,8)	7.9秒	7.9秒	8.3秒
float(11,8)	5.7秒	5.8秒	6秒

 

 

 

 

 可以看出使用decimal字段会比float更耗时，而实际业务不要求很高的精度，所以选择float，这种字段在数据量为100万条数据时，获取100KM内的距离排序取10条只需要1秒左右时间

优化

在以上测试实际耗时还是令人很难接受，可以看出数据量越小则耗时越少，优化的思路是过滤掉不需要的数据后再进行运算排序

比如已知某点(20.000006, 10.000005) 获取其固定距离的最大最小经纬度，先对数据进行一次筛选，即lng = 20.000006， lat= 10.000005

$range = 180 / pi() / 6370986 * 1;     //最后1 就代表搜索 1m 之内
$lngR = $range / cos($lat * pi() / 180);
$maxLng = $lng + $lngR;//最大经度
$minLng = $lng - $lngR;//最小经度
$maxLat = $lat + $range;//最大纬度
$minLat = $lat - $range;//最小纬度

select t.* from(
select id,lat,lng,ST_Distance_sphere(point(lng,lat), point(20.000006, 10.000005)) 'dis' from partner_store_song1000
where
lng < 20.000006 + (180 / pi() / 6370986 * 10000 )/ cos(10.000005 * pi() / 180)
 and lng > 20.000006 - (180 / pi() / 6370986 * 10000 )/ cos(10.000005 * pi() / 180)
 and lat < 10.000005 + 180 / pi() / 6370986 * 10000
 and lat > 10.000005 - 180 / pi() / 6370986 * 10000
)t 
where t.dis < 10000
order by t.dis limit 10;

1000万条数据检索结果

mysql字段类型	1公里内数据耗时	10公里内数据耗时	100公里内数据耗时
float(11,8)	20豪秒	110豪秒	0.9秒