Использование UDF для поиска по географическому расстоянию в MySQL

Первоначально написано Александр Рубин

В моем предыдущем посте о геопространственном поиске в MySQL я описал (наряду с другими вещами), как использовать функции географического расстояния. В этом посте я опишу функции геопространственного расстояния более подробно.

Если вам нужно рассчитать точное расстояние между 2 точками на Земле в MySQL (очень часто для приложений с геоприложением), у вас есть как минимум 3 варианта.

• Используйте хранимую функцию и  реализуйте  формулу haversine
• Используйте UDF (пользовательская функция) для haversine (см. Ниже)
• В MySQL 5.6 вы можете использовать функцию st_distance (недавно задокументированную), однако вы получите расстояние на плоскости, а не на земле; возвращаемое значение будет пригодно для сортировки по расстоянию, но не будет представлять фактические мили или километры.

MySQL хранит функцию для расчета расстояния на Земле

Ранее я привел  пример для хранимой MySQL функции, которая реализует формулу haversine. Однако подход, который я использовал, был не очень точным: он был оптимизирован для скорости. Если вам нужна более точная реализация формулы haversine, вы можете использовать эту функцию (результат будет в милях):

delimiter //
create DEFINER = CURRENT_USER function haversine_distance_sp (lat1 double, lon1 double, lat2 double, lon2 double) returns double
 begin
   declare R int DEFAULT 3958.76;
   declare phi1 double;
   declare phi2 double;
   declare d_phi double;
   declare d_lambda double;
   declare a double;
   declare c double;
   declare d double;
   set phi1 = radians(lat1);
   set phi2 = radians(lat2);
   set d_phi = radians(lat2-lat1);
   set d_lambda = radians(lon2-lon1);
   set a = sin(d_phi/2) * sin(d_phi/2) +
         cos(phi1) * cos(phi2) *
         sin(d_lambda/2) * sin(d_lambda/2);
   set c = 2 * atan2(sqrt(a), sqrt(1-a));
   set d = R * c;
   return d;
   end;
//
delimiter ;

(алгоритм основан на стандартной формуле, я использовал известный калькулятор скриптов Movable Type )

Это более медленная реализация, поскольку она использует арктангенс , однако она более точная. 

MySQL UDF для расстояния Haversine

Другой подход, который даст вам гораздо большую производительность, — это использование UDF. Есть несколько реализаций, я использовал  lib_mysqludf_haversine .

Вот простые шаги, чтобы установить его в MySQL 5.6 (также будет работать с более ранними версиями):

$ wget 'https://github.com/lucasepe/lib_mysqludf_haversine/archive/master.zip'
$ unzip master.zip
$ cd lib_mysqludf_haversine-master/
$ make
mysql> show global variables like 'plugin%';
+---------------+-------------------------+
| Variable_name | Value                   |
+---------------+-------------------------+
| plugin_dir    | /usr/lib64/mysql/plugin |
+---------------+-------------------------+
1 row in set (0.00 sec)
$ sudo cp lib_mysqludf_haversine.so /usr/lib64/mysql/plugin/
mysql> CREATE FUNCTION haversine_distance RETURNS REAL SONAME 'lib_mysqludf_haversine.so';
mysql> select haversine_distance(37.470295464, -122.572938858498, 37.760150536, -122.20701914150199, 'mi') as dist_in_miles;
+---------------+
| dist_in_miles |
+---------------+
|     28.330467 |
+---------------+
1 row in set (0.00 sec)

Пожалуйста, обратите внимание:

• Перед установкой убедитесь, что у вас установлен пакет mysql-devel или percona-server-devel (библиотеки разработки MySQL).
• Вам нужно будет указать последний параметр, который будет «mi», если вы хотите получить результат в милях, иначе он даст вам километры.

Функция MySQL ST_distance

В MySQL 5.6 вы можете использовать функцию ST_distance:

mysql> select st_distance(point(37.470295464, -122.572938858498), point( 37.760150536, -122.20701914150199)) as distance_plane;
+---------------------+
| distance_plane      |
+---------------------+
| 0.46681174155173943 |
+---------------------+
1 row in set (0.00 sec)

Как мы видим, он не дает нам фактического расстояния в миле или километрах, так как он не учитывает, что у нас есть широта и долгота, а не X и Y на плоскости.

Выполнение функций Geo Distance

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

Сначала я установил 2 балла (10 миль от аэропорта SFO)

set @rlon1 = 122.572938858498;
set @rlat1 = 37.470295464;
set @rlon2 = -122.20701914150199;
set @rlat2 = 37.760150536;

Далее я использую 4 функции для сравнения:

• Менее точная хранимая функция (haversine)
• Более точная хранимая функция (haversine)
• UDF для haversine
• MySQL 5.6 родной ST_distance (самолет)

Функция бенчмарка выполнит вышеуказанную функцию 100000 раз.

Вот результаты:

mysql>  select benchmark(100000,  haversine_old_sp(@rlat1, @rlon1, @rlat2, @rlon2)) as less_precise_mysql_stored_proc;
+--------------------------------+
| less_precise_mysql_stored_proc |
+--------------------------------+
|                              0 |
+--------------------------------+
1 row in set (1.46 sec)
mysql>  select benchmark(100000,  haversine_distance_sp(@rlat1, @rlon1, @rlat2, @rlon2)) as more_precise_mysql_stored_proc;
+--------------------------------+
| more_precise_mysql_stored_proc |
+--------------------------------+
|                              0 |
+--------------------------------+
1 row in set (2.58 sec)
mysql>  select benchmark(100000,  haversine_distance(@rlat1, @rlon1, @rlat2, @rlon2, 'mi')) as udf_haversine_function;
+------------------------+
| udf_haversine_function |
+------------------------+
|                      0 |
+------------------------+
1 row in set (0.17 sec)
mysql> select benchmark(100000, st_distance(point(@rlat1, @rlon1), point(@rlat2, @rlon1))) as mysql_builtin_st_distance;
+---------------------------+
| mysql_builtin_st_distance |
+---------------------------+
|                         0 |
+---------------------------+
1 row in set (0.10 sec)

Как мы видим, UDF дает гораздо более быстрое время отклика (что сравнимо со встроенной функцией).

Контрольная диаграмма (чем меньше, тем лучше)

Вывод