Методы поиска географических расстояний в MySQL

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

Если бы MySQL поддерживал это, я думаю, что ST_DWithin справился бы с этой задачей. Но это не так, поэтому я получил следующее выражение, которое использует буфер для создания круга, а затем ищет точки, которые попадают в этот круг:

ST_Within(geopoint, ST_Buffer(Point(@lat, @lng), @radius))

Кажется, он работает нормально, и я считаю, что он использует index. Но достаточно ли это хорошего решения? Насколько точны ST_Within и ST_Buffer для географических целей?

ОБНОВЛЕНИЕ. Я пришел к выводу, что MySQL не поддерживает географические координаты и что все операции выполняются на евклидовой плоскости (даже если вы укажете SRID). В зависимости от местоположения это в конечном итоге приводит к большим неточностям. Таким образом, перед использованием пространственных функций MySQL необходимо преобразовать координаты.


mysql gis mysql-5.6
person fromvega    schedule 07.01.2015    source источник
comment
Если вы выполняете много пространственных / географических задач, обновление до Postgres / PostGIS может быть лучшим вариантом в долгосрочной перспективе (вместо создания обходного пути каждый раз, когда вы обнаруживаете недостающую функцию).   -  person a_horse_with_no_name    schedule 08.01.2015

Ответы (1)


arrow_upward
3
arrow_downward

Нечто подобное мы делаем на работе.

Мы получаем около 1 миллиона запросов в час, и когда мы использовали пространственные индексы, это в основном приводило к отключению базы данных, и запросы переводились в состояние ожидания. Некоторые запросы ожидали обработки около 8,000 секунд (около 2 часов). Поэтому нам пришлось найти другой способ, и это было лучшее, что мы могли придумать, теперь он больше не выполняет резервное копирование базы данных и возвращает результаты в миллисекундах.

Что мы делаем, так это сначала у нас есть функция расстояния, которая выглядит так:

CREATE FUNCTION `distance`(`lat1` DECIMAL(10,7), `lon1` DECIMAL(10,7), `lat2` DECIMAL(10,7), `lon2` DECIMAL(10,7)) RETURNS double
BEGIN
    DECLARE X DOUBLE;
    DECLARE PI DECIMAL(21, 20);
    SET PI = 3.14159265358979323846;
    SET X  = SIN(lat1 * PI / 180)
    * SIN(lat2 * PI / 180)
    + COS(lat1 * PI / 180)
    * COS(lat2 * PI / 180)
    * COS((lon2 * PI / 180) - (lon1 * PI / 180));
    SET X = ATAN((SQRT( 1- POWER( X, 2))) / X);
    RETURN (1.852 * 60.0 * ((X / PI) * 180)) / 1.609344;
END

Удалите / 1.609344 на обратной линии, чтобы получить километры

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

CREATE PROCEDURE `MyRadius`(IN `p_lat` DOUBLE, IN `p_long` DOUBLE, IN `radius` INT)
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
    SELECT distance(p_lat, p_long, g.latitude, g.longitude) as distance, country, region, city
    from geocity g
    having distance <= radius
    order by distance asc limit 100;
END

Вы можете изменить предложение order, потому что я не уверен, как вы хотите его упорядочить.

person Get Off My Lawn    schedule 07.01.2015
comment
Проблема с вашим подходом в том, что он не использует индексы, поэтому он может быть очень медленным в больших базах данных! - person fromvega; 08.01.2015
comment
Наша база данных использует 3 таблицы, каждая из которых содержит минимум 1 000 000 записей, и использует индекс. У нас есть указатель на latitude и longitude - person Get Off My Lawn; 08.01.2015