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        |
  • 1
    There are several tools out that can build these fields but you do not say what your source network format is or even which GIS software you are using! You need to give us a bit more information if you want advice.
    – Hornbydd
    Sep 7, 2013 at 15:25
  • Adding more. Probably too far on the MWE side of things ;-) Sep 10, 2013 at 0:36

2 Answers 2


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


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.