equis.measure_datum

<< Click to Display Table of Contents >>

EQuIS 7  >>  Enterprise > EQuIS Live > Agents > Data Computation > Functions >

equis.measure_datum

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'.

 

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