Go Down

Topic: looking for the best and most efficient way to store my data. (Read 435 times) previous topic - next topic

Coos

A part of my aquarium computer project is writing and reading data (temperature, pH, and maybe more in the future). I'm able to store the data I want, to a text file on a sd card, and send requested data to my android app. I want to store data every 5 minutes and with my app I want to be able to retrieve data with a time frame of the last 6 hours, 12 hours, 3 days, 7 days and per month.

The data I'm storing  (date, time, temp1, temp2, temp3)  looks like:
18-10-18,14:45,23.00,24.25,20.50

I'm wondering what would be the best and most efficient way to store my data.

For starters: Is using a text file the best approach, or would it be better/ faster to use a binary file.

Another thing I'm thinking of is the number of files I should use.
I could use one file, but after a couple of months (maybe weeks?) the file would contain so much data that reading the file, in search of the requested data, would take a lot of time.
To solve this I think I could either use a second file to store the location of the data per day, and use that location to jump to the right location to save time searching.
Or I could use multiple files. One for each month, with two  registrations a day, and one for every last seven days, with a record every 5 minutes. For the latter I need to find a way to delete data older than 7 days and append the new data.

Advice anyone?

Coos

Here is my code:

Code: [Select]
#include <SPI.h>
#include <SD.h>
#include <DS3232RTC.h>
#include <Wire.h>
#include <OneWire.h>
#include <DallasTemperature.h>

File myFile;

#define pinCS 53    // SD-Card CS-pin
#define ONE_WIRE_BUS_PIN 22 //Onewire temperature pin (for all probes)

static char sdString[33];      // make a char array to hold incoming data from the SD card
unsigned long sdCardMillis = -3500UL;   // minus sdWriteDelay value for first run, so it won't skip the next round5 time

signed char round5delta[5] = {0, -1, -2, -3, -4};  // difference to the "rounded to nearest 5" value
long round5(long no) {
  return no + round5delta[no % 5];
}

OneWire oneWire(ONE_WIRE_BUS_PIN);
DallasTemperature sensors(&oneWire);
unsigned long readTempsMillis = 0;
int tempReadDelay = 1000; //update temperatures every second
DeviceAddress tempWaterSensor = { 0x28, 0xFF, 0xAD, 0xE8, 0xC1, 0x17, 0x04, 0xD9 };
DeviceAddress tempAirSensor   = { 0x28, 0xFF, 0x17, 0xE8, 0xC1, 0x17, 0x04, 0x2B };
DeviceAddress tempCaseSensor  = { 0x28, 0x01, 0x20, 0x43, 0x98, 0x25, 0x00, 0x8A };
int tempSensor[3] = {tempWaterSensor, tempAirSensor, tempCaseSensor};
float tempVal[3] = {0, 0, 0};     //tempWaterValue, tempAirValue, tempCaseValue
float maxTempVal[3] = {0, 0, 0};  //maxTempWaterValue, maxTempAirValue, maxTempCaseValue
float minTempVal[3] = {0, 0, 0};  //minTempWaterValue, minTempAirValue, minTempCaseValue
float tempCalVal[3];              //CalibrationTempWaterValue, CalibrationTempAirValue, CalibrationTempCaseValue

void setup() {
  Wire.begin();
  Serial.begin(9600);
  Serial1.begin(115200);
  setSyncProvider(RTC.get);   // the function to get the time from the RTC
  if (timeStatus() != timeSet)
    Serial.println("Unable to sync with the RTC");
  else
    Serial.println("RTC has set the system time");
  Serial.print("Initializing SD card...");
  if (!SD.begin(53)) {
    Serial.println("initialization failed!");
    while (1);
  }
  Serial.println("initialization done.");

  sensors.begin(); //temperature sensors;
  sensors.setResolution(tempWaterSensor, 10);
  sensors.setResolution(tempAirSensor, 10);
  sensors.setResolution(tempCaseSensor, 10);
}

void loop() {
  readTemps();
  requestTemps();
  writeToSDcard();
}

