Sensor data transmission with GSM to MySQL database

Using Arduino Networking, Protocols, and Devices
1

Hello everybody.
I'm working on a project where I collect sensor data periodically, then save it on an SD card and finally send several measurements together with the GSM module to a web server.

The goal is to take a measurement every hour. The transmission interval should be up to 3 weeks. So I would like to transmit the accumulated measurement data once every 3 weeks to a web server. In addition, I do not have just one measurement unit, but it can be several measurement units. The measuring units will be connected to the central unit in order to store and transmit the data there.

Number of measuring units: Up to 10
Number of measurements per day per measuring unit: Up to 24 (1 hour interval)
Number of days per transmission interval: Up to 21 days (3 weeks)

-> Maximum number of measurements per transmission: 102421 = 5040 measurements

At the moment I have a working script to send ONE measurement in json format via GSM to a database. I use the HTTP POST method and a php file, which reads out the data and writes it to the database.
I am saving a measurement already in the right format (json) to send it later.

A measurement looks like this on the SD card:
{"DT": 20171214155344,"T": 23.31,"H": 43.85,"VB": 6.77,"R": 442411170.00}
DT=DateTime (Timestamp from RTC), T=Temperature, H=Humidity, VB=Battery voltage, R=Resistance

I tend to save the data in the future in csv format on the SD card. The SD card should also serve as a backup if no radio connection is available at the current location.

The problem I have now recognized that if I want to transmit many measurements in this way, it will take a long time for everything to be transmitted via GSM. The main reason for this is the large delays it needs after the AT commands AT + HTTPACTION (4sec) and AT + HTTPREAD (3sec). Below you can see a section of the GSM send function, where these AT commands are executed.

for (int i = 0; i < numMeas; i++) {// numMeas=number of measurements to send
    gsm.SimpleWrite("AT+HTTPDATA="); //
    delay(200);
    gsm.SimpleWrite(toTransmitLength[i]); //length of one measurement i
    delay(200);
    gsm.SimpleWriteln(", 1000"); 
    delay(300);
     gsm.SimpleWriteln(*toTransmit[i]); //measurement i
    delay(500);
    gsm.SimpleWriteln("AT+HTTPACTION=1");
    delay(4000); 
    gsm.SimpleWriteln("AT+HTTPREAD");
    delay(3000); 
  }

What I would like to know is how I can transfer as much data as quickly as possible in order to achieve low energy consumption (battery powered). I suspect the method with HTTP Post and the file format json may not be suitable for it. I am looking for a better way to transfer this number of measurements to a MySQL database.

I use the following components:

Attachment:

  • Complete code (record values, save to SD card, send with GSM module)

P5_readIN_readOUT_send_varLength.ino (10 KB)

2

I avoid using Json over GSM due to the size of text generated by the protocol
I tend to transmit raw binary information which is converted to text at the server (taking into account little/big endian, word size, etc), e.g. one can transmit a 16 bit value range 0 to 65535 as two bytes
Similarly Lora limits the volume of data transmitted per day - transmitting binary then using the Payload format to convert to json then HTTP integration to transmit POST to a server works well

3

horace:
I avoid using Json over GSM due to the size of text generated by the protocol
I tend to transmit raw binary information which is converted to text at the server..

I tend to save the measurements in csv format on the SD card. Instead of reading out the measurements and converting them into binary raw data and sending them, would it also be possible to upload the hole csv file and then read it into the database with a php script? What is file size limit using POST?

4

if you have a file you could transfer it using FTP then read it into the database

5

horace:
if you have a file you could transfer it using FTP then read it into the database

Hello,

is it possible to update a file on a webserver (Keywords: mysql, apache, php) only by newer measurements, which aren't yet on the database?

For instance the old file on the database has 100 rows with measurements ("Row 1" contains the first measurement and "Row 100" contains the last measurement in the old file). My file on the sd card, which gets updated continuously with new sensor readings through my arduino, has 200 measurements at a later point in time. Can I send this newer csv-file to the database, calculate the amount of rows of the older file through php and append row 101 till 200 to the older file on the database?

Basically what I mean is row.length(old_file) + 1 : row.length(new_file).

I plan to give every measurement a timestamp through a RTC (First column timestamp, second column measurements). Maybe that would be also an option? Maybe it's possible to append by time, which means only appending measurements, which are newer than the last timestamp of the old file on he database.