jon/chirpstack-tutorial
1
0
Fork 0
Code Issues 1 Pull Requests Packages Projects Releases Wiki Activity
Page: Dev Notes
Pages
Dev Notes Home
Clone
29
Dev Notes
Jon Roeber edited this page 2024-01-22 03:55:17 +00:00
Table of Contents

20240121

Got TBeam working with GPS. Downlink from gateway is not working; had to authenticate using ABP.

Need to visualize it in QGIS now. Create a view, grant view permission to previously-created role.

First create a tbeam view:

CREATE OR REPLACE VIEW tbeam AS
WITH data(id, dev_eui, time, alt, lat, lon, rssi) AS
  (SELECT
    deduplication_id,
    dev_eui,
    time,
    CAST (object -> 'gpsLocation' -> '3' ->> 'altitude' AS FLOAT),
    CAST (object -> 'gpsLocation' -> '3' ->> 'latitude' AS FLOAT),
    CAST (object -> 'gpsLocation' -> '3' ->> 'longitude' AS FLOAT),
    CAST (rx_info -> 0 ->> 'rssi' AS INTEGER)
  FROM event_up)
SELECT
  id,
  dev_eui,
  time,
  ST_SetSRID(ST_MakePoint(lon, lat, alt), 4326) AS location,
  rssi
FROM data
WHERE dev_eui = 'fffe2002c855b594';
GRANT SELECT ON tbeam TO qgis;

Reception was dramatically better when the hotspot was on the roof. Graduated symbology worked fine in QGIS.

Next steps:

Elevation data tutorials/resources:

3D maps need to be in a "projected" geography like Mercator, not spheroid like WGS-84.

OpenGML model resources (for rendering buildings):

Other QGIS software to look into:

Huge list of free GIS data sources: https://freegisdata.rtwilson.com/

Turn old maps into 3D, web-viewable models: https://medium.com/@tjukanov/qgis-magic-old-raster-maps-to-3d-ddd0f550e0e8

20240117

Example SQL query to create a qgis_friendly view that converts Cayenne GPS location into PostGIS point, includes unique ID, RSSI, time, and gatewayId, and filters on gatewayId:

CREATE OR REPLACE VIEW qgis_friendly AS
WITH data(id, gatewayId, time, alt, lat, lon, rssi) AS
  (SELECT
    deduplication_id,
    rx_info -> 0 ->> 'gatewayId',
    time,
    CAST (object -> 'gpsLocation' -> '1' ->> 'altitude' AS FLOAT),
    CAST (object -> 'gpsLocation' -> '1' ->> 'latitude' AS FLOAT),
    CAST (object -> 'gpsLocation' -> '1' ->> 'longitude' AS FLOAT),
    CAST (rx_info -> 0 ->> 'rssi' AS INTEGER)
  FROM event_up)
SELECT
  id,
  gatewayId,
  time,
  ST_SetSRID(ST_MakePoint(lon, lat, alt), 4326) AS location,
  rssi
FROM data
WHERE gatewayId = '8d269c95ddfe92eb';

CREATE EXTENSION postgis; must be run on each database, not just the PostgreSQL instance.

Create a read-only Postgres user, ref https://stackoverflow.com/a/762649:

CREATE USER qgis WITH PASSWORD 'replace_me';
GRANT CONNECT ON DATABASE chirpstack_integration TO qgis;
GRANT USAGE ON SCHEMA public TO qgis;
GRANT SELECT ON qgis_friendly TO qgis;

Edit /etc/postgresql/14/main/postgresql.conf to have:

listen_addresses = '*'

Edit /etc/postgresql/14/main/pg_hba.conf to have:

# (custom) IPv4 internet connections:
host    all             all             0.0.0.0/0               scram-sha-256

systemctl restart postgresql

Allow PG internet access: ufw allow postgresql (not secure, need to add certificate, fine for testing)

Connect from local machine:

sudo apt install postgresql-client
psql -h chirpstack.roeber.dev -U qgis -d chirpstack_integration

Having an issue with getting the point to show up in QGIS. The geometry isn't showing correctly, something about a CRS not defined? Need to look into it more. Probably also need to get more of a variety of fake data points instead of just one point. For some reason the feature count is 0 when looking at the dataset. Am I missing an ID field? Probably something to do with the view's fields.