void writeToSDcard() {
  unsigned long sdWriteDelay = 3500UL;
  int timeInMin = (hour() * 60) + minute();
  char filename[13] = "12345678.TXT";
  char tempString[3][6] = {"00.00", "00.00", "00.00"};
  if (millis() - sdCardMillis >= sdWriteDelay && timeInMin == round5(timeInMin)) {    // record every 5 minutes
    sprintf(filename, "%02d%02d-Tmp.txt", year() % 100, month());
    Serial.print("Write to SD card: "); Serial.println(filename);
    myFile = SD.open(filename, FILE_WRITE);
    if (myFile) {
      for (byte t = 0; t < sizeof(tempVal) / sizeof(float); t++) {
        dtostrf(tempVal[t], 5, 2, tempString[t]);
      }
      sprintf(sdString, "%02d-%02d-%02d,%02d:%02d,%s,%s,%s", year() % 100, month(), day(), hour(), minute(), tempString[0], tempString[1], tempString[2]);
      myFile.println(sdString);
      myFile.close();
    }
    else {
      Serial.println("error opening txt document"); // if the file didn't open, print an error:
    }
    sdCardMillis = millis();
  }
}

bool cmpCA(char string1[], char string2[], byte sizeofString) {
  for (byte i = 0; i < sizeofString; i++) {
    if (string1[i] != string2[i] ) {
      return false;
    }
  }
  return true;
}

void readSDcard(byte line, byte timeFrame) {
  static bool timeFrameSet = false;
  static char dateTimeStart[15] = "00-00-00,00:00";
  static char dateTimeEnd[15] = "00-00-00,00:00";
  static byte multiplier = 6;
  if (!timeFrameSet && timeFrame > 0) {
    timeFrameSet = true;
    if (timeFrame == 168) multiplier = 7;
    multiplier = timeFrame / multiplier;
    tmElements_t sdElements = {second(), minute(), hour(), weekday(), day(), month(), (year() - 1970) };
    time_t sdTime = makeTime(sdElements);
    sdTime = sdTime - (timeFrame * 3600UL);
    breakTime(sdTime, sdElements);
    sprintf(dateTimeStart, "%02d-%02d-%02d,%02d:%02d", (sdElements.Year + 1970) % 100, sdElements.Month, sdElements.Day, sdElements.Hour, round5(sdElements.Minute));
    sprintf(dateTimeEnd, "%02d-%02d-%02d,%02d:%02d", year() % 100, month(), day(), hour(), round5(minute()));
  }
  static byte charPosS = 0; // to determine the character position within the selected line of the array sdString
  static bool firstLineFound = false;
  static byte lastLine = 0;
  static unsigned long activeLinePos = 0;
  myFile = SD.open("testfile.txt");
  if (myFile) {
    while (myFile.available() && !firstLineFound) {
      char recieved = myFile.read();           // read a byte, then
      if (recieved == '\n') {                  // if the byte is a newline character
        sdString[charPosS] = '\0';     // null terminate the sdString
        charPosS = 0;                  // set char position of sdString to the beginning
        if (cmpCA(sdString, dateTimeStart, sizeof(dateTimeStart) - 1)) { // if beginning of sdString is the same as dateTimeStart
          activeLinePos = myFile.position() - (sizeof(sdString) + 1);
          firstLineFound = true;    // Set the firstLineFound true
        }
      }
      else if (recieved != '\r') {       // if you got anything else but a carriage return character,
        sdString[charPosS] = recieved;   // add it to the end the array
        charPosS++;    // Go to the next char position
      }
    }
    if (line == 255) {
      firstLineFound = false;
      lastLine = 0;
      multiplier = 6;
      timeFrameSet = false;
      Serial1.println("sdData|done|");
    }
    else if (line == lastLine && lastLine > 0) {
      Serial1.println("sdData|end|");
    }
    else {
      Serial.print("multiplier = "); Serial.println(multiplier);
      myFile.seek(activeLinePos + (line * (sizeof(sdString) + 1) * multiplier));
      myFile.read(sdString, sizeof(sdString));
      Serial1.print("sdData|"); Serial1.print(line); Serial1.print("|"); Serial1.print(sdString); Serial1.println("|");
      if (cmpCA(sdString, dateTimeEnd, sizeof(dateTimeEnd) - 1)) { // check if beginning of sdString is the same as dateTimeEnd
        lastLine = line + 1;  // Mark this line the lastLine
      }
    }
    myFile.close();    // close the file
  }
  else {
    Serial.println("error opening txt document");     // if the file didn't open, print an error:
  }
}

