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

我有一个名为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;

在这里和其他地方查看了无数类似的问题之后，很显然，这是一个鲜为人知(和更新的方式)的工作方式，因此对此没有太多内容，因此，为什么要寻求一些澄清。

我的问题是:

这是最好的解决方案/我做对了吗？

这个方法会利用coordinates列上的空间索引吗？

使用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

最佳答案

这可能是最好的解决方案。首先让我们得到其他答案...
EXPLAIN SELECT ...说什么？ (这可能会回答您的Q2)。

您的查询将扫描整个表，而不考虑其他答案。也许您最后想要LIMIT ...？

可能有用的另一件事(取决于您的应用程序和优化程序):将边界框添加到WHERE子句。

无论如何，请执行以下操作以准确了解实际触摸了多少行:

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

找回那些答案；那么也许我们可以进一步迭代。

在显示状态后

好吧，Handler_read_rnd_next说这是一次全表扫描。 1000和1001-您有LIMIT 1000吗？

我推断出LIMIT并未影响SPATIAL的工作方式。也就是说，它做的很简单-(1)检查所有行，(2)排序，(3)LIMIT。

那么该怎么办？

计划A:确定您不希望结果超过X英里(km)，并在查询中添加一个“边界框”。

计划B:放弃空间，并采用更复杂的方式完成任务:http://mysql.rjweb.org/doc.php/latlng

关于mysql - 在MySQL 8中使用点数据类型和st_distance_sphere查找最近的地点，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/50438381/