Obsidian/Doktoritöö/001_artiklid/OSM/shell/sql/arhiiv/create_links.sql

-- 0. Eemalda olemasolev
DROP TABLE IF EXISTS :schema.links CASCADE;
DROP TABLE IF EXISTS :schema._noded_geom CASCADE;
DROP TABLE IF EXISTS :schema._raw_segments CASCADE;

-- 1. Lõika geomeetriad ristumiskohtades
CREATE UNLOGGED TABLE :schema._noded_geom AS
SELECT
  ST_Node(ST_Collect(geom)) AS geom  -- ühendame kõik teed ja lõikame ristumised
FROM :schema.ardok_highways;

-- 2. Võta igast lõigatud joonest üks segment
CREATE UNLOGGED TABLE :schema._raw_segments AS
SELECT 
  row_number() OVER () + 100000000 AS id, geom from ( SELECT
  (ST_Dump(geom)).geom AS geom
FROM :schema._noded_geom) rs;

-- 3. Lisa metaandmed lõigule, mille sees või mille osana link asub
CREATE UNLOGGED TABLE :schema.links AS
SELECT
  s.id,
  h.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 :schema.ardok_highways h
  WHERE ST_Intersects(h.geom, s.geom)  -- NB! sisuline kattuvus
  ORDER BY ST_Length(ST_Intersection(h.geom, s.geom)) DESC
  LIMIT 1
) h ON TRUE;


-- 4. Indeks ja PK
CREATE INDEX links_geom_idx ON :schema.links USING GIST (geom);
ALTER TABLE :schema.links ADD PRIMARY KEY (id);