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

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

echo "🔄 PGROUTING ettevalmistus (batch'idega) skeemis ${destination_schema}"

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

-- 🧹 1. Eemalda isoleeritud lõigud batchide kaupa
DO \$\$
DECLARE
  batch_size INTEGER := 10000;
  min_id INTEGER;
  max_id INTEGER;
  current_id INTEGER;
BEGIN
  SELECT MIN(id), MAX(id) INTO min_id, max_id FROM ${destination_schema}.links;
  current_id := min_id;

  WHILE current_id <= max_id LOOP
    WITH vertex_usage AS (
      SELECT source AS id FROM ${destination_schema}.links
      WHERE id BETWEEN current_id AND current_id + batch_size - 1
      UNION ALL
      SELECT target FROM ${destination_schema}.links
      WHERE id BETWEEN current_id AND current_id + batch_size - 1
    ),
    degree AS (
      SELECT id, COUNT(*) AS deg FROM vertex_usage GROUP BY id
    ),
    isolated_links AS (
      SELECT l.id
      FROM ${destination_schema}.links l
      JOIN degree s ON l.source = s.id
      JOIN degree t ON l.target = t.id
      WHERE l.id BETWEEN current_id AND current_id + batch_size - 1
        AND s.deg = 1 AND t.deg = 1
    )
    DELETE FROM ${destination_schema}.links
    WHERE id IN (SELECT id FROM isolated_links);

    RAISE NOTICE '🧹 Kustutatud isoleeritud lõigud vahemikust: % – %', current_id, current_id + batch_size - 1;
    current_id := current_id + batch_size;
  END LOOP;
END
\$\$;

-- 🧼 2. Kustuta olemasolev PGROUTING tiputabel (kui on)
DROP TABLE IF EXISTS ${destination_schema}.links_vertices_pgr;

-- 🔗 3. Loo uued tipud ja graafianalüüs
SELECT pgr_createVerticesTable('${destination_schema}.links', 'geom', 'source', 'target');
SELECT pgr_analyzeGraph('${destination_schema}.links', 0.001, the_geom := 'geom', id := 'id', source := 'source', target := 'target');

EOF

echo "✅ PGROUTING ettevalmistus tehtud batchide kaupa."