Obsidian/Doktoritöö/001_artiklid/OSM/OSM links.md

Jah, seda koodi saab kindlasti kiirendada ja optimeerida, eriti kuna tegemist on keeruka ruumilise JOIN-iga, mida tehakse mitmekordselt igas batch’is. Siin on kõige olulisemad punktid, kuidas seda kiiremaks ja tõhusamaks muuta:

---

✅ 1. Ära SELECT-i kogu nodes tabelit igas batch’is

Igas psql -c käsus tehakse:

nodes AS (SELECT * FROM ${pk}.nodes),

See tähendab, et kogu nodes tabel laetakse mällu iga 10 000 rea kaupa – see on ebavajalik kulu. Tee sellest ajutine temp tabel varem:

psql ${CONNECTION_NAME} -c "DROP TABLE IF EXISTS tmp_nodes;
CREATE TEMP TABLE tmp_nodes AS SELECT id, geom FROM ${pk}.nodes;
CREATE INDEX ON tmp_nodes USING GIST (geom);"

Ja siis asenda igas batch’is:

nodes AS (SELECT * FROM tmp_nodes)
-- või lihtsalt viita otse: tmp_nodes

---

✅ 2. Tee geomeetriafunktsioonid eeltöötlusena

ST_StartPoint(...) ja ST_EndPoint(...) arvutatakse iga JOIN puhul uuesti, iga kord. See on aeglane.

Lahendus: arvuta need korra ette ja salvesta ajutiselt:

psql ${CONNECTION_NAME} -c "DROP TABLE IF EXISTS tmp_link_points;
CREATE TEMP TABLE tmp_link_points AS
SELECT id, ST_StartPoint(geom) AS pt_start, ST_EndPoint(geom) AS pt_end, geom
FROM ${pk}.links;
CREATE INDEX ON tmp_link_points USING GIST (pt_start);
CREATE INDEX ON tmp_link_points USING GIST (pt_end);"

Ja siis batch'i sees tee näiteks:

WITH link_batch AS (
  SELECT * FROM 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 tmp_nodes n1 ON ST_DWithin(l.pt_start, n1.geom, 0.01) AND ST_Distance(l.pt_start, n1.geom) = 0
  JOIN tmp_nodes n2 ON ST_DWithin(l.pt_end, n2.geom, 0.01) AND ST_Distance(l.pt_end, n2.geom) = 0
)
UPDATE ${pk}.links l
SET source = d.source,
    target = d.target,
    cost = ROUND(d.cost::numeric, 1)
FROM link_data d
WHERE l.id = d.id;

---

✅ 3. Vähenda SQL kutsete arvu — tee kõik ühes psql sessioonis

Kui võimalik, tee kogu batchimine PostgreSQLi enda sees PL/pgSQL’ga või DO $$ BEGIN ... END $$. Aga isegi Bashis saad sa vältida iga 10k kaupa uut psql protsessi, kui kasutad nt:

psql ${CONNECTION_NAME} <<EOF
-- kõik 5 batchi siia
EOF

---

✅ 4. Kasuta paralleeltäitmist (advanced)

Kui Sul on mitu südamikku (CPU), saad paralleelselt töödelda nt:

parallel -j 4 "
psql ${CONNECTION_NAME} -c \"
-- sama batchipõhine SQL siia
\"
" ::: $(seq 1 $BATCH_SIZE $MAX_ID)

Aga siin pead olema ettevaatlik, et paralleelsed update'id ei läheks konflikti (soovitan ainult siis, kui tabelil on sobivad indeksid ja väldid samade id väärtuste kattumist).

---

🧠 Kokkuvõte – kiirendamise võtted

Võte	Mõju
tmp_nodes ja tmp_link_points tabelid	korduv SELECT → 1x SELECT
ST_StartPoint ja ST_EndPoint eelnevalt arvutatud	väldib 2x geom-funktsiooni iga JOIN-i korral
Vähem psql kutseid	väldib protsessikulu ja I/O overheadi
Paralleeltäitmine (vajadusel)	kiirem suurte andmehulkade korral

---

Soovi korral võin kõik selle koosmõjus ümber kirjutada täpse, optimeeritud bash-skriptina. Kas tahad?

Väga hea – siin on optimeeritud bash-skript, mis:

• valmistab ette tmp_nodes ja tmp_link_points (eelarvutatud geomeetriad ja GIST-indeksid),

• töötleb links tabeli ridu batchide kaupa (10 000 kaupa),

• uuendab source, target ja cost veerud kiirendatud kujul,

• kasutab ainult ühte psql sessiooni (vähem süsteemikulusid).

---

🔧 Optimeeritud Bash-skript: update_links_sources_targets_costs.sh

#!/bin/bash

# Määrangud
CONNECTION_NAME="postgresql://user:password@localhost/dbname"
SCHEMA="your_schema"  # näiteks: public
BATCH_SIZE=10000

echo "🔄 Alustame ettevalmistusega..."
psql "${CONNECTION_NAME}" -v schema="${SCHEMA}" <<EOF

-- 1. Eeltöötlus: arvuta tippude ja lõikude punktid
DROP TABLE IF EXISTS tmp_nodes;
CREATE TEMP TABLE tmp_nodes AS
SELECT id, geom FROM :schema.nodes;
CREATE INDEX ON tmp_nodes USING GIST (geom);

DROP TABLE IF EXISTS tmp_link_points;
CREATE TEMP TABLE tmp_link_points AS
SELECT id, ST_StartPoint(geom) AS pt_start, ST_EndPoint(geom) AS pt_end, geom
FROM :schema.links;
CREATE INDEX ON tmp_link_points USING GIST (pt_start);
CREATE INDEX ON tmp_link_points USING GIST (pt_end);

EOF

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

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

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

  i=$((END + 1))
done

echo "✅ Valmis!"

---

⚙️ Kuidas kasutada?

1. Muuda skriptis:

   • CONNECTION_NAME – sobivaks PostgreSQL ühendusstringiks

   • SCHEMA – nt public, mydata, vms

2. Salvesta faili: update_links_sources_targets_costs.sh

3. Tee käivitatavaks:

   chmod +x update_links_sources_targets_costs.sh
   

4. Käivita:

   ./update_links_sources_targets_costs.sh
   

---

🚀 Kasu

Parandus	Tulem
Temp-tabelid	Ei lae nodes ja links uuesti iga kord
Eelarvutatud punktid	Ei kordu ST_StartPoint()
Ruumiindeksid	Kiirem JOIN
Batching	Vähem mälu, väiksemad transaktsioonid
Üks psql per batch	Vähem süsteemikoormust

---

Kui soovid lisada ka paralleeltöötlust GNU parallel abil või täiendada teise andmeväljaga (nt travel_time, is_main_road), saan kohe ka selle lisada. Kas soovid?