Как решить проблемы в режиме онлайн с PostGIS

Question

Я нашел интересный вопрос о точечных задачах, которые нужно решить с помощью Java на stackru. Пост был сильно опущен и удален (справедливо!), Потому что, очевидно, кто-то просто хотел, чтобы другие делали его домашнюю работу. В любом случае, я подумал, что это будет действительно хороший пример PostGIS. Цель состоит в том, чтобы не использовать структуры plpgsql / control, поскольку там уже есть множество примеров - я хочу писать запросы. Таким образом, "параметры" находятся в CTE (давай, побей меня об этом:D ... или вставь код в функцию и поделись). Вот так.

Постановка задачи
Вам задают координаты (0,0) (0,1) (0,2) (0,3) (1,5) (1,4) (3,5) в качестве входных данных.

1. Проверьте, находятся ли указанные точки в одной линии.
2. Учитывая 2 точки, найдите точки между ними. Вход: (0,0) (0,3), Выход: (0,1) (0,2)
3. По заданной точке найдите точки на самой большой линии - с точки зрения содержащихся в ней точек - проходящих через эту точку. Вход: (0,1), Выход: (0,0) (0,1) (0,2) (0,3)

Answer 1

1.

WITH param AS (
    SELECT
        ARRAY[ST_MakePoint(0,0), ST_MakePoint(0,1), ST_MakePoint(0,2), ST_MakePoint(0,3), ST_MakePoint(1,5), ST_MakePoint(1,4), ST_MakePoint(3,5)] AS points
), ps AS (
    SELECT ROW_NUMBER() OVER() AS id, tmp.p
    FROM (
        SELECT UNNEST(points) AS p
        FROM param
    ) AS tmp
)
-- if every point triple forms a straight line, all points form a straight line
SELECT bool_and(ST_Area(ST_MakePolygon(ST_MakeLine(ARRAY[ps1.p, ps2.p, ps3.p, ps1.p]))) = 0)
FROM ps AS ps1, ps AS ps2, ps AS ps3
WHERE ps1.id < ps2.id
    AND ps2.id < ps3.id;

---

2.

WITH param AS (
    SELECT
        ARRAY[ST_MakePoint(0,0), ST_MakePoint(0,1), ST_MakePoint(0,2), ST_MakePoint(0,3), ST_MakePoint(1,5), ST_MakePoint(1,4), ST_MakePoint(3,5)] AS points,
        1 AS p1_index,
        4 AS p2_index
), ps AS (
    SELECT ROW_NUMBER() OVER() AS id, tmp.p
    FROM (
        SELECT UNNEST(points) AS p
        FROM param
    ) AS tmp
)
SELECT ST_AsText(ps.p)
FROM ps, param
WHERE ST_Intersects(ps.p, ST_MakeLine(points[p1_index], points[p2_index]))
    AND ps.id NOT IN (p1_index, p2_index);

---

3.

WITH param AS (
    SELECT
    ARRAY[ST_MakePoint(0,0), ST_MakePoint(0,1), ST_MakePoint(0,2), ST_MakePoint(0,3), ST_MakePoint(1,5), ST_MakePoint(1,4), ST_MakePoint(3,5)] AS points,
    2 AS p_index
), ps AS (
    SELECT (ROW_NUMBER() OVER())::INTEGER AS id, tmp.p
    FROM (
        SELECT UNNEST(points) AS p
        FROM param
    ) AS tmp
), straight_triple AS (
    SELECT ps1.id AS p1, ps2.id AS p2, ps3.id AS p3
    FROM ps AS ps1, ps AS ps2, ps AS ps3
    WHERE ST_Area(ST_MakePolygon(ST_MakeLine(ARRAY[ps1.p, ps2.p, ps3.p, ps1.p]))) = 0
        AND ps1.id < ps2.id
        AND ps2.id < ps3.id
    ORDER BY ps1.id
), candidate_line AS (
    -- the point could be on more than one line
    SELECT p1s || p2 || p3 AS p_indexes, ROW_NUMBER() OVER(ORDER BY cardinality(p1s || p2 || p3) DESC) AS n_points_rank
    FROM (
        -- group up points by point pairs they form a straight line with
        SELECT array_agg(p1) AS p1s, p2, p3, ROW_NUMBER() OVER(PARTITION BY (array_agg(p1))[1] ORDER BY cardinality(array_agg(p1)) DESC) AS length_rank
        FROM straight_triple
        GROUP BY p2, p3
    ) AS pair_intersection, param
    WHERE length_rank = 1 AND (p1s || p2 || p3) @> ARRAY[p_index]
)
-- pick the longest line, or any from multiple longest lines
SELECT ST_AsText(points[i])
FROM (
    SELECT UNNEST(p_indexes) AS i
    FROM candidate_line
    WHERE n_points_rank = 1
) AS indexes, param;