无半径MariaDB最近200个位置的距离公式

Question

我有MariaDB，服务器版本: 10.0.23-MariaDB，有纬度和经度列(浮动10,6)，加上一个从纬度和经度列计算的geo_location列(几何学)。

我想从一个人身上找到最近的200个人。中心的人具有传递给查询的纬度和经度。有没有办法在没有半径的情况下做到这一点？所以，如果人口密度很高，半径就会很小。如果人口密度很低，那么半径就会很大。

大约有400万行，它需要尽可能快。行可以首先根据它们所在的县进行过滤。有的县超大，人口密度低，有的县小，人口密度高。我需要最快的方法找到最近的200人。

Answer 1

SELECT *, ST_DISTANCE(geo_location, POINT(lon, lat)) AS distance 
FROM geotable 
ORDER by distance DESC
LIMIT 200;

坏消息是它将非常慢，因为st_distance()没有使用任何空间索引。您应该尝试通过使用最大半径来选择较少的记录来限制查询：

set @dist = 100;
set @rlon1 = lon-@dist/abs(cos(radians(lat))*69);
set @rlon2 = lon+@dist/abs(cos(radians(lat))*69);
set @rlat1 = lat-(@dist/69);
set @rlat2 = lat+(@dist/69); 

SELECT *, ST_DISTANCE(geo_location, POINT(lon, lat)) AS distance 
FROM geotable 
WHERE ST_WITHIN(geo_location,ENVELOPE(LINESTRING(point(@rlon1, @rlat1), point(@rlon2, @rlat2)))) 
ORDER by distance DESC 
LIMIT 200;

或者，如果你有每个国家的多边形坐标，你可以用它代替最大半径。

Answer 2

小数点6位足够好(16厘米/ 0.5英尺)，但FLOAT (1.7m / 5.6英尺)降低了一些精度。将(M,N)连接到FLOAT或DOUBLE本质上是没有好处的；您会招致2次循环，其中一次是浪费。

由于没有“二维”索引，所以在全球范围内“查找最近的”并不是一种简单的方法。但是，通过对一个维度使用分区，对另一个维度使用集群PRIMARY KEY，您可以完成相当好的工作。

大多数解决方案的真正问题是需要在没有找到有效项的情况下命中大量磁盘块。事实上，通常超过90%的接触行是不需要的。

所有这些都在我的lat/lng博客中得到了“解决”。它将触及大约800行来获得您想要的200行，并且它们将很好地聚集在一起，所以只需要触摸几个块。它不需要对国家进行任何预先过滤，但它确实需要对谈判桌进行一些彻底的重组。而且，如果你想区分两个人互相拥抱，我建议一个缩放的INT (16毫米/ 5/8英寸)-度* 10000000。而且，FLOAT将不与PARTITIONing一起工作；INT将使用。该链接中的代码使用MEDIUMINT缩放(2.7m / 8/8英尺)，但可以更改。