Get a Counties List

I need a list of counties of Germany (Landkreise), of France (Arrondissements) and of some other countries. Such lists are in Wikipedia in form of a table, i.e. Liste der Landkreise in Deutschland (no English version available) or List of arrondissements of France.

To extract the table Convert Wiki Tables to CSV works really well. Of course, every country has its special cases, i.e. in Germany there is "Aachen" and "Städteregion Aachen". I manually removed all cities that are part of a larger region for the German Landkreise.

But is there a way to get a better list out of Wikidata? I build a minimal SPARQL query using the query builder:

img1

Which results in this SPARQL query:

SELECT DISTINCT ?item ?itemLabel WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE]". }
  {
    SELECT DISTINCT ?item WHERE {
      ?item p:P31 ?statement0.
      ?statement0 (ps:P31/(wdt:P279*)) wd:Q106658.
    }
  }
}

The resulting list of districts has 690 elements which is clearly too many, because Germany currently has 294 rural districts. There are too many because historical districts are in there too, i.e. Borken or Zeitz district (GDR). A lot of them have properties for them being former districts, i.e. "dissolved, abolished or demolished date" or "replaced by", but a SPARQL query to filter all of them feels like too much trouble. Another way to filter would be to download the list of 690 entries and filter with code for the fields that mark former districts.

I will revisit the wikidata-query-and-filtering-with-code idea, if the manually edited wikipedia table extract has too many issues. For now I will keep going with the filtered wikitable extract.

Compare the temperature measurement of the Pico

The Raspberry Pi Pico has an internal temperature sensor. The measurements are done via comparing the ADC returned by the sensor with the current ADC of the Pico. I want to compare the internal sensor with an BME280 added to I2C. To record the data I will pull the temperature of both sensors with Homeassistant.

First we need to read the internal sensor on the Pico with MicroPython. For instructions on how to get MicroPython an the Pico and use the REPL see a previous post. Important here, because we want to use WiFi, use the RPI_PICO_W version.

Using the REPL to see the internal temperature:

import machine
adc_voltage = machine.ADC(4).read_u16() * (3.3 / (65536))
temp = 27 - (adc_voltage - 0.706) / 0.001721
print(f"Temperature: {temp:2.1f} °C")

Next, the same for an I2C sensor. I connected the BME280 to GP20 and GP21. There are other options, i.e. GP0 and GP1. The code to see which I2C devices are connected:

import machine
i2c = machine.I2C(0, sda=machine.Pin(20), scl=machine.Pin(21), freq=100000)
print([hex(i) for i in i2c.scan()])

This returns for the BME280: ['0x76'].

We don't want to implement the protocol for a BME280 ourselves, so we use a library that does that. I chose the one from robert-hh on github. Using rshell copy the file bme280_float.py to the Pico with cp bme_280.py /pyboard. Now we try if this worked again with the REPL:

import machine
import bme280_float as bme280
i2c = machine.I2C(0, sda=machine.Pin(20), scl=machine.Pin(21))
bme = bme280.BME280(i2c=i2c)
print(bme.read_compensated_data()[0])

This should return a reasonable temperature value in Celsius as float.

Before adding a webserver to the Pico we need a network connection via WiFi. This is pretty standard and well documented (the tutorial is for the ESP8266, but this works for the Pico, too):

import network
nic = network.WLAN(network.STA_IF)
nic.active(True)
nic.connect('your-ssid', 'your-key')
# check if this worked:
nic.ifconfig()
# for example:
# ('192.168.0.91', '255.255.255.0', '192.168.0.1', '192.168.0.1')

Next is a webserver running on the Pico. There are lots of alternatives to writing our own minimal webserver, so we have to choose from more or less feature complete ones out there. I chose microdot because it feels like Flask/FastAPI. This one has clearly too many features and is quite big for such a small problem, but I like the interface and future possibilities. We only need the microdot.py, so we copy it the same way as for the bme280 module in the rshell: cp src/microdot/microdot.py /pyboard.

Now a simple example in the REPL (connect to the WiFi first!):

from microdot import Microdot
app = Microdot()

@app.route("/")
async def index(request):
    return "Hello, world!"

app.run(debug=True, port=80)

Copy this to the REPL in 3 steps, to get the indention correct for the index function. Then connect from your Webbrowser to http://<ip-address-of-your-pico>/ and you should see a "Hello, world!".

We have all parts needed to start a webserver on the Pico with two temperature sensors pulled by Homeassistant. Save the following code as main.py and copy via rshell to the Pico: cp main.py /pyboard. After reboot the Pico should answer with two temperature values as json on http://<ip-address-of-your-pico>/.

import bme280_float as bme280
import machine
import network
from microdot import Microdot

