Obsidian/Doktoritöö/001_artiklid/OSM/shell/sql/fix_small_gaps.sql

SET myvars.schema TO :'schema';

DO $$
DECLARE
    r RECORD;
    schema_name TEXT := current_setting('myvars.schema');  -- loeme muutuja `-v schema`
    batch_size INTEGER := 1000;
    offset_val INTEGER := 0;
    done BOOLEAN := FALSE;
BEGIN
  WHILE NOT done LOOP
    done := TRUE;

    FOR r IN
      EXECUTE format($fmt$
        SELECT v1.id AS id1, v2.id AS id2,
               ST_MakeLine(v1.the_geom, v2.the_geom) AS new_geom
        FROM %I.links_vertices_pgr v1
        JOIN %I.links_vertices_pgr v2
          ON v1.id < v2.id
        WHERE ST_DWithin(v1.the_geom, v2.the_geom, 2)
          AND NOT EXISTS (
              SELECT 1 FROM %I.links l
              WHERE (l.source = v1.id AND l.target = v2.id)
                 OR (l.source = v2.id AND l.target = v1.id)
          )
        ORDER BY v1.id, v2.id
        LIMIT %s OFFSET %s
      $fmt$, schema_name, schema_name, schema_name, batch_size, offset_val)
    LOOP
      done := FALSE;

      EXECUTE format($sql$
        INSERT INTO %I.links (
          geom, source, target, cost, reverse_cost,
          eid, highway, surface, oneway, maxspeed, name
        )
        VALUES (
          $1, $2, $3,
          ST_Length($1)::float8,
          ST_Length($1)::float8,
          NULL, NULL, NULL, 0, NULL, NULL
        ) $sql$, schema_name)
      USING r.new_geom, r.id1, r.id2;
    END LOOP;

    offset_val := offset_val + batch_size;
  END LOOP;
END $$;