问题描述
我有一个名为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列属于 point数据类型.我将点插入到place表中，使用:
The coordinates column is of the point datatype. I inserted the points into the place table using:
st_geomfromtext('point($longitude $latitude)', 4326)
请注意，我已经使用了SRID.
Notice that I've made use of the SRID.
给出任何坐标，我想找到离它最近的位置(按升序排列).我目前想出的解决方案(通过阅读MySQL文档)看起来像这样:
Given any coordinates, I'd like to find the nearest places to it (order by ascending). The solution I have currently come up with (by reading the MySQL docs) looks like this:
select
*,
st_distance_sphere(`place`.`coordinates`, st_geomfromtext('Point($longitude $latitude)', 4326)) as distance
from place
order by distance asc;
在这里和其他地方查看了无数类似的问题之后，很明显，这是一个鲜为人知(和较新的方式)的工作，因此对此没有太多内容，因此，我为什么要澄清一下
After looking at countless similar questions on here and elsewhere, it's quite obvious that this is a lesser known (and newer way) of doing things so there's not much content on this, hence why I'm looking for a bit of clarification.
我的问题是:
- 这是最好的解决方案/我做对了吗?
- 这种方法会利用我在
coordinates列上获得的空间索引吗? - 使用 st_distance_sphere时，是否需要指定地球的半径才能获得准确的结果? (否，默认情况下使用地球半径)
- Is this the best solution / Am I doing this right?
- Will this method make use of the spatial index I have on the
coordinatescolumn? - When using st_distance_sphere, do I need to specify the radius of the Earth in order to get accurate results? ( no, it uses the radius of the earth by default)
编辑，以下是这些答案:
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
推荐答案
可能是最好的解决方案.首先让我们得到其他答案...
It may be the best solution. Let's get some other answers first...
EXPLAIN SELECT ...说什么? (这可能会回答您的第二个问题.)
What does EXPLAIN SELECT ... say? (This may answer your Q2).
您的查询将扫描整个表，而不考虑其他答案.也许您最后想要LIMIT ...?
Your query will scan the entire table, regardless of the other answers. Perhaps you want LIMIT ... on the end?
可能有用的另一件事(取决于您的应用程序和优化程序):在WHERE子句中添加一个边界框.
Another thing that might be useful (depending on your app and on the Optimizer): Add a bounding box to the WHERE clause.
在任何情况下，请执行以下操作以准确了解实际触摸了多少行:
In any case, do the following to get an accurate feel for how many rows are actually touched:
FLUSH STATUS;
SELECT ...;
SHOW SESSION STATUS LIKE 'Handler%';
获得这些答案；那么也许我们可以进一步迭代.
Get back with those answers; then perhaps we can iterate further.
显示状态后
好吧，Handler_read_rnd_next说这是一次全表扫描. 1000和1001-您有LIMIT 1000吗?
Well, Handler_read_rnd_next says that it was a full table scan. The 1000 and 1001 -- did you have LIMIT 1000?
我推断LIMIT没有被考虑为SPATIAL的工作方式.也就是说，它做的很简单-(1)检查所有行，(2)排序，(3)LIMIT.
I deduce that LIMIT is not factored into how SPATIAL works. That is, it does the simplistic thing -- (1) check all rows, (2) sort, (3) LIMIT.
那该怎么办?
计划A:确定您不希望获得比X英里(公里)远的结果，并在查询中添加边界框".
Plan A: Decide that you don't want results farther than X miles (km) and add a "bounding box" to the query.
计划B:放弃空间，并采用更复杂的方式来完成任务: http: //mysql.rjweb.org/doc.php/latlng
Plan B: Abandon Spatial and dig into a more complex way to do the task: http://mysql.rjweb.org/doc.php/latlng
这篇关于在MySQL 8中使用点数据类型和st_distance_sphere查找最近的地点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！