Software apps and online services
This project was developed as part of the AWS IoT Virtual Hackathon (July 13th - 18th 2017). We - a team from AXA Germany's Data Innovation Lab - wanted to experiment with AWS IoT & sensor data to see how fast one can set up a working solution with only basic knowledge about AWS!
At AXA Germany we want to work in a new and more collaborative way (at least for insurance companies), where there are no fixed desks but flexible and variable workstations.
Thus, in order to exchange and develop ideas together, nobody is entitled to call a desk his own anymore - regardless of whether you are an employee, a manager or even a member of the board!
However, the concept of open and variable workspaces also raises some questions for the data-savvy mind:
- How many percent of the available desks are occupied (for a given period of time or at the moment)?
- Are there areas or desks that are highly popular while others are not used at all?
- How long has an individual desk been occupied on a given day?
- And notably, will everybody get a desk when coming to work?
Develop a prototypical presence detection for a sample of individual workstations, utilizing real time sensor data coming from Raspberry PIs (+ Sense HATs) and Amazon Web Services.
Here, (1) a web application or (2) a voice inquiry to Amazon's Alexa should indicate whether a sample desk is occupied or not.
Furthermore the captured streams of sensor data should be visualized as part of a real-time dashboard and stored to foster further historical analysis.
First, a Raspberry Pi with Sense HAT (pitch, pressure, humidity and temperature sensors) was attached to each of the four sample desks in the Data Innovation Lab.
The devices were set up and connected to AWS IoT through the MQTT protocol, utilizing the AWS IoT Device SDK for Python.
The following diagram shows the high-level architecture for the subsequent processing steps and involved AWS solutions, where the data ingested into the AWS IoT platform is further processed via Kinesis.
Kinesis Analytics was utilized to process the streaming data coming from the Sense HAT sensors in real time with standard SQL. The script below simply creates a binary coding, repesenting whether a desk is occupied or not.
-- Taking a "branch" from the continuous stream of data -- Here all columns already have to be defined CREATE OR REPLACE STREAM "DESTINATION_SQL_STREAM" ( device VARCHAR(1) , max_roll DOUBLE , min_roll DOUBLE , sitting_bool DOUBLE ); -- This is another statement needed to actually create the table in some S3 location. CREATE OR REPLACE PUMP "STREAM_PUMP_1" AS INSERT INTO "DESTINATION_SQL_STREAM" SELECT STREAM "device" , MAX("roll") OVER TEN_SECOND_SLIDING_WINDOW AS max_roll , MIN("roll") OVER TEN_SECOND_SLIDING_WINDOW AS min_roll , CASE WHEN MAX("roll") OVER TEN_SECOND_SLIDING_WINDOW - MIN("roll") OVER TEN_SECOND_SLIDING_WINDOW > 0.05 THEN 1 ELSE 0 END AS sitting_bool FROM "SOURCE_SQL_STREAM_001" -- Results partitioned by device and a 10-second sliding time window WINDOW TEN_SECOND_SLIDING_WINDOW AS ( PARTITION BY "device" RANGE INTERVAL '30' SECOND PRECEDING);
Therefore, it evaluates the last 30 seconds of the stream and compares the MIN and MAX of the roll values transmitted from a given Raspberry Pi (see below). If the value is greater than the threshold derived within a separate local analysis of experiment data, it will suggest that somebody is sitting at the desk.
To put it briefly, a desk will be seen as occupied when the vibration caused by typing (or similiar) is higher than on an empty desk.
The processed and enhanced sensor data (occupation dummy, time stamps etc.) from each sample desk was then written into a relational database in AWS RDS, which in turn will be read out by a web application in AWS Elastic Beanstalk.
As can been seen in the graph below, the application indicates whether one of the sample desks is currently occupied or not.
In addition, a prototypical Alexa 'Roomplaner' skill has been developed. It replies to the question whether a free desk is available, by naming the desks (a,b,c,d) which are free at the moment (see video below).
Within the course of the AWS IoT Virtual Hackathon we developed various ideas how to utilize IoT devices and AWS within corporate office environments.
The idea of focusing on AXA Germany's new workplace concept keeps its promises: relevant analytical questions combined with an ease in potential implementation led to fast results and a steep learning curve with AWS.
Though simple in implementation, the prototype outlined above carries the potential to answer a series of highly relevant questions in the future. Given the collection of historical data, one could for example investigate, which less-desirable workspaces should be enhanced or upgraded or how workspaces could be aligned to changing work environmens and needs.
Roland Scharrer, Sven Ahlers, Peter Thesling, Moritz Knorr, Mario Classen, Mark Klaisoongnoen
Data Innovation Lab | AXA Germany