In-Situ HydroVu Agent Example Remap

<< Click to Display Table of Contents >>

Live > Agents > Web API Agents > Web API Vendor > In-Situ HydroVu >

In-Situ HydroVu Agent Example Remap

Before using the In-Situ HydroVu Agent or Historical Data Loader, the parameters and units must be added to the RT_REMAP and RT_REMAP_DETAIL database tables. The following SQL statements provide an example.

 

 

DECLARE @remap_code VARCHAR(20) = 'In-Situ HydroVu';

 

IF NOT EXISTS(SELECT * FROM rt_remap WHERE remap_code = @remap_code)

BEGIN

INSERT rt_remap (remap_code, status_flag)

VALUES (@remap_code, 'A');

END

 

DELETE FROM rt_remap_detail WHERE remap_code = @remap_code;

 

DECLARE @external_field VARCHAR(256) = 'parameter';

 

DECLARE @qualifiers TABLE (internal_value VARCHAR(128), external_value VARCHAR(256));

 

-- https://www.hydrovu.com/public-api/docs/index.html

-- Authorize > Friendly Name Lookup > Try it out > Execute

 

-- Parameter (case insensitive), Friendly Name (256 character max)

INSERT

 @qualifiers (internal_value, external_value)

VALUES

  ('1', 'Temperature')

,('2', 'Pressure')

,('3', 'Depth')

,('4', 'Level: Depth to Water')

,('5', 'Level: Elevation')

,('9', 'Actual Conductivity')

,('10', 'Specific Conductivity')

,('11', 'Resistivity')

,('12', 'Salinity')

,('13', 'Total Dissolved Solids')

,('14', 'Density')

,('16', 'Baro')

,('17', 'pH')

,('18', 'pH MV')

,('19', 'ORP')

,('20', 'DO')

,('21', '% Saturation O2')

,('22', 'NO3-')

,('23', 'NH4+')

,('24', 'Cl-')

,('25', 'Turbidity')

,('26', 'Battery Voltage')

,('28', 'Flow Rate')

,('29', 'Total Flow (volume)')

,('30', 'Partial Pressure O2')

,('31', 'Total Suspended Solids')

,('32', 'External Voltage')

,('33', 'Battery Level')

,('34', 'RWT Concentration')

,('35', 'RWT Fluorescence')

,('36', 'Cl- mV')

,('37', 'NO3- as N')

,('38', 'NO3- mV')

,('39', 'NH4+ as N')

,('40', 'NH4+ mV')

,('41', 'NH3 as N')

,('42', 'Sigma NH3')

,('49', 'Velocity')

,('50', 'Chl-a Concentration')

,('51', 'Chl-a Fluorescence')

,('54', 'BGA-PC Concentration')

,('55', 'BGA-PC Fluorescence')

,('58', 'BGA-PE Concentration')

,('59', 'BGA-PE Fluorescence')

,('flowVelocity', 'Flow Velocity')

,('density', 'Density')

,('concentration', 'Concentration');

 

INSERT INTO rt_remap_detail (external_field, external_value, internal_value, status_flag, remap_code)

SELECT @external_field, external_value, internal_value, 'A', @remap_code

FROM @qualifiers;

 

SELECT * FROM rt_remap_detail WHERE remap_code = @remap_code AND external_field = @external_field ORDER BY ISNUMERIC(internal_value) DESC, TRY_CAST(internal_value AS BIGINT), internal_value;

 

SET @external_field = 'unit';

 

DECLARE @units TABLE (internal_value VARCHAR(128), external_value VARCHAR(15));

 

-- Unit (case insensitive), Friendly Name (15 character max)

INSERT

 @units (internal_value, external_value)

VALUES

  ('1', 'C')

,('2', 'F')

,('17', 'psi')

,('19', 'kPa')

,('20', 'bar')

,('21', 'mbar')

,('22', 'mm Hg')

,('23', 'in Hg')

,('24', 'cm H2O')

,('26', 'torr')

,('27', 'atm')

,('33', 'mm')

,('34', 'cm')

,('35', 'm')

,('37', 'in')

,('38', 'ft')

,('65', 'uS/cm')

,('66', 'mS/cm')

,('81', 'ohm-cm')

,('97', 'psu')

,('113', 'ppm')

,('114', 'ppt')

,('117', 'mg/L')

,('118', 'ug/L')

,('120', 'g/L')

,('121', 'ppb')

,('129', 'g/cm3')

,('145', 'pH')

,('162', 'mV')

,('163', 'V')

,('177', '% sat')

,('193', 'FNU')

,('194', 'NTU')

,('209', 'ft3/s')

,('210', 'ft3/min')

,('211', 'ft3/hr')

,('212', 'ft3/day')

,('213', 'gal/s')

,('214', 'gal/min')

,('215', 'gal/hr')

,('216', 'Mgal/day')

,('217', 'm3/s')

,('218', 'm3/min')

,('219', 'm3/hr')

,('220', 'm3/day')

,('221', 'L/s')

,('222', 'ac-ft/d')

,('223', 'mL/min')

,('225', 'ft3')

,('226', 'gal')

,('227', 'Mgal')

,('228', 'm3')

,('230', 'ac-ft')

,('241', '%')

,('257', 'RFU')

,('274', 'mL/hr')

,('275', 'L/min')

,('276', 'L/hr')

,('337', 'ft/s')

,('338', 'm/s')

,('megaliters', 'ML')

,('yard', 'yd')

,('acreInchPerMinute', 'ac-in/min')

,('g/mL', 'g/mL')

,('acreFeetPerSecond', 'ac-ft/sec')

,('kilolitersPerMinute', 'kL/min')

,('milliter', 'mL')

,('litersPerDay', 'L/d')

,('megalitersPerDay', 'ML/d')

,('millilitersPerSecond', 'mL/s')

,('acreInchPerDay', 'ac-in/d')

,('megalitersPerMinute', 'ML/min')

,('acreInchPerHour', 'ac-in/hr')

,('liter', 'L')

,('acreFeetPerMinute', 'ac-ft/min')

,('kilolitersPerSecond', 'kL/sec')

,('acreInch', 'ac-in')

,('kilolitersPerDay', 'kL/d')

,('megalitersPerSecond', 'ML/sec')

,('megalitersPerHour', 'ML/hr')

,('kiloliters', 'kL')

,('kilolitersPerHour', 'kL/hr')

,('millilitersPerDay', 'mL/day')

,('acreInchPerSecond', 'ac-in/sec')

,('acreFeetPerHour', 'ac-ft/hr')

,('inH2o', 'in H2O');

 

INSERT INTO rt_remap_detail (external_field, external_value, internal_value, status_flag, remap_code)

SELECT @external_field, external_value, internal_value, 'A', @remap_code

FROM @units;

 

SELECT * FROM rt_remap_detail WHERE remap_code = @remap_code AND external_field = @external_field ORDER BY ISNUMERIC(internal_value) DESC, TRY_CAST(internal_value AS BIGINT), internal_value;