LoRaWAN-Signal-Mapping/server/db_template.sql

-- See for data provided by TTN https://www.thethingsnetwork.org/docs/applications/mqtt/api.html
CREATE TABLE IF NOT EXISTS readings (
	id INTEGER PRIMARY KEY, -- Random unique integer
	latitude FLOAT NOT NULL, -- Latitude component of GPS co-ordinates of reading
	longitude FLOAT NOT NULL, -- Longitude component of GPS co-ordinates of reading,
	data_rate TEXT, -- The id of the data rate code in the data_rates table that describes the data rate at which the message was transmitted
	code_rate TEXT -- The coding rate at which the message was transmitted. FUTURE: This may need to be an INTEGER field - not sure
	-- bit_rate INTEGER, -- The bit rate at which the message was transmitted -- We didn't actually recieve this - disparity between API docs & actual message
);
CREATE TABLE IF NOT EXISTS rssis (
	id INTEGER PRIMARY KEY, -- Random unique int
	reading_id INTEGER, -- The id of the object in the readings table that this rssi measurement belongs to
	gateway_id TEXT, -- Gateway id that the RSSI was from
	rssi FLOAT, -- The RSSI value itself
	snr FLOAT, -- The signal-to-noise ratio
	channel INTEGER -- The channel that the RSSI was received over. We might be able to use this to detect single-channel gateways
);
CREATE TABLE IF NOT EXISTS gateways (
	id TEXT PRIMARY KEY, -- The gateway's name
	latitude FLOAT, -- Latitude component of the claimed GPS co-ordinates of the gateway
	longitude FLOAT, -- Longitude component of the claimed GPS co-ordinates of the gateway
	altitude FLOAT -- Claimed alitude of the gateway
);


-- UPDATE 1 --

--------------

-- UPDATE 2 --

--------------

-- ....