Luftdatenpumpe: Adjusting location labels with a focus on sensor id instead of station id

Hi,

I was just going to take a look at some sensors and noticed that the sensor IDs do not match those from the sensor.community.
Which of course makes searching a bit more difficult, or you can’t just link it if you know your sensor ID.
Is there a reason for this?

1 Like

Dear Oliver,

I think you are referring to Luft: LDI » luftdaten.info map for Frankfurt/Main (DE)? The identifiers you’ve cited from there, are station identifiers, and not sensor identifiers.

By running that command, you can inquire the metadata from those stations, which also yield information about the attached sensors, including their identifiers.

luftdatenpumpe stations --network=ldi --station=4155,63814,65721 --reverse-geocode

On the other hand, you can also easily filter by sensor identifier, using, for example:

luftdatenpumpe stations --network=ldi --sensor=8228,76777

With kind regards,
Andreas.

JSON output for first command
[
  {
    "station_id": 4155,
    "name": "Jacobystra\u00dfe, Ostend, Frankfurt, Hessen, DE",
    "position": {
      "latitude": 50.118,
      "longitude": 8.714,
      "altitude": 117.2,
      "country": "DE",
      "exact_location": 0,
      "indoor": 0,
      "geohash": "u0yjn79qdbm7"
    },
    "location": {
      "place_id": "9213464",
      "licence": "Data \u00a9 OpenStreetMap contributors, ODbL 1.0. https://osm.org/copyright",
      "osm_type": "way",
      "osm_id": "84623850",
      "lat": "50.11822765",
      "lon": "8.71417606698424",
      "display_name": "2, Jacobystra\u00dfe, Ostend, Frankfurt, Regierungsbezirk Darmstadt, Hessen, 60385, Deutschland",
      "boundingbox": [
        "50.1181274",
        "50.1183649",
        "8.7140236",
        "8.7143285"
      ],
      "address": {
        "country_code": "DE",
        "country": "Deutschland",
        "state": "Hessen",
        "state_district": "Regierungsbezirk Darmstadt",
        "county": "Frankfurt",
        "postcode": "60385",
        "city": "Frankfurt",
        "city_district": "Ostend",
        "suburb": "Ostend",
        "road": "Jacobystra\u00dfe",
        "house_number": "2"
      }
    },
    "sensors": [
      {
        "sensor_id": 8229,
        "sensor_type_name": "BME280",
        "sensor_type_id": 17
      },
      {
        "sensor_id": 8228,
        "sensor_type_name": "SDS011",
        "sensor_type_id": 14
      }
    ]
  },
  {
    "station_id": 63814,
    "name": "Jacobystra\u00dfe, Ostend, Frankfurt, Hessen, DE",
    "position": {
      "latitude": 50.118,
      "longitude": 8.714,
      "altitude": 117.4,
      "country": "DE",
      "exact_location": 0,
      "indoor": 0,
      "geohash": "u0yjn79qdbm7"
    },
    "location": {
      "place_id": "9213464",
      "licence": "Data \u00a9 OpenStreetMap contributors, ODbL 1.0. https://osm.org/copyright",
      "osm_type": "way",
      "osm_id": "84623850",
      "lat": "50.11822765",
      "lon": "8.71417606698424",
      "display_name": "2, Jacobystra\u00dfe, Ostend, Frankfurt, Regierungsbezirk Darmstadt, Hessen, 60385, Deutschland",
      "boundingbox": [
        "50.1181274",
        "50.1183649",
        "8.7140236",
        "8.7143285"
      ],
      "address": {
        "country_code": "DE",
        "country": "Deutschland",
        "state": "Hessen",
        "state_district": "Regierungsbezirk Darmstadt",
        "county": "Frankfurt",
        "postcode": "60385",
        "city": "Frankfurt",
        "city_district": "Ostend",
        "suburb": "Ostend",
        "road": "Jacobystra\u00dfe",
        "house_number": "2"
      }
    },
    "sensors": [
      {
        "sensor_id": 75231,
        "sensor_type_name": "BME280",
        "sensor_type_id": 17
      },
      {
        "sensor_id": 75230,
        "sensor_type_name": "SDS011",
        "sensor_type_id": 14
      }
    ]
  },
  {
    "station_id": 65721,
    "name": "Jacobystra\u00dfe, Ostend, Frankfurt, Hessen, DE",
    "position": {
      "latitude": 50.118,
      "longitude": 8.714,
      "altitude": 117.3,
      "country": "DE",
      "exact_location": 0,
      "indoor": 0,
      "geohash": "u0yjn79qdbm7"
    },
    "location": {
      "place_id": "9213464",
      "licence": "Data \u00a9 OpenStreetMap contributors, ODbL 1.0. https://osm.org/copyright",
      "osm_type": "way",
      "osm_id": "84623850",
      "lat": "50.11822765",
      "lon": "8.71417606698424",
      "display_name": "2, Jacobystra\u00dfe, Ostend, Frankfurt, Regierungsbezirk Darmstadt, Hessen, 60385, Deutschland",
      "boundingbox": [
        "50.1181274",
        "50.1183649",
        "8.7140236",
        "8.7143285"
      ],
      "address": {
        "country_code": "DE",
        "country": "Deutschland",
        "state": "Hessen",
        "state_district": "Regierungsbezirk Darmstadt",
        "county": "Frankfurt",
        "postcode": "60385",
        "city": "Frankfurt",
        "city_district": "Ostend",
        "suburb": "Ostend",
        "road": "Jacobystra\u00dfe",
        "house_number": "2"
      }
    },
    "sensors": [
      {
        "sensor_id": 76778,
        "sensor_type_name": "BME280",
        "sensor_type_id": 17
      },
      {
        "sensor_id": 76777,
        "sensor_type_name": "SDS011",
        "sensor_type_id": 14
      }
    ]
  }
]
JSON output for second command
[
  {
    "station_id": 4155,
    "position": {
      "latitude": 50.118,
      "longitude": 8.714,
      "altitude": 117.2,
      "country": "DE",
      "exact_location": 0,
      "indoor": 0,
      "geohash": "u0yjn79qdbm7"
    },
    "sensors": [
      {
        "sensor_id": 8228,
        "sensor_type_name": "SDS011",
        "sensor_type_id": 14
      }
    ]
  },
  {
    "station_id": 65721,
    "position": {
      "latitude": 50.118,
      "longitude": 8.714,
      "altitude": 117.3,
      "country": "DE",
      "exact_location": 0,
      "indoor": 0,
      "geohash": "u0yjn79qdbm7"
    },
    "sensors": [
      {
        "sensor_id": 76777,
        "sensor_type_name": "SDS011",
        "sensor_type_id": 14
      }
    ]
  }
]

