从点表中使用MySQL查找最近的点

Question

从点表中使用MySQL查找最近的点

mysqlalgorithmgeolocationlocationquery-builder

9

我有一个类似于这样的数据库模式（来自Google的此教程）-

所以对于它们来说，图表中的实际点是这样的：

我想要的是找到与给定点（按点编号point_id）附近的点，按距离排序。

在数据库中，一个点的位置 (x,y) 是 (point_x,point_y)。

我想使用 MySQL 解决这个问题，因为我的数据库已经在 MySQL 中。

更新-

计算两点之间的距离非常容易，就像这样-

我想用MySQL按距离排序。

重新-

为了消除混淆，我希望稍后只保留圆圈内的点。但现在我只想找到排序后的点。

因此您可以忽略圆圈。

我不知道如何做，有人可以帮忙吗？

- Abrar Jahin

1

你会如何定义“最近点”？ - 1000111

1

计算两点之间的距离是一个常见的问题，在更新的问题中已经给出。 - Abrar Jahin

我没有要求那个。你标记了一个圆圈。可能你想要圆圈内的所有点。这就是为什么我问你抓取这些点的标准是什么？ - 1000111

是的，我想找到圆内的点，不过现在我只想找到排序后的点。 - Abrar Jahin

问题已更新，供您参考。 - Abrar Jahin

2个回答

1

请尝试以下查询 [一个直接的方法]: 假设你想找到距离点 point_id = 5 最近的20个点。 SET @givent_point_id := 5;

SELECT 
P1.point_id,
P1.point_name,
P1.point_x,
P1.point_y,
(POW(ABS((P2.point_x - P1.point_x)),2) + POW(ABS((P2.point_y - P1.point_y)),2)) AS sqr_distance
FROM Point P1,
    (SELECT point_x,point_y FROM Point WHERE point_id = @givent_point_id) P2
WHERE P1.point_id <> @givent_point_id
ORDER BY sqr_distance
LIMIT 20;

这里有演示

更多信息: 您可以查看MySQL空间数据类型。

MySQL空间索引使用R-tree作为数据结构，该数据结构专门设计用于空间访问方法。

R树是用于空间访问方法的树形数据结构，即用于索引多维信息，例如地理坐标、矩形或多边形。

- 1000111

很抱歉，我不能使用这种直接的方式。因为它太慢了，如果总点数> 10000，则我们无法从此查询中得到答案。 - Abrar Jahin

因为它是服务器，所以可能会在1秒内收到1000个请求，而且可能有100000个点。所以这个过程将失败。 - Abrar Jahin

不，这是数据库问题，因此我们必须应用一些算法以有效地找到结果，而不是这种方式。 - Abrar Jahin

你运行过这个查询吗？执行时间是多少？你可以分享一下这个查询的“Explain…”结果。@AbrarJahin - 1000111

现在只有4个点，所以不能进行比较。如果你拥有10000个点或更多，就可以进行比较。没问题，让我试试是否有其他选择，如果没有，我应该接受你的答案。谢谢你的帮助。 - Abrar Jahin

我已经找到了一种更好、更高效的方法。 - Abrar Jahin

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Abrar Jahin · Accepted Answer

我已经找到了比 @1000111 的解决方案更好的解决方法。

MySQL 中有一种自定义的 DB 类型适用于这种数据，可以提供更好的性能。

MySQL 中的 OpenGIS非常适合此类问题。

函数列表点击这里。

该StackOverflow问题中给出了对示例定义的说明。

我的解决方案如下 -

DB 表格 -

CREATE TABLE geoTable
(
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(30) NOT NULL,
    geoPoint POINT NOT NULL,
    SPATIAL INDEX(geoPoint)
) ENGINE=MyISAM;


INSERT INTO geoTable (name, geoPoint)
VALUES
  ( "A", GeomFromText('POINT(0.1 -1.01)') ),
  ( "B", ST_GeomFromText('POINT(56.31 2.81)') ),
  ( "C", ST_GeomFromText('POINT(11.1 1.176)') ),
  ( "ui", ST_GeomFromText('POINT(9.1 2.1)') );

SQL查询-

SELECT
  id,
  name,
  X(geoPoint) AS "latitude",
  Y(geoPoint) AS "longitude",
  (
    GLength(
      LineStringFromWKB(
        LineString(
          geoPoint, 
          GeomFromText('POINT(51.5177 -0.0968)')
        )
      )
    )
  )
  AS distance
FROM geoTable
  ORDER BY distance ASC;

这里提供了一个SQL Fiddle的示例，点击这里查看。

查看执行时间-

对于150条数据，只需要13毫秒。