A few months ago we embarked on a journey towards a simple, yet foolish goal: building a journey planner that’s largely written in MariaDB’s SQL dialect.
In the first part of this blog series I introduced the GTFS-based data model and described a naive pathfinding algorithm in the form of a common table expression (CTE) that helps us find reasonably short paths between any two train stations in the Dutch railway network.
In the second blog I showed how a stored procedure can be used to look up departure times and find (mostly) optimal connections between trains. We ended up with a gargantuan stored procedure that “really puts everything together” by including the chonky CTE from the first blog.
A user can then call this stored procedure with the names of two train stations and a preferred time of departure:
The results are returned in a tabular format. It’s technicall human-readable, but it’s certainly not user-friendly.
In this third and final blog about this little travel planner, I’ll show you the friendly (but not necessarily user-friendly) web interface that I built for our CTE-powered stored procedure.
This what Inter-CTE’s main page looks like:
The page you see here displays the travel planner, which provides a user-friendly
interface for the
PLAN_JOURNEY stored procedure.
As you can see, I’ve chosen to use a range slider for the date input. This may look a bit weird, but it’s a convenient way to show that Inter-CTE’s database only contains data for the current day; the slider only lets you select departure times that actually exist in the database.
When you press the “Plan” button, Inter-CTE will show all advice returned by
PLAN_JOURNEY in a slightly more appealing format:
The first two suggestions in this list are usually fine. After that, they tend to become progressively worse, so don’t bother scrolling down too much. :-)
For those who don’t trust Inter-CTE’s planning capabilities at all, there’s also a departures page that simply lists all trains departing from a station within the next hour or so (I may have made an off-by-one error somewhere).
As an added bonus, I added a web-based query editor for your (and my) convenience. This query editor enables you to on a database, and I really do mean any query.
You can ask it to
SHOW TABLES, run queries that generate
FizzBuzz sequences, or even drop the database:
I was planning to offer a hosted version of this app, but apparently Inter-CTE doesn’t run on my potato virtual private server with 2 GB of memory. Sadly, that’s all I can afford as an overeducated 0.1X developer at an NGO.
That’s why I have published Inter-CTE’s source code on GitHub under a permissive license. This means you can run the app on your own machine, and play with (and break) it as much as you want!