Найти записи в Geofence (круг), используя MySQL Spatial поля

Проблема

Я сейчас храню latitude а также longitude значения для местоположения, используя MySQL POINT тип поля в формате:

POINT(51.507351 -0.127758)

Я никогда раньше не использовал этот тип поля, и поэтому не имею никакого опыта с запросами, и как на самом деле, эффективно, использовать хранимые данные.

Мое исследование

Я нашел много ссылок, которые демонстрируют различные методы для поиска предметов в пределах указанного радиуса. Тем не менее, большинство из них используют независимые latitude а также longitude поля вместо использования пространственных полей MySQL.

Пожалуйста, смотрите следующее:

Мой вопрос

Я пытаюсь найти любые записи в пределах указанного радиуса (в метрах). Исходя из структуры моей таблицы, каков наилучший и наиболее эффективный метод поиска в моих записях и возврата любых элементов в пределах указанного радиуса (круг, а не прямоугольник)?

Это то, что я до сих пор:

SELECT
*,
(
6373 * acos (
cos ( radians( PASSED_IN_LATITUDE ) )
* cos( radians( X(location) ) )
* cos( radians( Y(location) ) - radians( PASSED_IN_LONGITUDE ) )
+ sin ( radians( PASSED_IN_LATITUDE ) )
* sin( radians( X(location) )
)
) AS distance
FROM locations
HAVING distance < PASSED_IN_RADIUS

Я взял приведенный выше код из другого ответа, но, учитывая, что этот ответ был опубликован 2 года назад, я предположил, что он устарел, и поэтому этот метод может быть не самым эффективным …

2

Решение

Предполагая, что у вас есть пространственный ключ на месте, вы можете сделать что-то вроде этого:

select * from locations where
contains(geomfromtext('polygon($bounding_rect_coords)'),location)
and earth_distance(location,point($lat,$lon)) < $radius

Координаты ограничивающего прямоугольника должны быть рассчитаны с использованием следующих формул:

$deg_to_rad = $PI/180.0
$rad_to_deg = 1.0/$deg_to_rad
$delta_y = $rad_to_deg *($radius / ($earth_radius * cos($lat*$deg_to_rad))) // the length of the parallel = EARTH_R * cos(lat)
$delta_x = $rad_to_deg * ($radius/$earth_radius)
$x1 = $lat - $delta_x
$x2 = $lat + $delta_x
$y1 = $lon - $delta_y
$y2 = $lon + $delta_y

Тогда прямоугольник получается с

geomfromtext('polygon(($x1 $y1,$x2 $y1,$x2 $y2, $x1 $y2, $x1 $y1))')

Лучше всего это сделать в приложении для разгрузки сервера базы данных.

Этот прямоугольник на самом деле является сферическим прямоугольником, поэтому в его вычислении используется константа PI. Идея проста. Для данной параллели конвертируйте радиус поиска в градусы долготы. Именно на сколько градусов восточнее и западнее нам нужно пройти от цели, чтобы покрыть баллы наших кандидатов. Затем вычислите то же самое для градусов широты — в отличие от долготы, это не будет зависеть от координат, так как все меридианы имеют одинаковую длину. Вот сколько градусов нам нужно пройти на север и юг.

Вышеприведенные вычисления предполагают, что радиус поиска меньше, чем длина параллели, что будет иметь место в большинстве Соединенных Штатов для разумного радиуса поиска, но может не выполняться, например, в некоторых частях Аляски. Так что было бы неплохо проверить это (если delta_y> 90) и соответствующим образом обрезать. Вам также следует проверить, правы ли вы на Северном или Южном полюсе, там что-то сломалось. Но, надеюсь, ваши данные не имеют слишком много полярных записей.

За earth_distance() у вас есть несколько вариантов:

  • Используй мой UDF (http://github.com/spachev/mysql_udf_bundle) (самый быстрый, но вам нужно иметь возможность устанавливать UDF на ваш сервер)
  • Напишите функцию хранилища MySQL. Вы можете начать с http://gist.github.com/aramonc/6259563 и настроить при необходимости (нужна способность создавать функции).
  • Просто вставьте приведенное выше вычисление расстояния непосредственно в запрос (безобразно, но не требует специальной настройки или привилегий)

С вашими вычислениями все в порядке, хотя ему два года — за последние два года не было обнаружено ничего революционного в том, что касается измерения расстояния между двумя точками на земле, насколько я знаю.

Ваш оригинальный метод будет работать, за исключением того, что он будет неэффективным. Добавление contains Предложение позволяет нам свести наш поиск к (надеюсь) относительно небольшому набору, который гарантированно очень быстро попадет в радиус поиска. Затем мы берем каждого кандидата и отфильтровываем тех, кто не сделал разрез на earth_distance(),

Я должен добавить стандартный отказ от ответственности, что я вставил переменные в SQL, которые, возможно, не были очищены. Обязательно проверяйте результирующие SQL-запросы для атак SQL-инъекций при написании реального производственного кода.

1

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

Других решений пока нет …

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