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

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

OUTPUT="logs/links_table_stats.log"
echo "📊 Genereerin statistika kõigile links_* tabelitele skeemis ${destination_schema}" | tee "$OUTPUT"

QUERY=$(psql ${CONNECTION_NAME} -d ${destination_dbname} -tA -c "
  SELECT string_agg(
    format(
      'SELECT %L AS table_name, COUNT(*) AS total_rows, COUNT(DISTINCT ST_AsText(geom)) AS unique_geoms, 
      (COUNT(*) - COUNT(DISTINCT ST_AsText(geom))) AS diff_rows FROM %I.%I',
      tablename, '${destination_schema}', tablename
    ),
    ' UNION ALL '
  )
  FROM pg_tables
  WHERE schemaname = '${destination_schema}'
  AND tablename LIKE 'links_%'
  AND tablename NOT IN ('links_vertices', 'links')
  AND tablename ~ '^links_[0-9]+$';
")

#echo -e "\nKäivitan SQL-i:\n$QUERY\n" | tee -a "$OUTPUT"

psql ${CONNECTION_NAME} -d ${destination_dbname} -c "$QUERY" | tee -a "$OUTPUT"

echo -e "\n✅ Tulemused salvestatud faili: $OUTPUT"