What to do with boat data....

[AntarcticPilot]

I use MySQL and a web server.

My Arduino has a TCP/IP stack available due to the GSM card. This has embedded HTTP commands. Therefore the arduino simply makes a http call to my webserver with the values as parameters. Note: some details have been changed to protect my system.

So the Arduino makes this web call to the server...

http://www.yourwebsite.com/yourscript.php?value0="15"&value1="54"&value2="13"&value3="13"&value4="1"

This is the code in the arduino

String webstring = "AT+HTTPPARA=\"URL\",\"http://";
  webstring.concat("YOUR_URL");
  webstring.concat("/yourscript.php?value0=");
  
  webstring.concat(t);
  webstring.concat("&value1=");
  webstring.concat(h);
  webstring.concat("&value2=");
  webstring.concat(batt1);
  webstring.concat("&value3=");
  webstring.concat(batt2);
  webstring.concat("&value4=");
  webstring.concat("1");
  webstring.concat("\"\r\n");
  sprintf(webbuffer, "%s", webstring.c_str());
  printLCD(ALERT_ROW, F("HTTP GET Param"));

On the webserver I then have a php script as follows

<?php

$value0=$_GET['value0'];
$value1=$_GET['value1'];
$value2=$_GET['value2'];
$value3=$_GET['value3'];
$value4=$_GET['value4'];


