使用MySQL Spatial Extensions从一个点找到N个最近的LineString

I'm using MySQL Spatial Extensions to store data about roads and hotels. I store the hotel data as a Point while I store the road data as LineString. The tables look like this

我正在使用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`),
)

The visualization of an instance would be like this.

实例的可视化将是这样的。

My problem is given a number N and a point P, what is the SQL query to find N nearest roads from point P? The distance is defined by the smallest perpendicular distance between a segment in the road to the point like shown above. (although in the reality, the nearest distance should be between the highway gate and a hotel, but in this case, we can enter the highway from any point :P)

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

If there is no single SQL statement solution for this problem, an intermediary SQL query and a post-processing are acceptable for me. But what would be an efficient SQL query and how to post-process the data?

如果没有针对此问题的单个SQL语句解决方案,则可以接受中间SQL查询和后处理。但是什么是有效的SQL查询以及如何对数据进行后处理?

3 个解决方案

#1

You can create two functions in the database:

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

1. Distance : This will give you distance between two points

距离:这将给你两点之间的距离

2. DistanceFromLine : Here distance will be calculated from each point in line, and will give you the shortest distance.

DistanceFromLine:此处距离将从每个点的线上计算出来,并为您提供最短的距离。

Compare the distance between your point and lines and choose the shortest one.

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

Here is the Distance function

这是距离函数

---

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//

---

Here is DistanceFromLine function:

这是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//

---

#2

This was a very useful answer for me, but I'm using MySQL 5.7.18, which has more advanced or just different geo query functions. The distance function posted isn't needed anymore- use ST_Distance_Sphere. So here's an update of the same code to make DistanceFromLine compliant with modern (5.7.6+) MySQL...

这对我来说是一个非常有用的答案,但我使用的是MySQL 5.7.18,它具有更高级或更不同的地理查询功能。不再需要发布的距离函数 - 使用ST_Distance_Sphere。所以这里是相同代码的更新,使DistanceFromLine符合现代(5.7.6+)MySQL ......

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 =  ST_NumPoints(route);
              simple_loop: LOOP
       SET a = a+1;
       SET currentpoint = ST_PointN(route,a);
       SET currentDistance = ST_Distance_Sphere(point1,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//

#3

I have been also working on this issue, but unfortunately finding the nearest road for Hotels is an unfavorable solution. I have found that the entrance the road is on is the definitive answer. In other words the address. This means having an address table and matching points to nearest address road.

我一直在研究这个问题,但遗憾的是找到最近的酒店之路是一个不利的解决方案。我发现道路上的入口是明确的答案。换句话说就是地址。这意味着有一个地址表和匹配点到最近的地址道路。