app = Microdot()

def connect_wifi():
    nic = network.WLAN(network.STA_IF)
    nic.active(True)
    # set your WiFi here:
    nic.connect('your-ssid', 'your-key')

def get_internal_temp():
    adc_voltage = machine.ADC(4).read_u16() * (3.3 / (65536))
    return 27 - (adc_voltage - 0.706) / 0.001721

def get_bme280_temp():
    i2c = machine.I2C(0, sda=machine.Pin(20), scl=machine.Pin(21))
    bme = bme280.BME280(i2c=i2c)
    return bme.read_compensated_data()[0]

@app.route("/")
async def index(request):
    return {
        "internal_temperature": get_internal_temp(),
        "bme280_temperature": get_bme280_temp(),
    }

connect_wifi()
app.run(port=80)

Adding debug=True in the app.run() didn't work for me when running outside of rshell. So be aware that printing may block standalone starting.

And finally the Homeassistant setting to poll from this new sensor. This is added to the configuration.yaml:

rest:
  - scan_interval: 60
    resource: http://192.168.0.91/
    sensor:
      - name: "Pico internal temperature"
        value_template: "{{ value_json.internal_temperature }}"
      - name: "Pico BME280 temperature"
        value_template: "{{ value_json.bme280_temperature }}"

In my Homeassistant dashboard the two sensors are shown like this:

img1

A few days of both sensors shown using InfluxDB/Grafana:

img2

The internal sensor graph is jittering a lot and is clearly a few degrees of. The jittering could be solved by windowing the query in InfluxDB and the temperature error by adjusting the formula. But on the positive side both curves are behaving the same. So if only a temperature delta is needed the internal sensor may be good enough.

The Flux code to get the graph:

from(bucket: "home_assistant")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["entity_id"] == "pico_bme280_temperature" or r["entity_id"] == "pico_internal_temperature")
  |> filter(fn: (r) => r._field == "value")
  |> keep(columns: ["_time","entity_id", "_field", "_value"])

Pico and MicroPython to display GPS data

Thanks a lot to microcontrollerslab.com for a really good starting point by using MicroPython to get GPS data from a NEO-6M using a Raspberry PI Pico.

The things I did differently: I used different Pins on the Pico and I adjusted the code to a more pythonic style.

First the Pins. For I2C I used Pin 20 and Pin 21, the same as in my previous post. And for UART the Pins that are at the bottom of the Pico nearest to the Neo module on my breadboard, which are Pin 16 and Pin 17 for TX/RX.

The code from microcontrollerslab after some refactoring:

import machine
import utime
import ssd1306

def convert2degree(raw_degrees):
    try:
        raw_f = float(raw_degrees)
        first = int(raw_f / 100)
        result = float(first + (raw_f - float(first * 100)) / 60.0)
        return f"{result:.6f}"
    except ValueError:
        return ""

class GPS:
    def __init__(self):
        i2c = machine.I2C(0, sda=machine.Pin(20), scl=machine.Pin(21))
        self.oled = ssd1306.SSD1306_I2C(128, 32, i2c)
        self.gps = machine.UART(
            0, baudrate=9600, tx=machine.Pin(16), rx=machine.Pin(17)
        )

    def display(self, latitude, longitude, satellites, gps_time):
        self.oled.fill(0)
        self.oled.text(f"Lat: {latitude}", 0, 0)
        self.oled.text(f"Lng: {longitude}", 0, 10)
        self.oled.text(f"{satellites} -- {gps_time}", 0, 20)
        self.oled.show()

    def run(self):
        while True:
            parts = str(self.gps.readline()).split(",")

            if parts[0] == "b'$GPGGA" and len(parts) == 15:
                if (
                    parts[1] and parts[2] and parts[3] and parts[4]
                    and parts[5] and parts[6] and parts[7]
                ):
                    latitude = convert2degree(parts[2])
                    if parts[3] == "S":
                        latitude = f"-{latitude}"
                    longitude = convert2degree(parts[4])
                    if parts[5] == "W":
                        longitude = f"-{longitude}"
                    satellites = parts[7]
                    gps_time = f"{parts[1][0:2]}:{parts[1][2:4]}:{parts[1][4:6]}"

                    if latitude and longitude:
                        self.display(latitude, longitude, satellites, gps_time)

            utime.sleep_ms(500)

if __name__ == "__main__":
    g = GPS()
    g.run()

The convert2degree function failed sometimes, so I set latitude or longitude to an empty string when this happens and doesn't display the values on the OLED.

My hardware setup with a Raspberry PI Pico, a NEO-6M module and a 128x32 oled display:

img1

I removed the exact GPS coordinates. At this window I get 8 satellites, which is actually not so bad.