LDI data plane v2

Entwicklung / Development Maschinenraum / Machine room
1

Synopsis

We are bringing the outcome of Erneuerung der Luftdatenpumpe into production.

Machinery

Data mungler.

Dashboard updates

Data source updates

Timeseries data in InfluxDB

  • Database :
    ldi_v2
  • Measurement :
    ldi_readings

Metadata in PostgreSQL

  • Database :
    weatherbase
  • Table/View :
    ldi_network

Remark: The names of the data sources in Grafana will be identical to those of the database names in InfluxDB.

1 Like
2

Screenshots

Does it render?

Worldmap and address

image
image.png728×575 86.1 KB

Map display with station list. Filter by different synthesized address components and sensor type.

Map overlay

image
Extended yet compact synthesized address on map overlay, includes station id.

3

Database schema

Does it print?

Timeseries data in InfluxDB

> use ldi_v2
Using database ldi_v2

> show tag keys
name: ldi_readings
tagKey
------
geohash
sensor_id
station_id

> show field keys
name: ldi_readings
fieldKey             fieldType
--------             ---------
P0                   float
P1                   float
P2                   float
durP1                float
durP2                float
humidity             float
max_micro            float
min_micro            float
pressure             float
pressure_at_sealevel float
ratioP1              float
ratioP2              float
samples              float
temperature          float

Metadata in PostgreSQL

While you can join individual parts yourself, you should just be using the composite view ldi_network, see schema below.

weatherbase=# \d

 Schema |            Name             |   Type   |  Owner
--------+-----------------------------+----------+----------
 public | ldi_network                 | view     | hiveeyes
 public | ldi_osmdata                 | table    | hiveeyes
 public | ldi_osmdata_station_id_seq  | sequence | hiveeyes
 public | ldi_sensors                 | table    | hiveeyes
 public | ldi_sensors_sensor_id_seq   | sequence | hiveeyes
 public | ldi_stations                | table    | hiveeyes
 public | ldi_stations_station_id_seq | sequence | hiveeyes

Let us point out the synthetic composite fields you might find handy when working with station information metadata, especially when displaying location names and addresses. Please let us know when you need more fields or would like to have different values or formatting styles of address components.

  • name_and_id
  • state_and_city
  • state_and_country
  • country_and_countrycode
  • city_and_state_and_country
weatherbase=# \d ldi_network

           Column           |       Type       | Modifiers
----------------------------+------------------+-----------
 station_id                 | integer          |
 name                       | text             |
 latitude                   | double precision |
 longitude                  | double precision |
 altitude                   | double precision |
 country                    | text             |
 geohash                    | text             |
 sensor_id                  | integer          |
 sensor_type                | text             |
 state_and_city             | text             |
 name_and_id                | text             |
 country_and_countrycode    | text             |
 state_and_country          | text             |
 city_and_state_and_country | text             |
 osm_address_more           | text             |
 osm_road                   | text             |
 osm_type                   | text             |
 osm_lat                    | text             |
 osm_licence                | text             |
 osm_display_name           | text             |
 osm_county                 | text             |
 osm_state                  | text             |
 osm_house_number           | text             |
 osm_id                     | text             |
 osm_city_district          | text             |
 osm_neighbourhood          | text             |
 osm_lon                    | text             |
 osm_city                   | text             |
 osm_place_id               | text             |
 osm_country                | text             |
 osm_postcode               | text             |
 osm_country_code           | text             |
 osm_suburb                 | text             |
 osm_state_district         | text             |
 osm_town                   | text             |
 osm_address29              | text             |
 osm_pedestrian             | text             |
 osm_village                | text             |
 osm_administrative         | text             |
 osm_residential            | text             |
 osm_industrial             | text             |
 osm_footway                | text             |
 osm_continent              | text             |
 osm_region                 | text             |
 osm_path                   | text             |
 osm_cycleway               | text             |
 osm_common                 | text             |
 osm_post_box               | text             |
 osm_address26              | text             |
1 Like
4

Timeseries data examples

> SELECT * FROM ldi_readings WHERE station_id=1071 ORDER BY time desc LIMIT 3;

FIXME

Metadata examples

SELECT * FROM ldi_network WHERE station_id=1071;
Field Value
name Gerichtstraße, Gesundbrunnen, Mitte, Berlin, DE
name_and_id Gerichtstraße, Gesundbrunnen, Mitte, Berlin, DE (#1071)
state_and_city Berlin » Berlin
state_and_country Berlin, Deutschland (DE)
country_and_countrycode Deutschland (DE)
city_and_state_and_country Berlin, Berlin, Deutschland (DE)
5

GIS features on metadata

After preparing for this by Adding PostGIS the other day, we recently added LDI GIS capabilities through PostGIS.

Luftdatenpumpe [now] uses a PostGIS POINT to store the geoposition of LDI stations.

Basic example

-- Find specified number of nearest stations through sorting by distance,
-- display name and textual representation of coordinates.
SELECT name, ST_AsText(geopoint) AS geopoint
FROM ldi_stations
ORDER BY geopoint <-> 'POINT(9.18001 48.77844)'
LIMIT 3;

Output

name geopoint
Turmstraße, Stuttgart-Mitte, Stuttgart, Baden-Württemberg, DE POINT(9.176 48.776)
Kriegsbergstraße, Stuttgart-Mitte, Stuttgart, Baden-Württemberg, DE POINT(9.178 48.784)
Gaisburgstraße, Stuttgart-Mitte, Stuttgart, Baden-Württemberg, DE POINT(9.188 48.776)

(3 rows)

More examples

Please enjoy LDI GIS capabilities through PostGIS.

1 Like