Differences

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

--- emrp2021:start [2022/03/09 18:45] – [5. Dynamic pivot and DashPlotly] sinan001
+++ emrp2021:start [2022/03/10 22:54] (current) – [6. Links and Tutorials] sinan001
@@ Line 405: / Line 405: @@
 And now we can say that everything is done regarding the dash plotly part in our project.
-===== 5. Dynamic pivot and DashPlotly =====
+===== 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.
+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//|
 ===== 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