$opendb=mysql_connect("localhost","USERNAME","PASSWORD") or mysql_error("ERROR");
mysql_select_db("DATABASE",$opendb);
if ($opendb){
  $query = "INSERT INTO DATABASE VALUES( '', '$value0', '$value1',  '$value2', '$value3', '$value4', UNIX_TIMESTAMP());";
  /* Run the query */
  $result= MYSQL_QUERY($query) or die( "ERROR" );
  mysql_close($opendb);
  echo "DBOK";
} else {
echo "ERROR";

Once its into a database on a webserver then the "world is your lobster" - you can present and manipulate the data in a multitude of ways.

You can run a small server on a Raspberry Pi

A quick look at your server code suggests that it would be vulnerable to a SQL insertion attack. Creating SQL strings by concatenation is dangerous (see https://xkcd.com/327/ for an amusing take on this!).

Of course, if this is entirely on a firewalled internal network, then it probably isn't a real concern, but it is bad practice.

 

[prv]

A quick look at your server code suggests that it would be vulnerable to a SQL insertion attack. Creating SQL strings by concatenation is dangerous (see https://xkcd.com/327/ for an amusing take on this!).

Of course, if this is entirely on a firewalled internal network, then it probably isn't a real concern, but it is bad practice.

And the trouble is that a lot of people are doing stuff like this and then selling it, for use by non-technical people on the public Internet.

Pete

 

[SiteSurfer]

Agreed. No reason not to avoid injection issues now with the wrapper libs available making it even easier.

 

[mattnj]

I decided on RRDTool with/without Highcharts (depending on how fancy you want it to look)
example below of both.

Found it fairly simple too....which was important!

The beauty of these, is the size does not grow and grow, old data is averaged out for you.

 

[laika]

Classic IoT thinking - starting with the technology that's available instead of a problem that needs solving, resulting in a toaster that's connected to the Internet for no good reason

+1, although in the wider world the problem which *actually* needs solving is "How do we bag some of the cash the DTI have earmarked for IoT innovation to keep us in lattes and fixies for another 6 months?"

Data are "stuff". Liveaboard philosophy is surely "Do I *need* these? If not why am I keeping them?". "Just in case" is a not a good answer. "Because I want to be able to see pressure changes over the past 36 hours to help with navigational decisions" probably is.

Are you going to need to see these data anywhere other than on the boat? If not "the cloud" (/dropbox) doesn't need to be an issue.

If you do still want to do this then select from "Whatever is easiest" and "What do I want to learn?".

If you want to broaden your skills up the stack then look at mysql+apache+cgi leveraging gnuplot. If you'd rather spend your valuable time deepening your electronics knowledge append to a csv file exported from your pi that you can load into Calc on the laptop.

[Last time I did a similar store+visualise exercise (to solve an argument about who was using our broadband data allowance in a shared house) I used mysql+apache+Perl with the GD::graph module but perl is a mucky language and all a bit 15 years ago now]

 

[Trundlebug]

Hmm I can't help thinking the OP is trying to do something very straightforward in a very complicated way.

Let me get this right, you want to spend ages setting up some kind of network, programming it to record all manner of data, then convert the data to another kind for more efficient storage because it feels right, then analyse it and program it further so that if anything goes out of set parameters it will notify you from a central location?

I have a helm in my boat, and all the key equipment is linked into it so I can control everything from there. If anything goes outside set parameters an alarm (or several alarms) sounds. Many of these are adjustable.
Am I missing something, other than the long winded data storage, programming and interfacing?

 

[GHA]

Am I missing something, other than the long winded data storage, programming and interfacing?

Yes, there is a little cheap microcontroller doing a job of getting gps, ais , wind and other data to the nav computer which broadcasts it over wifi , primarily so I can have opencpn with ais running in the cockpit on a waterproof xperia tablet, only other display there is a raymarine depth/log. And a vhf ram mic.

Loads cheaper than any off the shelf alternatives.

Not that difficult and as all the data is going through the arduino microcontroller anyway it's simple to record it all, just a few lines of code. It's not long winded, it's mostly done already. I'm just not sure of the pros & cons of different storage formats & manipulating the data afterwards, which is fun .

So why not?

 

[prv]

Data are "stuff". Liveaboard philosophy is surely "Do I *need* these?

Well, ish. Unlike possessions on a liveaboard's boat, more data within reason doesn't take up more physical space. Over the last 20 years or so, storage capacity has been growing faster than I've been generating personal data, so that my new iMac's home directory includes Old.dmg, a complete copy of my old Mac Mini, which contains ~/Old/ which is a complete copy of the Macbook before it, which contains ~/Old/ which was the Powerbook before that, back through several iterations of Linux, my university Solaris account, a Windows ME laptop, right back to a directory containing files off the floppy disks I used with an already-obsolete Amstrad PPC640 to type up projects in the fourth form at school.

Carrying 20 years' digital baggage around doesn't cause me any difficulty in the way that the all physical possessions from those years would.

Pete

 

[Hydrozoan]

... Since all that data is there for the taking it seems silly not to record it. ...

Is that the IT equivalent of 'It might come in handy sometime.'?

 

[adwuk]

I have a helm in my boat, and all the key equipment is linked into it so I can control everything from there. If anything goes outside set parameters an alarm (or several alarms) sounds. Many of these are adjustable.

That's a bit like saying that aircraft pilots have all of the instruments they need right in front of them while they are flying. So why do Rolls Royce, Boeing and Airbus go to so much trouble to create their own data and send it back to base for analysis? Not saying that an individual should create gigabytes of data for each trip down the coast, but some might be useful. And if the OP has some fun in the process, so much the better!

 

[GHA]

Is that the IT equivalent of 'It might come in handy sometime.'?

Not really, it just seems so silly and lazy not to.

It might come in handy

Anyway, accurate numbers from the real world are just great.

Don't you keep a log?

 

[laika]

Carrying 20 years' digital baggage around doesn't cause me any difficulty in the way that the all physical possessions from those years would.

It's philosophy, not practicality. I've got room for random crap in my bilges but I prefer to keep them clear. If you have it you will always have to worry about transferring it between media when doing a physical cleanup, worrying if you should be backing it up and wasting a bit of time wondering whether you should really delete it. Throwing things away is liberating. Not, of course, *that* file on the usb stick which should remain in the envelope with your lawyer who has instructions to mail it should any "accident" happen to befall you...

When quoting Randall Monroe never forget how old he isn't. "High School Zip Disk" will date form this century.

 

[mlines]

A quick look at your server code suggests that it would be vulnerable to a SQL insertion attack. Creating SQL strings by concatenation is dangerous (see https://xkcd.com/327/ for an amusing take on this!).

Of course, if this is entirely on a firewalled internal network, then it probably isn't a real concern, but it is bad practice.

Well its me as an amateur playing around and coding on the extreme of my ability so it is bound to be completely full of bad practice and all advice is gratefully received! Not 100% sure what you mean by the concatenation part , is it this

$query = "INSERT INTO DATABASE VALUES( '', '$value0', '$value1', '$value2', '$value3', '$value4', UNIX_TIMESTAMP());";

rather than the concatenate statements on the Arduino?

 

[Hydrozoan]

... Anyway, accurate numbers from the real world are just great.

Don't you keep a log?

A log is one thing .... But you're right about accurate numbers from the real world - just think of the fun numbers you (or your financial controller) could derive if you put all your boating costs on this putative database, too. :ambivalence

 

[GHA]

A log is one thing .... But you're right about accurate numbers from the real world - just think of the fun numbers you (or your financial controller) could derive if you put all your boating costs on this putative database, too. :ambivalence

????

Now why would you want to do that? It's not easy.

There seems to be a lot of negativity for doing something which is easy, just a few lines of code and an SD card - I don't understand why anyone *wouldn't*?? It's so obvious, IMHO. The data might hardly ever get looked at but so what, it's an easy thing to do so why not do it??

 

[prv]

When quoting Randall Monroe never forget how old he isn't.

I've formed the impression he's about the same age as me...


...google google...


..ok, two years younger, same age as my brother. Close enough

Pete

 

[prv]

Not 100% sure what you mean by the concatenation part , is it this

$query = "INSERT INTO DATABASE VALUES( '', '$value0', '$value1', '$value2', '$value3', '$value4', UNIX_TIMESTAMP());";

Yes. What will happen if I submit to your webserver a $value4 consisting of foo'); DROP TABLE DATABASE; ? Work out what your $query string will end up being...

Is your webserver open to the whole Internet, or is there some kind of password that only your Arduino knows? There are thousands of machines out there patiently trying out automated attacks against every server they can find...

Pete

 

[Hydrozoan]

????

Now why would you want to do that? It's not easy.

There seems to be a lot of negativity for doing something which is easy, just a few lines of code and an SD card - I don't understand why anyone *wouldn't*?? It's so obvious, IMHO. The data might hardly ever get looked at but so what, it's an easy thing to do so why not do it??

I'm sorry - adding financial data was meant only in jestful response to your 'accurate numbers from the real world'! I can well see the fun that one might get from doing what you propose, and if it gives you both the pleasure and the learning experience of how to do it, I say 'Go for it!'. I think my attitude is perhaps partly an age thing - you end up having to sort out what you keep. So whilst I hear people say that keeping data costs next to nothing, I'm now rather with what (if I understand him correctly) laika is saying: stored data still has to be managed into the future, or it's essentially useless anyway.

But if you do proceed, have fun with it! :encouragement:

 

[prv]

If you have it you will always have to worry about transferring it between media when doing a physical cleanup,

Sure, but fortunately data transfer speeds are increasing along with disk sizes. I just plugged a Thunderbolt cable between the two Macs, booted the Mini in Target Disk Mode, and left them overnight for the iMac to slurp the Mini's brains into a read-only disk image. I've no idea how long it took, may only have been half an hour for all I know. It was done in the morning.

worrying if you should be backing it up

No worrying required - the answer is "yes"

Moore's Law and its storage and transfer analogies mean that the "huge pile of data" from a few years ago has always become a manageable amount of data when the time comes to move it on.

wasting a bit of time wondering whether you should really delete it.

Again, no wondering required - the answer is always "no"

Not, of course, *that* file on the usb stick which should remain in the envelope with your lawyer who has instructions to mail it should any "accident" happen to befall you...

There is an envelope deposited in my parents' safe, but it contains only handwritten encryption keys rather than actual data

Pete

 

[GHA]

I'm sorry - adding financial data was meant only in jestful response to your 'accurate numbers from the real world'! I can well see the fun that one might get from doing what you propose, and if it gives you both the pleasure and the learning experience of how to do it, I say 'Go for it!'. I think my attitude is perhaps partly an age thing - you end up having to sort out what you keep. So whilst I hear people say that keeping data costs next to nothing, I'm now rather with what (if I understand him correctly) laika is saying: stored data still has to be managed into the future, or it's essentially useless anyway.

But if you do proceed, have fun with it! :encouragement:

Ooops, I meant to put a smilie after the "it's not easy" to show i got the joke.

Data feng Shui. That will be the next big thing.

i shall be available to line up your hard drive inner mystical energies for a harmonious boot up process.