mysql - 在 MySQL 中查找距离给定点最近的点

Question

我有一组点，我从中创建了一个多点。

SET @multi_point = ST_GeomFromText('MULTIPOINT(-118.2845938 34.0252385, -118.2867610 34.0221188, -118.2905912 34.0227248, -118.284119 34.021846, -118.2864676 34.0186438, -118.2886342 34.0203211, -118.2907290 34.0193680, -118.2831326 34.0192874, -118.2828242 34.0205473)');

我还有一点

SET @home = ST_GeomFromText('POINT(-118.2819136 34.0261177)')

来自 @home 我想找到最近的 3 个点。它们是多点 @multi_point 的一部分。我想到首先使用 ST_DISTANCE 确定从 @home 点到多点中每个点的距离。但我得到以下信息:

SELECT ST_DISTANCE(@home, @multi_point)

+----------------------------------+
| ST_DISTANCE(@home, @multi_point) |
+----------------------------------+
|            0.0028207205958764945 |
+----------------------------------+

看起来我只得到了点到多点的最短距离。问题:

1. 有没有办法获得到每个点的最短距离，并确定这些点本身？

2. 是否有更好的方法来确定从一个点 @home 到 @multi_point 中的其余点的最近邻居？

编辑我尝试了 Paul Spiegel 将这些点存储在表格中。但是当我存储 Point 类型时，它被存储为垃圾值:

+-----------------+---------------------------+
| Name            | Coordinates               |
+-----------------+---------------------------+
| P1              |        [2]pxA@            |
| p2              |        +1Z]:PA@           |
| p3              |        a]KA@              |
| p4              |        X ]qgpA@           |
| p5              |        EW3|U]A@           |
| p6              |        M]bKzA@            |
| p7              |        Ku/]2ƇA@           |
| p9              |        =];A@              |
| p8              |        u`x]A@             |
+-----------------+---------------------------+

此外，当我查询它时，距离为空。

SELECT P.Name, ST_DISTANCE(@home, P.Coordinates) AS dist 
FROM Placemark P ORDER BY dist LIMIT 3;

+--------------+------+
| Name         | dist |
+--------------+------+
| p1           | NULL |
| p2           | NULL |
| p3           | NULL |
+--------------+------+

如何解决这个问题？

Answer 1

以下是使用临时表的示例:

drop temporary table if exists Placemark;
create temporary table Placemark(
    Name varchar(50),
    Coordinates point
);

insert into Placemark(Name, Coordinates) values
    ('p1', POINT(-118.2845938, 34.0252385)),
    ('p2', POINT(-118.2867610, 34.0221188)),
    ('p3', POINT(-118.2905912, 34.0227248)),
    ('p4', POINT(-118.284119 , 34.021846 )),
    ('p5', POINT(-118.2864676, 34.0186438)),
    ('p6', POINT(-118.2886342, 34.0203211)),
    ('p7', POINT(-118.2907290, 34.0193680)),
    ('p8', POINT(-118.2831326, 34.0192874)),
    ('p9', POINT(-118.2828242, 34.0205473));

SET @home = ST_GeomFromText('POINT(-118.2819136 34.0261177)');

SELECT 
    P.Name,
    ST_AsText(P.Coordinates) as Coordinates,
    ST_DISTANCE(@home, P.Coordinates) AS dist 
FROM Placemark P
ORDER BY dist
LIMIT 3

结果:

Name | Coordinates                    | dist
p1   | POINT(-118.2845938 34.0252385) | 0.00282072059587649
p4   | POINT(-118.284119 34.021846)   | 0.00480741199088132
p9   | POINT(-118.2828242 34.0205473) | 0.00564433773972052

演示:http://rextester.com/GMPK57301

要定义 POINT，请使用 POINT(-118.2845938, 34.0252385) 或 ST_GeomFromText('POINT(-118.2845938 34.0252385)')。要以可读形式查看值，请使用 ST_AsText()。