mysql - 使用 MySQL Spatial Extensions 从一个点查找 N 个最近的 LineString

Question

我正在使用 MySQL Spatial Extensions存储有关道路和酒店的数据。我将酒店数据存储为 Point，而将道路数据存储为 LineString。表格看起来像这样

CREATE TABLE IF NOT EXISTS `Hotels` (
    `id` int unsigned NOT NULL AUTO_INCREMENT,
    `name` text,
    `coordinate` point NOT NULL,
    PRIMARY KEY (`id`),
    SPATIAL KEY `coordinate` (`coordinate`),
)

CREATE TABLE IF NOT EXISTS `Roads` (
    `id` int unsigned NOT NULL AUTO_INCREMENT,
    `name` text,
    `route` linestring NOT NULL,
    PRIMARY KEY (`id`),
    SPATIAL KEY `coordinate` (`route`),
)

实例的可视化是这样的。

我的问题是给定一个数字 N 和一个点 P，从 P 点找到 N 条最近的道路的 SQL 查询是什么？该距离由道路中的路段与上图所示点之间的最小垂直距离定义。(虽然在现实中，最近的距离应该是在高速公路大门和酒店之间，但在这种情况下，我们可以从任何点进入高速公路:P)

如果这个问题没有单一的SQL语句解决方案，中间的SQL查询和后处理对我来说是可以接受的。但是什么是有效的 SQL 查询以及如何对数据进行后处理？

Answer 1

您可以在数据库中创建两个函数:

1. 距离:这将为您提供两点之间的距离
2. DistanceFromLine:这里的距离将从直线上的每个点开始计算，并给出最短距离。

比较点和线之间的距离并选择最短的。

这里是距离函数

---

delimiter //

CREATE FUNCTION distance (latA double, lonA double, latB double, LonB double)
RETURNS double DETERMINISTIC
    BEGIN
        SET @RlatA = radians(latA);
        SET @RlonA = radians(lonA);
        SET @RlatB = radians(latB);
        SET @RlonB = radians(LonB);
        SET @deltaLat = @RlatA - @RlatB;
        SET @deltaLon = @RlonA - @RlonB;
        SET @d = SIN(@deltaLat/2) * SIN(@deltaLat/2) +
        COS(@RlatA) * COS(@RlatB) * SIN(@deltaLon/2)*SIN(@deltaLon/2);
        RETURN 2 * ASIN(SQRT(@d)) * 637101;
    END//

---

这里是 DistanceFromLine 函数:

---

DROP function IF EXISTS `DistanceFromLine`;
delimiter //
    CREATE FUNCTION `DistanceFromLine`(
    route LINESTRING, point1 POINT
    ) RETURNS INT DETERMINISTIC
        BEGIN
        DECLARE a INT Default 0 ;
        DECLARE minDistance INT Default 0;
        DECLARE currentDistance INT Default 0;
        DECLARE currentpoint point ;
        DECLARE size INT Default 0 ;
        SET size =  NumPoints(route);
              simple_loop: LOOP
       SET a = a+1;
       SET currentpoint = PointN(route,a);
       SET currentDistance = Distance(X(point1), Y(point1),       
               X(currentpoint),Y(currentpoint));

       IF a = 1 THEN
        SET minDistance = currentDistance;
           END IF;

       IF currentDistance < minDistance THEN
        SET minDistance = currentDistance;
       END IF;
       IF a=size THEN
                 LEAVE simple_loop;
       END IF;
          END LOOP simple_loop;
     RETURN (minDistance);
 END//

---