August 30, 2016

pgRouting Notes

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

This is not the latest version (v2.0).

Note that a mapconfig.xml file is at: /usr/local/Cellar/osm2pgrouting/2.0.0/mapconfig.xml

Manually install the lastest version

(The following steps are based on version 2.1.)

  1. Download code from repo

  2. 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”
  3. Add the following headers to src/way.h (otherwise there will be errors)

     #include <string>
     #include <sstream>
     #include <iostream>
  4. Build and install

     cmake -H. -Bbuild
     cd build/
     make install


Prepare database

Create new database if needed:

createdb mydb

For each database that postgis and pgrouting are used, we should install corresponding extensions:

psql mydb -c "create extension postgis"
psql mydb -c "create extension pgrouting"

For test of installation:

psql mydb -c "SELECT postgis_full_version()"
psql mydb -c "SELECT pgr_version()"

Import OpenStreetMap data

OSM map data can be downloaded in many places e.g. Mapzen. We are interested in the OSM XML format.

For osm2pgrouting installed manually:

osm2pgrouting --file beijing_china.osm \
--conf /usr/local/share/osm2pgrouting/mapconfig.xml \
--dbname mydb \
--username username \

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 edges
  • ways_vertices_pgr: stores the end nodes of edges

Means of ways columns length, length_m, cost, reverse_cost, cost_s, reverse_cost_s. According to post here:

For osm2pgrouting V2.1:

  • length is length of the segment in degree units
  • cost and reverse_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 and reverse_cost_s is in time: seconds units (using the maxspeed value that is in km/hr transforming it to meters/second and using the length_m)

Do routings in SQL

For example, use pgRouting functions to calculate one-to-one shortest path: doc.

SELECT * FROM pgr_dijkstra(
    'SELECT gid as id, source, target, cost, reverse_cost FROM ways',
    100, 200,

Do routings in Python

I wrote a psycopgr Python module to encapsulate database connection (using psycopg2) and pgRouting calling. Here is a tutorial.