Storing of data, CSV, Sqlite?

General
1

Hi,

First off; I have been searching around the forum for answers to this and I’m pretty impressed by the forward leaning and helpful attitude to all levels of questions!

I have a little issue getting my sensor data stored in the correct way. The system is based on a RPi that is getting Long/Lat and time from a GPS, and temp and pressure via I2c. The sensors are continually pushing data via a running pythonscript in pythonshell. What I’m after is storing data as CSV into a text file with entry indexing so I will have a string with a format alike the example below. Later it will be loaded into excel or similar who will use comma seperation to utilize the data.

DD.MM.YY,00:00:00, Long, Lat, Temp, Pressure

Example:

1,07.09.2018,22:00:01,30.001,5.123,5c,1bar

2,07.09.2018,22:00:02,30.001,5.123,5c,1bar

3,07.09.2018,22:00:03,30.001,5.123,5c,1bar

So, I would like the message to be written for every second, with the GPS clock as the master. The system is entirely isolated and cannot run against any web-based service.

I have attempted with join nodes, batcher and more, but not really getting anywhere fruitfull. Anyone that can nudge me in the right direction on what to read up on?

2

It is not entirely clear how far you have got. Have you got the data into node-red? If so then exactly what form is it in there. You could copy/paste the output from debug nodes showing the data. Once we see what form the data is in then it will be clearer what can be done to get it into the form you want.

I am also a little confused by the thread title you mention sqlite, but then say you want to use csv. Perhaps you can clarify that too.

3

What do you want to do with the data after you have captured it?. It might be better to store it in a database rather than import it into excel, you could then query the data directly from the database to do graphing and statistical analysis.

4

The regular time interval involved in this would be a perfect use case for a time series database like influxDB. I bet it would dedupe like a dream.

5

I'm not sure what advantage Node-RED brings here? If you are already using Python for the sensor data, it isn't hard to get it to stream that data to a CSV file.

However, the choice of output format is going to depend on a few things. First amongst them being - exactly what are you intending to do with the data eventually?

If you will only ever process using some simple Excel, CSV may be best. As I say, probably easiest to do this in Python but easy enough in NR since there are the CSV and file out nodes.

If you intend to do more complex processing such as data transformations or using Microsoft's mapping addin for Excel then you might want to consider a database. SQLite is the easiest to manage but you would also need the SQLite ODBC driver so that Excel can access the data. Just watch out for data sizes. If your data is going to get very large, SQLite may not be best & you probably need a proper database server such as MySQL/MariaDB, Postgres or MS SQLserver.

If your aim is to map the data, Dave's worldmap node may do everything you need. Giving you web output.

Alternatively, you might want to look at Grafana which has a mapping addin. In that case, InfluxDB would be useful as it will easily handle large amounts of your data & Grafana allows you to build queries against InfluxDB databases very easily.

6

Hi,

Node Red brings the advantage for me as its more than one RPi involved, Dashboard and some future functions to include which has been very easy for me to implement compared to Python coding due to limited coding knowledge. So its more involved than whats in my inital posting.

As for now, the intention is to save the data as CSV string to a text file according to the time and this will be used together with a video with overlay(Video is recoderd with a seperated setup).

However; many thanks for your answer, its spot on for the help I was looking for. I will start looking into the different databases, where InfluxDB seems to be a good start. This is a underwater camera system which gathers data, the processing will be done off site so all data will only be stored, and some live displayed via the Dashboard node.

7

Hi Colin,

Ive setup an example to work with on a single computer. The data comes in the same format just that some is coming via a UDP from a second RPi and a pythonscript for the GPS data.

Im struggeling to see how I can get the GPS time to be the trigger on a continus stream and how do I get the string to keep its form if some parts a missing. As for now the string will be shortened if one of the sensors is missing.

If I loose a sensor I still need to get some numbers into the string so the string dont change form as the data would the be blended when exported and seperated using comma.

I put "CSV, Sqlite" in the header intended as a question if there maybe is some better ideas than the one im on now. I see that was not very specific.

