help need: upload GPS data to MySQL server with Sim900

[Rudi Ahlers]

i,

I wonder if anyone can help me with this one, please. And, yes, it's a
programming question I have.

My environment is as follows:
Arduino MEGA2560
Ultimate GPS breakout boad, with GPS connected to Serial3
Sim900 module, with Sim900 connected to Serial2
Micro-SD card module with 4GB micro-SD card.
PHP+MySQL web server to store and view GPS data


The idea is to have a GPS tracking device in a vehicle, which can
sometimes to out of 3G/GPRS coverage, but keep saving the data on a
micro-SD card, and then upload to a PHP web page, in order to save to
a MySQL server.

Right now, the Arduino is saving GPS information (lat, lon, date,
time, speed, direction) to a micro-sd card every 2 minutes, into a
.csv.

The vehicle operates in the bushes and the driver doesn't know much
about this kind of technology. But I don't want him to even know it
there's, so the "GPS tracker" should work autonomously.

I need to be able to view the data in real-time, or as close to
real-time as possible. It won't always be possible if there's no
GPRS/3G coverage, but that's fine, as long as I could at least get the
last-known location and direction.



I simply don't know where to begin with this, and have searched the
forums and google for a sample but couldn't find anything.

Can anyone please help me with this?
I just need some sample code, or even a tutorial / wiki / blog
article, showing how a .CSV file is opened and the data is posted to a
PHP file on a webserver, sequentially.




I know how to to submit a single string to a web page, but not a few
hundred, or thousand stings.
Lastly, I need a way to know where the data submission has stopped.

Let's say the .CSV file has 500 lines, and it submits strings to the
PHP webpage, but the GRPS signal is lost during transmission (i.e.
data plan has run out, vehicle moves into non-coverage area, problems
with GRPS provider, etc.) how will I know where it stopped, and where
to continue?

I suppose the data would need to be submitted, say every 5 or so minutes?
--
Kind Regards
Rudi Ahlers

Cell: 082 554 7532
Fax: 086 268 8492

[Michael da Silva Pereira]

Hi,

My thoughts on something like this would be to try the following:

Every 2 minutes:

* Craft a payload packet (lat,lon,date, other data, etc..)

* Initiate TCP connection to server

* Attempt Payload delivery

* Wait response (fairly low timeout, prob 10,15 seconds)

- Response = OK - Then check txt file for previously failed records and send (maybe batched)

-   ELSE, Save date,lon,lat,etc into txt of failed records.

* Close TCP

It's worth noting, using something like HTTP means way more overhead, in poor coverage areas this can be a issue. I'd rather use a simpler TCP server / client daemon, and write it to a DB on the server side (sqlite,mysql,etc.). If you don't have access to something with a public IP where you can run a daemon, then HTTP as a last resort.

UDP is also an option.

If you want, share your code off list and I can recommend some stuff.

You could also work on issuing instruction to the unit remotely, IE tell unit to stop transmitting, change transmission timing, buzzers, LEDs, relays. Some cool things your could do.

Thanks,

Mike

--
--
You received this message because you are subscribed to the Google
Groups "house4hack" group.
To post to this group, send email to house...@googlegroups.com
To unsubscribe from this group, send email to
house4hack+...@googlegroups.com
---------------------------------------------------------------------------------------------
www.house4hack.co.za     |    Centurion Tue 18-21 & Sat 9-14    |    Randburg Wed 18-21
---------------------------------------------------------------------------------------------
---
You received this message because you are subscribed to the Google Groups "house4hack" group.
To unsubscribe from this group and stop receiving emails from it, send an email to house4hack+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

Kind regards,

Michael da Silva Pereira

[Georg]

Hi

This might be of some use. Have not tested it  myself though.

http://www.open-electronics.org/the-gsmgprs-gps-shield-some-http-connections-examples/

Georg

[Kayle Maclou]

