Haversine公式中的联接运算 [英] Join operation in Haversine formula
问题描述
我正在按以下方式在PHP中实现Haversine公式
I am implementing Haversine formula in PHP as the follows
$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匹配的记录.查询如下.
And Inside the Haversine fetch loop, I have the query that iterates through the results of haversine to select records that matches the IDs returned by the haversine formula. The Query is as follows.
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
}
如何执行Join操作将这些查询混合为一个查询?从优化的角度来看，这样做是否明智?如果第二个表有5万个用户，而第一个表有近1000条记录呢?
HOw can I perform Join operation to mingle these queries into a single one? WOuld it be wise to do so, from the optimisation point of view if the second table has, like 50k users and the first table has almost 1000 records?
推荐答案
您在此处执行的对所有行进行操作的任何操作，对于有这么多记录的操作都会很慢.
Any operation you do here which operates on all rows will be slow with that many records.
您需要做的是利用索引的优势.要使用索引，它必须是一个简单的查询，而不是函数的结果(当前).
What you need to do is take advantage of index's. To use an index it must be a simple query and NOT the result of a function (as it is currently).
通过进行半径搜索，您正在做的事情是围绕一个点画一个圆，在制作圆之前使用一些三角函数，我们可以得出以下内容
What you are doing by doing a radius search is making a circle around a point, By using some trig before the circle is made we can come up with the following
其中S1是内部最大的正方形，S2是外部最小的正方形.
where S1 is the largest square inside, and S2 is the smallest square outside.
现在我们可以计算出这两个正方形的尺寸，并且S2的任何外部都将被索引指向，索引S1的内部将被索引所指向，剩下的小区域现在需要使用缓慢的方法.
Now we can work out the dimensions of these two squares and anything OUTSIDE of S2 is hit by and index, and anything INSIDE of S1 is hit by an index, leaving only the small area inside which now needs to be looked up using the slow method.
如果您需要到该点的距离，请忽略S1部分(因为圆内部的所有内容都具有haversine函数)，此处，虽然圆内部的所有内容都需要它，但并非每个点都在该距离之内，因此请注意仍然需要两个WHERE子句
If you need the distance from the point ignore the S1 sections (as everything inside of the circle needs the haversine function) as a note here, while everything inside of the circle needs it, not every point is within the distance, so both WHERE clauses are still needed
因此，让我们使用单位圆计算这些点
So lets calculate these points using the unit circle
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
重要
以上内容出于可读性考虑，请确保这些值正确转义/最好将其参数化
The above has text for readability, Please ensure these values are escaped correctly / preferably parameterized
这可以利用索引，并允许在较短的时间内进行连接
This then can take advantage of the index, and allow the join to happen in a faster time
添加联接将变为
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
重要
以上内容出于可读性考虑，请确保这些值正确转义/最好将其参数化
The above has text for readability, Please ensure these values are escaped correctly / preferably parameterized
假设您具有正确的索引，则此查询应保持快速并允许您进行联接.
Assuming you have the correct indexes this query should remain fast and allow you to do the join.
看看上面的代码，我不确定personal_id的来源，因此请留在原处
Looking at the code above im not sure where personal_id comes from so have left as it is
如果需要距查询的距离，可以删除S1正方形
if you need the distance from the query, you can remove the S1 square
(
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`))
)
返回选择，仍然使用S2.
back to the select, which still makes use of S2.
我还要确保删除查询6371中的魔术数"，即以千米为单位的地球半径.
I would also make sure to remove the "magic number" in the query 6371 is the radius of the earth in Kilometer
这篇关于Haversine公式中的联接运算的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
