Операция соединения в формуле Haversine
Я реализую формулу Haversine в PHP следующим образом
$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. Запрос заключается в следующем.
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
}
КАК я могу выполнить операцию объединения, чтобы объединить эти запросы в один? Было бы разумно сделать это с точки зрения оптимизации, если вторая таблица имеет, например, 50 тыс. Пользователей, а первая таблица имеет почти 1000 записей?
Решение
Любая операция, выполняемая здесь, которая работает со всеми строками, будет медленной с таким количеством записей.
Что вам нужно сделать, это воспользоваться индексами. Чтобы использовать индекс, это должен быть простой запрос, а НЕ результат функции (как сейчас).
То, что вы делаете, выполняя поиск по радиусу, делает окружность вокруг точки. Используя некоторую триггеру перед созданием круга, мы можем придумать следующее
где 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`))
)
вернуться к выбору, который по-прежнему использует S2.
Также я бы обязательно убрал «магическое число» в запросе 6371 — это радиус Земли в Километре
Другие решения
В этот В этом случае поместите первый запрос как производный подзапрос во второй:
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??