Let’s build a simple journey planner using SQL (part 3, that you can run yourself)

Published: 1 Aug 2023
Updated: 6 Aug 2023
Written by: Chun Fei Lung

What’s better than a crappy train travel planner that runs on my machine? A crappy train travel planner that runs on YOUR machine!

Am I tra-in’ too hard?

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:

CALL PLAN_JOURNEY(
    'Utrecht Leidsche Rijn',
    'Hilversum Media Park',
    '2023-08-01 13:37:00'
);

The results are returned in a tabular format. It’s technicall human-readable, but it’s certainly not user-friendly.

Really really putting it all together

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.

The app is called Inter-CTE (pronounced as “Intershitty”, with a Dutch “stone coal” accent) and is built entirely using three tried and tested web technologies from the ’90s: PHP, HTML, and CSS.

This what Inter-CTE’s main page looks like:

Inter-CTE’s main page shows a page from which a user can plan a
journey from one train station to another at a departure time of
their choosing. — Inter-CTE’s main page looks like a budget version of the planner that can be found on the website of the Dutch Railways.

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:

Eleven minutes to walk all the way from platform twenty-one 2 1.

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).

A page that shows all trains that depart from Tilburg University
station within the next hour. — I’m not entirely convinced that Tilburg University is a real university, but it does have a train station named after it.

As an added bonus, I added a web-based query editor for your (and my) convenience. This query editor enables you to run any query (side note: There’s also a /structuredql endpoint for those who want something like GraphQL that’s not actually GraphQL.) 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:

Inter-CTE’s query editor allows you to drop the database. — The query editor can be used to drop the schema. This kills the app.

Run it yourself

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!