найти ближайшие города, которые имеют списки (предприятия)

У меня уже есть два источника данных в Sphinx:

source cities {
    ...
    sql_query = SELECT id, city_name, state_name, state_abbr, latitude,
                longitude, population FROM cities;
    sql_attr_uint  = population
    sql_attr_float = latitude
    sql_attr_float = longitude
    ...
}

source listings {
    ...
    sql_query = SELECT entry_id, title, url_title, category_names, 
                address1, address2, city, state, zip, latitude, longitude,
                listing_summary, listing_url, extended_info FROM listings;
    sql_attr_float = latitude
    sql_attr_float = longitude
    ...
}

Используя PHP Sphinx API, я выполнил поиск соответствующих городов по названию и выполнил поиск по спискам в пределах 25 миль от лат / долготы без каких-либо проблем, но теперь мне нужно что-то вроде «присоединиться» к ним ... Я бы хотел быть способен:

a) при поиске городов по названию, верните только города, в которых есть списки в пределах 25 миль от них, и b) когда я просматриваю результаты для одного города (широта / долгота известна), выберите 3 ближайших города, в которых есть списки в пределах 25 миль. из них

Есть ли способ построить один поиск сфинкса, чтобы выполнить эти два поиска?

Изменить на основе цепочки комментариев ниже:

Я обновил свою таблицу городов, включив в нее точку поля типа Point, и создал для нее пространственный индекс:

> описать towns_copy;
+ ------------- + ----------------------- + ------ + ---- - + --------- + ---------------- +
| Поле | Тип | Null | Ключ | По умолчанию | Extra |
+ ------------- + ----------------------- + ------ + ---- - + --------- + ---------------- +
| id | mediumint (7) без знака | НЕТ | PRI | NULL | auto_increment |
| название города | varchar (64) | НЕТ | MUL | NULL | |
| имя_состояния | varchar (64) | НЕТ | | NULL | |
| state_abbr | varchar (8) | НЕТ | | NULL | |
| название округа | varchar (64) | НЕТ | | NULL | |
| county_id | smallint (3) без знака | НЕТ | | NULL | |
| широта | плавать (13,10) | НЕТ | MUL | NULL | |
| долгота | плавать (13,10) | НЕТ | | NULL | |
| население | int (8) без знака | НЕТ | MUL | NULL | |
| точка | точка | НЕТ | MUL | NULL | |
+ ------------- + ----------------------- + ------ + ---- - + --------- + ---------------- +

> показывать индексы от city_copy;
+ ------------- + ------------ + ------------ + --------- ----- + ------------- + ----------- + ------------- + ---- ------ + -------- + ------ + ------------ + --------- +
| Таблица | Non_unique | Key_name | Seq_in_index | Имя столбца | Сопоставление | Кардинальность | Sub_part | Упаковано | Null | Index_type | Комментарий |
+ ------------- + ------------ + ------------ + --------- ----- + ------------- + ----------- + ------------- + ---- ------ + -------- + ------ + ------------ + --------- +
| towns_copy | 0 | ПЕРВИЧНЫЙ | 1 | id | A | 23990 | NULL | NULL | | BTREE | |
| towns_copy | 0 | город / штат | 1 | название города | A | NULL | NULL | NULL | | BTREE | |
| towns_copy | 0 | город / штат | 2 | state_abbr | A | 23990 | NULL | NULL | | BTREE | |
| towns_copy | 1 | широта / длина | 1 | широта | A | NULL | NULL | NULL | | BTREE | |
| towns_copy | 1 | широта / длина | 2 | долгота | A | NULL | NULL | NULL | | BTREE | |
| towns_copy | 1 | население | 1 | население | A | NULL | NULL | NULL | | BTREE | |
| towns_copy | 1 | точка | 1 | точка | A | NULL | 32 | NULL | | Пространство | |
+ ------------- + ------------ + ------------ + --------- ----- + ------------- + ----------- + ------------- + ---- ------ + -------- + ------ + ------------ + --------- +

Но когда я пытаюсь обновить данные, чтобы создать точки из данных широты и долготы, я получаю сообщение об ошибке:

> обновить уставку города_копии = точка (широта, долгота);
Невозможно получить объект геометрии из данных, которые вы отправляете в поле GEOMETRY

Мой синтаксис выключен или я столкнулся с какой-то другой проблемой?

