Install PostGIS and pgRouting
On Mac
The following will install pgRouting
and its dependencies including PostGIS
, and PostgreSQL
if not installed:
brew install pgrouting
On Linux
(to add)
Install osm2pgrouting
osm2pgrouting is for importing OSM data into PostgreSQL database, and creating routable topology.
It is said that osm2pgrouting has limitation:
Can’t process “large” files, continents, big countries, or very large states.
For large files, we can use osm2po.
Install via homebrew (Mac)
brew install osm2pgrouting
Note that a mapconfig.xml file is at: /usr/local/Cellar/osm2pgrouting/[version]/share/osm2pgrouting/mapconfig.xml
Manually install the lastest version
(The following notes are based on version 2.1. Now with homebrew you can already install the latest version.)
- Download code from repo
- Modify
CMakeLists.txt
to install app to/usr/local/bin
and other files to/usr/local/share
- line 5 —> set (SHARE_DIR “/usr/local/share/osm2pgrouting”)
- line 36 —> RUNTIME DESTINATION “/usr/local/bin”
- Add the following headers to
src/way.h
(otherwise there will be errors)c++
#include <string>
#include <sstream>
#include <iostream>
- Build and install
sh
cmake -H. -Bbuild
cd build/
make
make install
Workflow
Prepare database
Create new database if needed:
createdb routing
For each database that postgis and pgrouting are used, we should install corresponding extensions:
psql routing -c "create extension postgis"
psql routing -c "create extension pgrouting"
For test of installation:
psql routing -c "SELECT postgis_full_version()"
psql routing -c "SELECT pgr_version()"
Import OpenStreetMap data
OSM map data can be downloaded in many places. We are interested in the OSM XML format.
To import map data to database:
osm2pgrouting --file beijing_china.osm
--conf /usr/local/Cellar/osm2pgrouting/[version]/share/osm2pgrouting/mapconfig.xml
--dbname routing
--username [username]
--clean
If you are adding data, do not use flag --clean
.
We could browse the tables with psql
or pgAdmin
.
psql mydb -c "dt"
Notes for tables
Two main tables:
ways
: stores the edgesways_vertices_pgr
: stores the end nodes of edges
Meanings of ways
columns length
, length_m
, cost
, reverse_cost
, cost_s
, reverse_cost_s
. According to post here:
length
is length of the segment in degree unitscost
andreverse_cost
is the length in degree units. (include the negative values for wrong way)length_m
is in meters (there is no cost_m or reverse_cost_m)cost_s
andreverse_cost_s
is in time: seconds units (using the maxspeed value that is in km/hr transforming it to meters/second and using thelength_m
)
Do routings in SQL
Now we can calculate routes with pgRouting
. For example, to calculate one-to-one shortest path:
SELECT * FROM pgr_dijkstra(
'SELECT gid as id, source, target, cost, reverse_cost FROM ways',
100, 200,
TRUE
Do routings in Python
I bet that You don’t want to write SQLs. Good news here if you like writing Python! I wrote a psycopgr
Python module to encapsulate database connection (via psycopg2
) and pgRouting
callings. Here is a tutorial.