Monday, September 23, 2013

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.

3 comments:

MarcG_NZ said...

Hi, Cool post. What software are you going to use to do the actual network analysis or do you do it all using SQL?

Thanks,

Marc

MarcG_NZ said...

Hi,
Cool post. I am interested to know what software you will use to do the actual network analysis once the network is created in SpatialLite, or do you just use SQL?

Thanks,

Marc

dominoc925 said...

Hi MarcG_NZ, plain SQL can be used to perform the routing analysis; but I wanted to use the graph nodes/segments for Dijkstra algorithm routing in an Android app I'm working on.