Create a new SpatiaLite database
- Run the spatialite-gui.exe.
The spatialite-gui [a GUI tool for SQLite/SpatiaLite] application appears. - Select Files | Creating a new (empty) SQLite DB.
The Creating a new, empty DB dialog appears. - Type in a new database name e.g. roads.sqlite. Click Save.
The database is created.
Load in the road segments data
- Select Files | Load Shapefile.
The Load Shapefile dialog appears. - Select a shapefile e.g. roads.shp. Click Open.
The Load Shapefile dialog box appears. - If necessary, choose an appropriate Charset Encoding, e.g. UTF-8.
- 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
- 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 - Then click the button Execute SQL Statement.
The view is created. - 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 - Click the Execute SQL Statement button.
The nodes table is created. - 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 - Click the Execute SQL Statement button.
The roads_network table is created.
Build the Network
- Select Files | Build Network.
The Build Network dialog box appears. - In the Base Table [graph] field, select the table created in the previous step, e.g. roads_network.
- In the NodeFrom column field, select the column start_id.
- In the NodeTo Column field, select the column end_id.
- In the Geometry Column field, select Geometry.
- Choose Uni-Directional or Bi-Directional.
- Choose the Cost type - GLength or Cost Column. If Cost Column is chosen, then select the Cost Column e.g. Cost.
- If necessary, choose Using OneWay columns or Not. If Using is chosen, then select the From To Column and To From Column.
- Click OK.
The network is created.
3 comments:
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
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
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.
Post a Comment