3. Luftdaten-Viewer Databases#

3.1. Introduction#

This section of the documentation outlines how to provision the PostGIS and InfluxDB databases. It will assume all services are properly installed and configured on your system, and otherwise will give you instructions how to start the corresponding services in sandbox mode.

3.1.1. Prerequisites#

When running in sandbox mode, those commands will start the services required to follow this tutorial. It is InfluxDB, PostGIS, and Redis:

make influxdb-start
make postgis-start
make redis-start

When running in production mode, you may need to configure your services to provide convenient authentication. On this matter, please have a look at Notes about PostgreSQL authentication.

3.2. PostGIS#

3.2.1. Create and provision PostGIS database#

Connect to PostGIS:

psql postgres://postgres@localhost:5432

When aiming to connect to PostGIS on a classic Linux host, where PostGIS is installed as a system service, those commands might work better:

su - postgres
psql

Create database:

CREATE DATABASE weatherbase;
\connect weatherbase;

Enable PostGIS extension:

CREATE EXTENSION postgis;

Create users:

CREATE ROLE luftdatenpumpe WITH LOGIN;
CREATE ROLE grafana WITH LOGIN PASSWORD 'readonly';
\q

3.2.2. Import data#

Pre-flight checks:

psql postgres://luftdatenpumpe@localhost:5432/weatherbase

Run luftdatenpumpe for the first time to manifest the database schema.

luftdatenpumpe stations \
    --network=ldi --station="49,1033" --reverse-geocode \
    --target=postgresql://luftdatenpumpe@localhost/weatherbase --progress

Create database view and grant permissions to “grafana” user.

luftdatenpumpe database --network=ldi \
    --target=postgresql://luftdatenpumpe@localhost/weatherbase \
    --create-view --grant-user=grafana

Note

These steps will have to be performed in order as the last --create-view step will only work after data in the tables has been materialized.

3.2.3. Sanity checks#

Let’s have a look if everything worked.

3.2.3.1. Database schema#

As visible by an administrator.

psql -U luftdatenpumpe -h localhost -d weatherbase --command '\dtv ldi_*'

               List of relations
 Schema |     Name     | Type  |     Owner
--------+--------------+-------+----------------
 public | ldi_network  | view  | luftdatenpumpe
 public | ldi_osmdata  | table | luftdatenpumpe
 public | ldi_sensors  | table | luftdatenpumpe
 public | ldi_stations | table | luftdatenpumpe
(4 rows)

3.2.3.2. Data#

  • Query the database view ldi_network here.

  • Use read-only account pretending to be Grafana.

psql \
    --username=grafana --host=localhost \
    --dbname=weatherbase --command='SELECT COUNT(*) FROM ldi_network;'

 count
-------
  1391

3.3. InfluxDB#

3.3.1. Create and provision InfluxDB database#

luftdatenpumpe readings --network=ldi --station="49,1033" \
    --target=influxdb://luftdatenpumpe@localhost/luftdaten_info

3.3.2. Sanity checks#

Let’s have a look if everything worked.

3.3.2.1. Database schema#

influx \
    -host localhost -username luftdatenpumpe \
    -database luftdaten_info \
    -execute 'SHOW FIELD KEYS; SHOW TAG KEYS;'

fieldKey    fieldType
--------    ---------
P1          float
P2          float
humidity    float
temperature float

tagKey
------
geohash
sensor_id
station_id

3.3.2.2. Database content#

influx \
    -host localhost -username luftdatenpumpe \
    -database luftdaten_info \
    -execute 'SHOW TAG VALUES WITH KEY = station_id;'

key        value
---        -----
station_id 1071
station_id 28

influx \
    -host localhost -username luftdatenpumpe \
    -database luftdaten_info \
    -execute 'SELECT COUNT(*) FROM ldi_readings;'

time count_P1 count_P2 count_humidity count_temperature
---- -------- -------- -------------- -----------------
0    4        4        4              4