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

#!/bin/bash
# Lae keskkonnamuutujad
source env.sh
# --- Määrangud ---
#CONNECTION_NAME="postgresql://osm@localhost:5432/data"
BATCH_SIZE=10000

# --- Lisa veerud kui neid ei ole ---
echo "🧩 Kontrollin 'source', 'target', 'cost' olemasolu..."
psql ${CONNECTION_NAME} <<EOF
DO \$\$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = '${destination_schema}' AND table_name = 'links' AND column_name = 'source') THEN
    ALTER TABLE ${destination_schema}.links ADD COLUMN source bigint;
  END IF;
  IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = '${destination_schema}' AND table_name = 'links' AND column_name = 'target') THEN
    ALTER TABLE ${destination_schema}.links ADD COLUMN target bigint;
  END IF;
  IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = '${destination_schema}' AND table_name = 'links' AND column_name = 'cost') THEN
    ALTER TABLE ${destination_schema}.links ADD COLUMN cost double precision;
  END IF;
  IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = '${destination_schema}' AND table_name = 'links' AND column_name = 'reverse_cost') THEN
    ALTER TABLE ${destination_schema}.links ADD COLUMN reverse_cost double precision;
  END IF;
END
\$\$;
EOF

# --- Loo unlogged tabelid korraks ---
echo "🔄 Valmistan ette punktide tabelid..."
psql ${CONNECTION_NAME} <<EOF
DROP TABLE IF EXISTS ${destination_schema}.tmp_nodes;
CREATE UNLOGGED TABLE ${destination_schema}.tmp_nodes AS
WITH all_points AS (
  SELECT ST_SnapToGrid(ST_StartPoint(geom), 0.01) AS geom FROM ${destination_schema}.links
  UNION
  SELECT ST_SnapToGrid(ST_EndPoint(geom), 0.01) AS geom FROM ${destination_schema}.links
),
distinct_points AS (
  SELECT DISTINCT geom FROM all_points
)
SELECT ROW_NUMBER() OVER () AS id, geom FROM distinct_points;
CREATE INDEX ON ${destination_schema}.tmp_nodes USING GIST (geom);

DROP TABLE IF EXISTS ${destination_schema}.tmp_link_points;
CREATE UNLOGGED TABLE ${destination_schema}.tmp_link_points AS
SELECT
  id,
  ST_SnapToGrid(ST_StartPoint(geom), 0.01) AS pt_start,
  ST_SnapToGrid(ST_EndPoint(geom), 0.01) AS pt_end,
  geom
FROM ${destination_schema}.links;
CREATE INDEX ON ${destination_schema}.tmp_link_points USING GIST (pt_start);
CREATE INDEX ON ${destination_schema}.tmp_link_points USING GIST (pt_end);

EOF

# --- Leia MAX_ID ---
MAX_ID=$(psql ${CONNECTION_NAME} -tA -c "SELECT max(id) FROM ${destination_schema}.links;")
echo "📏 MAX_ID = $MAX_ID"

# --- Batch-töötlus ---
i=1
while [ "$i" -le "$MAX_ID" ]; do
  END=$((i + BATCH_SIZE - 1))
  echo "🚀 Töötlen batch: $i - $END"

  psql ${CONNECTION_NAME} -v schema="${destination_schema}" -v i="${i}" -v end="${END}" <<EOF
WITH link_batch AS (
  SELECT * FROM :schema.tmp_link_points WHERE id BETWEEN :i AND :end
),
link_data AS (
  SELECT
    l.id,
    n1.id AS source,
    n2.id AS target,
    ST_Length(l.geom) AS cost
  FROM link_batch l
  JOIN :schema.tmp_nodes n1
    ON ST_DWithin(l.pt_start, n1.geom, 0.01) AND ST_Distance(l.pt_start, n1.geom) = 0
  JOIN :schema.tmp_nodes n2
    ON ST_DWithin(l.pt_end, n2.geom, 0.01) AND ST_Distance(l.pt_end, n2.geom) = 0
)
UPDATE ${destination_schema}.links l
SET source = d.source,
    target = d.target,
    cost = ROUND(d.cost::numeric, 1),
    reverse_cost = -1.0
FROM link_data d
WHERE l.id = d.id;
EOF

  i=$((END + 1))
done

psql ${CONNECTION_NAME} -v schema="${destination_schema}" <<EOF
-- Isolated lõikude eemaldamine
DELETE FROM :schema.links l
WHERE l.id IN (
  SELECT l1.id
  FROM :schema.links l1
  WHERE l1.source = l1.target
     OR l1.id IN (
       SELECT l2.id
       FROM :schema.links l2
       LEFT JOIN :schema.links_vertices_pgr v ON l2.source = v.id OR l2.target = v.id
       GROUP BY l2.id
       HAVING COUNT(DISTINCT v.id) = 1
     )
);

-- cost ja reverse_cost lisamine. Vajalik näiteks Dijkstra algoritmide jaoks. 
UPDATE :schema.links
SET cost = ST_Length(geom),
    reverse_cost = CASE WHEN oneway = 1 THEN -1 ELSE ST_Length(geom) END;
EOF

echo "✅ Valmis!"