Запрос к Базе Данных – ближайшие достопримечательности по GPS координатам.
В таблице будет около 1 млн записей.
Нужно найти оптимальный вариант.
Скрипт должен работать достаточно быстро, чтобы выдерживать трафик 10-50 тыс чел / сут
.
Конечно можно кешировать полученные данные и обновлять инф-ию только 1 раз из 100, тогда нагрузка упадет в 50-100 раз.
Ведь не обязательно перебирать объекты всей базы, можно ограничиться например квадратом 10-100 км.
Вообще возможно часть работы переложить на sql?
Например, высчитать нужный квадрат
$Emin = "50.0515" $Nmin = "31.373800" $Emax = "50.0715" $Nmax = "31.573800" $Num = 3000;
и составить такого типа запрос
$sql = " SELECT * FROM `koord` WHERE `E` >= ".$Emin." AND `N` >= ".$Nmin." AND `E` <= ".$Emax." AND `N` <= ".$Nmax." LIMIT 0 , ".$Num;
А вот можно ли теперь каким-то образом упорядочить полученные данные по возрастанию расстояния от нашей точки или такие действия можно возложить только на PHP, что может повлечь за собой дополнительную нагрузку.
Здесь, если $Num будет слишком большим, то нагрузка может слишком возрасти.
А если $Num, слишком маленький, то можем потерять часть важных для нас ближайших объектов, если плотность их достаточно большая?
9:20
Если подскажите по моему вопросу, готов оставить очень хороший отзыв. А если поможете реализовать пусть самый простой, но быстрый скрипт для моего случая, то готов оплатить работу.
9:34
Здравствуйте.
Можно сделать так, как вы предлагаете. Но простое вычитание широты и долготы - очень неточный метод.
Полагаю, что упорядочивать лучше с помощью mysql. В вашем случае достаточно можно сделать так: (E-E_SRC) + (N-N_SRC) as dist … ORDER BY DIST LIMIT 5. *_SRC - исходные координаты объекта. Индексы довольно быстро сработают.
У проблемы лимитирования только два решения: пагинация или использование более сложных формул, которые будут причиной большой нагрузки.
Могу порекомендовать обратить внимание на специальные Mysql расширения и sphinx. Но тут я не компетентен.
9:36
Сейчас проведу пару тестов, возможно есть быстрый вариант с невысокой точностью.
11:29
Получилось сделать следующий запрос: выборка в радиусе X километров ближайших N объектов (последнее - опционально).
Используется всего 1 индекс. Результат упорядочен по расстоянию. Но так как используются обычные арифметические операции, то не будут находиться точки возле пересечения параллелей (возле долготы 180 не будет видна точка, которая находится в 0, то же с широтой).
11:30
Которая находится в -180
11:37
Если это то, что нужно, то, пожалуйста, сообщите. Выборка делается очень быстро, т.к. используется один mysql индекс. Без дополнительной обработки на стороне php.
13:46
А операции взятия по модулю я так понимаю в sql не бывает? Иначе такой вариант скорее всего не подойдет, вероятность слишком уж грубой ошибки.
Хотя конечно, если другого варианта нет, то можно провести дополнительную сортировку по расстоянию уже на стороне сервера.
13:53
MOD (234, 10); 234 % 10
Но я не представляю, зачем это нужно.
13:55
Что бы задействовать индекс колонки, она не должна обрабатываться функцией.
14:14
А синусы, косинусы, такое есть в sql?
14:16
Да, конечно.
14:16
https://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html
14:18
Есть способ их использовать для нормальных расчетов, т.е. как того требуют координаты. Но при этом идет полный перебор данных таблицы, что будет быстро при небольшом количестве данных.
14:33
Ну если все именно так, то я не вижу проблем. С использованием модуля и этих функций все очень просто.
Пусть на нужно получить 30 объектов. Известна примерно плотность объектов на 1 км^2. Тогда можно вычислить сторону квадрата в которые должно поместиться 30 объектов. Конечно, лучше взять сторону с неким запасом. Далее, если в квадрате заданном по умолчанию обнаружить 30 объектов не удалось, то отправляем запрос еще раз с большими параметрами стороны квадрата. А если объектов оказалось слишком много, то уменьшаем сторону и т.п. Таким образом подпираем оптимальную сторону квадрата.
14:47
Могу только предложить уже упомянутый sql-запрос с ограничением площади поиска.
15:48
$n_s = 37.38633728027344; $e_s = -122.08582305908203; $km_limit = 1; $n_min = $n_s-$km_limit/111; $n_max = $n_s+$km_limit/111; $e_min = $e_s-$km_limit/111; $e_max = $e_s+$km_limit/111; SELECT * FROM koord WHERE n BETWEEN $n_min AND $n_max AND e BETWEEN $e_min AND $e_max ORDER BY ABS (n-$n_s)+ABS (e-$e_s) ASC LIMIT 10
14:01
Спасибо большое, только можете то же самое написать на примере моей базы данных, вот часть дампа.
Структура таблицы `koord`:
CREATE TABLE IF NOT EXISTS `koord` (
`itemID` int(11) NOT NULL,
`type` varchar(256) NOT NULL,
`koord_txt` varchar(256) NOT NULL,
`radius` varchar(256) NOT NULL,
`E` float NOT NULL,
`N` float NOT NULL,
`photo` varchar(256) NOT NULL,
`photos` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Дамп данных таблицы `koord`:
INSERT INTO `koord` (`itemID`, `type`, `koord_txt`, `radius`, `E`, `N`, `photo`, `photos`)VALUES (1000014, 'dostoprim', '50°16''59.88''''N, 30°26''52.08''''E', '0.02', 50.2833, 30.4478,'107989977', ''), (1000015, 'dostoprim', '50°3''33.84''''N, 31°28''41.88''''E', '0.02', 50.0594, 31.4783,'57232188', ''), (1000016, 'dostoprim', '50°5''30.66''''N, 31°24''41.58''''E', '0.02', 50.0919, 31.4116,'60839967', ''), (1000017, 'dostoprim', '50°4''26''''N, 31°27''41''''E', '0.02', 50.0739, 31.4614, '56071722', '')
Поля E и N - это как раз координаты, тип FLOAT
<?php
//подключаемся к БД
try{
$conn = new PDO("mysql:host=localhost;dbname=ИМЯ_БАЗЫ_ДАННЫХ","ИМЯ_ПОЛЬЗОВАТЕЛЯ", "ПАРОЛЬ_ПОЛЬЗОВАТЕЛЯ", array(
PDO::ATTR_CASE=>PDO::CASE_NATURAL,
PDO::ATTR_ERRMODE=>PDO::ERRMODE_WARNING,
PDO::MYSQL_ATTR_USE_BUFFERED_QUERY=>true,
PDO::ATTR_DEFAULT_FETCH_MODE=>PDO::FETCH_ASSOC
));
}catch(PDOException $e){
echo 'ERROR: ' . $e->getMessage();
}
$conn->query('SET character_set_connection=utf8mb4;');
//получаем координаты объекта, возле которого нужно найти ближайшие объекты
$itemID = '1000015';
$obj_prep = $conn->prepare('SELECT E,N FROM koord WHERE itemID = :itemID');
$obj_prep->execute(array(':itemID'=>$itemID));
$obj = $obj_prep->fetch();
if(empty($obj)){
die('Объект не найден, скрипт завершен.');
}
//подготавливаем данные для запроса
$koord_e = floatval($obj['E']);
$koord_n = floatval($obj['N']);
$km_limit = 100;
$n_min = $koord_n-$km_limit/80;
$n_max = $koord_n+$km_limit/80;
$e_min = $koord_e-$km_limit/80;
$e_max = $koord_e+$km_limit/80;
//делаем запрос на поиск(лимит - 10 строк)
$srch_prep = $conn->prepare('
SELECT
*
FROM
koord
WHERE
N >= :n_min AND N <= :n_max
AND
E >= :e_min AND E <= :e_max
AND
itemID != :itemID
ORDER BY
ABS(N-:n_s)+ABS(E-:e_s) ASC
LIMIT
10
');
$srch_prep->execute(array(
':n_min'=>$n_min,
':n_max'=>$n_max,
':e_min'=>$e_min,
':e_max'=>$e_max,
':n_s'=>$koord_n,
':e_s'=>$koord_e,
':itemID'=>$itemID
));
$srch = $srch_prep->fetchAll();//получаем все записи
var_dump($srch);
16:27
SQL запрос для добавления индекса:
ALTER TABLE `koord` ADD INDEX `E_N` (`E`, `N`);
17:56
Спасибо огромное! Скрипт работает, сейчас проверю все ли правильно отрабатывает.
Единственное пока не пойму почему, но кодировка определяется неправильно.
Вижу ??? вместо русских букв.
Ведь кодировка вроде задана верное
$conn->query('SET character_set_connection=utf8mb4;');Не знаете, что может быть?
17:57
Возможно, браузер не правильно определяет кодировку.
17:57
Нужно, что бы сервер ее явно сообщил.
17:58
Например, так:
header('Content-type: text/html; charset=utf-8');18:00
Не совсем так, но уже нашел, дописал
$obj_prep = $conn->query('set character_set_results="utf8"');18:00
$obj_prep = $conn->query('set character_set_results="utf8"');18:00
$obj_prep = $conn-> query ('set character_set_results=" utf8"');18:02
Это не обязательно:
$obj_prep =
18:04
Данный способ поиска по координатам не точен, зато очень быстр. Если вопросов больше не будет, то вот реквизиты:Z404471223484
18:09
Но почему-то без него кодировка определялась не правильно.
Еще такой вопрос, как в результате выполнения запроса сразу вывести это самое значение ABS (N-:n_s)+ABS (E-:e_s) по которому выполнялась сортировка?
18:11
Это значение чисто техническое, т.е. оно не измеряется в километрах.
SELECT *, ABS(N-:n_s)+ABS(E-:e_s) AS d FROM koord WHERE N >= :n_min AND N <= :n_max AND E >= :e_min AND E <= :e_max AND itemID != :itemID ORDER BY d ASC LIMIT 10
18:11
По поводу оплаты мы договаривались 5$?
18:13
В проекте цена указана 10. https://www.weblancer.net/projects/612307.html
18:15
А вы указывали 5$ вроде?
18:16
Скорее всего это я поменял, обычно имею привычку это делать по просьбам исполнителем, чтобы у них в отзывах не светились мелкие суммы…
18:17
Или Вы сразу поставили 10$?
18:18
Нет, я цену не менял. Но я с ней согласен, т.к. вы получили и запрос, и пример. Но решайте сами.
18:24
Хорошо, ваша реализация мне очень понравилась, поэтому я согласен на 10уе, хотя на сколько помню сам увеличил цену. Единственное, все же хочу немного усложнить формулу ABS (N-:n_s)+ABS (E-:e_s), сейчас читаю теорию по геометрии, надеюсь Вы мне поможете в реализации формулы на sql?
18:30
Если бы хоть как-то разбирался в геометрии, то конечно. Данная формула просто находит разницу между координатами исходной точки и подходящей точки. Чем больше эта разница, тем больше расстояние. ABS нужен для того, что бы убрать минусы.
18:33
Таким образом мы находим сумму 2-ух катетов, а расстояние это гипотенуза.
18:35
Ошибка может быть достаточно большой, примерно в 2 раза. Хотя если брать изначально радиус в два раза больший, то ошибку можно исправить уже на уровне php.
18:36
Вот самая точная формула для расчета, , но нам не нужна такая точность, чтобы землю считать сферой. Данных скрипт будет работать в основном для расстояний до 50 км, поэтому можем землю считать плоской
18:38
Наверно. Данная формула участвует только в сортировке. Для небольших расстояний действительно можно так сделать.
18:38
Вот упрощенная формула - http://oldskola1.narod.ru/trigF28.htm
18:38
Расстояние между точками - корень из суммы квадратов разностей координат? Можете написать для такой формулы sql?
18:39
Сейчас попробую.
18:41
SQRT (POW (N-: n_s, 2) + POW (E-: e_s, 2))
18:51
Проверил, работает очень быстро на большой базе.
А можете все же и эту самую точную формулу запрограммировать на sql? http://yvision.kz/post/344436 Хочу проверить ее скорость?
18:52
Кстати на php как- то можно стандартным способом проверить время выполнения sql запроса не прибегая к дополнительным методам, типа создания таймера?
18:55
Я обычно это делаю так:
$time = microtime(1); //выполнение кода echo number_format(microtime(1)-$time, 8);
18:59
Ок, спасибо, проверил разница во времени по 1 и 2 формулам очень не значительная.
0.00992703 0.01079202
18:59
6378137 * acos( cos( N ) * cos( :n_s ) * cos( E - :e_s ) + sin( N ) * sin( :n_s ) )
19:02
Деньги отправил, код пр. 45299
19:03
Получил. Спасибо.
19:03
Последнюю формулу проверил, тот же результат за 0.01407814
Итого:
0.00992703 0.01079202 0.01407814
Кстати это в секундах получается время?
19:04
Да.
19:06
Спасибо, отзыв добавил!
19:15
Добавил 100-ый отзыв.
19:16
Спасибо за сотрудничество.
21:10
Ух, 100-ый уже, спасибо!!
12:03
Антон здравствуйте!
Нашел не точность в последней формуле, она считает неправильно. Погрешность значительная - 10- 20 %.
А могли бы реализовать вот это формулу
http://www.kobzarev.com/programming/calculation-of-distances-between-cit...
А то что-то я мучаюсь и ничего не получается. Скрипт отдает пустой результат.
12:06
Я написал вот такой запрос
$srch_prep = $conn->prepare('
SELECT
6372795*atan2(sqrt(pow(cos(( :e_s * PI() / 180)) * sin(( :n_s * PI() / 180) - ( N * PI() / 180)), 2) + pow(cos(( E * PI() / 180)) * sin( :e_s * PI() / 180) - sin(( E * PI() / 180)) * cos(( e_s * PI() / 180)) * cos(( :n_s * PI() / 180) - ( N * PI() / 180)), 2)), (sin(( E * PI() / 180)) * sin(( :e_s * PI() / 180)) + cos(( E * PI() / 180)) * cos(( :e_s * PI() / 180)) * cos(( :n_s * PI() / 180) - ( N * PI() / 180)))) AS d,itemID, radius,E,N,photo
FROM
koord
WHERE
N >= :n_min AND N <= :n_max
AND
E >= :e_min AND E <= :e_max
AND
itemID <= 1000025
ORDER BY
d ASC
LIMIT
30');
Может увидите ошибку? Но ничего не отдает
12:22
А вот функция на PHP, которая вычисляет правильно.
12:36
Отбой! Нашел ошибку - лишняя неизвестная переменная M_PI
23:00
Ок.
- Для комментирования войдите или зарегистрируйтесь
Print 
PDF