Операция соединения в формуле 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 записей?

4

Решение

Любая операция, выполняемая здесь, которая работает со всеми строками, будет медленной с таким количеством записей.

Что вам нужно сделать, это воспользоваться индексами. Чтобы использовать индекс, это должен быть простой запрос, а НЕ результат функции (как сейчас).

То, что вы делаете, выполняя поиск по радиусу, делает окружность вокруг точки. Используя некоторую триггеру перед созданием круга, мы можем придумать следующее

Обведите два квадрата

где 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 — это радиус Земли в Километре

4

Другие решения

В этот В этом случае поместите первый запрос как производный подзапрос во второй:

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??
1

По вопросам рекламы [email protected]