Differences

This shows you the differences between two versions of the page.

--- emrp2021:start [2022/03/05 05:45] – [Table] sinan001
+++ emrp2021:start [2023/01/05 14:38] (current) – external edit 127.0.0.1
@@ Line 290: / Line 290: @@
 {{:emrp2021:ta_connection.png?600|}}
-| //Figure 43: Table "ta_connection"//  |
+| //Figure 44: Table "ta_connection"//  |
 == 4.3.2 Views and Trigger ==
@@ Line 297: / Line 297: @@
 Beside the tables there are also two views which serve as bases for Dash Plotly. "vi_last_meas" has the last measurement for each microcontroller.
 {{:emrp2021:vi_last_meas.png?700|}}
-| //Figure 44: View "vi_last_meas"//  |
+| //Figure 45: View "vi_last_meas"//  |
 "vi_prob_meas" has the latest problematic record for the microcontrollers. In case of missing sensor measurements, the number of missing measurements is displayed in the last column.
 {{:emrp2021:vi_prob_meas.png?700|}}
-| //Figure 45: View "vi_prob_meas"//  |
+| //Figure 46: View "vi_prob_meas"//  |
@@ Line 308: / Line 308: @@
 {{:emrp2021:trigger.png?700|}}
-| //Figure 46: Insert-Trigger after each insert//  |
+| //Figure 47: Insert-Trigger after each insert//  |
 ==== 4.4 Dash Plotly ====
 Plotly develops Dash and also offers a platform for writing and deploying Dash apps in an enterprise environment.
 {{:emrp2021:dashplotly001.png?600|}}
+| //Figure 48: Dash User Guide //  |
@@ Line 351: / Line 350: @@
 {{:emrp2021:step001.png?600|}}
+| // Figure 49: Import Libraries // |
 When we initialize Dash, we call the Dash class of dash. After that is done, we create an HTML div using the Div class from dash_html_components. Dash_html_component has all HTML tags, and dash_core_components has Graph, which renders interactive data visualizations by using plotly.js. The graph is used to create graphs on our layout. Dash also allows you to style the graph by changing colors for the background and text. Graph classes expect a figure object with the data to be plotted and the layout details. If you use the style attribute and pass an object with a specific color, you can change the background and so on.
@@ Line 357: / Line 358: @@
 {{:emrp2021:step002.png?600|}}
+| // Figure 50: Dash Layout // |
@@ Line 364: / Line 367: @@
 {{:emrp2021:alltrashbins.png?600|}}
+| // Figure 51: All Trash Bins Located in Moers // |
 in the above figure, you will be able to see all implemented trashbin with all information about them as ( location of trashbin, trashbin id)
-In the below figure we display only the measurements of all actively implemented sensors upon the request of the user which is an input to the call back function that we use.
+As can be seen in the below image, we display only the latest measurements from the active sensors upon request of the user, which we use as an input to a call-back function.
-{{:emrp2021:activesensor.png?600|}}
+{{:emrp2021:active_sensors.png?400|}}
+| // Figure 52: Last Measurements // |
-In the below figures you will show more detailed information about our project
+In the below figure you will show more detailed information about our project
+{{:emrp2021:last_measurement.png?400|}}
-{{:emrp2021:inactive.png?600|}}
+| // Figure 53: Problemetic Measurements // |
-{{:emrp2021:specifiedchannel.png?600|}}
@@ Line 386: / Line 387: @@
 {{:emrp2021:step004.png?600|}}
+| // Figure 54: Main  Function // |
 For our project, this is not enough for that reason we do some extra programming stuff that allows us to grab data from the database and display it. So for that, we prepared the following queries script file which facilitates our working and allows us to be connected to our own database which is built-in progress as seen below
 {{:emrp2021:database_connection.png?400|}}
+| // Figure 55: Database Connection // |
 then we define some functions which allow us to get needed information from the database as you will see in the figures below
 {{:emrp2021:functions001.png?400|}}
+| // Figure 56: Queries Part 1 // |
 {{:emrp2021:functions002.png?400}}
+| // Figure 57: Queries Part 2 // |
 And now we can say that everything is done regarding the dash plotly part in our project.
+===== 5. Dynamic pivot and Dash Plotly =====
+In the representation of the second map presented so far, which contains the last measurements for each device, only the last filling levels are taken into account. All other measured values are not considered. In order to be able to display all measured values and also new values based on new sensors, two basic requirements must be met. First, the measured values must be aggregated and then pivotoized. In the second point, it must be ensured that if a measurement type is added, this is also dynamically taken into account in the pivoted representation.
+After an intensive research we have found a prescribed function which is able to fulfill our requirements with few restrictions. The original function which can also be found under the link in the last section creates temporary tables which are deleted after execution. In our approach we have changed this point. A table is created and based on this table a view was created.6 parameters are passed. The first parameter is the name of the new view. The second parameter is the query used for the table of the view.  The third parameter contains columns that represent the reference columns that will be used for pivoting. New columns are specified by using the fourth parameter. The last two parameters define the content which can be found in the new columns and it is also possible to define a order for all columns. The only disadvantage is that the table used for the pivotized view has to be deleted every time the function is called, the same is true for the view.
+Dash Plotly had to be adapted as well. Every time the map is updated for the latest readings, the function must also be called to create a new table and view. In our case it is the function 'db_exec'.
+{{:emrp2021:db_exec.png?600|}}
+|//Figure 58: Funtion db_exec//|
+The function has no parameters but serves to call the original function 'colpivot' with the parameters. The nesting of the functions was done because the specification of the parameters in Python is very complex.
+{{:emrp2021:dp_include_function.png?600|}}
+|//Figure 59: Call of the function db_exec//|
+As a result, it is now possible to call up the last measured values for each device in a pivoted manner, instead of having to decide on a measurement type as in the previous version, it is now possible to call up the last date and the corresponding measurements for all devices.
+{{:emrp2021:all_measurements.png?990|}}
+|//Figure 60: last measurement per device//|
+In order for it to work, all columns must always be displayed, instead of defining only some columns statically, as was done in the previous version.
+{{:emrp2021:choose_all_columns.png?600|}}
+|//Figure 61: choose all columns//|
-===== 5. Links and Tutorials =====
+===== 6. Links and Tutorials =====
+  * __CODE used in the project:__ **https://github.com/ubaidadib/EMRP_WS_2021_Project**
   * Mix-Playlist about different topics: https://www.youtube.com/playlist?list=PL2SRmCaIeDVibo6IUItyKcmDCH955hqAT
   * Link for ttn: https://www.thethingsnetwork.org/
   * SQL-Querries (Postgresql) in Node-Red: https://flows.nodered.org/node/node-red-contrib-postgresql/in/MFnap-qr-MJE
   * TTN, MQTT Node-REd: https://www.thethingsindustries.com/docs/integrations/mqtt/
+  * dynamic pivoting using Postgresql PL/PGSQL: https://github.com/hnsl/colpivot