Timetable for Deutsche Bahn (DB) and local KA trains system

Petrows · January 2, 2021, 9:47pm

Hi all, want to share one new solution, how to use obtain and display current trains timetable. I used 2 services: DB API and KVV API, but this method could be easily used for other systems. I have 2 displays in our home - primary neat the door and second one for clients, my wife has small Nails-studio, so this display helps clients to orient in local trams. Currently she is banned from work due to lockdown, so decided to implement this old idea.

Architecture: python script -> MQTT bus -> OpenHAB items
DB APi is public and also could be used to display trams overall Germany, i used local KVV API just for interest.

Also, maybe this could be an example, how to implement connection to some random remote API.

So, first - you need to obtain API keys.

DB: just register at https://developer.deutschebahn.com/ and you can start to use API with nice test UI.
KVV: they also provide some API: KVV - Karlsruher Verkehrsverbund - Bahn und Bus - Open Data , but you need to register there. I am too lazy, my way is simplier - go to live.kvv.de and take it’s key.

Then, you need to find your train station to display.

DB: Use API to find station directly in developer console page.
KVV station ID could be parsed from URL on live.kvv.de page.

Then, use this scripts:

DB: kvv-timetable.py
KVV: kvv-timetable.py

Check built-in help for arguments. Both scripts are pretty simple: check API and place parsed values as MQTT topic:

# Publish routes to MQTT
publish.single(args.mqtt_topic+'/route-'+str(idx), payload=payload, qos=0, retain=True, hostname=args.mqtt_host,
               port=int(args.mqtt_port), client_id="KVV-Routes", auth=mqtt_auth)

Then, just define MQTT-driven generic things (4 routes):

Thing mqtt:topic:openhab:kvv_timetable "KVV Timetable" (mqtt:broker:openhab) {
	Channels:
		Type string : r0_route [stateTopic="kvv-timetable/route-0", transformationPattern="JSONPATH:$.route"]
		Type string : r0_dst [stateTopic="kvv-timetable/route-0", transformationPattern="JSONPATH:$.destination_city"]
		Type string : r0_time_str [stateTopic="kvv-timetable/route-0", transformationPattern="JSONPATH:$.time"]
		Type datetime : r0_time [stateTopic="kvv-timetable/route-0", transformationPattern="JSONPATH:$.tms"]
		Type string : r0_summary_text [stateTopic="kvv-timetable/route-0", transformationPattern="JSONPATH:$.summary_text"]
		Type string : r0_dir_text [stateTopic="kvv-timetable/route-0", transformationPattern="JSONPATH:$.direction_text"]

		Type string : r1_route [stateTopic="kvv-timetable/route-1", transformationPattern="JSONPATH:$.route"]
		Type string : r1_dst [stateTopic="kvv-timetable/route-1", transformationPattern="JSONPATH:$.destination_city"]
		Type string : r1_time_str [stateTopic="kvv-timetable/route-1", transformationPattern="JSONPATH:$.time"]
		Type datetime : r1_time [stateTopic="kvv-timetable/route-1", transformationPattern="JSONPATH:$.tms"]
        Type string : r1_summary_text [stateTopic="kvv-timetable/route-1", transformationPattern="JSONPATH:$.summary_text"]
        Type string : r1_dir_text [stateTopic="kvv-timetable/route-1", transformationPattern="JSONPATH:$.direction_text"]

		Type string : r2_route [stateTopic="kvv-timetable/route-2", transformationPattern="JSONPATH:$.route"]
		Type string : r2_dst [stateTopic="kvv-timetable/route-2", transformationPattern="JSONPATH:$.destination_city"]
		Type string : r2_time_str [stateTopic="kvv-timetable/route-2", transformationPattern="JSONPATH:$.time"]
		Type datetime : r2_time [stateTopic="kvv-timetable/route-2", transformationPattern="JSONPATH:$.tms"]
        Type string : r2_summary_text [stateTopic="kvv-timetable/route-2", transformationPattern="JSONPATH:$.summary_text"]
        Type string : r2_dir_text [stateTopic="kvv-timetable/route-2", transformationPattern="JSONPATH:$.direction_text"]

		Type string : r3_route [stateTopic="kvv-timetable/route-3", transformationPattern="JSONPATH:$.route"]
		Type string : r3_dst [stateTopic="kvv-timetable/route-3", transformationPattern="JSONPATH:$.destination_city"]
		Type string : r3_time_str [stateTopic="kvv-timetable/route-3", transformationPattern="JSONPATH:$.time"]
		Type datetime : r3_time [stateTopic="kvv-timetable/route-3", transformationPattern="JSONPATH:$.tms"]
        Type string : r3_summary_text [stateTopic="kvv-timetable/route-3", transformationPattern="JSONPATH:$.summary_text"]
        Type string : r3_dir_text [stateTopic="kvv-timetable/route-3", transformationPattern="JSONPATH:$.direction_text"]
}

