Find nearest points with MySQL from points Table
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.
Finding distance of 2 points is so easy like this-
I want to sort on distance with MySQL.
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-
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)') );
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.
Try this query please [a straight forward approach]:
Suppose, you want to find the nearest 20 points of the point having point_id = 5
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;
More: You may have a look at MySQL SPATIAL DATATYPE.
MySQL spatial indexes use R-tree as data structure which is specially designed for spatial access methods.
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.