Storing Sensor Data for Analysis of the Office

Question

I have currently been tasked with designing an application that tracks several different measurements around the office, eg. the temperature, light, presence of people, etc. Having never really worked on data analysis before, I would like some guidance on how to store this data (which database design to use).

What we're looking at currently are around 50 sensors that only send data when an event of interest occurs: if the temperature changes by 0.5 degrees or if the light turns on/off or if a room becomes occupied/vacant. So, the data will only be updated every few seconds. Also, in the future, I'd like to analyse some of the data. Hence, the data must be persistent in the database. What kind of technologies would you suggest to carry out this task?

Answer 1

I have been doing similar project in my college. I have classroom and I'm supposed to collect data like temp, humidity, light, occupancy, etc. Assuming that you have worked with sensors and motes to use, I'm going to explain rest of the structure.

You need sensor network setup and like you said you have done it. These sensor networks generally do not send data directly over internet so you need a Gateway that can collect data from sensors and send it over internet to local server.

On server side you need REST API and you could use any language to develop it and I use PHP. I find it very easy to use and develop using PHP. This REST API shall receive data from Gateway and store it into database. I use mysql database because amount of data is not so big for us. But if your data is big enough you can use big data Nosql tool like mongoDB or so. Whatever type of database you use structure remains same.

For sending data from Gateway to server you can use protocols like HTTP or MQTT whichever you feel comfortable. What I do is I have WSN controller that sends data over USB to Gateway then Gateway sends data to server over Ethernet. So I had to develop USB to Ethernet Gateway. If you can just take two UART terminals out of your controller you can build UART to Ethernet Gateway using any microcontroller or even Arduino Ethernet shield would work in that case.

In my case data is sensed periodically but as you said you are sensing data when event of interest occurs then you can use poisson distribution method over periodically collected data to predict what is average number of events per day and then you can decide if your data is big or not.

Answer 2

The best choice for storage technologies will depend largely on how much data (in terms of bytes) you expect to accumulate over the lifetime of your project, so the first thing i would do is try to get some sample data, or make some educated guesses (e.g. how many bytes does 1 temperature recording take up X how many change events am I expecting per day X how many temperature sensors X how many days worth of data you want to store and analyse over time).

Once you have a rough idea of how much data you need to store and analyse, you can use that to start narrowing down your choices. There's no right answer, and others may disagree, but I would suggest that if you're dealing with anything less than terabytes of data, you don't need hadoop (I noticed that's a tag in your question) - hadoop is not really a data storage solution (although it does have it's own file system called HDFS or just DFS), it's more of a framework for processing and transforming huge quantities of data. Also if you don't have thousands of events per second to record, you probably don't need NoSQL solutions either.

For storage of structured data, given that you've never really done data analysis before, SQL databases are probably the way to go if you have gigabytes or less, and SQL will be easier and more useful to learn - it's mature, been around for ages and is still the go-to standard in most industries, so there are plenty of learning resources. Maybe try out MySQL Community Edition server (free, open source) as a start, I would also recommend the MySQL Workbench to help you get started (a bunch of GUI tools you can use to mess around with SQL when learning)

PS I don't know anything about capturing signals from sensors, so maybe there are more appropriate technologies which I'm not aware of!

Answer 3

Echoing @James Allen's suggestion, the total amount of data at any point in time is important to know. It will tell you how much disk you need. Also important is the rate at which new data will be received and old data deleted as this will be limited by the IOPS of your hardware

From your use case any popular DBMS on commodity hardware should suffice. Something like PostgreSQL can handle terabytes of data at hundreds of transactions per second on a run-of-the-mill four core server.

Have a look at the R programming language for analysis. SQL is great for reads and writes but a histogram or linear regression is very tricky in comparison.