User Tools

Site Tools


sqlite
no way to compare when less than two revisions

Differences

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
 +<code>
 +CREATE VIEW tmp_roads AS
 +SELECT *, STARTPOINT(geometry) AS startp, ENDPOINT(geometry) AS endp
 +FROM roads
 +</code>
  
 +====== Create nodes ======
 +<code>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;
 +</code>
 +
 +
 +====== JOIN Spatial ======
 +<code>
 +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
 +</code>
 +
 +====== Create road network ======
 +<code>
 +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
 +</code>
sqlite.txt · Last modified: 2019/01/04 12:10 by Jan Forman