I’ve been contemplating a similar scenario for a while, using a RaspberryPi instead of an Arduino, and have come up with the following options to get the data from the tracking device to the webserver:

·        Sockets - Seems to be the most efficient, but are more complex to use. http://en.wikipedia.org/wiki/Network_socket

·        HTTP – Relatively easy to use, but it requires the ability to perform native HTTP requests on the Arduino? If this is possible, it should be simple to send that CSV file line-by-line, to your PHP webpage, one HTTP request at a time, using a “GET”, “POST” or even “PUT”. http://en.wikipedia.org/wiki/Hypertext_Transfer_Protocol

·        Web Sockets – Typically used by a web browser, BUT is can also be used by any component supposing this functionality. If such a component existed for the Arduino, then this option would be my initial choice during the POC, which could later be converted to “conventional” sockets. http://en.wikipedia.org/wiki/WebSocket

Regards,
Kayle Maclou

[Jacques]

+1 for HTTP.

I think sockets are overkill for what is being done. That being said, if HTTP is not an option you will have to use sockets.

The server you are referring to might also impact your decision. Shared hosting would be fine for HTTP but I am unsure what you would need for the socket option. Maybe a VPS or a dedicated server.

[Rudi Ahlers]

Thanx for the suggestions, Michael.

So far I'm saving GPS data to a .CSV file, every 2 minutes. The string
is about 70 - 80 characters long, so 10 lines of data (20 minutes) is
1KB. This isn't a big file.

The problem I'm having right now, is with the Arduino Code, and the
SIM900 module. There are some GSM libraries available, but they're
full of delay() functions, which means the whole Arduino sketch is
halted while the GSM module attempts a connection to the server and
upload the data. If the 3G / GPRS signal is weak, it can waste a good
10 minutes trying to negotiate a connection.

So I need to try and work around this. The problem is, I'm not a C /
C++ developer and I'm working off samples off the internet. There are
some samples where guys have sent SMS's through a Sim900 module (or,
shield if you want to use that term) without using the SIM900 library,
by issueing native "AT" commands. I just can't find an example where a
response is read back, from an HTTP server, in order to know if the
CSV file was received by the server, and the Arduino can delete it off
the SD card.

[Rudi Ahlers]

Hi George,

I did look at that page, but the delay() functions throughout the
SIM900 library makes the whole process very slow. Please see my other
response for explanation.

> --
> --
> You received this message because you are subscribed to the Google
> Groups "house4hack" group.
> To post to this group, send email to house...@googlegroups.com
> To unsubscribe from this group, send email to
> house4hack+...@googlegroups.com
> ---------------------------------------------------------------------------------------------
> www.house4hack.co.za | Centurion Tue 18-21 & Sat 9-14 | Randburg Wed 18-21
> ---------------------------------------------------------------------------------------------
> ---
> You received this message because you are subscribed to the Google Groups
> "house4hack" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to house4hack+...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.

[Rudi Ahlers]

I have my own dedicated servers and a nice fast pipe, so that's not an issue ;)
The load I'm expecting right now shouldn't be a problem on a shared or
VPS account either.

On Tue, Aug 5, 2014 at 1:08 PM, Jacques <serve...@gmail.com> wrote:
> +1 for HTTP.
>
> I think sockets are overkill for what is being done. That being said, if
> HTTP is not an option you will have to use sockets.
>
> The server you are referring to might also impact your decision. Shared
> hosting would be fine for HTTP but I am unsure what you would need for the
> socket option. Maybe a VPS or a dedicated server.
>
> On 05 Aug 2014, at 1:02 PM, Kayle Maclou <kayle....@gmail.com> wrote:
>
> I've been contemplating a similar scenario for a while, using a RaspberryPi
> instead of an Arduino, and have come up with the following options to get
> the data from the tracking device to the webserver:
>
> · Sockets - Seems to be the most efficient, but are more complex to
> use. http://en.wikipedia.org/wiki/Network_socket
>

> · HTTP - Relatively easy to use, but it requires the ability to

