m.k.ge•o•mat•ics |ˌjēəˈmatiks|

While working through Regina Obe and Leo Hsu’s PostGIS In Action I thought that I’d jump into the world of routing. My plan was to develop a sample application that could be used to plan bicycle routes throughout the city of Seattle. A quick google search proved that someone has already done it, and done it very well! http://www.ridethecity.com/ provides cycling routes using OSM data for many major cities, Seattle included.

Undeterred and inspired, i decided to compile the pgRouting set of tools for PostGIS and give them a whirl.

My primary tutorial for moving through the install and execution of functions came from the 2009 FOSS4G Tokyo & Osaka workshop entitled, “FOSS4G routing with pgRouting tools and OpenStreetMap road data.” Although my installation on Ubuntu Netbook Remix (UNR) 9.10 required a little different setup, this guide definitely got me 99% of the way there.

The majority of my installation woes were caused by the different pathways used on my UNR install of PostgreSQL vs. what are apparently the standard paths.

After attempting to execute cmake to compile pgRouting, I’d be presented with an error stating that the ‘POSTGRESQL_INCLUDE_DIR’ was not found. A locate command pointed me to the correct path for my PostgreSQL installation. By modifying the FindPostgreSQL.cmake file to search for the correct path, I was back in business.

Following the workshop instructions, I then attempted to create the database directly from the terminal, which yielded the following result.

matt@matt-netbook:~$ createdb -U postgres routing
createdb: could not connect to database postgres: could not connect to server: No such file or directory
 Is the server running locally and accepting
 connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

After reading the documentation associated with “createdb”, i tried adding the “-h” flag pointing to “localhost”, which solved the problem.

The final error which I ran into had to do with the “$libdir” environment variable. While trying to register the pgRouting functions in my new database, I’d be presented with the following:

psql:/usr/share/postlbs/routing_core.sql:32: ERROR:  could not access file "$libdir/librouting": No such file or directory
psql:/usr/share/postlbs/routing_core.sql:43: ERROR:  could not access file "$libdir/librouting": No such file or directory
psql:/usr/share/postlbs/routing_core.sql:53: ERROR:  could not access file "$libdir/librouting": No such file or directory

Getting impatient at this point (i wanted to route!) I modified the SQL files to reference the explicit path of my PostgreSQL lib directory. Once that was done, I had a working routing database!

Loading the sample data, creating the indexes, and executing the queries was amazingly straightforward. To test visualizing the data, I exported one of the tutorial queries directly into a new table.

SELECT * INTO export
 FROM dijkstra_sp('ways', 10, 20);

The route depicted in red as seen in QGIS.

Just for kicks, I tried exporting the data as GeoJSON and visualzing it via OpenLayers.

The following SQL query aggregates the exported line segments into a single GeoJSON object:

SELECT ST_AsGeoJSON(ST_UNION(the_geom)) AS geom_union
FROM export;

Using the vector-formats OL example, which displays GeoJSON in either EPSG 4326 or 102113, I was able to visualize the line segment with no problem.

GeoJSON representation of line segment generated using pgRouting, displayed in OpenLayers

Well that’s all for one day. So it looks like the bike riding app is out, but I’m sure that there will be many more interesting ideas for pgRouting that will come to mind as I continue to explore PostGIS.