PostGIS Spatial Database Engine UMN Mapserver Boston Geographic Information Systems    Checkout our PostGIS in Action book.  First chapter is a free download   PostGreSQL Object Relational Database Management System
GIS Books  Home   Consulting Services  About Boston GIS   Boston GIS Blog  Postgres OnLine Journal
PostGIS in Action is out in hard-copy,
download the first chapter
and SQL Primer for free. Tips and Tricks for PostGIS
  GIS Article comments Comments Rss
pgRouting: Loading OpenStreetMap with Osm2Po and route querying

For this exercise we are going to demonstrate how to use OSM2PO and an OSM pbf to create a routing network. Much of this is borrowed from Anita Graser's tutorial OSM2PO QuickStart

For our exercise, we love using the Teczno Metro Extracts. These are great and have been recently updated April 2013 as of this writing. They are separate OSM feeds for major world cities, and are packaged in various formats Standard: osm xml, osm pbf (compressed OSM format), and shape files. We'll be using Boston for this tutorial, but you can swap Boston with your favorite metro destination.

Windows specific instructions

  1. Install PostGIS 2.0 either via Stackbuilder or copy the respective PostGIS 2.0/2.1 binaries using one of winnie's build bot builds
  2. From Winnie grab the pgRouting 32-bit or 64-bit based (currently 1.07dev) binaries on which version of PostgreSQL 9.2 you are using:
    Winnie Build Bot Builds extract and copy the files to your ..PostgreSQL/9.2 install folder.

Install PostGIS and pgRouting

These instructions are the same for all OS if your PostGIS build was compiled with raster support (don't get extension feature without raster, sorry) ad using latest development version of pgRouting (1.07+) that now includes CREATE EXTENSION support.

In an existing database or new db run

CREATE EXTENSION postgis;
CREATE EXTENSION pgrouting;

Using OSM2PO to prepare OSM data for pgRouting use

OSM2PO is both a routing engine and a OSM 2 pgRouting loader written in Java. So it's crossplatform assuming you have a Java runtime loaded on your computer

  1. Download osm2po-4.7.7.zip (full version and details is http://osm2po.de
  2. )
  3. extract the zip (it already comes with precompiled JARS -- so no compile required and should work on all OS with Java runtime)
  4. Copy the demo.bat (or demo.sh if on Unix/Linux) and rename it boston.bat or boston.sh
  5. edit it replacing the .pbf url with: http://osm-extracted-metros.s3.amazonaws.com/boston.osm.pbf
    (You can use a different metro by looking at Teczno Metro Extracts options and copying the respective link).
    So your final batch script should look like: java -Xmx512m -jar osm2po-core-4.7.7-signed.jar prefix=hh tileSize=x http://osm-extracted-metros.s3.amazonaws.com/boston.osm.pbf

    If Java is not in your OS path, you will have to use the full path instead of just java. E.g. on windows would be something like (C:\Program Files (x86)\Java\jre7\bin\java)

  6. Osm2Po comes with its own mini-webserver with routing engine that reads from the pbf for you to spot check things. You can get to it by opening your browser and pasting in: http://localhost:8888/Osm2poService. Case sensitive. It will look something like this:
  7. You should now have an SQL file in the hh folder called: hh_2po_4pgr.sql

    Load this file up with PSQL. Note this file will be generally too big to use pgAdmin, so you need to use PSQL. If you are on windows you can create a batch script with something like this:

    
    set PSQL="C:\Program Files\PostgreSQL\9.2\bin\psql"
    set PGPORT=5432
    set PGHOST=localhost
    set PGPASSWORD=something
    cd hh
    %PSQL% -U postgres -d mydb -q -f "hh_2po_4pgr.sql"
    pause

    and then execute the batch script by Right Click -> Run As Administrator

    For Linux you can set the settings much the same except use ${PSQL} instead of %PSQL% and get rid of the set So Unix/Linux sh would look something like below. If psql is not in your path, you might have to give full path to psql executable.

    
    PSQL="psql"
    PGPORT=5432
    PGHOST=localhost
    PGPASSWORD=something
    cd hh
    ${PSQL} -U postgres -d mydb -q -f "hh_2po_4pgr.sql"
    pause

Directions

Create a view to get in structure wanted by astar_sp_delta.


CREATE OR REPLACE VIEW vw_boston_routing
AS 
SELECT id As gid, osm_id, osm_name, osm_meta, osm_source_id, osm_target_id, 
       clazz, flags, source, target, km, kmh, cost, cost as length, reverse_cost, x1, 
       y1, x2, y2, geom_way As the_geom
  FROM hh_2po_4pgr;

Okay now for the fun part, putting pgRouting to work.

Let's compute the shortest path using the source: 131904 and Target: 17500 we had pictured. The function that takes advantage of spatial index is called astar_sp_delta. The alternative one shortest_path_astar (took 1 second) takes an SQL statement, but for this example is about 4 times slower since it doesn't utilize geometry.

The 0.1 is in units of the spatial reference (in this case degrees) and is bounding box expansion to expand the bounding box that includes both source and target vertex).

The astar_sp_delta function will return a table consisting of columns (id, gid, the_geom) which you can then join back with your input data to get additional elements. The below queries took about 250ms on my 32-bit PostgreSQL 9.2 on windows 7.

If you want to map the route, you'll need the geometry. As shown in this query

SELECT r.id, s.gid, s.osm_name,s.cost, s.km, s.kmh , r.the_geom
  FROM astar_sp_delta('vw_boston_routing',
               131904, 17500, 0.1) As r INNER JOIN 
        vw_boston_routing AS s ON r.gid =  s.gid ;

If you just want the edges output and don't need to render it, leave out the geometry column.

SELECT r.id, s.osm_name
  FROM astar_sp_delta('vw_boston_routing',
               131904, 17500, 0.1) As r INNER JOIN 
        vw_boston_routing AS s ON r.gid =  s.gid ;
id |            osm_name
---+---------------------------
  1 | Kent Street
  2 | Kent Street
  3 | Beacon Street
 :
 19 | Cambridge Street
 20 | Cambridge Street
 :
 68 | Merrimac Street
 :
 83 | John F. Fitzgerald Surface Road
 84 | State Street
(84 rows)


Post Comments About pgRouting: Loading OpenStreetMap with Osm2Po and route querying




This Document is available under the GNU Free Documentation License 1.2 http://www.gnu.org/copyleft/fdl.html & for download at the BostonGIS site http://www.bostongis.com

Boston GIS      Copyright 2014      Paragon Corporation