List nearby points sorted by distance in Mysql Spatial

Question

List nearby points sorted by distance in Mysql Spatial

Navigation

#1 by (1 votes)

3

I have georeferenced points of interest in a table

CREATE TABLE 'poi' (
    'poiID' INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    'name' VARCHAR(50) NULL DEFAULT NULL,
    'location' POINT NOT NULL,
    PRIMARY KEY ('poiID'),
    SPATIAL INDEX 'location' ('location')
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM;

The field location use field SPATIAL POINT indexed to speed up searches.

I have generated several manually inserted Barcelona points with

INSERT INTO 'poi'(location) VALUES(POINTFROMTEXT('POINT(<lat> <lng>)'));

I now want to create the query to show the points near a specific location.

mysql

asked by Webserveis 01.12.2017 в 20:05

source

1 answer

How to add columns from values in a row? TypeError: integer argument expected, got 'str'

score 1 · Accepted Answer

The issue is that you understand by close. Let's see, first of all we define an example according to your question:

CREATE TABLE 'poi' (
    'poiID' INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    'name' VARCHAR(50) NULL DEFAULT NULL,
    'location' POINT NOT NULL,
    PRIMARY KEY ('poiID'),
    SPATIAL INDEX 'location' ('location')
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM;

INSERT INTO poi(name, location) VALUES('Barcelona 1', POINTFROMTEXT('POINT(41.48459 2.175202)'));
INSERT INTO poi(name, location) VALUES('Barcelona 2', POINTFROMTEXT('POINT(41.312452 2.108002)'));
INSERT INTO poi(name, location) VALUES('Barcelona 3', POINTFROMTEXT('POINT(41.375156 2.154084)'));
INSERT INTO poi(name, location) VALUES('Calafell', POINTFROMTEXT('POINT(41.196385 1.566925)'));
INSERT INTO poi(name, location) VALUES('Tarragona', POINTFROMTEXT('POINT(41.102283 1.227722)'));

Note: Points are real points obtained from google maps.

Now, we have a point of interest in Barcelona, for example: POINT(41.374991 2.149186) and we just want to sort all the points in the table by the "distance", we could do as suggested in this question :

SELECT @punto := 'POINT(41.374991 2.149186)';
SELECT
  poiID,
  name,
  X(location) AS "latitude",
  Y(location) AS "longitude",
  GLength(LineString(location, GeomFromText(@punto)))  AS distance
FROM poi
  ORDER BY distance ASC;

That is:

For each point we put a line between it and the POI using LineString()
Then we calculate the distance with GLength()

The result:

+---+-------------+-----------+----------+-----------------------+
| 3 | Barcelona 3 | 41.375156 | 2.154084 | 0.0049007784075594946 |
+---+-------------+-----------+----------+-----------------------+
| 2 | Barcelona 2 | 41.312452 | 2.108002 | 0.07488156233012315   |
+---+-------------+-----------+----------+-----------------------+
| 1 | Barcelona 1 | 41.48459  | 2.175202 | 0.11264445417773167   |
+---+-------------+-----------+----------+-----------------------+
| 4 | Calafell    | 41.196385 | 1.566925 | 0.60903856639543      |
+---+-------------+-----------+----------+-----------------------+
| 5 | Tarragona   | 41.102283 | 1.227722 | 0.9609711528240588    |
+---+-------------+-----------+----------+-----------------------+

We see that effectively "distance" is a number that reflects the closeness between two points, as we have stated. The problem is if you also want to define a "radius" of proximity, that is, nearby points would be those that are within a radius of 10 kilometers, for example. Here the issue is that the distance that returns GLength() is a number within the reference system, if we wanted to see the points within a radius of 10 km we could use this Formula :

SELECT
    poiID, 
    name,
    (
      6371 * acos (
      cos ( radians(X(POINTFROMTEXT(@punto))) )
      * cos( radians( X(location) ) )
      * cos( radians( Y(location) ) - radians(Y(POINTFROMTEXT(@punto))) )
      + sin ( radians(X(POINTFROMTEXT(@punto))) )
      * sin( radians( X(location) ) )
    )
) AS distance_km
FROM poi
HAVING distance_km < 10
ORDER BY distance_km

Exit:

+-------+-------------+---------------------+
| poiID | name        | distance_km         |
+-------+-------------+---------------------+
| 3     | Barcelona 3 | 0.40910330831504094 |
+-------+-------------+---------------------+
| 2     | Barcelona 2 | 7.757493953438743   |
+-------+-------------+---------------------+

Keep in mind that it is an approximation to the real distance, does not take into account heights and presupposes that the earth is a perfect sphere.