This shows you the differences between two versions of the page.
— | sqlite [2019/01/04 12:10] (current) – created - external edit 127.0.0.1 | ||
---|---|---|---|
Line 1: | Line 1: | ||
+ | ====== 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 ====== | ||
+ | < | ||
+ | SELECT '' | ||
+ | UPDATE nodes SET ID = ROWID; | ||
+ | </ | ||
+ | |||
+ | |||
+ | ====== JOIN Spatial ====== | ||
+ | < | ||
+ | SELECT a.ID, | ||
+ | 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 | ||
+ | </ |