Obsidian/Doktoritöö/001_artiklid/OSM/OSM teed graafiks.md

Väga hea – sul on juba olemas põhjalik skript OSM-i maanteede (ardok_highways) töötlemiseks ning olemasolev Bash-põhine töövoog graafi (links, nodes) ja PGROUTING toe loomiseks.

Allpool annan kokkuvõtliku juhise ja parendatud SQL/Bash struktuuri, et luua teedevõrgu graaf links tabelist kasutades sinu ardok_highways tabelit.

---

✅ Eesmärk: Loo links ja nodes tabelid ardok_highways baasil

1. Lähteandmed

• Tabel: ${destination_schema}.ardok_highways
  
• Veerg: geom (LINESTRING, SRID 3301)
  
• ID: id
  
• Tee tüüp: hwtype
  

---

🧰 2. Töövoog (kaasaegsem ja lihtsustatud)

📦 a) links loomine

DROP TABLE IF EXISTS ${destination_schema}.links CASCADE;

CREATE TABLE ${destination_schema}.links AS
WITH
  dumped_lines AS (
    SELECT (ST_Dump(geom)).geom AS geom, id, hwtype
    FROM ${destination_schema}.ardok_highways
  ),
  unified AS (
    SELECT ST_Union(geom) AS geom FROM dumped_lines
  ),
  split_lines AS (
    SELECT (ST_Dump(ST_LineMerge(ST_Union(geom)))).geom AS geom
    FROM dumped_lines
  ),
  numbered AS (
    SELECT ROW_NUMBER() OVER () AS id, geom
    FROM split_lines
    WHERE GeometryType(geom) = 'LINESTRING'
  ),
  attributed AS (
    SELECT
      l.id,
      MIN(h.id) AS eid,
      h.hwtype,
      l.geom
    FROM numbered l
    JOIN ${destination_schema}.ardok_highways h
      ON ST_Intersects(h.geom, ST_LineSubstring(l.geom, 0.01, 0.99))
    GROUP BY l.id, l.geom, h.hwtype
  )
SELECT * FROM attributed;

See eemaldab kattuvad jooned, teeb need unikaalseks ja seob lähte-ID ja tüübi.

---

🧠 b) nodes loomine

DROP TABLE IF EXISTS ${destination_schema}.nodes CASCADE;

CREATE TABLE ${destination_schema}.nodes AS
WITH all_nodes AS (
  SELECT ST_StartPoint(geom) AS geom FROM ${destination_schema}.links
  UNION
  SELECT ST_EndPoint(geom) AS geom FROM ${destination_schema}.links
),
distinct_nodes AS (
  SELECT DISTINCT geom FROM all_nodes
)
SELECT ROW_NUMBER() OVER () AS id, geom FROM distinct_nodes;
CREATE INDEX IF NOT EXISTS ${pk}_nodes_geom_idx ON ${pk}.nodes USING GIST (geom);

---

🔗 c) links täiendamine source, target, cost

ALTER TABLE ${destination_schema}.links
ADD COLUMN source bigint,
ADD COLUMN target bigint,
ADD COLUMN cost double precision,
ADD COLUMN reverse_cost double precision DEFAULT -1;

BATCH_SIZE=10000
MAX_ID=$(psql -tA -c "SELECT max(id) FROM ${pk}.links;")

for ((i=1; i<=MAX_ID; i+=BATCH_SIZE)); do
  END=$((i+BATCH_SIZE-1))
  echo "Processing batch $i - $END"

  psql ${CONNECTION_NAME} -c "
  WITH link_batch AS (
    SELECT * FROM ${pk}.links WHERE id BETWEEN $i AND $END
  ),
  nodes AS (SELECT * FROM ${pk}.nodes),
  link_data AS (
    SELECT
      l.id,
      l.geom,
      n1.id AS source,
      n2.id AS target,
      ST_Length(l.geom) AS cost
    FROM link_batch l
    JOIN nodes n1 ON ST_DWithin(ST_StartPoint(l.geom), n1.geom, 0.01)
                 AND ST_Distance(ST_StartPoint(l.geom), n1.geom) = 0
    JOIN nodes n2 ON ST_DWithin(ST_EndPoint(l.geom), n2.geom, 0.01)
                 AND ST_Distance(ST_EndPoint(l.geom), 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;"
done

---

⚙️ d) PGROUTING ettevalmistus

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');

---

🏷️ e) Indeksid ja võtmed

