create spatialite routing data using openstreetmap

create osm routes

#* original post here http://blog.mikeasoft.com/2010/09/24/local-map-rendering-and-route-finding-with-libchamplain-spatialite-and-open-street-map/

Importing OSM data into spatialite

spatialite_osm -o mydownloadeddata.osm -d myNewDB.sqlite -T roads -m

Generating a routing table

spatialite_network -d myNewDB.sqlite -T roads -g geometry -c cost -t node_to -f node_from -n name --oneway-fromto oneway_fromto --oneway-tofrom oneway_tofrom -o roads_net_data

spatialite myNewDB.sqlite 'CREATE VIRTUAL TABLE "roads_net" USING VirtualNetwork("roads_net_data")'.

I’ve joined these stages together into a small script, available here: populate_spatialite.sh. It takes two parameters, the first being the OSM data to import and the second is the spatialite database to be created:

./populate_spatialite.sh nantwich.osm nantwich.sqlite


#!/bin/sh
# bash file to 
echo "Creating spatialite file '$2' from openstreetmap data file '$1'"
spatialite_osm -o $1 -d $2 -T roads -m
echo "Populating road network data"
spatialite_network -d $2 -T roads -g geometry -c cost -t node_to -f node_from -n name --oneway-fromto oneway_fromto --oneway-tofrom oneway_tofrom -o roads_net_data --overwrite-output
echo "Creating virtual table 'roads_net' for routing queries."
spatialite $2 'CREATE VIRTUAL TABLE "roads_net" USING VirtualNetwork("roads_net_data")'
echo "Done!"

Finding and drawing a route

Find out how many points there are in the route
SELECT NumPoints(Geometry) FROM Roads_net 
           "WHERE nodeFrom = %d AND nodeTo = %d LIMIT 1", from, to);