SpatiaLite: Creating to, from nodes from a normal road layer (LINE)

Question

Following this tutorial but using my own data. How do I create the from_node and to_node columns?

I am using Spatialite and Sqlite3. I'd prefer to not use a gui as I'm working remotely via ssh on a server.

The network is currently held in a spatialite database with a geometry column e.g:

id  |  road_name   |  geometry
1   |  Phil Street |  

I wish to, using sql, add column to and from node e.g.:

id  |  road_name   |  from_node  |  to_node  |  geometry
1   |  Phil Street |  1          |  2        |

Answer 1

If i understood your problem correctly here is postgis way of do it in SQL PgRouting (postgresql) SQL for assign_vertex_id. It creates from and to nodes your data. Sqlite needs functions like ST_StartPoint(geom) and ST_Endpoint(geom). Then implement SQL into SQLite flavor SQL

Answer 2

You can read the answer from thread https://groups.google.com/forum/#!topic/spatialite-users/6ldqMjzGWhw

Basically it is really extracting distinct startpoints and endpoints to act as nodes in the graph, creating IDs for nodes and attaching them as segment attributes by using a spatial join. Everything is doable without gui.