Obsidian/Doktoritöö/001_artiklid/OSM/shell/arhiiv/merge_links_enhanced.sh

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

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

# Leia esimene relation_id, mida kasutada struktuuri eeskujuks
LINKS_TEMPLATE=$(psql ${CONNECTION_NAME} -At -d ${destination_dbname} -c "SELECT relation_id FROM ${destination_schema}.ardok_administrative WHERE admin_level = '6' LIMIT 1;")

psql ${CONNECTION_NAME} -d ${destination_dbname} <<EOF
DO \$\$
DECLARE
  schema_name text := '${destination_schema}';
  template_name text := 'links_' || '${LINKS_TEMPLATE}';
  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
    EXECUTE format('SELECT tablename FROM pg_tables WHERE schemaname = %L AND tablename LIKE %L', schema_name, 'links_%')
  LOOP
    EXECUTE format('INSERT INTO %I.links SELECT * FROM %I.%I', schema_name, schema_name, r.tablename);
  END LOOP;

  -- Eemalda geomeetrilised duplikaadid (identsete geomidega read, hoides ühe)
  EXECUTE format($sql$
    DELETE FROM %I.links a
    USING %I.links b
    WHERE a.ctid < b.ctid AND ST_Equals(a.geom, b.geom)
  $sql$, schema_name, schema_name);

  -- Lisa id veerg ja täida järjestikuse ID-ga
  EXECUTE format('ALTER TABLE %I.links ADD COLUMN id SERIAL PRIMARY KEY', schema_name);

  -- Loo ruumiline indeks
  EXECUTE format('CREATE INDEX links_geom_idx ON %I.links USING GIST (geom)', schema_name);
END
\$\$;
EOF