php - 查找最近的有列表(企业)的城市

Question

我在 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) 当我查看一个城市(纬度/经度已知)的结果时，拉出距离它们 25 英里以内的 3 个最近的城市

有没有办法构建一个单一的 sphinx 搜索来完成这两个查找？

根据以下评论链进行编辑:

我已经更新了我的城市表以包含一个点类型的字段点并在其上创建了一个空间索引:

> describe cities_copy;+-------------+-----------------------+------+-----+---------+----------------+| Field       | Type                  | Null | Key | Default | Extra          |+-------------+-----------------------+------+-----+---------+----------------+| id          | mediumint(7) unsigned | NO   | PRI | NULL    | auto_increment || city_name   | varchar(64)           | NO   | MUL | NULL    |                || state_name  | varchar(64)           | NO   |     | NULL    |                || state_abbr  | varchar(8)            | NO   |     | NULL    |                || county_name | varchar(64)           | NO   |     | NULL    |                || county_id   | smallint(3) unsigned  | NO   |     | NULL    |                || latitude    | float(13,10)          | NO   | MUL | NULL    |                || longitude   | float(13,10)          | NO   |     | NULL    |                || population  | int(8) unsigned       | NO   | MUL | NULL    |                || point       | point                 | NO   | MUL | NULL    |                |+-------------+-----------------------+------+-----+---------+----------------+> show indexes from cities_copy;+-------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+| Table       | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |+-------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+| cities_copy | 0          | PRIMARY    | 1            | id          | A         | 23990       | NULL     | NULL   |      | BTREE      |         || cities_copy | 0          | city/state | 1            | city_name   | A         | NULL        | NULL     | NULL   |      | BTREE      |         || cities_copy | 0          | city/state | 2            | state_abbr  | A         | 23990       | NULL     | NULL   |      | BTREE      |         || cities_copy | 1          | lat/long   | 1            | latitude    | A         | NULL        | NULL     | NULL   |      | BTREE      |         || cities_copy | 1          | lat/long   | 2            | longitude   | A         | NULL        | NULL     | NULL   |      | BTREE      |         || cities_copy | 1          | population | 1            | population  | A         | NULL        | NULL     | NULL   |      | BTREE      |         || cities_copy | 1          | point      | 1            | point       | A         | NULL        | 32       | NULL   |      | SPATIAL    |         |+-------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

但是当我尝试更新数据以从纬度/经度数据创建点时，出现错误:

> update cities_copy set point = Point(latitude, longitude);Cannot get geometry object from data you send to the GEOMETRY field

是我的语法不对还是遇到了其他问题？

Answer 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