Присоединить операцию в формуле Хаверсина

Я использую формулу Хаверсина в 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 }

HOw я могу выполнить операцию Join, чтобы объединить эти запросы в одну? Было бы разумно сделать это, с оптимизационной точки зрения, если вторая таблица имеет, как и 50 тыс. Пользователей, а первая таблица имеет почти 1000 записей?

Как подготовить существующую базу данных для поиска местоположения?Использование формулы Хаверсина с PostgreSQL и PDO Измерение расстояния между двумя координатами в PHP

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

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

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

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

где 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??