Openstreetmaps contains a lot of detailed infvormation of many parts of the world; This information is contained in an OSM-file, and can be freely used for any application.
My goal is to perform route-calculations using Open Street Map data (i.e. determine possible routes from A to B and find the shortest path).
There is quite some information available on the web, however some of it seems outdated a bit. ( outdated to the extend that some of the steps to take or commands to use are no longer exactly valid, although the general idea is still the same).
These are my notes on how to get a working system that allows me to use OSM-data for determining a route, using Spatialite, an SQLITE-version adapter for use with geoinformation and spatial queries..
Preparation steps/ installing packages
Prerequisits, or what to do first:
we need to install spatialite and spatialite tools.
emerge -av spatialite spatialite-tools
Spatialite is now available on your system, and running “spatialite” give a similar database-terminal as when running ‘sqlite’
Overview: Start routing.
Since the actual routing-algorithm is performed by operations in the database some preparation is needed for the data to be ready for use in routing:
- Get an OSM-file for an area of interest ( i.e. your hometown )
- Use ‘spatilite-tools to import the data in an sqlite/spatialite database. ( spatialite is sqlite with some additional functionalite specific for routing purposes).
- Create the necessary routing info (again using spatialite-tools).
- Start querying the spatialite-database, either directly ( command prompt), or using pyspatialite , a modified version of pysqlite that takes into account the extra functionality of a spatialite-database (compared to an sqlite database).
Get an OSM-file
- Go to Openstreetmap,
- Select the “export”-tab ,
- Select (either manually or by using lon/lat coordinates) an area
You are only allowed to export a small area due to server limitations, but larger files can be downloaded completely at various locatios, for an overview see the download-section.
However, when starting to use Spatialite, better start with a small area for testing purposes ( since processing whole continents takes some more processor-time).
Import in Spatialite / sqlite database
This step assumes you have an OSM-file called name.osm (replace name with the actual name).
Create an sqlite-database from the osm-file:
spatialite_osm_net -o name.osm -d
Next, create a new table containing network information in the database (note: next command is one command, and should be placed on one line in bash):
spatialite_network -d name.sqlite -T roads -g geometry -c length -t node_to -f node_from -n name -o roads_net_data
Last, create a table called “roads_net_tabel” with “graph”-information about the roads connection various nodes in your selected area:
spatialite name.sqlite 'CREATE VIRTUAL TABLE "roads_net" USING VirtualNetwork("roads_net_data")'
(again, this is one line, not two as it might appear in your browser)
See this link about routing using spatialite, specifically the paragraph “Creating routing data”.
Start spatialite using
Now you entered a regular sqlite-shell.
Start routing using:
select * from roads_net where nodefrom = 1840 AND nodeto = 2638 ;
This simple command now queries the database for a route from node 1840 to node 2638. ( Note that the nodes I query from and to are chosen randomly by me in this example).
Result should look something like the following:
If you are unsure if the nodes 1840 or 2680 exist ( fe because you selected a small OSM-area with only a few tens of nodes), test the routing using:
select * from roads_net where nodefrom = 1 AND nodeto = 3 ;
if somehow this doesnt return any results, ther emight have been a problem when creating the routing-info.
Try ( in the spatialite-shell):
spatialite> select * from roads_nodes limit 10;
This should give something like the following, where the first column is the node-ID in this database, and the second column is the OSM-node-id: