Как перебирать точки, чтобы разделить строку на части в PgSQL?
Контекст: я работаю с базой данных PostgreSQL (10.6) и PostGIS 2.5.
Мне нужна помощь в создании функции, которая разделит строку каждой строки из таблицы, используя точки из другой таблицы, и вставит разделенные части в новую таблицу. Каждая линия имеет свои правильные точки, и точки имеют точный порядок.
Я знаю, как разделить строку, используя ST_Line_Substring (документация здесь), и я знаю, как это сделать, используя точку, используя ST_LineLocatePoint (документация здесь).
У меня есть 2 проблемы: я не хочу разбивать линию в точке, а между каждой точкой в соответствии с их положением на линии в виде дроби. Таким образом, если есть точка на 0%, а следующая на 20% от линии, первая часть изменится от 0% до 10% от исходной линии. Вот небольшая иллюстрация (извините, если выглядит по-детски): 
Черная линия представляет собой исходную строку, синие кружки — это точки, а цветные «линии» представляют, какой тип строки_подстроки я хочу.
Решение, которое я придумал для этой проблемы, - просто добавить долю предыдущей точки и текущей точки на линии и разделить на 2 для начала части, и то же самое с текущей точкой и следующей, для конца части, как написано ниже:
ST_Line_Substring(line.geom, (
((ST_LineLocatePoint(line.geom, previous_point.geom) + ST_LineLocatePoint(line.geom, current_point.geom)) / 2),
((ST_LineLocatePoint(line.geom, current_point.geom) + ST_LineLocatePoint(line.geom, next_point.geom)) / 2)
)
Моя вторая проблема, я понятия не имею, как получить доступ к предыдущей и следующей точке из таблицы, содержащей точки. Сначала я хотел использовать какой-то механизм цикла, но я видел много ответов из других тем, говорящих, что это не оптимально, и лучше их избегать, и я не нашел ничего похожего на индекс в SQL для перебирать набор результатов или таблицу, чтобы легко получить доступ к предыдущему или следующему элементу.
Итак, мой главный вопрос: как мне перебирать таблицу точек для каждой строки, чтобы получить доступ к предыдущей, текущей и следующей точке одновременно?
@JimJones Я не очень привык к CTE, какие необработанные данные вам нужны? Всего несколько строк, чтобы увидеть, как сделаны таблицы?
Оператор создания таблицы и несколько записей помогут другим воспроизвести вашу среду и значительно увеличат ваши шансы получить ответ:-) CTE — это не что иное, как механизм для создания временной таблицы на основе запроса, чтобы вы могли re может запросить его во втором операторе. Довольно мощный, но может стать очень медленным при работе с большими таблицами.
Вот созданные таблицы и несколько кортежей для вставки: colabedit.com/vk78w Я не добавлял геометрические данные, потому что не могу ими поделиться, вам просто нужно знать, что они хранятся в формате SRID 4326. Точки несколько регулярно располагаются близко к линии, но не на ней.
Ответы 1
Я нашел решение, я использую курсор, а затем параметр RELATIVE, который позволяет мне получить доступ к другим индексам, кроме строки, которую я повторяю, вот как выглядит моя функция:
CREATE OR REPLACE FUNCTION splitLine(
id character varying,
linestring geometry(POINT,4326)
)
RETURNS VOID AS $$
DECLARE
prior_point RECORD;
current_point RECORD;
next_point RECORD;
cursor_point SCROLL CURSOR FOR SELECT id, line_id, line_order, geom
FROM points
WHERE id LIKE line_id
ORDER BY line_order;
BEGIN
OPEN cursor_points;
LOOP
FETCH FROM cursor_point INTO prior_point;
FETCH RELATIVE 1 FROM cursor_point INTO current_point;
FETCH RELATIVE 2 FROM cursor_point INTO next_point;
-- Operations on my points and line
END LOOP;
CLOSE cursor_point;
END; $$
LANGUAGE plpgsql;
Эй, Адриан. Не могли бы вы также предоставить несколько необработанных данных? Возможно, подзапросы или CTE должны подойти. Потрясающая иллюстрация кстати.