Paragon Corpoation PostGIS Spatial Database Engine OSGeo.org The Open Source Geospatial Foundation UMN Mapserver Boston Geographic Information Systems   
FOSS4G International 2017, August 14th-18th 2017
   PostGreSQL Object Relational Database Management System
Home   About Boston GIS   Consulting Services  Boston GIS Blog  Postgres OnLine Journal  Planet PostGIS  PostGIS Funding

Purpose of BostonGIS

BostonGIS is a testbed for GIS and Web Mapping solutions utilizing open source, freely available and/or open gis technologies. We will be using mostly Boston, Massachusetts data to provide mapping and spatial database examples.

If you have some thoughts or comments on what you would like to see covered on this site, drop us a line on our Feed Back page.


GIS Tutorials on Opensource and OpenGIS technologies Tutorials
GIS Article comments Article and Tutorial Comments
Boston GIS BLog Rss FeedBoston GIS blog

PDF HTML All BostonGIS tutorials packaged together in an E-Book.


Boston GIS Store

Loading


Tutorial and Tip Sites
Desktop GIS
External Data
GIS Events and Groups
GIS SDKs and Frameworks
External Resources
Glossary
GIS Blogs Around Boston
External GIS Blogs
External Papers Articles
GIS Quick Guides and References
OpenStreetMap and OpenLayers Tutorials
PostGIS, pgRouting, and PostgreSQL Tutorials
Part 1: Getting Started With PostGIS: An almost Idiot's Guide (PostGIS 2.2) more ...
pgRouting: Loading OpenStreetMap with Osm2Po and route querying

Printer Friendly

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
Part 1: Getting Started With PostGIS: An almost Idiot's Guide (PostGIS 2.0) more ...
OSCON 2009: Tips and Tricks for Writing PostGIS Spatial Queries more ...
PGCon2009: PostGIS 1.4, PostgreSQL 8.4 Spatial Analysis Queries, Building Geometries, Open Jump more ...
PLR Part 3: PL/R and Geospatial Data Abstraction Library (GDAL) RGDAL more ...
PostGIS Nearest Neighbor: A Generic Solution - Much Faster than Previous Solution more ...
Solving the Nearest Neighbor Problem in PostGIS more ...
PLR Part 2: PL/R and PostGIS more ...
PLR Part 1: Up and Running with PL/R (PLR) in PostgreSQL: An almost Idiot's Guide more ...
Part 2 - PostGIS and SharpMap in ASP.NET 2.0 using VB.NET: Displaying the Maps more ... download
Part 1 - PostGIS and SharpMap in ASP.NET 2.0 using VB.NET: Compiling SharpMap with PostGIS more ...
Part 3: PostGIS Loading Data from Non-Spatial Sources more ...
Part 2: Introduction to Spatial Queries and SFSQL with PostGIS more ...
Miscellaneous Tutorials/Cheatsheets/Examples
SpatiaLite Tutorials
Boston External Map Examples
SQL Server 2008 Tutorials
UMN Mapserver Tutorials
General Commentary
Locations of visitors to BostonGIS
Boston GIS      Copyright 2017      Paragon Corporation