1. Grafana: Adjusting the table view

Adding another column to the table view at Luft: LDI » luftdaten.info map for Frankfurt/Main (DE) is easy. Just add station_id to the projection list at the SQL query at luftdatenpumpe/dashboard-map.json at 0.21.1 · earthobservations/luftdatenpumpe · GitHub, like

SELECT osm_country_code, state_and_city, name_and_id, sensor_type_name, sensor_id 
FROM ldi_network 
WHERE station_id IN ($ldi_station_id) AND sensor_type_name IN ($ldi_station_sensortype) 
ORDER BY osm_country_code, state_and_city, name_and_id, sensor_type_name;

2. Grafana: Adjusting the map view

On the documentation about Luftdaten-Viewer Grafana, you will find this command to create a mapping file consumed by the Map Panel, in order to display the overlays on circles.

luftdatenpumpe stations --network=ldi --source=postgresql://luftdatenpumpe@localhost/weatherbase \
  --target=json.flex+stream://sys.stdout \
  --target-fieldmap='key=station_id\|str,name=road_and_name_and_id'

The database column road_and_name_and_id in database view ldi_network will get used to format the overlay string in Grafana.

image

road_and_name_and_id is defined at luftdatenpumpe.target.rdbms.create_views, along with other alias fields. On this spot, it will be possible to add more display formats.

With kind regards,
Andreas.


P.S.: Just to get you an idea how this looks like on the database side, when everything is in place, and how you can start to explore the data in order to customize the system to your needs.

weatherbase=> SELECT DISTINCT(road_and_name_and_id) FROM ldi_network WHERE road_and_name_and_id LIKE '%Frankfurt%' LIMIT 5;
          road_and_name_and_id
