What are the advantages of pgSQL+PostGIS+pgRouting over a simple MySQL+handwritten routing algorithms?

Question

Goal:

To have a routable set of roads in a database and to have a software that can calculate the shortest path (e.g. simple Dijkstra) through this set in a reasonable amount of time (up to 2 seconds). Required to be usable in the real world by real vehicles.

Problem:

I have written a few parsers to parse .osm nodes and edges into a MySQL database. Then I applied a Java application that loads all the nodes and edges into memory, creates a graph from those and does a Dijkstra's algorithm routing on it. I am satisfied with the speed that it has.

Recently I found out that Openstreetmap project has switched from MySQL to PostgreSQL.

Then I found the pgRouting project.

http://www.slideshare.net/antonpa/shortest-path-search-for-real-road-networks-and-dynamic-costs-with-pgrouting-1097533

I was impressed by the possible advantages:

• it can take traffic lights into account;
• it can take crossings into account;
• it can take signs and restrictions into account;
• it even can take slopes into account!
• it can take turn restrictions into account;
• it even promises to take weather conditions into account.

So the questions are:

1. Are all those advantages real?
2. Isn't pgRouting a kind of a blackbox? There are just a few tutorials on how to use it, and even these tutorials introduce even more unanswered questions. Isn't it simpler to build the desired [Goal] system from scratch?
3. What's the whole purpose of storing routable data the PostgreSQL/PostGIS way (geometry columns, strange database schema) instead of having just two tables - nodes and edges in a simple MySQL database?

Answer 1

Are all those advantages real?

It can actually do those things, if that's what you mean. It takes work from you to set the data up properly.

Isn't pgRouting a kind of a blackbox? There are just a few tutorials on how to use it, and even these tutorials introduce even more unanswered questions. Isn't it simpler to build the desired [Goal] system from scratch?

The documentation isn't good, that's true. But, all of the source is there. It's hardly a black box and can keep things DRY.

What's the whole purpose of storing routable data the PostgreSQL/PostGIS way (geometry columns, strange database schema) instead of having just two tables - nodes and edges in a simple MySQL database?

If MySQL does everything you need and you're familiar with it then use that. PostgreSQL/PostGIS is much more powerful from a relational database and spatial analysis perspective. That doesn't mean you have to use it or even that it's better for your application. If you want better performance, more features and strong spatial analysis functions, switch to PostgreSQL/PostGIS. If what you have works well enough, don't worry about it.

See also: Cross Compare of SQL Server, MySQL, and PostgreSQL