Geekzone: technology news, blogs, forums
Guest
Welcome Guest.
You haven't logged in yet. If you don't have an account you can register now.




6 posts

Wannabe Geek


Topic # 229048 5-Feb-2018 10:58
Send private message quote this post

So I'm attempting on creating an irrigation system at home using an Arduino with Wireless + Solenoid valve and a few sensors. I wanted to create a small db to stores sensor data. The data would record at 1 min intervals so it wouldn't need to be a grunty server. My main question is, what would be the most cost effective, low power, small factor database server. I was thinking of using a Raspberry PI running Raspbian connected to a 1TB external HDD which would be under < $200, alternatively I could go for something more scale-able and get a small Celeron/i3 Intel NUC. Has anyone had a similar project/dilemma?


Filter this topic showing only the reply marked as answer Create new topic
What does this tag do
920 posts

Ultimate Geek
+1 received by user: 185

Subscriber

  Reply # 1951881 5-Feb-2018 11:12
One person supports this post
Send private message quote this post

How long a history do you want to keep?

 

You could look at using Home Assistant on a Raspberry Pi, log to it using MQTT

 

Alternatively, various ways of getting the Arduino to log directly to a cloud based MQTT broker  (or something like ThingSpeak)




6 posts

Wannabe Geek


  Reply # 1951887 5-Feb-2018 11:20
Send private message quote this post

I was actually thinking of connecting my Arduino to my wireless network (same with the db server) then using this library to connect to the MySQL server.

 

https://www.arduinolibraries.info/libraries/my-sql-connector-arduino


 
 
 
 


1622 posts

Uber Geek
+1 received by user: 182

Subscriber

  Reply # 1951889 5-Feb-2018 11:25
One person supports this post
Send private message quote this post

+1 for MQTT. Probably not the simplest solution, but would be the most flexible IMHO. Your Arduinos can easily publish to a local MQTT broker (there are plenty of guides/tutorials about how to do this). Then once you have the data coming into your broker, which can happily run on a RPi, you have all sorts of options.

 

Personally I would run mqttwarn (https://github.com/jpmens/mqttwarn) which is a simple python script which will monitor MQTT topics and forward the messages onto different services. There are loads of options including multiple database options, even Google Docs Spreadsheet. 

 

Using this sort of architecture you can easily change the way you log your data without having to touch your Arduinos. 

 

Personally I run InfluxDB which is a database designed specifically for time series data, so is very efficient at storing regular (i.e. sensor) data. Not sure if it runs on a RPi tho, it didn't used to but that was a while ago.




6 posts

Wannabe Geek


  Reply # 1951893 5-Feb-2018 11:35
Send private message quote this post

Looking further into your solutions, maybe it'll be more hassle free implementing an MQTT broker on the PI (using Mosquito) and then using mqttwarn to forward the data onto an InfluxDB rather than fiddling with the MySQL lib. The GitHub repo you mentioned has some superb instructions which should make configuring it a piece of cake. Now I guess it's time to look for a unwanted Pi


1622 posts

Uber Geek
+1 received by user: 182

Subscriber

  Reply # 1951895 5-Feb-2018 11:39
Send private message quote this post

I might be able to help you with that ;). I have a couple of RPi2s which have just become free, you are welcome to one of them for the cost of shipping if you are interested?


3729 posts

Uber Geek
+1 received by user: 436

Trusted

  Reply # 1951953 5-Feb-2018 12:13
Send private message quote this post

I'd be using influxdb as well (if for at home).  Another option is to use the http api on thingspeak and push your values there.





Previously known as psycik

NextPVR/OpenHAB: 
Gigabyte AMD A8 Brix --> Samsung LA46A650D via HDMI, NextPVR,OpenHAB with Aeotech ZWave Controller
Media:Chromecast v2, ATV4, Roku3, Raspberry PI temperature Sensors and Bluetooth LE Sensors,HDHomeRun Dual
Windows 2012 
Host (Plex Server/Crashplan): 2x2TB, 2x3TB, 1x4TB using DriveBender, Samsung 850 evo 512 GB SSD, Hyper-V Server with 1xW10, 1xW2k8, 2xUbuntu 16.04 LTS, Crashplan, NextPVR channel for Plex,NextPVR Metadata Agent and Scanner for Plex


226 posts

Master Geek
+1 received by user: 15


  Reply # 1953359 7-Feb-2018 22:39
Send private message quote this post

For what it is worth, In one of my lives, I build datalogging, control systems and environmental monitoring networks.
A couple of these are rainfall simulation with 200 nozzles, 32 valves and 48 relays.

