Obsidian/Doktoritöö/001_artiklid/OSM/shell/merge_links.sh

#!/bin/bash
set -e
source env.sh

echo "🔗 Ühendan ja puhastan kõik links_* tabelid skeemist ${destination_schema}"

psql ${CONNECTION_NAME} -d ${destination_dbname} <<EOF

-- 1. Ühendame kõik maakondlikud links_* tabelid ühiseks tabeliks
DO \$\$
DECLARE
  schema_name text := '${destination_schema}';
  template_name text := (
    SELECT 'links_' || relation_id
    FROM ${destination_schema}.administrative
    WHERE admin_level = '6'
    LIMIT 1
  );
  r record;
BEGIN
  RAISE NOTICE 'Loon %', schema_name || '.links';
  EXECUTE format('DROP TABLE IF EXISTS %I.links CASCADE', schema_name);
  EXECUTE format('CREATE TABLE %I.links (LIKE %I.%I INCLUDING ALL)', schema_name, schema_name, template_name);

FOR r IN
  SELECT tablename
  FROM pg_tables
  WHERE schemaname = schema_name AND tablename LIKE 'links_%'
    AND tablename NOT IN ('links', 'links_vertices')
LOOP
    EXECUTE format('INSERT INTO %I.links SELECT * FROM %I.%I', schema_name, schema_name, r.tablename);
  END LOOP;
END
\$\$;

-- 2. Eemalda täpselt kattuvad geomeetriad (maakondade piiride tõttu)
DO \$\$
DECLARE
  schema_name text := '${destination_schema}';
BEGIN
  EXECUTE format(
    'DELETE FROM %I.links a
     USING (
       SELECT geom, MIN(ctid) AS keep_ctid
       FROM %I.links
       GROUP BY geom
       HAVING COUNT(*) > 1
     ) b
     WHERE a.geom = b.geom AND a.ctid <> b.keep_ctid',
    schema_name, schema_name
  );
END
\$\$;

-- 3. Eemalda isoleeritud lõigud
DO \$\$
DECLARE
  schema_name text := '${destination_schema}';
BEGIN
  -- Loo optimeeritud tiputabel
  EXECUTE format('
    DROP TABLE IF EXISTS %I.links_vertices;
    CREATE UNLOGGED TABLE %I.links_vertices AS
    WITH endpoints AS (
      SELECT ST_SnapToGrid(ST_StartPoint(geom), 0.00001) AS pt FROM %I.links
      UNION ALL
      SELECT ST_SnapToGrid(ST_EndPoint(geom), 0.00001) FROM %I.links
    )
    SELECT pt FROM endpoints;
    CREATE INDEX links_vertices_geom_idx ON %I.links_vertices USING GIST (pt);',
    schema_name, schema_name, schema_name, schema_name, schema_name
  );

  -- Eemalda need lõigud, millel ei ole ühendusi teise punktiga
  EXECUTE format('
    DELETE FROM %I.links l
    WHERE NOT EXISTS (
      SELECT 1 FROM %I.links_vertices v
      WHERE ST_DWithin(ST_SnapToGrid(ST_StartPoint(l.geom), 0.00001), v.pt, 0.001)
         AND EXISTS (
           SELECT 1 FROM %I.links_vertices vv
           WHERE NOT ST_Equals(vv.pt, v.pt)
             AND ST_DWithin(ST_SnapToGrid(ST_EndPoint(l.geom), 0.00001), vv.pt, 0.001)
         )
    );',
    schema_name, schema_name, schema_name
  );
END
\$\$;

-- 4. Lisa järjestikune ID veerg ja indeks
DO \$\$
DECLARE
  schema_name text := '${destination_schema}';
  column_exists boolean;
BEGIN
  SELECT EXISTS (
    SELECT 1
    FROM information_schema.columns
    WHERE table_schema = schema_name
      AND table_name = 'links'
      AND column_name = 'id'
  ) INTO column_exists;

  IF column_exists THEN
    EXECUTE format('ALTER TABLE %I.links DROP COLUMN id;', schema_name);
  END IF;
  EXECUTE format('ALTER TABLE %I.links ADD COLUMN id SERIAL PRIMARY KEY;', schema_name);
  EXECUTE format('CREATE INDEX links_geom_idx ON %I.links USING GIST (geom);', schema_name);
END
\$\$;

EOF