Storing my data

TTN Network and Services
1

Hi All,

Been busy but found some time to hop on the new backend. Got the thing running with ttnctl and get my data with a mqtt client (mqttlens and Node-red). Am wondering whats the best / easy way to store my data in a database. First thing i have to figure out is how to convert my base64 data with a easy function in node-red (i keep getting the whole message instead of the data in the payload. Any help with that is welcome;).

Next to store it in the cloud or mysql db on my rpi. For the current project i would like to geohash my data and use it in a map solution. I see there are a couple of solutions out there, but not sure which would be the least complicated setup (very limited programming skills).

Thanks in advance for some tips and tricks

Ewoud

2

First, you can use the “payload functions” in the dashboard to convert the binary data from your nodes to JSON. You can use the node-red-contrib-ttn package to easily get your data from TTN into Node-RED.

To store your data in a database, Node-RED is probably not the easiest option. We are experimenting with a collector feature (implemented in Go) that subscribes to MQTT and stores data in InfluxDB. The basic idea of any integration is roughly the same. You have a long-running process that on one side subscribes to MQTT, and on the other side connects with a database or an IoT platform. When uplink messages arrive, you just insert them in the database or forward them to the IoT platform.

I think the easiest option is to use our Node.JS client to retrieve the data, maybe do some simple processing and then store it in a database with geo support (I personally really like RethinkDB).

Good luck!

2 Likes
3

The node-red-contrib-ttn doesn’t work, at least not for the staging backend. The data is delivered slightlty different. However in node-red you can easily subscribe with mqtt, pass it through the json converter and process the data. My node-red flow looks like this and works perfectly:

1 Like
4

Can you be a bit more precise on how exactly you data is delivered differently?

5

Hi Romeo, skip the “slightly different data” but keep the “doesn’t work”. At least for me.
I’m getting this error when I try to use it:

msg : error
TypeError: Super expression must either be null or a function, not object

Because the standard node-red tools worked fine, I didn’t dig into it further.

1 Like
6

Hi joris,

Could you paste the node info on your extract function?

Thanks

Ewoud

7

this is the function to create the INSERT query for mysql:

var mqtt_topic = msg.topic;
var payload = JSON.stringify(msg.payload).replace(/[\\$'"]/g, "\\$&");
var metadata = msg.payload.metadata.shift();
msg.topic = "INSERT INTO ttn SET " +
    "timestamp = NOW()," + 
    "json_payload='" + payload + "'," + 
    "payload = '" + msg.payload.payload + "'," +
    "port = '" + msg.payload.port + "'," +
    "counter = '" + msg.payload.counter + "'," +
    "dev_eui = '" + msg.payload.dev_eui + "'," +
    "frequency = '" + metadata.frequency + "'," +
    "datarate = '" + metadata.datarate + "'," +
    "codingrate = '" + metadata.codingrate + "'," +
    "gateway_timestamp = '" + metadata.gateway_timestamp + "'," +
    "gateway_time = '" + metadata.gateway_time + "'," +
    "channel = '" + metadata.channel + "'," +
    "server_time = '" + metadata.server_time + "'," +
    "rssi = '" + metadata.rssi + "'," +
    "lsnr = '" + metadata.lsnr + "'," +
    "rfchain = '" + metadata.rfchain + "'," +
    "crc = '" + metadata.crc + "'," +
    "modulation = '" + metadata.modulation + "'," +
    "gateway_eui = '" + metadata.gateway_eui + "'," +
    "altitude = '" + metadata.altitude + "'," +
    "longitude = '" + metadata.longitude + "'," +
    "latitude = '" + metadata.latitude + "'," +
    "mqtt_topic = '" + mqtt_topic + "'" +
    ";";
return msg;

The ttn payload from the devise is unaltered stored in the database (base64).

This is the database:

CREATE TABLE `ttn` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `json_payload` text,
  `timestamp` datetime DEFAULT NULL,
  `payload` text,
  `port` int(11) DEFAULT NULL,
  `counter` int(11) DEFAULT NULL,
  `dev_eui` varchar(16) DEFAULT NULL,
  `frequency` decimal(4,1) DEFAULT NULL,
  `datarate` varchar(11) DEFAULT NULL,
  `codingrate` varchar(11) DEFAULT NULL,
  `gateway_timestamp` int(11) DEFAULT NULL,
  `gateway_time` varchar(32) DEFAULT NULL,
  `channel` int(11) DEFAULT NULL,
  `server_time` varchar(32) DEFAULT NULL,
  `rssi` int(11) DEFAULT NULL,
  `lsnr` int(11) DEFAULT NULL,
  `rfchain` int(11) DEFAULT NULL,
  `crc` int(11) DEFAULT NULL,
  `modulation` varchar(11) DEFAULT NULL,
  `gateway_eui` varchar(32) DEFAULT '',
  `altitude` int(11) DEFAULT NULL,
  `longitude` decimal(7,5) DEFAULT NULL,
  `latitude` decimal(7,5) DEFAULT NULL,
  `mqtt_topic` varchar(256) DEFAULT NULL,
  PRIMARY KEY (`id`)
);
2 Likes
8

Can you specify which version of node.js you are using?

9

Node v0.10.29
Node-red v.0.13.4

Looks like an old nodejs version to me!

I tried to update to nodes 4.4.5, but that was not trivial on my debian jessie distribution (rasp.pi), because that was not part of the standard package list. However, after installing I couldn’t run node-red anymore, because it required the Nodejs 0.10.

10

FYI,

Longitude goes from -180 to 180

Your:

Will not store Longitude DDD,ddddd

For SQL DECIMAL; The first number is the total number of digits stored, and the second is the number after the decimal point.

So use DECIMAL(8,5)

Then you have another problem:

TTN uses 7 decimals:

To store those you need:

  • DECIMAL(9,7) for latitude
  • DECIMAL(10,7) for longitude

For really really precise locations (eg. earthquake measurements in Groningen) you want (10,8) and (11,8)

References:

1 Like
11

why are you kicking 2 year old topics ?
it’s nice off you to help in anyway but he really… after this long time ?

12

Because more and more people will (hopefully) search the Forum for knowledge.

This topic has incorrect knowledge, which when used, causes (IMHO serious) issues.

I don’t look at when posts where posted, I focus on content

1 Like
13

would be nice if you could write a LAB story on the subject ?

’ I don’t look at when posts where posted ’
but you should

14

Frankly, I do not belief in the Blog style because incorrect information is forever cast in stone.
Give me Wiki style and I’m your man.

Further downside of the LAB Stories is that eventually information is hidden/dispersed in a shitload of half useless stories. No one cleans up or enhances old info with new insights.

But I am a Internet heretic,
after 28 Net years I belief the best thing that happened to mankind was the burning of the Alexandria library.
We should do the same with Google content; like everything in life information should automagically decay.

I still would have written the same reply

1 Like
15

yeah me to :sunglasses:

16

The greatest library the world had ever seen. I hope this is just hyperbole…

17

Any progress?

I need help to send my TTN data to https://corlysis.com/ which expects line protocol (SQL style) because it can’t handle json. The site turns time-based data into beautiful Grafana graphs with ease. I’m an enthusiast but not a programmer. Suggestions would be greatly appreciated.

18

Help: Storing Payload size of 21 byte

how to insert payload into mysql database.

19

That’s the Storage Integration now.

20

I have done that part somehow using integration.Can I implement HTTP integration to my website hosted by apache server. If yes which URL should I give?Help me please i have very less idea about webdevelopment.