Analyzing Railway Traffic in the Netherlands

Gabor Szarnyas2024-05-31

Introduction

The Netherlands, the birthplace of DuckDB, has an area of about 42,000 km² with a population of about 18 million people. The high density of the country is a key factor in its extensive railway network, which consists of 3,223 km of tracks and 397 stations.

Information about this network's stations and services is available in the form of open datasets. These high-quality datasets are maintained by the team behind the Rijden de Treinen (Are the trains running?) application.

In this post, we'll demonstrate some of DuckDB's analytical capabilities on the Dutch railway network dataset. Unlike most of our other blog posts, this one doesn't introduce a new feature or release: instead, it demonstrates several existing features using a single domain. Some of the queries explained in this blog post are shown in simplified form on DuckDB's landing page.

Loading the Data

For our initial queries, we'll use the 2023 railway services dataset. To get this dataset, download the services-2023.csv.gz file (330 MB) and load it into DuckDB.

First, start the DuckDB command line client on a persistent database:

duckdb railway.db

Then, load the services-2023.csv.gz file into the services table.

CREATE TABLE services AS
    FROM 'services-2023.csv.gz';

Despite the seemingly simple query, there is quite a lot going on here. Let's deconstruct the query:

• First, there is no need to explicitly define a schema for our services table, nor is it necessary to use a COPY ... FROM statement. DuckDB automatically detects that the 'services-2023.csv.gz' refers to a gzip-compressed CSV file, so it calls the read_csv function, which decompresses the file and infers its schema from its content using the CSV sniffer.

• Second, the query makes use of DuckDB's FROM-first syntax, which allows users to omit the SELECT * clause. Hence, the SQL statement FROM 'services-2023.csv.gz'; is a shorthand for SELECT * FROM 'services-2023.csv.gz';.

• Third, the query creates a table called services and populates it with the result from the CSV reader. This is achieved using a CREATE TABLE ... AS statement.

Using DuckDB v0.10.3, loading the dataset takes approximately 5 seconds on an M2 MacBook Pro. To check the amount of data loaded, we can run the following query which pretty-prints the number of rows in the services table:

SELECT format('{:,}', count(*)) AS num_services
FROM services;

We can see that more than 21 million train services ran in the Netherlands in 2023.

Finding the Busiest Station per Month

Let's ask a simple query first: What were the busiest railway stations in the Netherlands in the first 6 months of 2023?

First, for every month, let's compute the number of services passing through each station. To do so, we extract the month from the service's date using the month function, then perform a group-by aggregation with a count(*):

SELECT
    month("Service:Date") AS month,
    "Stop:station name" AS station,
    count(*) AS num_services
FROM services
GROUP BY month, station
LIMIT 5;

Note that this query showcases a common redundancy in SQL: we list the names of non-aggregated columns in both the SELECT and the GROUP BY clauses. Using DuckDB's GROUP BY ALL feature, we can eliminate this. At the same time, let's also turn this result into an intermediate table called services_per_month using a CREATE TABLE ... AS statement:

CREATE TABLE services_per_month AS
    SELECT
        month("Service:Date") AS month,
        "Stop:station name" AS station,
        count(*) AS num_services
    FROM services
    GROUP BY ALL;

To answer the question, we can use the arg_max(arg, val) aggregation function, which returns the column arg in the row with the maximum value val. We filter on the month and return the results:

SELECT
    month,
    arg_max(station, num_services) AS station,
    max(num_services) AS num_services
FROM services_per_month
WHERE month <= 6
GROUP BY ALL;

Maybe surprisingly, in most months, the busiest railway station is not in Amsterdam but in the country's 4th largest city, Utrecht, thanks to its central geographic location.

Finding the Top-3 Busiest Stations for Each Summer Month

Let's change the question to: Which are the top-3 busiest stations for each summer month? The arg_max() function only helps us find the top-1 value but it is not sufficient for finding top-k results. Luckily, DuckDB has extensive support for SQL features, including window functions and we can use the rank() function to find top-k values. Addtionally, we use make_date to reconstruct the date, strftime to turn it into the month's name and array_agg:

SELECT month, month_name, array_agg(station) AS top3_stations
FROM (
    SELECT
        month,
        strftime(make_date(2023, month, 1), '%B') AS month_name,
        rank() OVER
            (PARTITION BY month ORDER BY num_services DESC) AS rank,
        station,
        num_services
    FROM services_per_month
    WHERE month BETWEEN 6 AND 8
)
WHERE rank <= 3
GROUP BY ALL
ORDER BY month;

This gives the following result:

We can see that the top 3 spots are shared between four stations: Utrecht Centraal, Amsterdam Centraal, Schiphol Airport, and Amsterdam Sloterdijk.

Directly Querying Parquet Files through HTTPS or S3

DuckDB supports querying remote files, including CSV and Parquet, via the HTTP(S) protocol and the S3 API. For example, we can run the following query:

SELECT "Service:Date", "Stop:station name"
FROM 'https://blobs.duckdb.org/nl-railway/services-2023.parquet'
LIMIT 3;

It returns the following result:

Using the remote Parquet file, the query for answering Which are the top-3 busiest stations for each summer month? can be run directly on a remote Parquet file without creating any local tables. To do this, we can define the services_per_month table as a common table expression in the WITH clause. The rest of the query remains the same:

WITH services_per_month AS (
    SELECT
        month("Service:Date") AS month,
        "Stop:station name" AS station,
        count(*) AS num_services
    FROM 'https://blobs.duckdb.org/nl-railway/services-2023.parquet'
    GROUP BY ALL
)
SELECT month, month_name, array_agg(station) AS top3_stations
FROM (
    SELECT
        month,
        strftime(make_date(2023, month, 1), '%B') AS month_name,
        rank() OVER
            (PARTITION BY month ORDER BY num_services DESC) AS rank,
        station,
        num_services
    FROM services_per_month
    WHERE month BETWEEN 6 AND 8
)
WHERE rank <= 3
GROUP BY ALL
ORDER BY month;