And items:

// This file defines Railroad and other time-table information

String timetable_0_route { channel="mqtt:topic:openhab:kvv_timetable:r0_route" }
String timetable_0_dst { channel="mqtt:topic:openhab:kvv_timetable:r0_dst" }
String timetable_0_summary_text { channel="mqtt:topic:openhab:kvv_timetable:r0_summary_text" }
DateTime timetable_0_time { channel="mqtt:topic:openhab:kvv_timetable:r0_time" }
String timetable_0_time_str { channel="mqtt:topic:openhab:kvv_timetable:r0_time_str" }
String timetable_0_dir_text { channel="mqtt:topic:openhab:kvv_timetable:r0_dir_text" }

String timetable_1_route { channel="mqtt:topic:openhab:kvv_timetable:r1_route" }
String timetable_1_dst { channel="mqtt:topic:openhab:kvv_timetable:r1_dst" }
String timetable_1_summary_text { channel="mqtt:topic:openhab:kvv_timetable:r1_summary_text" }
DateTime timetable_1_time { channel="mqtt:topic:openhab:kvv_timetable:r1_time" }
String timetable_1_time_str { channel="mqtt:topic:openhab:kvv_timetable:r1_time_str" }
String timetable_1_dir_text { channel="mqtt:topic:openhab:kvv_timetable:r1_dir_text" }

String timetable_2_route { channel="mqtt:topic:openhab:kvv_timetable:r2_route" }
String timetable_2_dst { channel="mqtt:topic:openhab:kvv_timetable:r2_dst" }
String timetable_2_summary_text { channel="mqtt:topic:openhab:kvv_timetable:r2_summary_text" }
DateTime timetable_2_time { channel="mqtt:topic:openhab:kvv_timetable:r2_time" }
String timetable_2_time_str { channel="mqtt:topic:openhab:kvv_timetable:r2_time_str" }
String timetable_2_dir_text { channel="mqtt:topic:openhab:kvv_timetable:r2_dir_text" }

String timetable_3_route { channel="mqtt:topic:openhab:kvv_timetable:r3_route" }
String timetable_3_dst { channel="mqtt:topic:openhab:kvv_timetable:r3_dst" }
String timetable_3_summary_text { channel="mqtt:topic:openhab:kvv_timetable:r3_summary_text" }
DateTime timetable_3_time { channel="mqtt:topic:openhab:kvv_timetable:r3_time" }
String timetable_3_time_str { channel="mqtt:topic:openhab:kvv_timetable:r3_time_str" }
String timetable_3_dir_text { channel="mqtt:topic:openhab:kvv_timetable:r3_dir_text" }

That’s all! Now setup systemd service to run this script(s) every minute and items will be updated in realtime. For display, you can simple add them to sitemap (summary_text channel):

Group item=timetable_0_summary_text label="Trains" icon="calendar" {
    Frame label="KVV" {
        Text item=timetable_0_summary_text label="" icon="calendar"
        Text item=timetable_1_summary_text label="" icon="calendar"
        Text item=timetable_2_summary_text label="" icon="calendar"
        Text item=timetable_3_summary_text label="" icon="calendar"
    }
    Frame label="Deutsche Bahn" {
        Text item=timetable_db_0_summary_text label="" icon="calendar"
        Text item=timetable_db_1_summary_text label="" icon="calendar"
        Text item=timetable_db_2_summary_text label="" icon="calendar"
        Text item=timetable_db_3_summary_text label="" icon="calendar"
    }
}

