Find nearest points with MySQL from points table

Question

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

Thus, the actual points in the graph for them are:

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

The location of the point (x,y) is ( point_x , point_y ) in the DB

I want to solve it using MySQL , because my database is already in MySQL.

---

Update -

Finding a distance of 2 points is so simple:

I want to sort by distance with MySQL.

---

Re -

To eliminate the confusion, I want the points inside the circle to be later. But now I want to find only sorted points.

This way you can ignore circles.

---

I do not know how to do this, can anyone help?

Answer 1

I found a better solution than solution @ 1000111.

MySQL has a custom DB type that provides better performance.

OpenGIS in MySQL is perfect for this.

Functions are given here .

An illustrative definition is given in https://stackoverflow.com/a/166269/168 .

My solution is this:

Table 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 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 runtime -

For 150 entries, this is only 13 ms.

Answer 2

Try this request, please [direct approach]:

Suppose you want to find the next 20 points of a point that has 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; 

Demo here

More: Perhaps you can take a look at the MySQL DATATYPE PROBLEM . p>

MySQL spatial indexes use R-tree as a data structure specifically designed for spatial access methods.

R-trees are tree-like data structures used for spatial access methods, that is, for indexing multidimensional information, such as geographic coordinates, rectangles or polygons.