Wandrer.earth helps me cycle more by tracking all the roads/streets/ways I already cycled on. The goal is to use every street possible in a region. Private ways and forbidden roads are already removed. Walking is tracked too, but not that important for me.

On Wandrer.earth you can download an all-points excel file. Excel is not the way I want to work with data, so I wrote a converter to SQL which I can view in a datasette

The code for the converter is on github: http://github.com/mfa/wandrer-earth-to-sqlite.

A few remarks about the code:

• Earth (so the overall value) is removed

• The list of continents is needed because they are toplevel, the same as suburbs

• Administrative levels are different in every country, so numbers are used

• The fieldnames are transfered to be more sql-like

• Monthly champion and points are two different tables

• The bonus points rows are dropped for now

I wanted to answer two questions with this new dataset:

Where did I get the most bonus points. The query for this in SQL looks like this:

SELECT full_name, name, points, updated FROM champions ORDER BY points DESC

For me this looks like this:

And the second question is: Which regions are easiest to bring to 25%. The query:

SELECT pure_name, full_name, completed_in_km, percentage,
       distance_to_25_in_km, distance_to_50_in_km, distance_to_75_in_km
FROM points WHERE percentage < 25 ORDER BY distance_to_25_in_km

The result for my data:

The second row looks suspicious, but this area seems to be very small. The last row is Tokyo. I will probably not visit Tokyo in the near future to cycle there again. So maybe filtering on a region is a good idea, and this is easy with SQL now. Just add a WHERE level_2 == "Germany" to limit to Germany.

This solves my current need for planning new routes and gives an easier way to see the monthly champions I achieved.

Yesterday I ported my last consumer of meinsack.click over to use Homeassistant as datasource. As far as I know nobody is using this service anymore and so today is about time to stop hosting it and write a bit about it.

The project started at the end of 2015 by importing all streets and districts of Stuttgart into a Django database. We imported the Gelber Sack dates from a form on the website of the company that collected them at that time. Years later they stopped providing the form and we parsed the text copied from their PDF. The Code of the Django version is archived on Github: https://github.com/opendata-stuttgart/meinsack.

Updating Django and PostgreSQL for a readonly service felt wrong, so I wrote a version using Datasette at the end of 2020. This version was initially hosted on Google Cloud Run but had so much Crawler traffic that I needed to move it to an already existing Hetzner VPS. The Datasette Version of Meinsack.click is now archived on Github too: https://github.com/mfa/meinsack-datasette.

Today (end of 2023) it is time to cancel the domain and stop running the datasette. There are a few alternatives for Stuttgart now. I started to use the ical stream and extract the data from there. There is even an all-solved-solution with support for a lot of other cities and regions in HACS: https://github.com/mampfes/hacs_waste_collection_schedule.

I use a kindle to display status information like next waste disposal and weather. This was the last consumer of my custom written sensor backend that I migrated to Homeassistant in the last months.

A website that can be displayed on an old Kindle4 is not able to use a lot of Javascript. I tried the basic solution described by nirkons on Github, but it was really annoying to debug for the stuff I want to display. So I decided to choose a different solution: Render a HTML file using a Template in Homeassistant and updating it via cron.

First the Template. I used the developer-tools inside Homeassistant to fiddle around with everything I want. The resulting template is stored in kindle.jinja in config/custom_templates. Important here: changing a template file on disk needs a Homeassistant restart.

Because of changes in Homeassistant 2024.4 there is no forecast in the weather state anymore. To get this back we need a new template in the configuration.yaml:

template:
  - trigger:
    - platform: time_pattern
      minutes: "*"
    - platform: homeassistant
      event: start
    - platform: event
      event_type: event_template_reloaded
    action:
      - service: weather.get_forecasts
        target:
          entity_id: weather.forecast_home
        data:
          type: daily
        response_variable: daily_forecast
    sensor:
      - name: "Weather Daily Forecast"
        unique_id: home_daily_forecast
        state: "{{ states('weather.forecast_home') }}"
        attributes:
          forecast: "{{ daily_forecast['weather.forecast_home'].forecast[:3] }}"

The file has lots of CSS on the top and quite a bit of HTML, but a minimal version of kindle.jinja looks like this:

{% macro kindle_html() %}
<html>
  <head>
    <title>Kindle Status Display</title>
  </head>
  <body>
    Current time and date: <p id="time"></p>, <p id="date"></p><br>
    next Recycling collection: {{ as_timestamp(states('sensor.yellow_bag')) | timestamp_custom('%d.%m.%Y') }}
    <pre>
    temperatures tomorrow:
    min: {{ state_attr('sensor.weather_daily_forecast', 'forecast')[1].templow|round }}&#176;C
    max: {{ state_attr('sensor.weather_daily_forecast', 'forecast')[1].temperature|round }}&#176;C
    </pre>
  </body>
  <script src="kindle.js"></script>
</html>
{% endmacro %}

The kindle.js is important to update the current time and date on the kindle and to trigger a regular reload. The code for this is as old school as possible:

function updateDate(){
  d = new Date();
  document.getElementById("date").innerHTML = d.getDate().toString() + "." + parseInt(d.getMonth()+1).toString() + "." + d.getFullYear();
  document.getElementById("time").innerHTML = d.getHours().toString() + ":" + d.getMinutes().toString();
}

updateDate();

setInterval(function(){
  updateDate();
}, 1000*60);

setInterval(function () {
  location.reload();
}, 1000*60*60);

The version I actually use adds extra '0' if needed and is a bit more messy. We store kindle.js and the rendered html template output later in config/www/ which can be accessed without any authentication via http://your-homeassistant-ip:8123/local/kindle.html. For more information about hosting html files with Homeassistant, see https://www.home-assistant.io/integrations/http/#hosting-files.

To render the macro we stored we need a long lived token which can be generated in the profile of Homeassistant. This curl command renders a template and stores it to kindle.html:

curl -s -H "Authorization: Bearer YOUR_LONG_LIVED_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"template": "{% from \"kindle.jinja\" import kindle_html %}{{ kindle_html() }}"}' \
  http://localhost:8123/api/template > ~/.homeassistant/www/kindle.html

The template command {% from "kindle.jinja" import kindle_html %}{{ kindle_html() }} imports the macro and calls it. This can of course be tested in the developer-tools like we did before. I put the curl command inside a script file and call this script via cron every 10 minutes. For later debugging on the bottom of the rendered html is a {{ now }} to see when the file was last rendered.

My kindle display now looks like this:

The html/css can be optimized a bit more and some sensor averages may be nice, but this is enough for today.