我目前在mysql数据库中有不到一百万个位置，都有经度和纬度信息。
我试图通过查询找到一个点和许多其他点之间的距离。它没有我想要的那么快，尤其是每秒100+次。
有没有比mysql更快的查询或更快的系统?我使用这个查询:
SELECT
name,
( 3959 * acos( cos( radians(42.290763) ) * cos( radians( locations.lat ) )
* cos( radians(locations.lng) - radians(-71.35368)) + sin(radians(42.290763))
* sin( radians(locations.lat)))) AS distance
FROM locations
WHERE active = 1
HAVING distance < 10
ORDER BY distance;
注:提供的距离单位为英里。如果需要公里，请使用6371而不是3959。
当前回答
$objectQuery = "SELECT table_master.*, ((acos(sin((" . $latitude . "*pi()/180)) * sin((`latitude`*pi()/180))+cos((" . $latitude . "*pi()/180)) * cos((`latitude`*pi()/180)) * cos(((" . $longitude . "- `longtude`)* pi()/180))))*180/pi())*60*1.1515 as distance FROM `table_post_broadcasts` JOIN table_master ON table_post_broadcasts.master_id = table_master.id WHERE table_master.type_of_post ='type' HAVING distance <='" . $Radius . "' ORDER BY distance asc";
其他回答
我真的很喜欢@Māris kiseovs的解决方案，但我喜欢许多其他人可能会从他的例子中得到Lat和lng的POINTS。在概括它时，我想我会分享它。在我的情况下，我需要找到所有的开始点，在end_point的一定半径内。
我希望这能帮助到一些人。
SELECT @LAT := ST_X(end_point), @LNG := ST_Y(end_point) FROM routes WHERE route_ID = 280;
SELECT
*,
(6371e3 * ACOS(COS(RADIANS(@LAT)) * COS(RADIANS(ST_X(start_point)))
* COS(RADIANS(ST_Y(start_point)) - RADIANS(@LNG)) + SIN(RADIANS(@LAT))
* SIN(RADIANS(ST_X(start_point))))) AS distance
FROM routes
WHERE MBRContains
(
LineString
(
Point (
@LNG + 15 / (111.320 * COS(RADIANS(@LAT))),
@LAT + 15 / 111.133
),
Point (
@LNG - 15 / (111.320 * COS(RADIANS(@LAT))),
@LAT - 15 / 111.133
)
),
POINT(ST_Y(end_point),ST_X(end_point))
)
HAVING distance < 100
ORDER By distance;
使用mysql
SET @orig_lon = 1.027125;
SET @dest_lon = 1.027125;
SET @orig_lat = 2.398441;
SET @dest_lat = 2.398441;
SET @kmormiles = 6371;-- for distance in miles set to : 3956
SELECT @kmormiles * ACOS(LEAST(COS(RADIANS(@orig_lat)) *
COS(RADIANS(@dest_lat)) * COS(RADIANS(@orig_lon - @dest_lon)) +
SIN(RADIANS(@orig_lat)) * SIN(RADIANS(@dest_lat)),1.0)) as distance;
参见:https://andrew.hedges.name/experiments/haversine/
参见:https://stackoverflow.com/a/24372831/5155484
参见:http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/
注意:LEAST用于避免null值，如https://stackoverflow.com/a/24372831/5155484上建议的注释
我需要解决类似的问题(从单点的距离过滤行)，并通过结合原始问题的答案和评论，我想出了解决方案，这对我来说完美的MySQL 5.6和5.7。
SELECT
*,
(6371 * ACOS(COS(RADIANS(56.946285)) * COS(RADIANS(Y(coordinates)))
* COS(RADIANS(X(coordinates)) - RADIANS(24.105078)) + SIN(RADIANS(56.946285))
* SIN(RADIANS(Y(coordinates))))) AS distance
FROM places
WHERE MBRContains
(
LineString
(
Point (
24.105078 + 15 / (111.320 * COS(RADIANS(56.946285))),
56.946285 + 15 / 111.133
),
Point (
24.105078 - 15 / (111.320 * COS(RADIANS(56.946285))),
56.946285 - 15 / 111.133
)
),
coordinates
)
HAVING distance < 15
ORDER By distance
坐标是POINT类型的字段，具有空间索引 6371是用千米来计算距离的 56.946285为中心点纬度 24.105078为中心点经度 15是最大距离，单位为千米
在我的测试中，MySQL使用SPATIAL index on coordinates字段来快速选择矩形内的所有行，然后为所有过滤过的地方计算实际距离，以排除矩形角落中的地方，只留下圆圈内的地方。
这是我的结果的可视化:
灰色星形表示地图上的所有点，黄色星形表示MySQL查询返回的点。矩形(但圆圈外)角内的灰色星星由MBRContains()选择，然后由HAVING子句取消选择。
set @latitude=53.754842;
set @longitude=-2.708077;
set @radius=20;
set @lng_min = @longitude - @radius/abs(cos(radians(@latitude))*69);
set @lng_max = @longitude + @radius/abs(cos(radians(@latitude))*69);
set @lat_min = @latitude - (@radius/69);
set @lat_max = @latitude + (@radius/69);
SELECT * FROM postcode
WHERE (longitude BETWEEN @lng_min AND @lng_max)
AND (latitude BETWEEN @lat_min and @lat_max);
源
一个快速，简单和准确(对于较小的距离)的近似可以用球面投影完成。至少在我的路由算法中，与正确的计算相比，我得到了20%的提升。在Java代码中，它看起来像:
public double approxDistKm(double fromLat, double fromLon, double toLat, double toLon) {
double dLat = Math.toRadians(toLat - fromLat);
double dLon = Math.toRadians(toLon - fromLon);
double tmp = Math.cos(Math.toRadians((fromLat + toLat) / 2)) * dLon;
double d = dLat * dLat + tmp * tmp;
return R * Math.sqrt(d);
}
不太了解MySQL(对不起!)。
请确保您了解限制(assertEquals的第三个参数表示以公里为单位的精度):
float lat = 24.235f;
float lon = 47.234f;
CalcDistance dist = new CalcDistance();
double res = 15.051;
assertEquals(res, dist.calcDistKm(lat, lon, lat - 0.1, lon + 0.1), 1e-3);
assertEquals(res, dist.approxDistKm(lat, lon, lat - 0.1, lon + 0.1), 1e-3);
res = 150.748;
assertEquals(res, dist.calcDistKm(lat, lon, lat - 1, lon + 1), 1e-3);
assertEquals(res, dist.approxDistKm(lat, lon, lat - 1, lon + 1), 1e-2);
res = 1527.919;
assertEquals(res, dist.calcDistKm(lat, lon, lat - 10, lon + 10), 1e-3);
assertEquals(res, dist.approxDistKm(lat, lon, lat - 10, lon + 10), 10);
