{"id":11,"date":"2014-04-20T16:31:53","date_gmt":"2014-04-20T16:31:53","guid":{"rendered":"http:\/\/www.sqldashboards.com\/b\/?p=11"},"modified":"2014-04-20T16:37:25","modified_gmt":"2014-04-20T16:37:25","slug":"real-time-charting-electrical-sensor-data","status":"publish","type":"post","link":"https:\/\/www.sqldashboards.com\/b\/real-time-charting-electrical-sensor-data\/","title":{"rendered":"Real-Time Charting of Electrical Sensor Data"},"content":{"rendered":"<p>Let&#8217;s look at how we can display real-time and historical sensor data for Household Electrical usage using <a href=\"http:\/\/www.sqldashboards.com\">sqlDashboards<\/a>.<\/p>\n<p>The historical data is taken from the <a href=\"http:\/\/archive.ics.uci.edu\/ml\/datasets\/Individual+household+electric+power+consumption\">UCI Machine Learning Repository<\/a>. This is a single CSV file with 1 minute readings between December 2006 and November 2010. i.e.<\/p>\n<h2>The Sensor Data Sql Dashboard<\/h2>\n<p><img loading=\"lazy\" src=\"\/images\/sql-sensor-data-gif-large.gif\" height=\"478\" width=\"752\" alt=\"sql dashboard of household electric sensor data\"\/><\/p>\n<h2>Time Series Data Format<\/h2>\n<p>The data came in the following format:<\/p>\n<table>\n<tr>\n<th>date<\/th>\n<th>time<\/th>\n<th>Global_active_power<\/th>\n<th>Global_reactive_power<\/th>\n<\/tr>\n<tr>\n<td>2006-12-16<\/td>\n<td>17:24:00<\/td>\n<td>4.216<\/td>\n<td>0.418<\/td>\n<\/tr>\n<tr>\n<td>2006-12-16<\/td>\n<td>17:25:00<\/td>\n<td>5.36<\/td>\n<td>0.436<\/td>\n<\/tr>\n<tr>\n<td>2006-12-16<\/td>\n<td>17:26:00<\/td>\n<td>5.374<\/td>\n<td>0.498<\/td>\n<\/tr>\n<\/table>\n<p> Where the columns have the following meaning:<\/p>\n<ul>\n<li>date: Date in format dd\/mm\/yyyy<\/li>\n<li>time: time in format hh:mm:ss<\/li>\n<li>global_active_power: household global minute-averaged active power (in kilowatt)<\/li>\n<li>global_reactive_power: household global minute-averaged reactive power (in kilowatt)<\/li>\n<li>voltage: minute-averaged voltage (in volt)<\/li>\n<li>global_intensity: household global minute-averaged current intensity (in ampere)<\/li>\n<li>sub_metering_1: energy sub-metering No. 1 (in watt-hour of active energy). It corresponds to the kitchen, containing mainly a dishwasher, an oven and a microwave (hot plates are not electric but gas powered).<\/li>\n<li>sub_metering_2: energy sub-metering No. 2 (in watt-hour of active energy). It corresponds to the laundry room, containing a washing-machine, a tumble-drier, a refrigerator and a light.<\/li>\n<li>sub_metering_3: energy sub-metering No. 3 (in watt-hour of active energy). It corresponds to an electric water-heater and an air-conditioner.\n<\/ul>\n<h2>Creating the SQL Dashboard Charts<\/h2>\n<p>To create the live charts I:<\/p>\n<ol>\n<li>Imported the data to MySQL database.<\/li>\n<p>[raw]<br \/>\n<textarea rows=\"8\" cols=\"100\" class='code' id='mysql-eleectric-code-13'><br \/>\nCREATE TABLE `power` (<br \/>\n  `date` date NOT NULL,<br \/>\n  `time` time NOT NULL,<br \/>\n  Global_active_power double DEFAULT NULL,<br \/>\n  Global_reactive_power double DEFAULT NULL,<br \/>\n  Voltage double DEFAULT NULL,<br \/>\n  Global_intensity double DEFAULT NULL,<br \/>\n  Sub_metering_1 double DEFAULT NULL,<br \/>\n  Sub_metering_2 double DEFAULT NULL,<br \/>\n  Sub_metering_3 double DEFAULT NULL);<\/p>\n<p>LOAD DATA INFILE &#8216;household_power_consumption.txt&#8217; INTO TABLE power FIELDS TERMINATED BY &#8216;;&#8217;  IGNORE 2075000 LINES  (@Date,Time,Global_active_power,Global_reactive_power,Voltage,Global_intensity,Sub_metering_1,Sub_metering_2,Sub_metering_3)  SET Date = str_to_date(@Date, &#8216;%d\/%m\/%Y&#8217;);<br \/>\n<\/textarea><script type='text\/javascript'>var myCodeMirror = CodeMirror.fromTextArea(document.getElementById('mysql-eleectric-code-13'), {lineNumbers: true, matchBrackets: true, indentUnit: 4, tabMode: 'default', mode: 'text\/x-plsql', readOnly:true });<\/script><br \/>\n[\/raw]<\/p>\n<li>Created a java process to insert live sensor data.<\/li>\n<li>Opened <a href=\"http:\/\/www.sqldashboards.com\">sqlDashboards<\/a><\/li>\n<li>Added a time series monthly graph and using the sql query:<br \/>\n       <br \/><code class='console'>select STR_TO_DATE(DATE_FORMAT(Date, '%Y-%m-01'),'%Y-%m'),AVG(Global_active_power),AVG(Global_reactive_power) FROM power GROUP BY DATE_FORMAT(Date, '%Y-%m-01')<\/code>\n   <\/li>\n<li>Added a daily time series rollup graph and using the sql query:<br \/>\n       <br \/><code>CREATE TABLE daily AS select Date,AVG(Global_active_power),AVG(Global_reactive_power) FROM power WHERE Date>'2009-11-11' GROUP BY Date;<\/code>\n   <\/li>\n<\/li>\n<li>Added a bar chart showing the different meter readings as bar charts per year:<br \/>\n       <br \/><code>select CAST(YEAR(Date) AS CHAR(4)) AS Year,AVG(Sub_metering_1),AVG(Sub_metering_2),AVG(Sub_metering_3) from power GROUP BY YEAR(Date)<\/code>\n   <\/li>\n<li>Added further queries to show the live data for today&#8230;<\/li>\n<\/ol>\n<p>This gave me the final dashboard:<br \/>\n<img loading=\"lazy\" src=\"http:\/\/www.sqldashboards.com\/b\/wp-content\/uploads\/2014\/04\/time-series-sensor-data.png\" alt=\"Sensor Data time Series Sql Charts\" width=\"755\" height=\"480\" class=\"alignnone size-full wp-image-15\" srcset=\"https:\/\/www.sqldashboards.com\/b\/wp-content\/uploads\/2014\/04\/time-series-sensor-data.png 755w, https:\/\/www.sqldashboards.com\/b\/wp-content\/uploads\/2014\/04\/time-series-sensor-data-300x190.png 300w\" sizes=\"(max-width: 755px) 100vw, 755px\" \/><\/p>\n<p>[raw]<br \/>\n<!--\n\n\n\/\/ kdb hackery\n\/ faked tables for todays power usage and usage by sector\npowerUse:([] t:10:00+til 60; volts:1 _ {x*rand[0.2]+0.9}\\[60;1.0]);\nsector:([] t:11:00; grp:`suburb`rural`industrial; use:40+3?10.);\n\n\/ add rows for time + 1 minute\nincPowerUse:{`powerUse insert update t+1,volts*rand[0.2]+0.9 from select[-1] from powerUse};\nincSector:{`sector insert 0!update use:use*rand[0.04]+0.96 0.955 0.965+3?0.04,t+1 from select by grp from sector;};\n\n\/ add on timer\n.z.ts:{ incPowerUse[]; incSector[]; };\n.z.ts[];\nsystem \"t 1000\";\n\/ query to use in showing sector movement\nselect last grp,last t,last use,change:last[use]-first -5 sublist use by grp from sector;\n--><br \/>\n[\/raw]<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Let&#8217;s look at how we can display real-time and historical sensor data for Household Electrical usage using sqlDashboards. The historical data is taken from the UCI Machine Learning Repository. This is a single CSV file with 1 minute readings between December 2006 and November 2010. i.e. The Sensor Data Sql Dashboard Time Series Data Format [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[7],"tags":[9,4,3,8],"_links":{"self":[{"href":"https:\/\/www.sqldashboards.com\/b\/wp-json\/wp\/v2\/posts\/11"}],"collection":[{"href":"https:\/\/www.sqldashboards.com\/b\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqldashboards.com\/b\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqldashboards.com\/b\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqldashboards.com\/b\/wp-json\/wp\/v2\/comments?post=11"}],"version-history":[{"count":9,"href":"https:\/\/www.sqldashboards.com\/b\/wp-json\/wp\/v2\/posts\/11\/revisions"}],"predecessor-version":[{"id":21,"href":"https:\/\/www.sqldashboards.com\/b\/wp-json\/wp\/v2\/posts\/11\/revisions\/21"}],"wp:attachment":[{"href":"https:\/\/www.sqldashboards.com\/b\/wp-json\/wp\/v2\/media?parent=11"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqldashboards.com\/b\/wp-json\/wp\/v2\/categories?post=11"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqldashboards.com\/b\/wp-json\/wp\/v2\/tags?post=11"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}