This query yields the same result as the query above, and completes (depending on the network speed) in about 1–2 seconds. This speed is possible because DuckDB doesn't need to download the whole Parquet file to evaluate the query: while the file size is 309 MB, it only uses about 20 MB of network traffic, approximately 6% of the total file size.

The reduction in network traffic is possible because of partial reading along both the columns and the rows of the data. First, Parquet's columnar layout allows the reader to only access the required columns. Second, the zonemaps available in the Parquet file's metadata allow the filter pushdown optimization (e.g., the reader only fetches row groups with dates in the summer months). Both of these optimizations are implemented via HTTP range requests, saving considerable traffic and time when running queries on remote Parquet files.

Largest Distance between Train Stations in the Netherlands

Let's answer the following question: Which two train stations in the Netherlands have the largest distance between them when traveling via rail? For this, we'll use two datasets. The first, stations-2022-01.csv, contains information on the railway stations (station name, country, etc.). We can simply load and query this dataset as follows:

CREATE TABLE stations AS
    FROM 'https://blobs.duckdb.org/data/stations-2022-01.csv';

SELECT
    id,
    name_short,
    name_long,
    country,
    printf('%.2f', geo_lat) AS latitude,
    printf('%.2f', geo_lng) AS longitude
FROM stations
LIMIT 5;

The second dataset, tariff-distances-2022-01.csv, contains the station distances. The distances are defined as the shortest route on the railway network and they are used to calculate the tariffs for ticket. Let's peek into this file:

head -n 9 tariff-distances-2022-01.csv | cut -d, -f1-9

Station,AC,AH,AHP,AHPR,AHZ,AKL,AKM,ALM
AC,XXX,82,83,85,90,71,188,32
AH,82,XXX,1,3,8,77,153,98
AHP,83,1,XXX,2,9,78,152,99
AHPR,85,3,2,XXX,11,80,150,101
AHZ,90,8,9,11,XXX,69,161,106
AKL,71,77,78,80,69,XXX,211,96
AKM,188,153,152,150,161,211,XXX,158
ALM,32,98,99,101,106,96,158,XXX

We can see that the distances are encoded as a matrix with the diagonal entries set to XXX. As explained in the dataset's description, this string implies that the two stations are the same station. If we just load the values as XXX, the CSV reader will assume that all columns have the type VARCHAR instead of numeric values. While this can be cleaned up later, it's a lot easier to avoid this problem altogether. To do so, we use the read_csv function and set the nullstr parameter to XXX:

CREATE TABLE distances AS
    FROM read_csv(
        'https://blobs.duckdb.org/data/tariff-distances-2022-01.csv',
        nullstr = 'XXX'
    );

To make the NULL values visible in the command line output, we set the .nullvalue dot command to NULL:

.nullvalue NULL

Then, using the DESCRIBE statement, we can confirm that DuckDB has inferred the column correctly as BIGINT:

FROM (DESCRIBE distances)
LIMIT 5;

To show the first 9 columns, we can run the following query with the #1, #2, etc. column indexes in the SELECT statement:

SELECT #1, #2, #3, #4, #5, #6, #7, #8, #9
FROM distances
LIMIT 8;

We can see that the data was loaded correctly but the wide table format is a bit unwieldy for further processing: to query for pairs of stations, we need to first turn it into a long table using the UNPIVOT statement. Naïvely, we would write something like the following:

CREATE TABLE distances_long AS
    UNPIVOT distances
    ON AC, AH, AHP, ...

However, we have almost 400 stations, so spelling out their names would be quite tedious. Fortunately, DuckDB has a trick to help with this: the COLUMNS(*) expression lists all columns and its optional EXCLUDE clause can remove given column names from the list. Therefore, the expression COLUMNS(* EXCLUDE station) lists all column names except station, precisely what we need for the UNPIVOT command:

CREATE TABLE distances_long AS
    UNPIVOT distances
    ON COLUMNS (* EXCLUDE station)
    INTO NAME other_station VALUE distance;

This results in the following table:

SELECT station, other_station, distance
FROM distances_long
LIMIT 3;

Now we can join the distances_long table on the stations table along both the start and end stations, then filter for stations which are located in the Netherlands. We introduce symmetry breaking (station < other_station) to ensure that the same pair of stations only occurs once in the output. Finally, we select the top-3 results:

SELECT
    s1.name_long AS station1,
    s2.name_long AS station2,
    distances_long.distance
FROM distances_long
JOIN stations s1 ON distances_long.station = s1.code
JOIN stations s2 ON distances_long.other_station = s2.code
WHERE s1.country = 'NL'
  AND s2.country = 'NL'
  AND station < other_station
ORDER BY distance DESC
LIMIT 3;

The results show that there are pairs of train stations, which are at least 425 km away – quite the distance for such a small country!

Conclusion

In this post, we demonstrated some of DuckDB's key features, including automatic detection of formats based on filenames, auto-inferencing the schema of CSV files, direct Parquet querying, remote querying, window functions, unpivot, several friendly SQL features (such as FROM-first, GROUP BY ALL, and COLUMNS(*)), and so on. The combination of these allows for formulating queries using different file formats (CSV, Parquet), data sources (local, HTTPS, S3), and SQL features. This helps users answer queries quickly and efficiently.

In the next installment, we'll take a look at temporal data using AsOf joins and geospatial data using the DuckDB spatial extension.