Using Spatialite to prepare road segments for routing analysis

SpatiaLite is a extension to the light weight database Sqlite that extends the database with spatial processing capabilities similar to what PostGis gives PostgreSQL. One of the capabilities that I wanted to use recently was the ability to perform shortest path routing analysis using the Dijkstra algorithm. In order to perform the analysis, the roads network data has to be processed into a suitable form for SpatiaLite to use. The following illustrates how to do the processing (assuming the road data is topologically correct). It can be done using the spatialite-gui executable which can be downloaded from http://www.gaia-gis.it/gaia-sins/.

Create a new SpatiaLite database

1. Run the spatialite-gui.exe.
   
   The spatialite-gui [a GUI tool for SQLite/SpatiaLite] application appears.
   
   
2. Select Files | Creating a new (empty) SQLite DB.
   
   The Creating a new, empty DB dialog appears.
   
   
3. Type in a new database name e.g. roads.sqlite. Click Save.
   
   The database is created.
   
   

Load in the road segments data

1. Select Files | Load Shapefile.
   
   The Load Shapefile dialog appears.
   
2. Select a shapefile e.g. roads.shp. Click Open.
   
   The Load Shapefile dialog box appears.
   
   
3. If necessary, choose an appropriate Charset Encoding, e.g. UTF-8.
   
4. If necessary, type in the correct coordinate system SRID for the Shapefile e.g. 4326. Click OK.
   
   The Shapefile is loaded.
   
   

Preprocess the road segments for network building

1. In the SQL entry field, type in the following statement.
   
   CREATE VIEW tmp_roads AS
   SELECT *, STARTPOINT(geometry) AS startp, ENDPOINT(geometry) AS endp
   FROM roads
   
   
   
2. Then click the button Execute SQL Statement.
   
   The view is created.
   
3. Type in the following SQL statement to create a nodes table containing all the starting and ending nodes.
   
   CREATE TABLE nodes AS
   SELECT ROWID 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
   
4. Click the Execute SQL Statement button.
   
   The nodes table is created.
   
5. Type in the following SQL statement.
   
   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
   
6. Click the Execute SQL Statement button.
   
   The roads_network table is created.
   

Build the Network

1. Select Files | Build Network.
   
   The Build Network dialog box appears.
   
   
2. In the Base Table [graph] field, select the table created in the previous step, e.g. roads_network.
   
3. In the NodeFrom column field, select the column start_id.
   
4. In the NodeTo Column field, select the column end_id.
   
5. In the Geometry Column field, select Geometry.
   
6. Choose Uni-Directional or Bi-Directional.
   
7. Choose the Cost type - GLength or Cost Column. If Cost Column is chosen, then select the Cost Column e.g. Cost.
   
8. If necessary, choose Using OneWay columns or Not. If Using is chosen, then select the From To Column and To From Column.
   
   
9. Click OK.
   
   The network is created.