-- 📍 Ekspordi allesjäänud probleemsed ristumispunktid (intersections_detected) visuaalseks kontrolliks

-- Eeldame, et intersections_unconnected sisaldab kõiki tuvastatud lõike
-- Ekspordime ainult need, mille id1 või id2 eksisteerib veel links tabelis

DROP TABLE IF EXISTS osm.intersection_points_remaining;

CREATE TABLE osm.intersection_points_remaining AS
SELECT DISTINCT
  ST_Intersection(l1.geom, l2.geom) AS geom,
  l1.id AS link1_id,
  l2.id AS link2_id
FROM osm.links l1
JOIN osm.links l2
  ON ST_Intersects(l1.geom, l2.geom)
WHERE l1.id < l2.id
  AND NOT (
    l1.source = l2.source OR l1.source = l2.target OR
    l1.target = l2.source OR l1.target = l2.target
  )
  AND GeometryType(ST_Intersection(l1.geom, l2.geom)) = 'POINT';

-- Indeks, et saaks kiiresti laadida
CREATE INDEX intersection_points_remaining_geom_idx
  ON osm.intersection_points_remaining USING GIST (geom);
