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.
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-
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;
See the execution time-
For 150 entry, it is only 13ms.