The main problem for new players to this sort of thing is they focus on the end goal, "I want to see the data", and not on the basic operation and control.
My home system it has 4 zones, I purchased a off the shelf 4 way solenoid system and controller, why? Because running complicated systems that are automatic still require input, and if you don't keep an eye it when it breaks(and it will) you end up with a watery mess.

Concentrate on the irrigating system allowing for expansion, I'd even go so far to say start with an off the shelf system to work out what works for you and what doesn't. They are pretty limited, and frankly annoying when compared to advanced control systems however you learn what and how with them. Or if you are going Arduino get the control working first. Doing this allows you to understand what and how you want to record.

Data, everyone wants 1 minute data. Sounds fine, but I tell you now from someone who collects data for research, 1 minute period is really only good for short high resolution information because high resolution bogs databases files down, then retrieval can get just as bad. Noting that control at 1 minute intervals is a perfect period, but data for what is happening is perfect at 15 minute, hourly and daily. 15 minute works well, but even then you can get bogged down with data handling. I typically use daily and hourly these days, which bring me to a data handling problem.

Time stamps, one main problem for you to understand early is the time stamp. Daily time the value 12 midnight or 24 hours actually does not exist, neither does the top of an hour as this becomes the next hour, or for the day the next day.
What?? you say, just consider the time clock for a day is 0 to 23:59:59.9999, what does this mean?
Consider, Day 1, if you collect data for the day to take a total daily rain or irrigation volume, so at the end of the day output this total and midnight. This outputs Day 1 as Day 2 time stamp because midnight is zero which is the next day.
How you handle this is your choice, there is a few methods, just be aware of it when data doesn't appear to plot on the right day or hour as you expect.





Masterpiece Calibration Ltd, isf certified

 

www.mastercal.co.nz

 

 

"I'm not a robot!"

3275 posts

Uber Geek
+1 received by user: 207

Trusted

  Reply # 1953368 7-Feb-2018 22:58
Send private message quote this post

SumnerBoy:

 

I might be able to help you with that ;). I have a couple of RPi2s which have just become free, you are welcome to one of them for the cost of shipping if you are interested?

 

 

What were you planning to do with the other one? :D


1622 posts

Uber Geek
+1 received by user: 182

Subscriber

  Reply # 1953369 7-Feb-2018 23:00
Send private message quote this post

Planning to give it away, to someone worthy...

1203 posts

Uber Geek
+1 received by user: 111


  Reply # 1953792 8-Feb-2018 18:39
One person supports this post
Send private message quote this post


I would probably just write the data into a text file in csv format, then create a new file every day. The old files can be added to an archive to save space.





Software Engineer

 


Filter this topic showing only the reply marked as answer Create new topic



Twitter »

Follow us to receive Twitter updates when new discussions are posted in our forums:



Follow us to receive Twitter updates when news items and blogs are posted in our frontpage:



Follow us to receive Twitter updates when tech item prices are listed in our price comparison site:





News »

Intel reimagines data centre storage with new 3D NAND SSDs
Posted 16-Feb-2018 15:21


Ground-breaking business programme begins in Hamilton
Posted 16-Feb-2018 10:18


Government to continue search for first Chief Technology Officer
Posted 12-Feb-2018 20:30


Time to take Appleā€™s iPad Pro seriously
Posted 12-Feb-2018 16:54


New Fujifilm X-A5 brings selfie features to mirrorless camera
Posted 9-Feb-2018 09:12


D-Link ANZ expands connected smart home with new HD Wi-Fi cameras
Posted 9-Feb-2018 09:01


Dragon Professional for Mac V6: Near perfect dictation
Posted 9-Feb-2018 08:26


OPPO announces R11s with claims to be the picture perfect smartphone
Posted 2-Feb-2018 13:28


Vocus Communications wins a place on the TaaS panel
Posted 26-Jan-2018 15:16


SwipedOn raises $1 million capital
Posted 26-Jan-2018 15:15


Slingshot offers unlimited gigabit fibre for under a ton
Posted 25-Jan-2018 13:51


Spark doubles down on wireless broadband
Posted 24-Jan-2018 15:44


New Zealand's IT industry in 2018 and beyond
Posted 22-Jan-2018 12:50


Introducing your new workplace headache: Gen Z
Posted 22-Jan-2018 12:45


Jucy set to introduce electric campervan fleet
Posted 22-Jan-2018 12:41



Geekzone Live »

Try automatic live updates from Geekzone directly in your browser, without refreshing the page, with Geekzone Live now.



Are you subscribed to our RSS feed? You can download the latest headlines and summaries from our stories directly to your computer or smartphone by using a feed reader.

Alternatively, you can receive a daily email with Geekzone updates.