Live Data Computation Agent – equis.measure

The purpose of this function is to determine the appropriate reference elevation (i.e. measure_datum) for the given location/well on the given date (@measurement_date).

Additional help documentation for this function is accessible here.

Parameters

Parameter	Type	Description
@facility_id	INT	Only look at DT_MEASURE_DATUM rows where FACILITY_ID = @facility_id.
@sys_loc_code	VARCHAR(20)	Only look at DT_MEASURE_DATUM rows where SYS_LOC_CODE = @sys_loc_code.
@measurement_date	DATETIME	Only look at closest DT_MEASURE_DATUM row where START_DATE <= @measurement_date, and/or closest DT_MEASURE_DATUM row where START_DATE > @measurement_date.

The DT_MEASURE_DATUM.STEP_OR_LINEAR value of the row where START_DATE is greater than @measurement_date determines how the measure_datum is calculated. It may be set to one of the following values:

•'step' - returns the DATUM_VALUE of the closest DT_MEASURE_DATUM row where START_DATE is less than or equal to @measurement_date.

•'linear' - returns a linear interpolation value using the closest DT_MEASURE_DATUM row where START_DATE is less than or equal to @measurement_date, and the closest DT_MEASURE_DATUM row where START_DATE is greater than @measurement_date.

•NULL or other value - 'linear' if a DT_MEASURE_DATUM row exists where START_DATE is greater than @measurement_date, otherwise 'step'.

Note: Step is the STEP_OR_LINEAR column's default value.

Example

You can copy and paste the following SQL into SQL Server and run/debug it to get a better feel for how this function works.

DECLARE @coord_type_code VARCHAR(20) = 'LAT LONG WGS 84';

IF NOT EXISTS(SELECT * FROM rt_coord_type WHERE coord_type_code = @coord_type_code)

BEGIN

INSERT

rt_coord_type (coord_type_code,status_flag,SRID)

VALUES

(@coord_type_code,'A','4326')

END

DECLARE @facility_id INT = 130001;

IF NOT EXISTS(SELECT * FROM dt_facility WHERE facility_id = @facility_id)

BEGIN

INSERT

dt_facility (facility_id, facility_code, coord_type_code, identifier, status_flag)

VALUES

(@facility_id, 'Facility_100001', 'LAT LONG WGS 84', 'PRIMARY', 'A');

END

DECLARE @sys_loc_code VARCHAR(20) = 'LOCATION_100001_1';

IF NOT EXISTS(SELECT * FROM dt_location WHERE sys_loc_code = @sys_loc_code)

BEGIN

INSERT

dt_location (facility_id, sys_loc_code, status_flag)

VALUES

(@facility_id, @sys_loc_code, 'A');

END

DECLARE @start_date_1 SMALLDATETIME = '2018-04-23T08:33:00Z';

IF NOT EXISTS(SELECT * FROM dt_measure_datum WHERE facility_id = @facility_id AND sys_loc_code = @sys_loc_code AND start_date = @start_date_1)

BEGIN

INSERT

dt_measure_datum (facility_id, sys_loc_code, start_date, datum_value, step_or_linear)

VALUES

(@facility_id, @sys_loc_code, @start_date_1, 100, 'step');

END

ELSE

BEGIN

UPDATE dt_measure_datum SET step_or_linear = 'step' WHERE facility_id = @facility_id AND sys_loc_code = @sys_loc_code AND start_date = @start_date_1;

END

DECLARE @start_date_2 SMALLDATETIME = '2018-06-01T09:47:00Z';

IF NOT EXISTS(SELECT * FROM dt_measure_datum WHERE facility_id = @facility_id AND sys_loc_code = @sys_loc_code AND start_date = @start_date_2)

BEGIN

INSERT

dt_measure_datum (facility_id, sys_loc_code, start_date, datum_value, step_or_linear)

VALUES

(@facility_id, @sys_loc_code, @start_date_2, 200, 'step');

END

ELSE

BEGIN

UPDATE dt_measure_datum SET step_or_linear = 'step' WHERE facility_id = @facility_id AND sys_loc_code = @sys_loc_code AND start_date = @start_date_2;

END

-- @start_date_1 <= @datum_utc_dt < @start_date_2

DECLARE @datum_utc_dt DATETIME2(0) = '2018-05-22T13:21:49Z';

-- If @start_date_2's step_or_linear = 'step'.

SELECT equis.measure_datum(@facility_id, @sys_loc_code, @datum_utc_dt) AS [equis.measure_datum: step];

-- Results

-- equis.measure_datum: step

-- 100

UPDATE dt_measure_datum SET step_or_linear = 'linear' WHERE facility_id = @facility_id AND sys_loc_code = @sys_loc_code AND start_date = @start_date_2;

-- If @start_date_2's step_or_linear = 'linear'.

SELECT equis.measure_datum(@facility_id, @sys_loc_code, @datum_utc_dt) AS [equis.measure_datum: linear];

-- Results

-- equis.measure_datum: linear

-- 174.359

UPDATE dt_measure_datum SET step_or_linear = NULL WHERE facility_id = @facility_id AND sys_loc_code = @sys_loc_code AND start_date = @start_date_2;

-- If @start_date_2's step_or_linear = NULL.

SELECT equis.measure_datum(@facility_id, @sys_loc_code, @datum_utc_dt) AS [equis.measure_datum: NULL];

-- Results

-- equis.measure_datum: NULL

-- 174.359

DELETE FROM dt_measure_datum WHERE facility_id = @facility_id AND sys_loc_code = @sys_loc_code AND start_date = @start_date_2;

-- If there's no dt_measure_datum row where start_date > @datum_utc_dt.

SELECT equis.measure_datum(@facility_id, @sys_loc_code, @datum_utc_dt) AS [equis.measure_datum: no start_date > @datum_utc_dt];

-- Results

-- equis.measure_datum: no start_date > @datum_utc_dt

-- 100

Live Data Computation Agent – equis.measure_datum

Live Data Computation Agent – equis.measure_datum

Parameters

Example