And as Habpanel item:

<table cellpadding="3" width="100%">
<tr>
  <td>
  	<table cellpadding="3" width="100%" class="timetable timetable-kvv">
      <tr>
        <td width="10%">
        	{{ itemValue('timetable_0_route') }}
        </td>
        <td width="60%" align="left">
        	{{ itemValue('timetable_0_dst') }}
        </td>
        <td width="30%">
        	{{ itemValue('timetable_0_time_str') }}
        </td>
      </tr>
      <tr>
        <td>
        	{{ itemValue('timetable_1_route') }}
        </td>
        <td align="left">
        	{{ itemValue('timetable_1_dst') }}
        </td>
        <td>
        	{{ itemValue('timetable_1_time_str') }}
        </td>
      </tr>
      <tr>
        <td width="10%">
        	{{ itemValue('timetable_2_route') }}
        </td>
        <td width="60%" align="left">
        	{{ itemValue('timetable_2_dst') }}
        </td>
        <td width="30%">
        	{{ itemValue('timetable_2_time_str') }}
        </td>
      </tr>
      <tr>
        <td>
        	{{ itemValue('timetable_3_route') }}
        </td>
        <td align="left">
        	{{ itemValue('timetable_3_dst') }}
        </td>
        <td>
        	{{ itemValue('timetable_3_time_str') }}
        </td>
      </tr>
    </table>
  </td>
</tr>
</table>

That’s all! Very simple and usefull. Some additional pics, how it looks like in reality:

Petrows · January 3, 2021, 5:38pm

Fixed couple of bugs found in KVV and DB scripts. Hope someone will find something useful. Also, i am thinking that multiply messages in MQTT bus is wrong, maybe better to have one message will full timetable, it this way it is guaranteed that timetable will be overwritten by new one on every update. Currently i have added padding in both scripts to send “-” route if actual data is less than expected.

Petrows · January 4, 2021, 1:13pm

I am thinking about to wrap Public transportation API as binding, does anyone need that?

Schmendrax · January 25, 2021, 9:00pm

I would love that !
Are you already working on that ? …otherwise I would try to make your script-based solution work

Petrows · January 25, 2021, 9:20pm

Hey, not yet. Nobody replied for a long time, so I have assumed it not really requested from community. So, please use scripts, they should be also fine

Schmendrax · January 27, 2021, 9:32pm

Hello Peter,
thanks for your reply! I never used scripts before, so therefore have no idea what additional packages have to be installed on my Pi, which additional bindings I need (if any?) in OH3 and where exactly (directory on the Pi) I have to copy your script to.

Could you please give me a hint or a short instruction how to get the script running ? …that would be awesome

Cheers,
Hendrik

Petrows · January 28, 2021, 7:07pm

Hey Hendrick,

So, easiest way is to try run this scripts and see an error. Usually it is not really hard to find what is missing. All my scripts have deps:

openhab-deps:
  pkg.installed:
    - pkgs:
      - mosquitto-clients
      - python3-lxml
      - python3-paho-mqtt
      - python3-dateparser

See my Saltstack config: saltstack-config/openhab.sls at master · petrows/saltstack-config · GitHub

Directory has no sense, you can run it from any. They place output to MQTT. From bindings nothing special required, only MQTT.

MDi7599 · March 29, 2023, 12:08pm

Hey Peter, I was wondering if you have this still working (especially looking at the KVV API changes done a few months ago)
Mathias

Petrows · July 3, 2023, 10:39am

Hey @MDi7599 ,

Unfortinately it does not work anymore, as they removed this easy-to-use API, i have removed it from mine dashboard.

(Also i have divorced and there is no need to use it anymore, as i am using bicycle most time).