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

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

# Kontrolli ühenduse muutujat
echo "🔗 Kasutan CONNECTION_NAME=${CONNECTION_NAME}"

# Võta kõik maakonnad admin_taseme 6 järgi
COUNTIES=$(psql ${CONNECTION_NAME} -At -d "$destination_dbname" -c "SELECT relation_id FROM ${destination_schema}.administrative WHERE admin_level = '6';")

#IFS=$'\n'
for county in $COUNTIES; do
  safe_name=$(echo "$county" | tr ' ' '_' )
  echo "📦 Töötlen $county → links_$safe_name"

  psql ${CONNECTION_NAME} -v schema=${destination_schema} -v county="'$county'" -v links_table="links_$safe_name" -d ${destination_dbname} <<'EOF'
\set ON_ERROR_STOP on

DROP TABLE IF EXISTS test_highways;
CREATE TEMP TABLE test_highways AS
SELECT h.*
FROM :schema.highways h
JOIN :schema.administrative a ON ST_Intersects(h.geom, a.geom)
WHERE a.relation_id = :county AND a.admin_level = '6';

DROP TABLE IF EXISTS :schema._noded_geom;
CREATE UNLOGGED TABLE :schema._noded_geom AS
SELECT ST_Node(ST_Collect(geom)) AS geom FROM test_highways;

DROP TABLE IF EXISTS :schema._raw_segments;
CREATE UNLOGGED TABLE :schema._raw_segments AS
SELECT (ST_Dump(geom)).geom AS geom
FROM :schema._noded_geom;

DROP TABLE IF EXISTS :schema.:links_table;
CREATE UNLOGGED TABLE :schema.:links_table AS
SELECT
  h.id AS eid,
  h.hwtype AS highway,
  h.surface,
  h.oneway,
  h.maxspeed,
  COALESCE(NULLIF(h.name, ''), NULLIF(h.hwtype, ''), 'Unnamed road') AS name,
  s.geom
FROM :schema._raw_segments s
JOIN LATERAL (
  SELECT *
  FROM test_highways h
  WHERE h.geom && s.geom AND ST_Intersects(h.geom, s.geom)
  ORDER BY ST_Length(ST_Intersection(h.geom, s.geom)) DESC
  LIMIT 1
) h ON TRUE;

EOF
echo -e "\n✅ Töödeldud links_$safe_name"
done