> perform native HTTP requests on the Arduino? If this is possible, it should
> be simple to send that CSV file line-by-line, to your PHP webpage, one HTTP
> request at a time, using a "GET", "POST" or even "PUT".
> http://en.wikipedia.org/wiki/Hypertext_Transfer_Protocol
>

> · Web Sockets - Typically used by a web browser, BUT is can also be

[Michael da Silva Pereira]

You could just modify the library and lower all the delays you find.

I'm a bit surprised they used delay() and not the better select(), perhaps look for both and change the delayed values.

I haven't personally played with the GPRS libraries, and find it rather bad that they are blocking. You could look at using an external interrupt in your circuit to bring up a DI causes you to process a payload. Like a timer every 2 minutes to hit a pin up, this interrupting anything currently running to grab a payload and either transmit or store the payload.

http://playground.arduino.cc/Code/Interrupts

I'd change the scenario slightly then:

1.) Connect GPRS

2.) Attach interrupt to pin with external timer

2.) Check file for records to be sent, or buffer != "" (IE you have a payload in the 'barrel')

3.) If records found send them (batched), save new file with the remaining "items"

4.) Else sleep ( then jump to #2 )

On Interrupt call:

1.) Detach interrupt

2.) Generate payload into a buffer variable

3.) Write to file

4.) Attach interrupt

The interrupt will call the method / callback when the pin goes high, put the payload in the buffer. So that when your code returns this data is sent off straight away, so it's as realtime as possible.

I also haven't mention detaching the interrupt when dealing with the files ( in case you get an interrupt when dealing with with a slow SD), but I'd recommend that. Just in case you writing to the file and get an interrupt, you don't want to open to file descriptors, all all types of other nasties.

detachInterrupt()

fopen()

fgets()

fclose()

attachInterrupt()

Be careful trying to send the entire CSV file, I'd rather batch it. IE send 10-20 records at a time. Create a new file with the remaining records and rename them.

The SMS modules is purely SMS, you won't get a data session over it unless it can do a GPRS connection for you. Sounds like that SMS900 is just a SMS module, with no data support.

Thanks

[RustyK9]

Good Day Rudi

Not sure if U've seen OpenGTS - Open GPS Tracking System...

http://opengts.sourceforge.net/

Did an experiment with it, a little over 4 years ago.

It's happy to except a csv file directly via a USB Mem stick or a feed from
an online source.

Back then the only GPS I had was an old Nokia E71 with GPS, ran an app on Nokia to
send Nmea gps strings to a small laptop (Atom with XP) via bluetooth to a virtual machine
Oracle VirtualBox + Ubuntu 10.04 Desktop that had installed OpenGTS.

Also did the experiment to a remote a Ubuntu server, it worked like a charm.

John

[Rudi Ahlers]

Hi John,

I've seen that system but it doesn't quite fit my needs....

> --
> --
> You received this message because you are subscribed to the Google
> Groups "house4hack" group.
> To post to this group, send email to house...@googlegroups.com
> To unsubscribe from this group, send email to
> house4hack+...@googlegroups.com
> ---------------------------------------------------------------------------------------------
> www.house4hack.co.za | Centurion Tue 18-21 & Sat 9-14 | Randburg Wed 18-21
> ---------------------------------------------------------------------------------------------
> ---
> You received this message because you are subscribed to the Google Groups
> "house4hack" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to house4hack+...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.

[Jacob Kruger]

Sorry haven't been following thread, but, if issue is to do with obtaining GPS data from various sources, etc., I have, for example, pulled open-street-map data off their server, and had it converted into XML format using a converter that's targeting another bit of android software, and you might then be able to extract relevant data?

 

Saying that, since have done similar with some of it to then also convert it into yet another data format, for an older piece of offline GPS software that runs on nokia/symbian phones, and requires a form of CSV data package, and tweaked that part of the conversion using a bit of python code myself.

 

Stay well

