Exercise 5: The Reuser’s Perspective (OGD)

Author
Affiliations

Julien A. Raemy

docuteam SA

University of Bern

Published

February 9, 2026

Modified

February 26, 2026

The Reuser’s Perspective (OGD)

NoteInstructions

Browse, search and do what is described below. You can do this exercise alone or in pairs. The whole exercise should take between 20 and 30 minutes. Afterwards, you will share what you have discovered and/or created with the class.

A - Exploring and Reusing Swiss OGD

  1. Go to opendata.swiss. What are the three cantons that provide the most datasets on opendata.swiss?

  2. Go to the Ist-Daten (Target/Actual Comparison of SBB departure/arrive times) page (https://opendata.swiss/en/perma/ist-daten-sbb@schweizerische-bundesbahnen-sbb) and download the CSV file. Open it in a spreadsheet application (Excel, LibreOffice Calc, Google Sheets) and have a look at its structure and content. Can you identify which columns correspond to the train station, the train number, the scheduled departure time, and the actual departure time? As you will notice, working with such a large raw file can be cumbersome — this is where APIs (see Q3) become much more convenient for quick, targeted requests.

  3. Now try to get transport information via an API instead of downloading a file. The Swiss public transport API at https://transport.opendata.ch/ is freely accessible without authentication. Try the following:

    1. Look up the next departures from a station of your choice, e.g.: https://transport.opendata.ch/v1/stationboard?station=Bern&limit=5

    2. Try changing the station parameter to another station and observe the results.

  4. How many mailboxes are there in your commune or city? Explore the application at https://wieviele-briefkaesten-gibt-es.streamlit.app/. Where do these data originally come from?

  • Q1: Use the “Organisation” or “Canton” filter on the opendata.swiss search page to see the counts.
  • Q2: The key columns are: Stop name (train station), Journey identifier (train number), Departure time / Arrival time (scheduled times), Departure forecast / Arrival forecast (actual times), Departure delay / Arrival delay, and Cancelled TF (cancellation flag).
  • Q3: The transport.opendata.ch API returns JSON. You can paste the URL directly in your browser to see the result. Try the /v1/connections endpoint as well (e.g. ?from=Lausanne&to=Genève). The API documentation at https://transport.opendata.ch/docs.html describes all available parameters.
  • Q4: The application estimates the number of delivery addresses / mailboxes (Zustelladressen / Briefkästen) for a user-defined area. To investigate where the data come from, you can look at the source code on GitHub (https://github.com/davidoesch/wo-sind-briefkaesten) or use your browser’s developer tools (Network tab) while the app loads to see which data sources are being fetched. The underlying data come from the Federal Building and Housing Register (GWR) of the Federal Statistical Office (BFS) and business locations from the Overture Maps Foundation.

B - OGD as Linked Data (LINDAS)

LINDAS (Linked Data Service) is the Swiss federal platform for publishing and querying government data as Linked Data.

Visualize

Go to visualize.admin.ch and use the Verkehrsleistung auf den Flugplätzen dataset (published by the Bundesamt für Zivilluftfahrt BAZL). This dataset records the number of annual air traffic movements (1 movement = 1 take-off or 1 landing) at Swiss airports.

  1. For the year 2023, were there more flight movements at Geneva or at Basel? Try to visualise this comparison on a map.

  2. Look at the data for Zurich Airport across multiple years. By how many movements (or what percentage) did flight traffic drop in 2020 and 2021 compared to 2019?

SPARQL

SPARQL (SPARQL Protocol and RDF Query Language) is the standard query language for Linked Data. Interestingly, tools like visualize.admin.ch use SPARQL queries behind the scenes — every chart or map you created above was powered by an automatically generated SPARQL query sent to the LINDAS endpoint.

You can write and run SPARQL queries directly at the LINDAS SPARQL Endpoint.

  1. Try the following query to explore the Archive database of the Swiss Federal Archives: the Archive database of the Swiss Federal Archives (https://culture.ld.admin.ch/.well-known/dataset/ais). This dataset uses the Records in Context (RiC) ontology. Try the following query to find archival records related to “Bern”:

    PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX rico: <https://www.ica.org/standards/RiC/ontology#>

SELECT ?record ?identifier ?title ?beginDate ?endDate ?expressedDate ?holder
WHERE {
  GRAPH <https://lindas.admin.ch/sfa/ais> {
    ?record rdf:type rico:Record .
    ?record rico:title ?title .
    ?record rico:identifier ?identifier .

    OPTIONAL { ?record rico:hasOrHadHolder ?holder }

    OPTIONAL {
      ?record rico:isAssociatedWithDate ?date .
      OPTIONAL { ?date rico:beginningDate ?beginDate }
      OPTIONAL { ?date rico:endDate ?endDate }
      OPTIONAL { ?date rico:expressedDate ?expressedDate }
    }

    FILTER(CONTAINS(LCASE(?title), "bern"))
  }
}
ORDER BY ?beginDate
LIMIT 100

    Run this query, then try modifying it to search for a different topic (e.g. a different city, a subject that interests you, or a specific date range).

  • Q1: On visualize.admin.ch, search for “Verkehrsleistung” or “Flugplätzen” to find the dataset. Once selected, choose a map visualisation and filter by the year 2023. Compare the circle sizes or values for Geneva and Basel.
  • Q2: Select the line chart view and filter for Zurich Airport only.
  • Q3: Records in Context (RiC) is an ontology developed by the International Council on Archives (ICA) for describing archival records as Linked Data. In the query, rico:Record represents an archival record, rico:title its title, and rico:isAssociatedWithDate links to date information. Try replacing "bern" in the FILTER with another term, or remove the filter entirely and add FILTER(?beginDate > "1900-01-01"^^xsd:date) to explore records from the 20th century onwards.
Back to top