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 don't have any idea how to do it, can anyone please help?

Answers


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.


Need Your Help

Compiled private key

security compilation cryptography

Is it safe to distribute a compiled program (C, say - definitely not Java) with a cryptographic private key, in the sense of the key being deducible from the executable? What if multiple executables

How to rewrite all 404's to index.php using cPanel URLs?

apache .htaccess url-rewriting http-status-code-404 cpanel

I'm trying to rewrite all 404's to index.php where I use PHP's parse_url() to determine which file to include (e.g. about-us.php, contact-us.php) and I'm getting some really weird results.