se/SE/dev/sql/biale_plamy.sql

DROP FUNCTION `test_find_shortest_way`;
CREATE DEFINER=`root`@`94.158.130.34` FUNCTION `test_find_shortest_way`(`pID` INT) RETURNS POINT NOT DETERMINISTIC READS SQL DATA SQL SECURITY DEFINER BEGIN

    DECLARE A, B, P POINT;
    DECLARE I, N INT;
    
    SET P = (SELECT `the_geom` FROM `Rozdzielcza_test_bzyk_punkty_adresowe` WHERE `ID` = pID);

    SET @the_geom = (SELECT `the_geom` FROM `Rozdzielcza_test_bzyk_drogi` WHERE st_distance(`the_geom`, P) < 0.01 ORDER BY st_distance(`the_geom`, P) LIMIT 1);
    IF @the_geom IS NULL THEN
        SET @the_geom = (SELECT `the_geom` FROM `Rozdzielcza_test_bzyk_drogi` WHERE st_distance(`the_geom`, P) < 0.1 ORDER BY st_distance(`the_geom`, P) LIMIT 1);
    END IF;

    SET N = coalesce(numpoints(@the_geom), 0);
    IF N < 2 THEN
        RETURN NULL;
    END IF;

    IF N = 2 THEN
        SET @line = @the_geom;
    ELSE
        SET @distance = NULL;
        SET I = 1;
        WHILE (I < N) DO
            SET A = pointn(@the_geom, I);
            SET B = pointn(@the_geom, I+1);
            SET @line_cur = LineStringFromText(concat('LineString(',X(A),' ',Y(A),',',X(B),' ',Y(B),')'));
            SET @distance_cur = st_distance(P, @line_cur);
            IF @distance is NULL or @distance_cur < @distance THEN
                SET @distance = @distance_cur;
                SET @line = @line_cur;
            END IF;
            SET I = I + 1;
        END WHILE;
    END IF;

    SET A = pointn(@line, 1);
    SET B = pointn(@line, 2);

    SET @A1 = (Y(A)-(Y(B)))/(X(A)-X(B));
    SET @B1 = Y(A)-@A1*X(A);
    SET @A2 = -1/@A1;
    SET @B2 = Y(P)-@A2*X(P);
    SET @X = (@B2-@B1)/(@A1-@A2);
    SET @Y = @A1*@X+@B1;

    SET @XPOINT = geomfromtext(concat('POINT(',@X,' ',@Y,')'));
    SET @distance = st_distance(@XPOINT, @line);
    IF @distance = st_distance(@XPOINT, A) THEN
        SET @result = A;
    ELSEIF @distance = st_distance(@XPOINT, B) THEN
        SET @result = B;
    ELSE
        SET @result = @XPOINT;
    END IF;
    RETURN @result;
END