-- таблица точек на дорогах напротив терминалов. Генерируются точки на всех ближайших дорогах в заданом радиусе
CREATE TEMP TABLE cut_points AS (
SELECT
terminals.id AS id, line_id AS line_id,
ST_ClosestPoint(subquery.wkb_geometry::geography, terminals.geom::geography) AS wkb_geometry,
ST_Distance(terminals.geom::geography, ST_ClosestPoint(subquery.wkb_geometry, terminals.geom)::geography) AS dist
FROM    (SELECT
            ST_Multi(St_collect(network.wkb_geometry)) AS wkb_geometry,
            network.line_id
        FROM
            (SELECT (ST_dump(wkb_geometry)).geom as wkb_geometry, id AS line_id FROM roads  ) AS network
        GROUP BY network.line_id    ) AS subquery,
        terminals
WHERE
    st_distance(st_closestpoint(subquery.wkb_geometry, terminals.geom)::geography,terminals.geom::geography) < $MAX_CONN_LEN
);
COMMENT ON TABLE cut_points IS 'nodes at network near poinst. Here points at all roads in distance';

 -- таблица для выборки только одной ближайшей точки от каждого терминала до дороги
CREATE TEMP TABLE nearest_cutpoints AS
  (SELECT cut_points.id,
          cut_points.line_id,
          cut_points.wkb_geometry
   FROM cut_points,

     (SELECT id,
             min(dist) AS dist
      FROM cut_points
      GROUP BY id) AS subquery
   WHERE cut_points.id=subquery.id
     AND cut_points.dist=subquery.dist);
COMMENT ON TABLE nearest_cutpoints IS 'Select only one nearest network node for each point';

    -- таблица связей терминалов с дорогами
  DROP TABLE IF EXISTS conn;
  CREATE TABLE conn AS (
    SELECT
    terminals.id AS id,
    ST_MakeLine(terminals.geom,nearest_cutpoints.wkb_geometry) AS wkb_geometry,
    ST_Distance(terminals.geom::geography, nearest_cutpoints.wkb_geometry::geography) AS dist
    FROM terminals JOIN nearest_cutpoints ON terminals.id = nearest_cutpoints.id
  );
COMMENT ON TABLE conn IS 'Lines from points to network';

--Добавление точку в дороги в местах примыкания пепендикуляров от поставщиков
--Insert nodes to network at intercections with lines from points
UPDATE roads
SET wkb_geometry=ST_Snap(roads.wkb_geometry,points_near_way.wkb_geometry,$TOLERANCE)
FROM
  (SELECT ST_Collect(points.wkb_geometry) AS wkb_geometry,
          points.line_id
   FROM nearest_cutpoints AS points
   GROUP BY points.line_id
    ) AS points_near_way
WHERE points_near_way.line_id=roads.id;