Routing analysis using Quantum GIS and SpatiaLite

After preparing a network graph in a SpatiaLite database for routing analysis in my previous post, you can perform shortest path analysis just by typing in SQL commands in the SpatiaLite-gui. The results are returned as result set rows which are textual - functional but difficult to visualize. It is more fun to do it in a visual environment such as Quantum GIS or QGIS for short as shown below.

1. Start QGIS.
   
   The QGIS application appears.
   
   
2. Select Layer | Add SpatiaLite Layer.
   
   The Add SpatiaLite Table(s) dialog box appears.
   
   
3. Click New. Browse and select a SpatiaLite database e.g. roads.sqlite. Click Open.
   
   The selected database is added to the Databases drop down list.
   
   
   
4. If not selected, then choose the database in the Databases drop down list. Click Connect.
   
   The list of tables is displayed.
   
   
5. Select roads. Click Add.
   
   The roads layer is displayed in the map.
   
   
6. Select Database | SpatiaLite | QSpatiaLite.
   
   The QspatiaLite dialog box appears.
   
   
7. In the SQL entry field, type in the following:
   
   SELECT * FROM roads_network_net
   WHERE nodefrom = 1
   AND nodeto = 1000
   
   
   
   Note: this queries the network graph for the shortest path between the from node #1 and the to node #1000. The node ids from the nodes table.
   
8. Click Run SQL.
   
   The result is displayed.
   
   
   Note: only the first record has a geometry. This geometry object contains the geometry of the shortest path. The subsequent rows list out each segment of the shortest path.
   
9. In the SQL entry field, type in the following:
   
   CREATE TABLE route1 AS
   SELECT * FROM roads_network_net
   WHERE nodefrom = 1
   AND nodeto = 1000
   AND geometry IS NOT NULL
   
   Note: this will create a route1 table that can be displayed in QGIS.
   
10. Click Run SQL.
    
    A table named 'route1' is created.
    
11. In the SQL Entry field, type in the following:
    
    SELECT RecoverGeometryColumn('route1','Geometry',4326,'LINESTRING','XY')
    
    Note: change 4326 to the appropriate SRID of your data.
    
12. Click Run SQL.
    
    The 'route1' table is registered as a spatial table.
    
13. In the list of tables of the QSpatiaLite dialog box, right click on the newly created table 'route1'.
    
    A pop up menu appears.
    
14. Choose Load in QGIS.
    
    The shortest path result layer is loaded in the map.