Go Down

Topic: Sensor data transmission with GSM to MySQL database (Read 167 times) previous topic - next topic

DanielRaetz

Dec 14, 2017, 05:40 pm Last Edit: Dec 14, 2017, 08:48 pm by DanielRaetz
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: 10*24*21 = 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.

Code: [Select]
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)

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 (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

DanielRaetz

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?

horace

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

Go Up