Got a point to show on the map, where I expected it! Adding an id column got the feature to show up.

Next steps:

  • Show more information about the feature on the map, color gradient is called "graduated symbology"
  • Get the real GPS sensor working or generate random data in an area
  • Limit the view to a certain time range or number of points for performance

20240116

https://github.com/CrunchyData/pg_featureserv - a lightweight alternative to Geoserver for serving WFS3 (found from https://gis.stackexchange.com/a/379506)

QGIS has very rich PostGIS integration.

Could periodically generate gpkg files: https://gis.stackexchange.com/a/365600

This guy in 2013 said to use PostGIS instead of Geoserver: https://gis.stackexchange.com/a/57693

A few WFS caches from 2011: https://gis.stackexchange.com/a/13972 (investigate WFS caching more)

Article on enforcing TLS in PostgreSQL: https://www.percona.com/blog/enabling-and-enforcing-ssl-tls-for-postgresql-connections/

Docs on pg_hba.conf: https://www.postgresql.org/docs/current/auth-pg-hba-conf.html - can force clients to use TLS and also optionally use a client certificate

Need to get familiar with PostgreSQL roles and accounts. That will be the primary way to determine if someone has "read meters" "read/modify/add meter metadata" or other tasks. Then create accounts with passwords, or use some other auth system (e.g. LDAP or RADIUS). OAuth2 is basically not supported (there is a Linux-only PAM module abandoned many years ago, https://www.janua.fr/postgres-oauth2-authentication/).

Geoserver does support OAuth 2.0, but it looks like maybe only a few providers. Maybe the OIDC 2.0 one works with any provider? https://docs.geoserver.org/latest/en/user/community/oauth2/installing.html

Probably best to start with PostGIS-internal role-based access control (RBAC), directly connected to QGIS.

QGIS can create joins and views directly, but best to have it done elsewhere (either in PostGIS directly from ChirpStack event table or as a microservice that receives events and puts them into a special QGIS-friendly table).

So the choice right now is between creating a microservice or creating a monstrous PostGIS command to create a view. The latter requires parsing JSON, which has a tutorial here: https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-json/

To create a view: https://www.guru99.com/postgresql-view.html

It might be best right now to try to create a view and then connect PostGIS that way. No need for a microservice right now (though later on it's likely there will need to be some admin control panel app).

20240114

PostGIS setup, PostgreSQL commands, ChirpStack integration

apt install postgis

sudo -u postgres psql (just psql -U postgres won't work)

CREATE EXTENSION postgis;

To select from user table:

select * from public."user"; (select * from user doesn't work; shows the currently-logged in psql user instead?)

CREATE DATABASE chirpstack_integration;

\c chirpstack_integration

`CREATE ROLE chirpstack_integration PASSWORD '6XlFKCb2ygWCt'; (need to add WITH LOGIN next time)

# GRANT ALL PRIVILEGES ON chirpstack_integration TO chirpstack_integration; (doesn't work the way you think, see https://serverfault.com/a/198018)

ALTER DATABASE chirpstack_integration OWNER TO chirpstack_integration;

list roles:

SELECT * FROM pg_roles;

roles and users are different; roles cannot login by default and are not listed in pg_user (but all users are listed in pg_role)

ALTER ROLE chirpstack_integration WITH LOGIN; (since I didn't do login in the first place...best to just make a user)

schema for integration: https://github.com/chirpstack/chirpstack/blob/master/chirpstack/src/integration/postgresql/migrations/00000000000000_initial/up.sql

q about payload decoding https://forum.chirpstack.io/t/payload-decoding-in-postgres-integration/16356

the two interesting tables are event_up and event_integration

Next steps:

  • Set up a device to send messages
    • Bonus points for setting up GPS sensor
  • Create a codec (Cayenne or JavaScript)
  • Observe entries in PostgreSQL chirpstack_integration database, event_up table, object column
  • Research how QGIS expects PostGIS data to look
  • Create a PostGIS view from the event_up table that QGIS will like (if possible)
    • If not possible, research creating a custom HTTP integration (in Go) that will receive data and put it into correct format in PostGIS for QGIS

potentially interesting article: https://www.line-45.com/post/using-qgis-postgis-dynamic-duo