Simplest DB storing controller



  • Got a need of 1-15 sensors transmitting in to a ethernet connected gateway - then storing these in a database for nice reporting via other tools.. As I look around I can see quite complex controllers which are suitable for controlling home automation. How about the pure simple sensor reading controllers? If I would want a "controller " that would connect to a ethernet gateway via my site router/firewall - picking up data and storing in a database. It would be neat to have something like a php / mysql solution running on an internet based webhotel. Are there any solutions already that would enable this? Primarely I would be using a series of temperature / humidity sensors and electrical power meter Any sensors..

    A first idea from my side would be to setup a php MGTT client that would pull the data and store it. But it feels like I would be inventing the wheel again here 🙂


  • Hero Member

    But it feels like I would be inventing the wheel again here

    Yes, I think so 😉

    Most controllers started simple and grew to what they are by now. Yes, it will take time to come up-to-speed with a controller but making your own scripts will for sure take more time. The good thing about doing things yourself is that you can do it your own way, but many controllers are open-source so you can also hack them.



  • I've been working on my own controller for the last several months as a side project (https://github.com/dretay/seneschal) and @daulagari is right... trying to build something end-to-end can be a huge undertaking. I ended up building my own controller mostly because i couldn't find one that integrated all the different IOT devices I'd bought for my house and I didn't want to host my data on another company's computers.

    One thing that may not be immediately obvious is how much compute power you will need for your controller. For example i have 8 temperature / humidity sensors and they currently generate about 40,000 readings a month. I ended up having to move my database server off a raspberry pi and onto a desktop because it requires significantly more resources to be able to query / aggregate 10's of thousands of data points than it does to simply store the readings .

    Since i just went through this if you do end up building your own controller I'd be happy to answer any questions you may have.



  • OK well I guess my need really is an IOT storing solution where the server polling functionality would be running on an external web hotel where I can purchase the uptime needed for the server/storage.

    Most of the controllers seems to have third party software needed in order to work. It feels like in the long run - this solution would be an ongoing development/configuration project for both sensors and the server bits. It would be nice not to have 10 different softwares + a dedicated "closet server" just to fill a simple database with 10 sensors data.

    Haven't seen any controller that would work to host on for instance a servage web hotel. Got any tips for which solution that might fit me there?



  • @ltz

    Forgot to add - Got several different buildings in different cities that would have sensor data and I would not like to have more then the sensors + gateways in each house/estate,


  • Hero Member

    Looks like you are looking for something like the MySensors cloud service, see http://www.mysensors.org/build/raspberry but I guess that will not happen soon.

    Not sure if there is a way to do something like SSL, but if so you could use the Ethernet gateway and connect to some kind of "closest server".


  • Hero Member

    There are several IOT services out there to which you could send your sensor data - for example see the list here https://iotdb.org/social/imadeit/post/95279811682/iot-datastore-roundup - to which the MySensors cloud service will one way be added perhaps.

    If you want to run your own, one question is whether you want to retain all data forever, or just a rolling buffer of more recent data. For the latter, something like RRDtool might be more efficient of time and space then a relational database. For full generality a relational database would work, but do calculate how big it's going to get and think of your usage patterns - do you need arbitrary queries?

    Using something like MQTT might be helpful as an intermediary between the sensor network and a database or datastore; it could also make it possible to run both Home Automation and data logging from the same network. For example, if the sensor network provides a broker, both the HA software and the logging software can subscribe to the relevant topics.


 

212
Online

8.5k
Users

9.3k
Topics

98.4k
Posts