Fastest way to transmit a lot of sensor data with sim900 to datatable

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.

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:

  • SIM900 GPRS/GSM Board Quad-Band Module Kit for Arduino, from sainsmart.com

  • Adafruit Assembled Data Logging shield for Arduino + 4GB SD Card

  • Arduino Mega 2560 Dev. Board

Hi Daniel,
This you can upload in 2 or 3 HTTP post requests,
I use Dreamfactory as backend for my applications, It allows me to upload a array of data in single HTTP request as below,

Inside HTTPDATA we send

 {"resource": 
     [
      {“DT”: 20171214155344,“T”: 23.31,“H”: 43.85,“VB”: 6.77,“R”: 442411170.00},
      {“DT”: 20171214155344,“T”: 23.31,“H”: 43.85,“VB”: 6.77,“R”: 442411170.00},
      {“DT”: 20171214155344,“T”: 23.31,“H”: 43.85,“VB”: 6.77,“R”: 442411170.00},
      {“DT”: 20171214155344,“T”: 23.31,“H”: 43.85,“VB”: 6.77,“R”: 442411170.00},
      ......,
      {“DT”: 20171214155344,“T”: 23.31,“H”: 43.85,“VB”: 6.77,“R”: 442411170.00}

  ]}

Something like this.
Now these all above rows get updated in database table as separate rows.
Check if your server allows this.
And also the way the above code is written it will take lot of time and is not the right way to implement it. Use a AT command library function which waits for a response for a AT command like OK or ERROR and moves to send next command. That way you can eliminate delays wherever possible.

Hello Ravi,
Thanks for your tip, I really appreciate that. I don’t know Dreamfactory, but I have now created a test instance and briefly looked at the environment. I have seen that I can create a local MySQL database or use an external one. What I do not understand yet is where do I have to install Dreamfactory? I have a Wordpress installation on my domain with a plugin that graphically displays the data of my MySQL data table. How i can check if the server allows it?
Basically Dreamfactory looks very powerful, that could really be a good solution. Do you really think that I can transfer over 1000 measurements in one request? That would be great, of course.

Regarding my code with the delays - I know that it is very ugly, that would have been one of my next steps anyway to save time again.

@Daniel I think you need to use some cloud instance running to use dreamfactory. Digital ocean have low cost options with root access. You can try installing there.

Dreamfactory is basically a Laravel based application. If you can install Laravel, you can install DF.

Alternatively you can try the 1 month evaluation version or demo provided on DF site. Enter your email address and you get a 1 month free trial.

The SIM900 can transfer 2K bytes of data reliably in single HTTP request, I have done 4K Bytes also but not reliable. I suggest to divide your data in chunks and send.

@RaviPujar I think first i will test it with the trial version on DF site.

I checked the size of one entry on http://bytesizematters.com. The following entry (measurement) has a size of 83bytes: {“DT”: 20171214155344,“T”: 23.31,“H”: 43.85,“VB”: 6.77,“R”: 442411170.00}

This would allow me to transmit about 24 measurements in one request till i reach 2k bytes… that would still be too many requests for several thousand measurements (up to 5k measurements) :confused:
Do you know if there are modules that can transmit much more at once?

Yes, you can try pushing the limit and see how much SIM900 allows.
For one shot upload switch to a 3G or 4G module. Like SIM5320 or SIM7100

Alternatively, You could create a transparent TCP connection and keep sending data to server by keeping TCP connection open. Try that and see.
Use AT+CIPMODE=1
For more details check TCP IP document of SIM900.