Live Data Computation Agent – Example 4

<< Click to Display Table of Contents >>

Navigation:  Live > Agents > Transformation Agents > Data Computation > Examples >

Live Data Computation Agent – Example 4

The following SERIES_FUNCTION_INFO converts pressure, in Pa, to water level elevation, in mH2O, for a given logger depth and reference elevation:

 

 

{
"sourceSeriesId": [
  130057
],
"value": "equis.logger_depth(@logger_id) - equis.unit_conversion(d.datum_value, 'Pa', 'mH2O', DEFAULT) - equis.measure_datum(@facility_id, @sys_loc_code, d.datum_utc_dt)"
}

 

 

equis.unit_conversion and equis.measure_datum are EQuIS functions, and equis.logger_depth can be added/updated using:

 

 

-- Returns the logger depth in dt_logger.custom_field_2 as a REAL.

 

IF OBJECT_ID(N'[equis].[logger_depth]') IS NOT NULL

DROP FUNCTION [equis].[logger_depth];

GO

 

CREATE FUNCTION [equis].[logger_depth]

(

 @logger_id BIGINT

)

RETURNS REAL

AS

BEGIN

DECLARE @logger_depth REAL;

 

SELECT

   @logger_depth = CAST(custom_field_2 AS REAL)

FROM

   dt_logger

WHERE

   logger_id = @logger_id;

 

RETURN @logger_depth;

END

 

 

This example assumes the computed series' DT_LOGGER.CUSTOM_FIELD_2 (logger depth) is '200'.

 

And the DT_LOGGER_DATUM rows for the source datum (where LOGGER_SERIES_ID is 130057) are:

 

DATUM_UTC_DT

DATUM_VALUE

2018-11-10 06:00:00

110000 (Pa)

2018-12-20 09:10:11

120000

2019-01-20 12:13:14

130000

 

And the DT_LOGGER_MEASURE (reference elevation) rows (where FACILITY_ID and SYS_LOC_CODE are the same as the computed series') are:

 

START_DATE

DATUM_VALUE

STEP_OR_LINEAR

2018-10-20 12:00:00

100 (m)

step

2019-01-03 06:00:00

130

step

 

And the RT_UNIT_CONVERSION_FACTOR row is:

 

REPORTED_UNIT

TARGET_UNIT

CONVERSION_FACTOR

STATUS_FLAG

Pa

mH2O

0.00010197442889221

A

 

Note: RT_UNIT rows for Pa and mH2O must be added before adding the RT_UNIT_CONVERSION_FACTOR row.

 

And there is no existing computed datum (nothing to delete, or update; only insert).

 

1.Create a table of date-times for the computed datum; add each source datum date-time:

 

DATUM_UTC_DT

2018-11-10 06:00:00

2018-12-20 09:10:11

2019-01-20 12:13:14

 

2.Calculate the computed datum values:

 

DATUM_UTC_DT

DATUM_VALUE

2018-11-10 06:00:00

200 - 110000 * 0.00010197442889221 - 100 = 88.78281 (m)

2018-12-20 09:10:11

200 - 120000 * 0.00010197442889221 - 100 = 87.76307

2019-01-20 12:13:14

200 - 130000 * 0.00010197442889221 - 130 = 56.74332

 

3.Insert the computed datum into DT_LOGGER_DATUM (set LOGGER_SERIES_ID = @target_series_id).

 

Note: This data can be charted in the Live Historical Chart widget without using the Data Computation Agent, or saving the data to DT_LOGGER_DATUM; see this example.