如何在欧几里德距离搜索/查询中优化mysql中的地理位置数据库 [英] how to optimize a geolocation database in mysql for euclidean distance search/query

问题描述

我使用mysql来存储地理位置数据。我想知道如何优化包含（x，y）坐标的表，以便查询将针对它进行优化。 （x，y将是lat / long）。例如，我有一个包含以下字段的表：id，x，y，notes。

>

在某个时间后，我将执行查询：
从geoloc
中选择*
其中sqrt（（x- @ x）^ 2 + （y - y）^ 2） delta

请注意，我不知道实际的SQL语句现在是如何工作的，所以以上只是我想要的非常粗略的想法。

那么我需要做些什么来优化这种查询类型的表？任何指针都非常感谢。

解决方案

对于这种查询，您将很难优化。一个更好的选择是从（x，y）坐标和 delta> 传递进来计算边界框。然后查询坐标位于该框中的任何位置。该查询将会更加简单，并且可以使用您可能在x和y字段中使用的任何索引。

当然，该查询的结果不是确切的，因为它是一个边界框而不是一个圆。如果你想得到更好的结果，你可以从边界框查询中得到结果，然后使用较慢的欧几里得方法来过滤那些不落入圆的那些。

i am using mysql to store geolocation data. i would like to know how to optimize the table holding the (x,y) coordinates so that queries will be optimized against it. (the x,y will be lat/long).

for example, i have a table with the following fields: id, x, y, notes.

at sometime later, i will perform a query: select * from geoloc where sqrt( (x-@x)^2 + (y-@y)^2 ) < delta

please note, i have no idea how the actual SQL statement will work right now, so the above is just a very rough idea of what i want.

so what do i have to do to optimize this table for this type of query? any pointers are greatly appreciated.

解决方案

You'll have a hard time optimizing for that kind of query. A better option would be to compute a bounding box from the (x,y) coordinates and delta passed in. Then query for any locations where the coordinates fall in that box. That query would be much simpler and would be able to use any indexes you might have on the x and y fields.

Of course the results from that query aren't as exact since it's a bounding box rather than a circle. If you want better results, you can take the results from the bounding box query, then use the slower euclidean method to filter out the ones that don't fall into the circle.

这篇关于如何在欧几里德距离搜索/查询中优化mysql中的地理位置数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！