Методи за търсене на географско разстояние в MySQL

Търся бързия начин за търсене на точки, които са на определено разстояние от друга дадена точка. Имам таблица MyISAM с пространствено индексирани точки, представящи географски местоположения (географска ширина, дължина).

Ако MySQL го поддържаше, мисля, че ST_DWithin щеше да свърши работа. Но не става, така че получих следния израз, който използва буфер за генериране на кръг и след това търси точки, които попадат в този кръг:

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

Изглежда, че работи добре и вярвам, че използва индекса. Но дали е достатъчно добро решение? Колко точни са ST_Within и ST_Buffer за географски цели?

АКТУАЛИЗАЦИЯ: Заключих, че MySQL не предлага поддръжка за географски координати и че всички операции се извършват в евклидова равнина (дори ако посочите SRID). В зависимост от местоположението, това в крайна сметка води до големи неточности. Така че координатите трябва да бъдат трансформирани преди използването на функциите MySQL Spatial.


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