I have a DB Schema like this (from this tutorial by Google) -
我有一个这样的DB模式(来自本教程的谷歌)-
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
我想要的是找到一个给定点(由point_id)附近的点(由距离排序)
Location of a point
(x,y)
is (point_x
,point_y
) in DB点(x,y)的位置在DB中为(point_x,point_y)
I want to solve it with MySQL because my DB is already in MySQL.
我想用MySQL解决因为我的DB已经在MySQL中了。
Update-
Finding distance of 2 points is so easy like this-
求两点的距离就像这样简单
I want to sort on distance with MySQL.
我想用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.
所以u可以忽略这些圆。
I don't have any idea how to do it, can anyone please help?
我不知道怎么做,谁能帮忙吗?
2 个解决方案
#1
3
I have found a better solution than @1000111 's solution.
我找到了一个比@1000111的更好的解决方案。
There is custom DB type in MySQL for this kind of data which gives a better performance.
MySQL中有针对这种数据的自定义DB类型,可以提供更好的性能。
OpenGIS in MySQL is perfect for this.
MySQL中的OpenGIS非常适合这种情况。
Functions are given here.
函数给出。
An illustrative definition is given in this * 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-
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;
An example SQL Fiddle is given here.
See the execution time-
看到了执行时间
For 150 entry, it is only 13ms.
在150个词条中,只有13ms。
#2
1
Try this query please [a straight forward approach]:
请尝试这个查询[一个直接的方法]:
Suppose, you want to find the nearest 20 points of the point having point_id = 5
假设您希望找到具有point_id = 5的点的最近20个点
SET @givent_point_id := 5;
设置@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;
演示
More: You may have a look at MySQL SPATIAL DATATYPE.
更多信息:您可以查看MySQL空间数据类型。
MySQL spatial indexes use R-tree
as data structure which is specially designed for spatial access methods.
MySQL空间索引使用R-tree作为数据结构,是专门为空间访问方法设计的。
R-trees are tree data structures used for spatial access methods, i.e., for indexing multi-dimensional information such as geographical coordinates, rectangles or polygons.
r树是用于空间访问方法的树数据结构,即。,用于索引地理坐标、矩形或多边形等多维信息。
#1
3
I have found a better solution than @1000111 's solution.
我找到了一个比@1000111的更好的解决方案。
There is custom DB type in MySQL for this kind of data which gives a better performance.
MySQL中有针对这种数据的自定义DB类型,可以提供更好的性能。
OpenGIS in MySQL is perfect for this.
MySQL中的OpenGIS非常适合这种情况。
Functions are given here.
函数给出。
An illustrative definition is given in this * 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-
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;
An example SQL Fiddle is given here.
See the execution time-
看到了执行时间
For 150 entry, it is only 13ms.
在150个词条中,只有13ms。
#2
1
Try this query please [a straight forward approach]:
请尝试这个查询[一个直接的方法]:
Suppose, you want to find the nearest 20 points of the point having point_id = 5
假设您希望找到具有point_id = 5的点的最近20个点
SET @givent_point_id := 5;
设置@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;
演示
More: You may have a look at MySQL SPATIAL DATATYPE.
更多信息:您可以查看MySQL空间数据类型。
MySQL spatial indexes use R-tree
as data structure which is specially designed for spatial access methods.
MySQL空间索引使用R-tree作为数据结构,是专门为空间访问方法设计的。
R-trees are tree data structures used for spatial access methods, i.e., for indexing multi-dimensional information such as geographical coordinates, rectangles or polygons.
r树是用于空间访问方法的树数据结构,即。,用于索引地理坐标、矩形或多边形等多维信息。