使用MySQL空间扩展从一个点查找N个最近的LineString [英] Find N Nearest LineString From A Point Using MySQL Spatial Extensions

问题描述

我正在使用 MySQL Spatial Extensions 来存储有关道路和道路的数据酒店.我将酒店数据存储为Point，而道路数据存储为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

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.

我的问题得到一个数字N和一个点P，从点P查找N条最近的道路的SQL查询是什么?该距离由道路上的路段到上述点之间的最小垂直距离定义. (尽管实际上，最近的距离应该在高速公路的大门和酒店之间，但是在这种情况下，我们可以从任意一点进入高速公路:P)

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)

如果没有单个SQL语句解决方案，那么我可以接受中间SQL查询和后处理.但是什么是有效的SQL查询以及如何对数据进行后处理?

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?

推荐答案

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

You can create two functions in the database:

1. 距离:这将为您提供两点之间的距离
2. DistanceFromLine:这里的距离将根据直线上的每个点计算，并且将为您提供最短的距离.

比较点和线之间的距离，然后选择最短的点.

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

这是距离函数

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函数:

---

Here is DistanceFromLine function:

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

这篇关于使用MySQL空间扩展从一个点查找N个最近的LineString的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！