Obsidian/Doktoritöö/001_artiklid/OSM/shell/arhiiv/merge_links_25070602.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} -c "DROP TABLE IF EXISTS ${destination_schema}.links CASCADE;"

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;

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

psql ${CONNECTION_NAME} -d ${destination_dbname} <<EOF
-- SQL-plokk, mis eemaldab ainult maakondade jagatud lõikude duplikaadid
DO \$\$
DECLARE
  schema_name text := '${destination_schema}';
BEGIN
  -- Eemalda duplikaatsed geomeetriad, mida esineb rohkem kui 1 kord
  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\$\$;
EOF

psql ${CONNECTION_NAME} -d ${destination_dbname} <<EOF
-- 3. Eemalda isoleeritud lõigud (st mõlemad tipud ei esine mujal)
DO \$\$
DECLARE
  schema_name text := '${destination_schema}';
BEGIN
  -- Loo ajutine tiputabel
  EXECUTE format('
    DROP TABLE IF EXISTS %I.links_vertices;
    CREATE UNLOGGED TABLE %I.links_vertices AS
    SELECT ST_StartPoint(geom) AS pt FROM %I.links
    UNION ALL
    SELECT ST_EndPoint(geom) FROM %I.links;', schema_name, schema_name, schema_name, schema_name
  );

  -- Eemalda lõigud, millel puuduvad ühendused teiste tippudega
  EXECUTE format('
    DELETE FROM %I.links l
    WHERE NOT EXISTS (
      SELECT 1 FROM %I.links_vertices v
      WHERE ST_DWithin(ST_StartPoint(l.geom), 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_EndPoint(l.geom), vv.pt, 0.001)
         )
    );',
    schema_name, schema_name, schema_name
  );
END\$\$;
EOF