Jacob Kruger
Blind Biker
Skype: BlindZA
"Roger Wilco wants to welcome you...to the space janitor's closet..."

--

[Rudi Ahlers]

Jacob, please follow the thread ;)


Who can I pay to help me with some working code, please?
- I can obtain write GPS data to a CSV file on the SD card, in
intervals of 2 minutes

But I can't upload the CSV file to a web server, in intervals of say 5
minutes, without interrupting the previous step. The SIM900 libraries
are full of "delay()" functions, which stop the whole sketch every
time it's called. So I need this part to run without stopping the rest
of the sketch. I know I should be using a millis() timers, but simply
cannot get it to work.

[Jan de Jager]

Hi Rudi,

I'm jumping in a bit late, but have some experience with your scenario. My solution was building a simple python based TCP server as handling http on the Atmega chips were way to complicated, with headers (especially with PHP) taking up way too much data.

I built a simple string containing the location data (probably better to encode this rather than plain text) and send it via a simple TCP connection to the python server. The python code for a TCP service is about 10 lines of code on its own, very simple. I can send you a sample if your need.

Skytraq chips use a very handy format for dealing with stream of gps coord, using a full locaiton and then a set af aplha (changes) to reduce the total amount of data required. https://code.google.com/p/skytraq-datalogger/

Regards

Jan de Jager

On Monday, August 4, 2014 12:57:45 PM UTC+2, Rudi Ahlers wrote:

[Hussain]

I don't know how comfortable you are with JavaScript (or whether this suggestion might have value), but perhaps you should consider:

Using Node.js (with some framework like Express on top of it) on the server-side to deal with the "blocking" issues. Although you could do this in just about any language, there should be literally tons of tutorials for Node/Express and you may even stumble across a few for a unique hardware solution like yours.

As far as transferring data goes, are you restricted to CSV only? Or is XML/JSON an option?

If JSON is an option, transferring the data to a DB like MongoDB shouldn't be as tough as the "line-by-line" situation with CSV.

Although all the tech mentioned above is new"er" and might seem trendy, they do have their use-cases and it may apply to your situation here.

Regards,
H

On Monday, August 4, 2014 12:57:45 PM UTC+2, Rudi Ahlers wrote:

[Jacques]

+1 for node.

I'm using it at the moment and loving it! HTTP or TCP server in a few lines of code. If you want to use it full stack then look at the MEAN stack. Works like a charm!

--

[Gert van den Berg]

On Thursday, August 7, 2014 3:45:06 PM UTC+2, Hussain wrote:

I don't know how comfortable you are with JavaScript (or whether this suggestion might have value), but perhaps you should consider:

Using Node.js (with some framework like Express on top of it) on the server-side to deal with the "blocking" issues. Although you could do this in just about any language, there should be literally tons of tutorials for Node/Express and you may even stumble across a few for a unique hardware solution like yours.

As far as I can tell, the blocking problems is in the (Arduino) client-side, with crappy libraries...

(The server-side stuff does not seem relevant at all yet...)

The right way to fix it is probably to get nice interrupt-driven code for everything (Probably serial interrupts as well as some timers)... The problem is writing it... If it is all AT-command based, some other libraries might also work... It also shouldn't be that hard to replace it... (Sim.com insist on registration for obtaining any useful info...)

(The Sim900 seem to have built-in Lua scripting support as well, so it might be possible to eliminate the Arduino and run everything on there... It seem to have a much better processor (ARM9) anyway...
http://www.propox.com/download/docs/SIM900.pdf )

Gert

[Peter Edwards]

Hi,

AdaFruit recently released a breakout module for the Sim800. I'm not sure if there are major differences between the 800 and 900 versions, but this page:
https://learn.adafruit.com/adafruit-fona-mini-gsm-gprs-cellular-phone-module/downloads

Has a wealth of information that may help? Specifically the AT commands for HTTP interaction with the module are nicely detailed in a datasheet.
-(e)