ALTER TABLE ${destination_schema}.links ADD PRIMARY KEY (id);
CREATE INDEX ON ${destination_schema}.links USING GIST (geom);
CREATE INDEX ON ${destination_schema}.links (source, target);

ALTER TABLE ${destination_schema}.nodes ADD PRIMARY KEY (id);
CREATE INDEX ON ${destination_schema}.nodes USING GIST (geom);

---

✨ Soovitused edasi:

• Tee lisaveerg is_main_road → kui hwtype IN (...)
  
• Lisa travel_time = cost / average_speed, kui maxspeed olemas
  
• Kaalude lisamine gravitatsioonimudeliks (vt eelmine vastus)
  

---

📌 Kui soovid:

• bashi wrapper-funktsioon nagu create_links või create_graph
  
• täiendatud versiooni PGROUTING analüüsiks
  
• automaatset reverse_cost genereerimist (nt ühesuunaliste teede jaoks)
  

...anna teada – võin vormistada kõik struktureeritult skriptina.

Siin on täiendatud SQL-töövoog, mis põhineb sinu ardok_highways → links → nodes süsteemil ning lisab järgmised 3 olulist veergu:

---

✅ 1. is_main_road

Märgib, kas teelõik kuulub põhi-/kõrval-/tugimaanteede hulka

✅ 2. travel_time

Aeg sekundites teelõigu läbimiseks, kui maxspeed on teada

✅ 3. weight_pop

Gravitatsioonianalüüsi kaal, impordituna piirkondade tabelist (eeldame, et ardok_polygons sisaldab veergu weight_pop ja relation_id)

---

🔧 Muudatused links tabeli täiendamiseks

📥 Lisa uued veerud

ALTER TABLE ${destination_schema}.links
ADD COLUMN is_main_road boolean,
ADD COLUMN travel_time double precision,
ADD COLUMN weight_pop double precision;

---

🧠 1. Märgista is_main_road

UPDATE ${destination_schema}.links
SET is_main_road = TRUE
WHERE hwtype IN (
  'motorway', 'motorway_link',
  'trunk', 'trunk_link',
  'primary', 'primary_link',
  'secondary', 'secondary_link',
  'tertiary', 'tertiary_link'
);

(ülejäänud jäävad NULL või FALSE)

---

🕓 2. Arvuta travel_time sekundites

Eeldame, et maxspeed on km/h — teisendame m/s → s:

UPDATE ${destination_schema}.links l
SET travel_time = ROUND(l.cost / (NULLIF(h.maxspeed, 0) * 1000.0 / 3600.0), 1)
FROM ${destination_schema}.ardok_highways h
WHERE l.eid = h.id AND h.maxspeed IS NOT NULL;

NB! NULLIF hoiab ära 0-ga jagamise. Kui maxspeed puudub, jääb travel_time NULL.

---

🌍 3. Lisa gravitatsiooniline kaal weight_pop

Eeldame, et saad iga teelõigu lähima piirkonna weight_pop väärtuse:

UPDATE ${destination_schema}.links l
SET weight_pop = p.weight_pop
FROM ${destination_schema}.ardok_administrative p
WHERE ST_Intersects(l.geom, p.geom)
  AND p.weight_pop IS NOT NULL;

Vajadusel võid kasutada ka ST_ClosestPoint vms, kui ST_Intersects annab mitu sobivat ala. Võin aidata ka ST_DWithin + ORDER BY ST_Distance LIMIT 1 variandiga.

---

🧹 Lisa indeksid uutele veergudele (soovituslik)

CREATE INDEX ON ${destination_schema}.links (is_main_road);
CREATE INDEX ON ${destination_schema}.links (travel_time);
CREATE INDEX ON ${destination_schema}.links (weight_pop);

---

📊 Kokkuvõte – mis sul nüüd on

Veerg	Kirjeldus
is_main_road	TRUE, kui tee kuulub põhi-/kõrvalteede hulka
travel_time	Läbimisaeg sekundites, kui maxspeed olemas
weight_pop	Gravitatsioonianalüüsi "mass" piirkonnast

---

Kui soovid seda töövoogu bash-funktsioonina (nt enhance_links()), annan hea meelega ka struktureeritud bash-skripti versiooni. Võin lisada ka weight_jobs, poi_count, vms kui sul on täiendavaid andmeid.