Chapter 4 ■ Data transformationFor example, MySQL provides additional controls for floating-point data types. That is, you can set the
number of whole digits as well as the number of decimals. You can do this with the float(m,d) syntax where
the m is the maximum number of digits and d is the number of decimals. Use this syntax when you need to
limit the size or display of floating-point numbers in MySQL.
Adding Derived or Calculated Data
Another common annotation is adding additional data to the row that is a derivation or calculation of the
original value. This includes values that are converted to change a unit of measure (Fahrenheit to Celsius),
enumeration based on ranges or transposed in some manner (scale, precision), or calculated columns
(arithmetic operations) where the result has meaning.
For example, there may be sensor data generated for a group of events or sensors that are used together
with other sensors to provide a result or perhaps situations where you want to scale, divide, convert the data
to a new unit of measure, or otherwise modify a value. In these cases, we add the new derived or calculated
data to the row. Let’s consider you have a sensor that measures distance in inches but you need the data to
be millimeters.^6 You could convert the data to inches easily with the following formula:^7
value_millimeters = value_inches * 25.4;
Recall we never want to discard the original value, so we would save both the value in inches as well as
the derived value. Thus, we would write the data as follows in a now-familiar Python script excerpt:
strData = "Distance milimeters: {0:.2f}, Distance inches: {1:.2f} Datetime: {2}\n"
file_log = open("data_log_python.txt", 'a')
dist_inches = read_sensor(1);
dist_mm = dist_inches * 25.4;
file_log.write(strData.format(dist_mm, dist_inches))
file_log.close()
Another common derived column is the use of a column to store the amount that a value has changed
since the last reading. While it is possible to calculate this from saved data, adding an additional column to
store the value can make reading and understanding the data easier. To do this, we would have to save the
old value and calculate the change by subtracting it from the new value. A positive result is the amount the
value increased; a negative value is the amount the value decreased.
Let’s take a closer look at an example of common derived and calculated columns.
Code Implementation
Derived and calculated data (columns) will be implemented in a specific manner. That is, there will be
a precise formula or translation that needs to be performed. In the following example, we will see three
samples of derived and calculated columns that I have encountered in my own and others’ IOT solutions.
The first is a derivation for calibration. Sometimes sensors need to be calibrated and the result of the
calibration will determine a value (sometimes a formula if the difference is not linear) that must be added or
subtracted to get a more accurate value.
The second is an example of a simple calculation where we have a sensor that measures weight on a plate
or platter for a number of objects. In the example, the number of objects is fixed, but in most cases this will also
be a variable. Savvy IOT hobbyists and enthusiasts will note calculating the average weight of irregular objects
may not be interesting, so we assume the objects for this example are all similar in size and composition.
(^6) Or more likely inches to centimeters.
(^7) I avoid the quaint but over used temperature example. You’re welcome.