Table of Contents

Create node from node to

Create temporary roads 

CREATE VIEW tmp_roads AS
SELECT *, STARTPOINT(geometry) AS startp, ENDPOINT(geometry) AS endp
FROM roads

Create nodes

CREATE TABLE nodes AS
SELECT '' AS ID, a.p AS Geometry FROM (SELECT DISTINCT tmp_roads.startp AS p FROM tmp_roads UNION SELECT DISTINCT tmp_roads.endp AS p FROM tmp_roads) a GROUP BY a.p;
UPDATE nodes SET ID = ROWID;

JOIN Spatial

SELECT a.ID,b.bod_sr70,b.tudu_m12,b.trat_ojr,b.bod_sr70_n,a.Geometry
FROM nodes_new AS a
JOIN nodes_tmp AS b ON a.ID = b.ID

Create road network

CREATE TABLE roads_network AS
SELECT a.*, b.id AS start_id, c.id AS end_id
FROM tmp_roads AS a
JOIN nodes AS b ON a.startp = b.Geometry
JOIN nodes AS c ON a.endp = c.Geometry