----------------------------------------
 Adickesallee, Frankfurt (#16544)
 Am Auerborn, Frankfurt (#65922)
 Am Musikheim, Frankfurt (Oder) (#4363)
 AntoninusstraĂźe, Frankfurt (#8311)
 AtzelbergstraĂźe, Frankfurt (#10108)
(5 rows)
weatherbase=> SELECT road_and_name_and_id, station_id FROM ldi_network WHERE road_and_name_and_id LIKE '%Frankfurt%' LIMIT 5;
           road_and_name_and_id            | station_id
-------------------------------------------+------------
 Am Musikheim, Frankfurt (Oder) (#4363)    |       4363
 BuschmĂĽhlenweg, Frankfurt (Oder) (#58456) |      58456
 BuschmĂĽhlenweg, Frankfurt (Oder) (#58456) |      58456
 BuschmĂĽhlenweg, Frankfurt (Oder) (#58474) |      58474
 BuschmĂĽhlenweg, Frankfurt (Oder) (#58474) |      58474
(5 rows)
1 Like

Vielen Dank Andreas.
I did not doubt that everything is possible because the data is available.
As soon as I have the local installation of the Luftdatenpumpe running, I will take a look and query accordingly. :slight_smile:

1 Like

I really wish you luck in getting the installation up and running :crossed_fingers:. As you can see, there are quite some bits and pieces involved, where it is sometimes not easy to make the parts work together.

I’ve just spotted a flaw within my last query demonstration. Of course, I intended to add the sensor_id you have been asking about, and also switched to the intended »Frankfurt am Main« in Hesse now ;].

weatherbase=> SELECT road_and_name_and_id, sensor_id FROM ldi_network
weatherbase-> WHERE road_and_name_and_id LIKE '%Frankfurt%' AND road_and_name_and_id NOT LIKE '%Oder%' LIMIT 5;
        road_and_name_and_id         | sensor_id
-------------------------------------+-----------
 Adickesallee, Frankfurt (#16544)    |     29612
 Am Auerborn, Frankfurt (#65922)     |     76987
 Am Auerborn, Frankfurt (#65922)     |     76986
 Am Oberforsthaus, Frankfurt (#1904) |      3778
 AntoninusstraĂźe, Frankfurt (#8311)  |     16404
(5 rows)

This one alternatively selects the stations by using the station IDs you have been presenting in your original post.

weatherbase=> SELECT road_and_name_and_id, sensor_id FROM ldi_network
weatherbase-> WHERE station_id IN (4155, 63814, 65721);
       road_and_name_and_id       | sensor_id
----------------------------------+-----------
 JacobystraĂźe, Frankfurt (#4155)  |      8229
 JacobystraĂźe, Frankfurt (#4155)  |      8228
 JacobystraĂźe, Frankfurt (#63814) |     75231
 JacobystraĂźe, Frankfurt (#63814) |     75230
 JacobystraĂźe, Frankfurt (#65721) |     76778
 JacobystraĂźe, Frankfurt (#65721) |     76777
(6 rows)
1 Like

Hallo Andreas,

It now looks like I have the basic system up and running. :slight_smile:

As you have already written, it is possible to retrieve the data from specified sensor_id’s. I could also provide a file containing the sensor_id’s.

Would this query be correct:

luftdatenpumpe stations \
  --network=ldi --sensor=8228,76777 \
  --reverse-geocode \
  --target=postgresql://luftdatenpumpe@localhost/weatherbase \
  --progress

I could also provide a list in a file with the sensor_id’s. Is it possible to include this list in the query so I don’t have to type all sensor_id’s in the query?

What exactly do I have to do to query the data regularly and write it to the influxdb so that I always have all the data?

Kind regards,
Oliver

1 Like

Excellent! :tada: :mirror_ball:

I think it is correct. You can always invoke the command without the --target option, to see its output written to STDOUT. In this manner, you can verify if the incantation works as intended.

luftdatenpumpe stations --network=ldi --reverse-geocode --sensor=8228,76777

Luftdatenpumpe currently can’t do that directly. For a workaround on Unix systems, see Read list of station or sensor identifiers from file · Issue #46 · earthobservations/luftdatenpumpe · GitHub.

Hi Andreas,

I have now reached the point where I can do some view adjustment.

I would like to add the following data to the pop-up in the map:
District (Sachsenhausen SĂĽd), Postcode (PLZ), City (#SensorID)
P2.5, P10
And the possibility to click on the sensor to open the trend display.
I don’t understand how to adjust this in the code.

1 Like

Hi Oliver,

That effectively means you managed to install the system completely? Congratulations! Thank you also for reporting about your progress on behalf of the other topics. I will pick that up to improve the documentation at the spots where you have been struggling a bit.

Would choosing those fields be correct for your needs?

weatherbase=> SELECT osm_state_district, osm_postcode, osm_city, sensor_id FROM ldi_network;
     osm_state_district     | osm_postcode | osm_city  | sensor_id
----------------------------+--------------+-----------+-----------
 Regierungsbezirk Darmstadt | 60385        | Frankfurt |     76777
 Regierungsbezirk Darmstadt | 60385        | Frankfurt |      8228
(2 rows)

I will submit a corresponding patch.

The corresponding feature of Panodata Map Panel would be Clickthrough links. Maybe you can figure it out from the bits of the documentation there. Otherwise, please let me know.

With kind regards,
Andreas.

Add OSM-based synthetic geolocation field for micro-regions by amotl · Pull Request #52 · earthobservations/luftdatenpumpe · GitHub has a corresponding patch. Let me know if you need any adjustments.

luftdatenpumpe stations \
  --network=ldi \
  --source=postgresql://luftdatenpumpe@localhost/weatherbase \
  --target=json.flex+stream://sys.stdout \
  --target-fieldmap='key=station_id|str,name=district_postcode_city_sensorid'
[
  {
    "key": "66115",
    "name": "Regierungsbezirk Darmstadt, 60385, Frankfurt (#76777)"
  },
  {
    "key": "66117",
    "name": "Regierungsbezirk Darmstadt, 60385, Frankfurt (#8228)"
  }
]

At Add OSM-based synthetic geolocation field for micro-regions by amotl · Pull Request #52 · earthobservations/luftdatenpumpe · GitHub, you said:

Excellent. With Adjust OSM-based synthetic geolocation fields for micro-regions by amotl · Pull Request #56 · earthobservations/luftdatenpumpe · GitHub, I’ve added the variant suburb_postcode_city_sensorid, and also adjusted the formatting as you suggested.

You are aiming to add a linebreak within the unit label? I don’t think that’s possible. The corresponding Grafana widget looks like this and everything what is written into the unit label text fields is used verbatim, and I don’t think there is a chance to add custom formatting to this part.