pgRouting

Why did I choose to discuss pgRouting?

In the spring of 2016, I was taking a spatial database design course at Temple University. The course outlined many aspects of databases and the languages geared towards them. However, I wanted to delve deeper in a particular aspect, extensions and specifically, find out the strengths they can offer. Thus how I stumbled upon pgRouting. And lets just say, I have a love/hate relationship with this extension.

Introduction to pgRouting:

pgRouting is an extension for PostGIS, hence the pg prefix, and focuses primarily on routing line networks (roads, sidewalks, etc). It comes with a surplus of different algorithms to calculate shortest distance (both mileage and time, if time exists as a field) and create topological datasets. These functions are what define pgRouting and set it apart from other extensions, it fills a unique niche that may come in handy for the select few that are pursuing such a task.

Tips & Tricks:

pgRouting is not heavily documented and can be a bit finicky at times, so I just want to lay out a few tips/tricks I have learned as I troubleshooted my way through the extension.

IF you are following a guide other than mine (or even mine), make sure you are reading the most up to date version of it. I made this mistake and it set me back many days and after it was all said and done, I didn’t even need the step to begin with…

pgRouting has two types of functions: functions that start with an underscore and those that don’t. From my understanding, the difference between the two are ones meant to be used by users and ones meant to be used admins, superusers, or owners. This brings me to my actual reasoning, make sure you have the necessary privileges for what you’re trying to accomplish. For instance, createTopology() is a function that is crucial for other pgRouting functions to function properly and it was failing everytime I ran it, but did not inform me properly of the issue it was encountering. I eventually found out, after banging my head on my wall for quite some time, that it was a privilege error and you needed enhanced privileges. I have not experimented with all of the functions pgRouting offers, but I  am sure other functions require enhanced privileges as well. MAKE SURE YOU HAVE CORRECT PRIVILEGES ASSIGNED TO YOUR ACCOUNT, it could save you a lot of time and heart ache.

LASTLY, but not least. pgRouting is extremely literal. For example, pgr_dijkstra has many ways to run it, but the method I chose required five parameters: text, big int, big int, boolean, boolean. Within the text parameter, it required the selection of a primary key name id, if anything else it would fail, a source and target, each must be named source and target, and the length of the geom, which must be named cost. Without them being named exactly as they need to be, the function will fail to run.

Will be continued…