I mentioned a few posts back that there was a great resource for
downloading OpenStreetMap data, and that it was relatively easy to
import osm data into
PostGIS for use with
calculate shortest paths and various other network-based operations. In
this post, I’ll outline the steps required to get all this up and going,
and provide a quick example to show how this can be combined with QGIS
to visualise the computed shortest path directly.
Firstly, we need to install all the required packages. I’m assuming you
PostGIS installed, but if not, have a look
here for a quick guide to getting things set up (Note that the latest
PostgreSQL is now 8.4).
sudo apt-get install postgresql-server-dev-8.4 libboost-graph-dev
If you don’t have them already, you might also need tools for building the required software packages, as well as subversion and cmake.
sudo apt-get install build-essential subversion cmake
To be able to run the driving distance algorithms we need CGAL:
sudo apt-get install libcgal*
And the traveling sales person algorithm requires GAUL:
wget http://downloads.sourceforge.net/gaul/gaul-devel-0.1850-0.tar.gz tar -xzf gaul-devel-0.1850-0.tar.gz cd gaul-devel-0.1850-0/ ./configure --disable-slang make sudo make install sudo ldconfig
Now it’s time to download, build, and install
pgRouting. If you don’t
have subversion, or you don’t want to have the latest trunk version of
pgRouting, you can also download it manually.
svn checkout http://pgrouting.postlbs.org/svn/pgrouting/trunk pgrouting cd pgrouting/ cmake -DWITH_TSP=ON -DWITH_DD=ON . make sudo make install
Once we’ve got that installed and ready to go, we need to set up
PostgreSQL so that it ‘trusts’ local database connections.
sudo gedit /etc/postgresql/8.4/main/pg_hba.conf
Scroll to the bottom, and make sure you change the
METHOD to ‘trust’.
# TYPE DATABASE USER CIDR-ADDRESS METHOD local all all trust
And now that we’ve made these changes, we need to restart
sudo /etc/init.d/postgresql-8.4 restart
Next we simply create a routing database to store our data in…
createdb -U postgres routing createlang -U postgres plpgsql routing
psql -U postgres -f /usr/share/postgresql/8.4/contrib/postgis-1.5/postgis.sql routing psql -U postgres -f /usr/share/postgresql/8.4/contrib/postgis-1.5/spatial_ref_sys.sql routing
…add all the
psql -U postgres -f /usr/share/postlbs/routing_core.sql routing psql -U postgres -f /usr/share/postlbs/routing_core_wrappers.sql routing psql -U postgres -f /usr/share/postlbs/routing_topology.sql routing
…including the traveling salesman functions…
psql -U postgres -f /usr/share/postlbs/routing_tsp.sql routing psql -U postgres -f /usr/share/postlbs/routing_tsp_wrappers.sql routing
…and finally the driving distance functions.
psql -U postgres -f /usr/share/postlbs/routing_dd.sql routing psql -U postgres -f /usr/share/postlbs/routing_dd_wrappers.sql routing
We now have a fully working
pgRouting database ready to be populated
with data! So in order to do that relatively painlessly, we first
install the osm2pgrouting tool, which will help us import our osm
data directly into our
pgRouting database with the correct structure and everything.
svn checkout http://pgrouting.postlbs.org/svn/pgrouting/tools/osm2pgrouting/trunk osm2pgrouting cd osm2pgrouting/ make
Once that’s finished building, we can go ahead and download our osm data from http://download.geofabrik.de/osm/. See this previous post for details. For this example, I’ll be using the osm data for Ireland
wget http://download.geofabrik.de/osm/europe/ireland.osm.bz2 bzip2 -d ireland.osm.bz2
Once we have that downloaded and extracted, we’re ready to import the
osm data into our database using the
./osm2pgrouting -file /home/cfarmer/Downloads/ireland.osm -conf mapconfig.xml -dbname routing -user postgres -clean
Once that is finished (could take a long time) we’re ready to query the network (Note that the values 52343 and 39219 represent network node ids)…
psql -U postgres routing select * from shortest_path ('select gid as id, source::int4, target::int4, length::double precision as cost from ways', 52343, 39219, false, false);
…to produce something like this:
vertex_id | edge_id | cost ----------+---------+--------------------- 52343 | 78055 | 0.217641978736602 52341 | 78052 | 0.0230665826613562 52342 | 78053 | 0.0839311516838216 20390 | 28717 | 0.166809293071158 20389 | 28716 | 0.493120178133836 20388 | 28715 | 0.271165901884914 20387 | 112841 | 0.101669458767093 14183 | 22893 | 0.106433172954507 ...
Assuming your database is structured as
pgRouting expects (which it
should be if you’ve used
osm2pgrouting), you can use the some of the
functions which return geometries for use with other
select * from dijkstra_sp('ways', 52343, 39219);
id | gid | the_geom ----+--------+---------------------------- 1 | 78055 | 0105000020E610000001000... 2 | 78052 | 0105000020E610000001000... 3 | 78053 | 0105000020E610000001000... 4 | 28717 | 0105000020E610000001000... 5 | 28716 | 0105000020E610000001000... 6 | 28715 | 0105000020E610000001000... 7 | 112841 | 0105000020E610000001000... 8 | 22893 | 0105000020E610000001000... ...
These queries are all fine and dandy, and can easily be used to
calculate the distances of shortest paths etc, but what I really want to
do is visualise this output in a GIS so I can get an idea of what these
shortest paths looks like. In another previous post, I mentioned
how we could visualise spatial
SQL queries directly in QGIS from both
the Python console, and using a handy plugin. We can do the same thing
pgRouting to produce a lovely spatial representation of our
shortest path query:
And there you go, a full fledged routing library built right into our database!