[{"id":"8370d3a.da8593","type":"inject","z":"269383d6.44e96c","name":"","topic":"Time","payload":"Time","payloadType":"str","repeat":"3","crontab":"","once":false,"onceDelay":0.1,"x":190,"y":180,"wires":[["b4df6ad5.bd94e8"]]},{"id":"ed07fe86.ff02f","type":"inject","z":"269383d6.44e96c","name":"Pressure","topic":"Pressure","payload":"Pressure","payloadType":"str","repeat":"3","crontab":"","once":false,"onceDelay":0.1,"x":180,"y":240,"wires":[["a0db1853.be96c8"]]},{"id":"a5084f80.6cbe","type":"inject","z":"269383d6.44e96c","name":"","topic":"Temp","payload":"Temp","payloadType":"str","repeat":"3","crontab":"","once":false,"onceDelay":0.1,"x":190,"y":300,"wires":[["bd611d3e.df981"]]},{"id":"bda2f928.252748","type":"file","z":"269383d6.44e96c","name":"Store","filename":"D:\\Test\\Testdata","appendNewline":true,"createDir":true,"overwriteFile":"false","x":930,"y":320,"wires":[[]]},{"id":"265dd58d.7bf9ea","type":"debug","z":"269383d6.44e96c","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":930,"y":260,"wires":[]},{"id":"b4df6ad5.bd94e8","type":"change","z":"269383d6.44e96c","name":"SET-Parts_ID","rules":[{"t":"set","p":"parts.id","pt":"msg","to":"1","tot":"num"},{"t":"set","p":"parts.index","pt":"msg","to":"1","tot":"num"}],"action":"","property":"","from":"","to":"","reg":false,"x":400,"y":180,"wires":[["2c0e2268.07c39e"]]},{"id":"a0db1853.be96c8","type":"change","z":"269383d6.44e96c","name":"SET-Parts_ID","rules":[{"t":"set","p":"parts.id","pt":"msg","to":"2","tot":"msg"},{"t":"set","p":"parts.index","pt":"msg","to":"2","tot":"num"}],"action":"","property":"","from":"","to":"","reg":false,"x":400,"y":240,"wires":[["2c0e2268.07c39e"]]},{"id":"bd611d3e.df981","type":"change","z":"269383d6.44e96c","name":"SET-Parts_ID","rules":[{"t":"set","p":"parts.id","pt":"msg","to":"3","tot":"num"},{"t":"set","p":"parts.index","pt":"msg","to":"3","tot":"num"}],"action":"","property":"","from":"","to":"","reg":false,"x":400,"y":300,"wires":[["2c0e2268.07c39e"]]},{"id":"12280c63.bc8c14","type":"sort","z":"269383d6.44e96c","name":"","order":"ascending","as_num":true,"target":"parts.id","targetType":"msg","msgKey":"","msgKeyType":"elem","seqKey":"payload","seqKeyType":"msg","x":590,"y":180,"wires":[["2f11b7f9.fd72e8"]]},{"id":"2c0e2268.07c39e","type":"batch","z":"269383d6.44e96c","name":"","mode":"count","count":"4","overlap":0,"interval":10,"allowEmptySequence":false,"topics":[],"x":570,"y":120,"wires":[["12280c63.bc8c14"]]},{"id":"2f11b7f9.fd72e8","type":"join","z":"269383d6.44e96c","name":"","mode":"custom","build":"string","property":"payload","propertyType":"msg","key":"topic","joiner":",","joinerType":"str","accumulate":false,"timeout":"","count":"4","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":610,"y":220,"wires":[["b39ca7a3.9ee278"]]},{"id":"b54a95f7.03a718","type":"inject","z":"269383d6.44e96c","name":"","topic":"Gpsll","payload":"GPSLL","payloadType":"str","repeat":"3","crontab":"","once":false,"onceDelay":0.1,"x":200,"y":120,"wires":[["8420807d.d3215"]]},{"id":"8420807d.d3215","type":"change","z":"269383d6.44e96c","name":"SET-Parts_ID","rules":[{"t":"set","p":"parts.id","pt":"msg","to":"4","tot":"num"},{"t":"set","p":"parts.index","pt":"msg","to":"4","tot":"num"}],"action":"","property":"","from":"","to":"","reg":false,"x":400,"y":120,"wires":[["2c0e2268.07c39e"]]},{"id":"b39ca7a3.9ee278","type":"change","z":"269383d6.44e96c","name":"SET Topic","rules":[{"t":"set","p":"topic","pt":"msg","to":"Endstring","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":650,"y":260,"wires":[["265dd58d.7bf9ea","bda2f928.252748"]]}]