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

问题描述

我有这样的

因此，图表中的实际点是像这样 -

我想要的是找到积分靠近指定点（由 point_id ）按距离排序点

点的位置（x，y）在DB point_x ， point_y ） >

我想解决它与

我想根据距离排序MySQL。

---

Re -

为了消除混淆，我希望以后可以在圆内点。但现在我只想找到已排序的点。

所以你可以忽略这些圈子。

---

我不知道该怎么做，任何人都可以请帮忙吗？

解决方案

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

>

只有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屋！