есть две таблицы: линии и точки, нужно для каждой точки найти ближайшую линию, попробовал вот такой запрос, подскажите как можно его оптимизировать:

WITH unfiltered AS
(
    SELECT t1.id id1,
           t2.id id2,
           t1.geometry g1,
           t2.geometry g2,
           ST_Distance(t1.geometry, t2.geometry) dist
    FROM terminals t1,
         private.auto_roads t2
    ORDER BY t1.id, dist ASC
)
SELECT DISTINCT ON (id1) id1, id2, g1, g2 FROM unfiltered;

Lateral join

@Komzpa спасибо! Как-то так?

    SELECT t1.geometry g1,
           t2.geometry g2
    FROM terminals t1,
    LATERAL (
        SELECT geometry
        FROM private.auto_roads
        ORDER BY ST_Distance(t1.geometry, auto_roads.geometry) ASC
        LIMIT 1
    ) t2

да

напиши сколько у тебя записей в таблицах и как долго выполняется?

130606x6390

запустил

пару секунд выполняется, но я ORDER BY ST_Distance заменил на OREDR BY <->

Вот так пока оставил:

SELECT
  terminals.geometry g1
  roads.geometry g2
FROM
  terminals
CROSS JOIN LATERAL
  (SELECT geometry
   FROM private.auto_roads
   ORDER BY
     terminals.geometry <-> geometry
   LIMIT 1) AS roads;

Есть LINESTRING и есть точка на ней, как добавить эту точку в эту LINESTRING (не в конец, а именно между соседними)?

Видимо, ST_Snap мне поможет.

Не помог ST_Snap, в итоге пока остановился вот  на таком варианте:

WITH geom AS
  ( SELECT 'LINESTRING(0 0, 5 5, 100 100)'::geometry l,
           'POINT(50 50)'::geometry p),
     series AS
  ( SELECT generate_series(1, ST_NPoints(l)-1) n
   FROM geom)
SELECT ST_AsText(ST_AddPoint(geom.l, geom.p, series.n))
FROM geom,
     series
WHERE ST_Contains(ST_MakeLine(ST_PointN(l, n), ST_PointN(l, n+1)), geom.p);

`

Пример ответа:

             st_astext             
-----------------------------------
 LINESTRING(0 0,5 5,50 50,100 100)
(1 row)

Подскажите можно ли как-то куммулятивно обновить поле, если WHERE возвращает несколько записей?

не понятен вопрос. Обновить все записи?

Вот простой пример, чтобы понять чего я хочу:

with out as (
  select 1 id, generate_series(1, 10) n
)
update ttt
set value = value + out.n
from out
where ttt.id = out.id;

хочу в value получить общую сумму, а оно возвращает только +1

посчитай в select агрегацию и присвой в update агрегацию+1

а если у меня не +, а некая функция, которую я хочу последовательно применить к полю?

ты можешь написать такой селект, который сделает то, что тебе надо получить на выходе?