13.10.2009 13:09:45
Если ваши объекты достаточно близко друг к другу для вас , чтобы иметь возможность использовать плоскую модель Земли, вам необходимо преобразовать широту и долготу в декартову систему, скажем, UTMили Pulkovoили любой другой проекция лучше всего подходит вашей стране. Что касается UPDATE, это должно быть некоторые проблемы с MySQLверсией. Попробуйте это: SET coord = GeomFromText(CONCAT('Point(', latitude, ' ', longitude, ')'))с latitudeи longitudeпреобразуются в декартову систему.
Quassnoi 13.10.2009 20:20:41
Я не конвертировал lat / long в декартову, но это именно тот запрос, который я использовал для генерации точек в прошлый раз, и в результате мне пришлось использовать этот материал LineStringFromWKB / AsBinary.
Ty W 13.10.2009 20:24:07
и города (точки) содержатся в Соединенных Штатах (по крайней мере, на данный момент).
Ty W 13.10.2009 20:24:47
Версия MySQL: mysql Ver 14.14 Distrib 5.1.34, для redhat-linux-gnu (i686) с использованием readline 5.1
Ty W 13.10.2009 20:26:17
1 ОТВЕТ
РЕШЕНИЕ

Вам нужно сделать следующее:

  • Создайте дополнительное GEOMETRYполе, которое будет содержать Point(Latitude, Longitude), заменив широту и долготу метрическими координатами для плоской земли.

  • Создать SPATIALиндекс по этому полю

  • Исправьте первый запрос:

    SELECT  *
    FROM    cities cc
    WHERE   EXISTS
            (
            SELECT  NULL
            FROM    listings cp
            WHERE   MBRContains(LineString(Point(cc.latitude - 25, cc.longitude - 25), Point(cc.latitude + 25, cc.longitude + 25)), cp.Coords)
                    AND GLength(LineString(cc.Coords, cp.Coords)) <= 25
            )

Чтобы узнать три ближайших города, выполните этот запрос:

SELECT  cp.*
FROM    cities cc
CROSS JOIN
        cities cp
WHERE   cc.id = @id
ORDER BY
        GLength(LinePoint(cc.Coords, cp.Coords))
LIMIT 3

Однако учтите, что это будет не очень эффективно, если у вас много городов.

Чтобы сделать его эффективным, вам нужно создать таблицу тесселяции (которая будет выкладывать поверхность Земли рядом с вами), рассчитать порядок близости плиток и объединиться с ними.

Вот простой скрипт для демонстрации:

CREATE TABLE t_spatial (id INT NOT NULL PRIMARY KEY, coords Point) ENGINE=MyISAM;

INSERT
INTO    t_spatial
VALUES
(1, Point(0, 0)),
(2, Point(0, 1)),
(3, Point(1, 0)),
(4, Point(1, 1));

SELECT  s1.id, s2.id, GLength(LineString(s1.coords, s2.coords))
FROM    t_spatial s1
CROSS JOIN
        t_spatial s2
2
13.10.2009 17:48:57
тогда это просто использование mysql, верно? Я надеялся продолжить поиск в сфинксе, потому что он намного быстрее (а у нас 25000 городов).
Ty W 13.10.2009 13:54:33
Вы можете создать свой индекс Sphinx, используя MySQLв качестве источника данных. Таким образом, вы можете продолжать поиск Sphinxи использовать MySQLпространственные возможности.
Quassnoi 13.10.2009 13:56:52
BTWКаковы typeваши источники?
Quassnoi 13.10.2009 13:58:36
тип для обоих источников - mysql. всякий раз, когда я делал пространственные поиски в прошлом, я делал это через sphinx, используя что-то вроде $ cl-> SetGeoAnchor («широта», «долгота», $ широта pi () / 180, $ longitude pi () / 180) ; $ cl-> SetFilterFloatRange ('@ geodist', 0.0, $ distance * 1609.344);
Ty W 13.10.2009 14:10:27
Я не мог заставить GLength работать с точками, пока я не использовал LineStringFromWKB, но спасибо за быстрое изложение пространственных возможностей MySQL. Я что-то упустил, и можно ли это сократить, чтобы вернуть расстояние между двумя точками? GLength (LineStringFromWKB (LineString (AsBinary (c.point), AsBinary (f.point)))) AS расстояние
Ty W 13.10.2009 17:01:45