Haversine公式中的联接运算
我正在PHP中实现Haversine公式，如下所示
$result=mysqli_query($mysqli,"SELECT *,( 6371 * acos( cos( radians({$lat}) ) * cos( radians( `latitude` ) ) * cos( radians( `longitude` ) -radians({$lon}) ) +sin( radians({$lat}) ) * sin( radians( `latitude` ) ) ) ) AS distance FROM `places` HAVING distance <= {$radius} ORDER BY distance ASC") or die(mysqli_error($mysqli));在Haversine循环中，我有一个查询，它遍历haversine的结果，以选择与haversine公式返回的ID匹配的记录。查询如下。
while($row = mysqli_fetch_assoc($result))
{
$rest_time=$row['id'];
$result1=mysqli_query($mysqli,"SELECT * FROM my_friends WHERE personal_id='".$personal_id."' AND id='".$rest_time."'") or die(mysqli_error($mysqli));
//Some operations here
}HOw我可以执行连接操作将这些查询混合到一个查询中吗？从优化的角度来看，如果第二个表有50k用户，而第一个表有将近1000条记录，那么这样做是明智的吗？
回答 2
Stack Overflow用户
发布于 2016-10-26 21:27:35
您在这里所做的对所有行操作的任何操作都会很慢，因为有那么多记录。
您需要做的是利用索引。要使用索引，它必须是一个简单的查询，而不是the result of a function (目前的情况)。
你所做的是做一个半径搜索是在一个点周围做一个圆，在圆形成之前使用一些三角，我们可以得到以下内容
其中S1是里面最大的正方形，而S2是外面最小的正方形。
现在我们可以计算出这两个正方形的尺寸，S2之外的任何东西都被和索引击中，而S1中的任何东西都被索引击中，只剩下现在需要用慢速方法查找的小区域。
如果您需要从点到点的距离，请忽略S1部分(因为圆圈中的所有东西都需要haversine函数)作为注释，而圆内的所有东西都需要它，但并不是每个点都在距离之内，所以仍然需要两个WHERE子句。
所以让我们用单位圆来计算这些点。
function getS1S2($latitude, $longitude, $kilometer)
{
$radiusOfEarthKM = 6371;
$latitudeRadians = deg2rad($latitude);
$longitudeRadians = deg2rad($longitude);
$distance = $kilometer / $radiusOfEarthKM;
$deltaLongitude = asin(sin($distance) / cos($latitudeRadians));
$bounds = new \stdClass();
// these are the outer bounds of the circle (S2)
$bounds->minLat = rad2deg($latitudeRadians - $distance);
$bounds->maxLat = rad2deg($latitudeRadians + $distance);
$bounds->minLong = rad2deg($longitudeRadians - $deltaLongitude);
$bounds->maxLong = rad2deg($longitudeRadians + $deltaLongitude);
// and these are the inner bounds (S1)
$bounds->innerMinLat = rad2deg($latitudeRadians + $distance * cos(5 * M_PI_4));
$bounds->innerMaxLat = rad2deg($latitudeRadians + $distance * sin(M_PI_4));
$bounds->innerMinLong = rad2deg($longitudeRadians + $deltaLongitude * sin(5 * M_PI_4));
$bounds->innerMaxLong = rad2deg($longitudeRadians + $deltaLongitude * cos(M_PI_4));
return $bounds;
}现在您的查询变成
SELECT
*
FROM
`places`
HAVING p.nlatitude BETWEEN {$bounds->minLat}
AND {$bounds->maxLat}
AND p.nlongitude BETWEEN {$bounds->minLong}
AND {$bounds->maxLong}
AND (
(
p.nlatitude BETWEEN {$bounds->innerMinLat}
AND {$bounds->innerMaxLat}
AND p.nlongitude BETWEEN {$bounds->innerMinLong}
AND {$bounds->innerMaxLong}
)
OR (
6371 * ACOS(
COS(RADIANS({ $lat })) * COS(RADIANS(`latitude`)) * COS(
RADIANS(`longitude`) - RADIANS({ $lon })
) + SIN(RADIANS({ $lat })) * SIN(RADIANS(`latitude`))
)
)
)) <= {$radius}
ORDER BY distance ASC 重要
上述文本具有可读性，请确保正确转义这些值(最好参数化)。
这样就可以利用索引，并允许连接在更快的时间内进行。
添加连接，这将成为
SELECT
*
FROM
`places` p
INNER JOIN my_friends f ON f.id = p.id
WHERE p.latitude BETWEEN {$bounds->minLat}
AND {$bounds->maxLat}
AND p.longitude BETWEEN {$bounds->minLong}
AND {$bounds->maxLong}
AND (
(
p.latitude BETWEEN {$bounds->innerMinLat}
AND {$bounds->innerMaxLat}
AND p.longitude BETWEEN {$bounds->innerMinLong}
AND {$bounds->innerMaxLong}
)
OR (
6371 * ACOS(
COS(RADIANS({ $lat })) * COS(RADIANS(`latitude`)) * COS(
RADIANS(`longitude`) - RADIANS({ $lon })
) + SIN(RADIANS({ $lat })) * SIN(RADIANS(`latitude`))
)
)
) <= {$radius}
AND f.personal_id = {$personal_id}
ORDER BY distance ASC 重要
上述文本具有可读性，请确保正确转义这些值(最好参数化)。
假设您有正确的索引，这个查询应该保持快速，并允许您进行连接。
看看上面的代码，我不知道personal_id是从哪里来的，所以它已经离开了
如果需要与查询的距离，可以删除S1方格。
(
p.latitude BETWEEN {$bounds->innerMinLat}
AND {$bounds->innerMaxLat}
AND p.longitude BETWEEN {$bounds->innerMinLong}
AND {$bounds->innerMaxLong}
) 然后移动OR的第二部分
6371 * ACOS(
COS(RADIANS({ $lat })) * COS(RADIANS(`latitude`)) * COS(
RADIANS(`longitude`) - RADIANS({ $lon })
) + SIN(RADIANS({ $lat })) * SIN(RADIANS(`latitude`))
)返回到select，它仍然使用S2。
我还将确保删除查询6371中的“魔术号”，即地球半径(以公里为单位)。
Stack Overflow用户
发布于 2016-10-25 05:24:12
在本例中，将第一个查询作为派生子查询放在第二个查询中：
SELECT p.*, f.* -- Select only the columns you need, not all
FROM
(
SELECT *,
( 6371 * acos( cos( radians({$lat}) ) * cos( radians( `latitude` ) )
* cos( radians( `longitude` ) -radians({$lon}) )
+sin( radians({$lat}) ) * sin( radians( `latitude` ) ) )
) AS distance
FROM `places`
HAVING distance <= {$radius}
ORDER BY distance ASC"
LIMIT 10 -- Didn't you forget this??
) AS p
JOIN my_friends AS f ON f.personal_id p.personal_id
AND id='".$rest_time."'" -- Huh??https://stackoverflow.com/questions/40199403
复制相似问题
腾讯云开发者
