Meshview

This repo gives very easy instructions on how to set it up for Bayme.sh traffic. It took about 10 minutes to get this running on a Macbook pro. Overnight the database grew to 16MB. This software creates a SQLite database.

Schema

CREATE TABLE node (
	id VARCHAR NOT NULL, 
	node_id BIGINT, 
	long_name VARCHAR, 
	short_name VARCHAR, 
	hw_model VARCHAR, 
	firmware VARCHAR, 
	role VARCHAR, 
	last_lat BIGINT, 
	last_long BIGINT, 
	channel VARCHAR, 
	last_update DATETIME, 
	PRIMARY KEY (id), 
	UNIQUE (node_id)
);


CREATE TABLE packet (
	id BIGINT NOT NULL, 
	portnum INTEGER, 
	from_node_id BIGINT, 
	to_node_id BIGINT, 
	payload BLOB, 
	import_time DATETIME, 
	channel VARCHAR, 
	PRIMARY KEY (id)
);

CREATE TABLE packet_seen (
	packet_id BIGINT NOT NULL, 
	node_id BIGINT NOT NULL, 
	rx_time BIGINT NOT NULL, 
	hop_limit INTEGER, 
	hop_start INTEGER, 
	channel VARCHAR, 
	rx_snr FLOAT, 
	rx_rssi INTEGER, 
	topic VARCHAR, 
	import_time DATETIME, 
	PRIMARY KEY (packet_id, node_id, rx_time), 
	FOREIGN KEY(packet_id) REFERENCES packet (id)
);

CREATE TABLE traceroute (
	id INTEGER NOT NULL, 
	packet_id BIGINT, 
	gateway_node_id BIGINT, 
	done BOOLEAN, 
	route BLOB, 
	import_time DATETIME, 
	PRIMARY KEY (id), 
	FOREIGN KEY(packet_id) REFERENCES packet (id)
);

Interesting Queries

(Thanks to nullrouten)

Node location

All nodes with GPS position information

    SELECT 
        node_id,
        last_lat / 1e7 AS lat,
        last_long / 1e7 AS lon,
        channel
    FROM node
    WHERE last_lat IS NOT NULL 
      AND last_long IS NOT NULL
    ORDER BY node_id;

SNR

Received Signal to Noise Ratio of each packet in 1dB bins

    SELECT a.channel, snr_bucket, COUNT(*) as num_seen
    FROM (
    SELECT 
        n.channel,
        -- floor to 1 dB bins, accounting for negatives
        CASE 
            WHEN rx_snr >= 0 THEN CAST(rx_snr AS INT)
            ELSE CAST(rx_snr AS INT) - (rx_snr < CAST(rx_snr AS INT))
        END AS snr_bucket
    FROM packet_seen ps
    JOIN packet p ON ps.packet_id = p.id
    JOIN node n ON ps.node_id = n.node_id
    WHERE rx_snr IS NOT NULL
    ) a
    GROUP BY a.channel, snr_bucket
    ORDER BY a.channel, snr_bucket;