# Find nearest points with MySQL from points Table

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

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;
```

Demo Here

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.