void readTemps() {
  if (readTempsMillis == 0) {
    sensors.requestTemperatures();
    readTempsMillis = millis();
  }
}

void requestTemps() {
  if (millis() - readTempsMillis > tempReadDelay) {
    readTempsMillis = 0;
    for (byte t = 0; t < sizeof(tempVal) / sizeof(float); t++) {
      tempVal[t] = tempCalVal[t] + sensors.getTempC(tempSensor[t]);
      if (tempVal[t] > maxTempVal[t]) {
        maxTempVal[t] = tempVal[t];
      }
      if ((minTempVal[t] == 0) || (minTempVal[t] == -127.00)) {
        minTempVal[t] = tempVal[t];
      }
      if (tempVal[t] < minTempVal[t]) {
        minTempVal[t] = tempVal[t];
      }
    }
  }
}

Nick_Pyner

I want to store data every 5 minutes ........
18-10-18,14:45,23.00,24.25,20.50

I'm wondering what would be the best and most efficient way to store my data.

For starters: Is using a text file the best approach, or would it be better/ faster to use a binary file.
Better is a relative term, tempered by how unnecessarily difficult you want to make things for yourself and, since you are storing the data at 5 min intervals, why would you worry about being faster?

The smallest SD card you can buy will probably hold about twenty years of data.

You could use date as filename and make a new file at midnight, or a change of month to do it monthly. The rest of your post is incomprehensible, but I don't suppose you need to delete anything for quite a while.


Lucario448

For starters: Is using a text file the best approach, or would it be better/ faster to use a binary file.
Binary makes more sense on time-critical implementations or inter-machine communication. If none of those are the case, then text is totally fine.

Also binary encoding "compresses" the data, so for space efficiency you could go this route. But, if the file is intended to be read by a human, and space is abundant (> 2 GB is a gigantic space for plain text); the disadvantages aren't a big deal.



I could use one file, but after a couple of months (maybe weeks?) the file would contain so much data that reading the file, in search of the requested data, would take a lot of time.
Unless you implement all the juggling involved in an enterprise-level digital database, the only workaround will be fixed-length records. By either binary-enconding, or fixed-length strings (padded with binary zeroes if the actual one is smaller than the defined length).

 


For the latter I need to find a way to delete data older than 7 days and append the new data.
Isn't that easy. You can't load the entire file into RAM in order to modify it (as PC text editors do).

You'll have to create a temporary file to store a copy of the other one, but without the data to delete. Then this temporary file will become the original one, by deleting or truncating the old one.
Is possible, but not that easy and quick.

Coos

Thanks for the replies. Makes sense; space is not an issue, and time is not an issue for storing the data.
The problem I'm having is the time it takes searching trough the file for the first requested record.

Lets say, for instance, that I want to receive the records of the past six hours on my android app on my phone.
My app sends the request to my Wi-Fi module, this module sends it via serial communication to my arduino.
The arduino then starts searching for the first requested record.
This search takes quite some time.
At the moment I'm using a test file with records starting this month. It contains over 5000 records, en the search to record number 5000+ takes more than 7 seconds.

Any idea how to solve this?

Nick_Pyner

I'm having is the time it takes searching trough the file for the first requested record.

Lets say, for instance, that I want to receive the records of the past six hours on my android app on my phone.
I would not have thought the phone was the best place to do this, but I guess it depends very much on the app. Having said that, at your aforementioned five minute intervals, six hours represents 72 lines of trivial data
18-10-18,14:45,23.00,24.25,20.50
so God only knows how it can be so hard. On the other hand 5000 records is about 17 days accumulation, so I guess the first thing you need to do is come up with some decisions about what you really want to do, as that will very likely have some bearing on how you do it. You might start by determining what the real needs are.

Go Up