使用 MySQL 从点表中查找最近的点 [英] Find nearest points with MySQL from points Table

问题描述

我有一个

所以他们在图中的实际点是这样的-

我想要的是找到给定点附近的点(按point_id)按距离排序的点

<块引用>

点(x,y)在DB中的位置是(point_x,point_y)

我想用

我想用 MySQL 对距离进行排序.

<小时>

重新-

为了消除混乱，稍后我想要圆圈内的点.但现在我只想找到排序后的点.

所以你可以忽略圆圈.

<小时>

我不知道怎么做，有人可以帮忙吗?

解决方案

我找到了比@1000111 的解决方案更好的解决方案.

在 MySQL 中为此类数据提供了自定义 DB 类型，可提供更好的性能.

对于 150 条记录，只有 13ms.

I have a DB Schema like this (from this tutorial by Google) -

So the actual points in a graph for them is like this-

What I want is to find points near a given point (by point_id) point ordered by distance

Location of a point (x,y) is (point_x,point_y) in DB

I want to solve it with MySQL because my DB is already in MySQL.

---

Update-

Finding distance of 2 points is so easy like this-

I want to sort on distance with MySQL.

---

Re-

For removing the confusions, I want the points inside the circle, later. But now I want to find only the sorted points.

So u can ignore the circles.

---

I don't have any idea how to do it, can anyone please help?

解决方案

I have found a better solution than @1000111 's solution.

There is custom DB type in MySQL for this kind of data which gives a better performance.

OpenGIS in MySQL is perfect for this.

Functions are given here.

An illustrative definition is given in this StackOverflow question.

My solution is like this-

DB Table-

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 Query-

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;

---

An example SQL Fiddle is given here.

See the execution time-

For 150 entry, it is only 13ms.

这篇关于使用 MySQL 从点表中查找最近的点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！