如何按照距离给定点的远近对行进行排序，MySQL？

Question

我正在尝试从我的表中获取到离给定点最近的100个点。

我正在使用

SELECT *, GLENGTH(
            LINESTRINGFROMWKB(
              LINESTRING(
                ASBINARY(
                  POINTFROMTEXT("POINT(40.4495 -79.988)")
                ),
                ASBINARY(pt)
              )
            )
          )
 AS `distance` FROM `ip_group_city` ORDER BY distance LIMIT 100

(是的，那很痛苦。我刚刚谷歌了一下。我不知道如何在MySQL中正确地测量距离)

执行时需要很长时间。EXPLAIN 表明没有possible_keys。

我在pt列上创建了一个SPATIAL索引:

CREATE SPATIAL INDEX sp_index ON  ip_group_city (pt);

虽然我不太知道如何正确使用它。你能帮我吗？

- Valentin Golev

Answer 1

由于您没有WHERE子句，因此没有受影响的索引。我认为您应该改进这个查询，通过使用MBR_函数（适用于MySQL 5.0或更高版本）或ST_函数（适用于MySQL 5.6或更高版本）等方式。

类似以下内容：

SELECT *, GLENGTH(
            LINESTRINGFROMWKB(
              LINESTRING(
                ASBINARY(
                  POINTFROMTEXT("POINT(40.4495 -79.988)")
                ),
                ASBINARY(pt)
              )
            )
          )
 AS `distance` 
FROM `ip_group_city` 
WHERE
MBRWithin(
        pt, -- your point
        GeomFromText('Polygon( -- your line (in polygon format) from pt to target point 
                        (
                            #{bound.ne.lat} #{bound.ne.lng}, --North East Lat - North East Long
                            #{bound.ne.lat} #{bound.sw.lng}, --North East Lat - South West Long
                            #{bound.sw.lat} #{bound.sw.lng}, --
                            #{bound.sw.lat} #{bound.ne.lng},
                            #{bound.ne.lat} #{bound.ne.lng}
                        )
                    )')
      )
ORDER BY distance LIMIT 100

- Nguyen Viet Anh

Answer 2

我过去使用大圆方程来进行这些类型的计算。我不确定性能如何比较，但值得尝试并进行比较。

这里有一篇很好的SO帖子，介绍了如何在MySQL中实现它。

- Abe Miessler

---

它根本不使用空间，那么它（空间索引）有什么用呢？ - Valentin Golev

你的解决方案是唯一有潜力使用空间索引的类型。我只是建议另一种值得尝试的方法，看看性能如何比较。 - Abe Miessler

哦。我开始使用了一个类似于你的方法，但性能变得更糟了。 - Valentin Golev

Answer 3

请看这些问题：

• 在'X'公里（或英里）范围内查找城市

- Craig Trader

---

它不进行排序，也完全不使用空间。 - Valentin Golev