使用MySQL 8中的点数据类型和st_distance_sphere函数查找最近的地点

Question

我有一个名为place的表：

id | name       | coordinates (longitude, latitude)
1  | London     | -0.12574, 51.50853
2  | Manchester | -2.25, 53.41667
3  | Glasgow    | -4.25, 55.86667

coordinates列属于点数据类型。我使用以下命令将这些点插入到了place表中：

st_geomfromtext('point($longitude $latitude)', 4326)

注意，我已经使用了SRID。

对于任何坐标，我想找到最近的地方（按升序排序）。目前我想到的解决方案（通过阅读MySQL文档）如下：

select
    *,
    st_distance_sphere(`place`.`coordinates`, st_geomfromtext('Point($longitude $latitude)', 4326)) as distance
from place
order by distance asc;

在这里和其他地方查看了无数类似的问题后，很明显这是一种较少人知道（也是较新的方法），因此没有太多相关内容，这就是我寻求澄清的原因。

我的问题如下：

1. 这是最好的解决方案/我是否做得正确？
2. 这种方法会使用我在coordinates列上拥有的空间索引吗？
3. 使用st_distance_sphere时，我需要指定地球半径以获得准确的结果吗？ （编辑：不需要，它默认使用地球半径）

编辑，下面是这些答案：

explain select ...; 返回：

id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra
1  | SIMPLE      | place | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 115687 | 100.00   | Using filesort

flush status; select ...; show session status like 'Handler%';

返回以下内容：

Variable_name              | Value
Handler_commit             | 1
Handler_delete             | 0
Handler_discover           | 0
Handler_external_lock      | 2
Handler_mrr_init           | 0
Handler_prepare            | 0
Handler_read_first         | 1
Handler_read_key           | 1001
Handler_read_last          | 0
Handler_read_next          | 0
Handler_read_prev          | 0
Handler_read_rnd           | 1000
Handler_read_rnd_next      | 119395
Handler_rollback           | 0
Handler_savepoint          | 0
Handler_savepoint_rollback | 0
Handler_update             | 0
Handler_write              | 0

- Matt Kieran

---

那个问题是关于SQL Server的，答案要么反映了这一点，要么建议使用Haversine公式。 - Matt Kieran

不幸的是，在这种情况下，SQL Server非常不同。多年前确实如此，Haversine是唯一可行的方法，但如果你深入挖掘，就会发现MySQL引入了一整套新的函数，有助于存储和查询空间点/多边形等。 - Matt Kieran

你使用的MySQL版本是什么？这很重要，因为更新的版本提供了更完整的st_...函数。 - O. Jones

@MattKieran 你可能是 Stack Overflow 上第一批使用 MySQL 8 地理空间功能的人之一。 - O. Jones

MySQL 5.7.6 添加了 ST_Distance_Sphere()。 - Rick James

Answer 1

这可能是最好的解决方案。让我们先得到其他答案...

EXPLAIN SELECT ...说了什么？（这可能回答了你的Q2）。

无论其他答案如何，您的查询都将扫描整个表。也许您想在末尾加上LIMIT ...？

另一件可能有用的事情（取决于您的应用和优化器）：在WHERE子句中添加一个边界框。

无论如何，为了准确感知实际触及多少行，请执行以下操作：

FLUSH STATUS;
SELECT ...;
SHOW SESSION STATUS LIKE 'Handler%';

回答完这些问题后，也许我们可以进一步迭代。

在SHOW STATUS之后

好的，Handler_read_rnd_next表示这是一次全表扫描。1000和1001 - 你使用了LIMIT 1000吗？

我推断LIMIT并没有被纳入SPATIAL的工作方式中。也就是说，它只做了简单的事情 - (1) 检查所有行，(2) 排序，(3) LIMIT。

那么该怎么办呢？

计划A：决定您不想要超过X英里（公里）的结果，并将"bounding box"添加到查询中。

计划B：放弃空间查询，深入研究更复杂的任务执行方式：http://mysql.rjweb.org/doc.php/latlng。

- Rick James