Jump to main content.

Total Waters Oracle Service Public Methods

General Description

Purpose:

To return calculated totals of point counts, linear meters, and area sq km for NHD and Program data. Depending on the bounding parameters, this information may be calculated on the fly, or pulled from pre-calculated tables. The interface has several options for summarization, including "Border Waters", custom shapes, and FCODE groupings.

Process:

The procedures listed below should be called through ORACLE PL /SQL. For all procedures, the results are returned via a PL/SQL Ref Cursor.
 
Any Errors can be retrieved from the PL/SQL call stack using either DBMS_UTILITY.FORMAT_ERROR_STACK or DBMS_UTILITY.FORMAT_ERROR_BACKTRACE. DBMS_UTILITY.FORMAT_ERROR_STACK retrieves the error number and verbal description of the error while DBMS_UTILITY.FORMAT_ERROR_BACKTRACE retrieves the error number and the line location where the error occurred. The latter may be more valuable when reporting problems.

Bounding Box

Method Name

total_waters.tw_bounding_box.process_job

Description

This is the main entry-level procedure that is takes a bounding box specification and returns rolled up counts/sums of the program systems desired, including NHD.

Input Parameters
Parameter Type Domain Description
p_pgm_sys_array SYS.GENSTRINGSEQUENCE One or more valid program abbreviations as defined in the RAD_EVTPRG_LUT program_systems table A list of program systems to total. NHD is always added to this list, if not already included.
p_long_1 NUMBER -180 to 180 (inclusive) Longitude of the lower-left corner of the bounding box.
p_lat_1 NUMBER -90 to 90 (inclusive) Latitude of the lower-left corner of the bounding box.
p_long_2 NUMBER -180 to 180 (inclusive) Longitude of the upper-right corner of the bounding box.
p_lat_2 NUMBER -90 to 90 (inclusive) Latitude of the upper-right corner of the bounding box.
p_border_policy INTEGER 0,1 How to handle State borders.

0 - ignore borders.

1 - take into account borders
p_grouping_name VARCHAR2 A valid grouping name from the fcode_group table Used to identify what level of fcode rollup to use.

p_nhd_custom INTEGER 0,1,2

*** ONLY 0 IS VALID IN CURRENT IMPLEMENTATION *** 		How to handle NHD-based vs. Custom shapes.

0 - only nhd-based data

1 - only non-nhd-based data

2 - both nhd-based and non-nhd data
p_state_grouping INTEGER 0,1 Whether the results should be broken out by State.

0 - no State Subtotals breakout

1 - State Subtotals
p_huc_grouping INTEGER 0,1 Whether the results should be broken out by sub basin (8 digit HUC).

0 - No Sub Basin Subtotals

1 - Sub Basin Subtotals
Output Parameters

Data is available from the temporary results table (directly). Data is also returned via an Oracle Ref Cursor. The ref cursor returns all the rows from the results table for that transaction.
Results table: TW_TEMP_BB_RESULTS

The data in the Temp tables (results,etc.) will remain until the session is closed, or manually deleted. If separate independent analysis for a new set of data is to be run, then it is recommended to start a new session.
Parameter Type Domain Description
p_return_cursor SYS_REFCURSOR
A PL/SQL ref cursor containing the results.

Cursor Field Definitions
Field Type Domain Description
st VARCHAR2(2) NULL or valid st from state_lut The state of the data. Null is used if the data is not to be broken out by state.
nhd_st VARCHAR2(2) NULL or valid st from state_lut The state the NHD is assigned to. For program data refers to state Indexed NHD assigned to.
huc VARCHAR2(8) NULL or 8 digit HUC The HUC of the data. Null is used if the data is not to be broken out by HUC.
pgm_sys VARCHAR2(10) Valid prg_sys_id from the program_systems table The program system of the data. NHD is a valid entry.
fcode_group VARCHAR2(50) Valid fcode group value from fcode_group table The grouping of FCODE based on the fcode grouping policy.
border_flag INTEGER 0, 1, or NULL 0 if row has no borders
1 if it has only borders
NULL if no border request was made
nhd_flag INTEGER 0, 1 Whether the row encompasses NHD-based data or non-nhd-based data

1 nhd-based data
0 custom-shaped data
feature_class VARCHAR2(1) l, a, or p Count of point events for the row
feature_total NUMBER positive NUMBER Sum of the linear features in METERS, or sum of area feature in SQ_KM, or number of point features for row.

Data Sources

 Event Tables (point/line/area custom point/ custom line/ custom area) for each Program System
NHD tables (linear/area).

Process Notes

Proper privledges via the tw_user role must be granted to any user attempting to run the bounding box portion of the TW Service against resticted event programs.

PL/SQL Example

**Note that there may be issues with the dbms_output of the Ref Cursor. Depending on the number of records returned a buffer overflow error may occur. A set serveroutput on size 999999 may help alleviate the issue. Also a change to a dbms_output for each column as opposed to a single dbms_output statement should circumvent buffer overflow errors.

DECLARE
v_pgm_sys_array SYS.GENSTRINGSEQUENCE := SYS.GENSTRINGSEQUENCE();
v_long_1 NUMBER := -80.52882;
v_lat_1 NUMBER := 41.632112;
v_long_2 NUMBER := -80.521594;
v_lat_2 NUMBER := 41.639793;
v_border_policy INTEGER := 1;
v_grouping_name VARCHAR2(10) :='INT_PEREN';
v_nhd_custom INTEGER := 0;
v_state_grouping INTEGER := 1;
v_huc_grouping INTEGER := 1;
v_results_cursor SYS_REFCURSOR;

TYPE bb_results_rec IS RECORD (
st VARCHAR2(2),
nhd_st VARCHAR2(2),
huc VARCHAR2(8),
pgm_sys VARCHAR2(10),
fcode_group VARCHAR2(50),
border_flag INTEGER,
nhd_flag INTEGER,
feature_class VARCHAR2(1),
feature_total NUMBER
);
v_tw_return_rec bb_results_rec;

BEGIN
-- populate the program system array
v_pgm_sys_array.extend(3);
v_pgm_sys_array(1) := 'NHD';
v_pgm_sys_array(2) := '305B';
v_pgm_sys_array(3) := '303D';


-- execute the procedure
BEGIN
ow_service.tw_bounding_box.process_job(
v_pgm_sys_array,
v_long_1,
v_lat_1,
v_long_2,
v_lat_2,
v_border_policy,
v_grouping_name,
v_nhd_custom,
v_state_grouping,
v_huc_grouping,
v_results_cursor
);
EXCEPTION
-- Handle Exceptions as you will
WHEN OTHERS
THEN
dbms_output.put_line('#### ERROR REPORTED ####');
dbms_output.put_line(DBMS_UTILITY.FORMAT_ERROR_STACK);
END;


-- Spin through the cursor to see what we got back
LOOP
IF v_results_cursor%ISOPEN THEN
FETCH v_results_cursor INTO v_tw_return_rec;
EXIT WHEN v_results_cursor%NOTFOUND;
dbms_output.put_line(
' **ST = ' || v_tw_return_rec.st ||
' **NHD_ST = ' || v_tw_return_rec.nhd_st ||
' **HUC = ' || v_tw_return_rec.huc ||
' **PGM_SYS = ' || v_tw_return_rec.pgm_sys ||
' **FCODE_GROUP = ' || v_tw_return_rec.fcode_group ||
' **BORDER_FLAG = ' || v_tw_return_rec.border_flag ||
' **NHD_FLAG = ' || v_tw_return_rec.nhd_flag ||
' **FEATURE_CLASS = ' || v_tw_return_rec.feature_class ||
' **FEATURE_TOTAL = ' || v_tw_return_rec.feature_total);
ELSE
EXIT;
END IF;
END LOOP;

IF v_results_cursor%ISOPEN THEN
CLOSE v_results_cursor;
END IF;

END;
/

Usage Notes/ Limitations

The Bounding box must be defined by the Lat/Long of bottom left to upper right. The data is only broken down by State Geography (with or without border waters), HUC, and/or FCODE. Custom event data summaries are currently not implemented.

State/HUC/FCODE

Method Name

total_waters.tw_state_huc_fcode.process_job

Description

This is the main entry-level procedure that will return total point event counts, total linear waters (meters), and total area waters (square kilometers) summarized by state and/or sub basin (8 digit HUC). Results can be returned for program data as well as the NHD.

Input Parameters
Parameter Type Domain Description
p_pgm_sys_array SYS.GENSTRINGSEQUENCE One or more valid program abbreviations as defined in the RAD_EVTPRG_LUT program_systems table A list of program systems to total.
p_st_array SYS.GENSTRINGSEQUENCE NULL or valid st from state_lut A list of the states to be restricted to in the summarization. If empty, then no state restrictions are imposed.
p_huc_array SYS.GENSTRINGSEQUENCE NULL or valid HUC A list of the sub basins (8 digit HUCS) to be restricted to in the summarization. If NULL, then no limitations on sub basins are imposed.
p_border_policy INTEGER 0,1 How to handle State borders: 0 to ignore borders, 1 To take into account borders
p_grouping_name VARCHAR2(50) A valid grouping name from the fcode_group table Used to identify what level of fcode rollup to use.

p_nhd_custom INTEGER 0,1,2

*** ONLY 0 IS VALID IN CURRENT IMPLEMENTATION *** 		How to handle NHD-based vs. Custom shape events.

0 - only nhd-based data

1 - only non-nhd-based data

2 - both nhd-based and non-nhd data.
p_st_grouping INTEGER 0,1 Whether the results should be broken out by State.

0 - No State Subtotal

1 - State Subtotals
p_huc_grouping INTEGER 0,1 Whether the results should be broken out by sub basin (8 digit HUC).

0 - No Sub Basin Subtotal

1 - Sub Basin Subtotal


Output Parameters

Data is available from the temporary results table (directly). Data is also returned via an Oracle Ref Cursor. The ref cursor returns all the rows from the results table for that transaction. Results table: TW_TEMP_BB_RESULTS

The data in the Temp tables (results,etc.) will remain until the session is closed, or manually deleted. If separate independent analysis for a new set of data is to be run, then it is recommended to start a new session.

Parameter Type Domain Description
p_return_cursor SYS_REFCURSOR
A PL/SQL ref cursor containing the results.

Cursor Field Definitions
Field Type Domain Description
st VARCHAR2(2) NULL or valid st from state_lut The state of the data. Null is used if the data is not to be broken out by state.
nhd_st VARCHAR2(2) NULL or valid st from state_lut The state the NHD is assigned to. For program data refers to state Indexed NHD assigned to.
huc VARCHAR2(8) NULL or 8 digit HUC The HUC of the data. Null is used if the data is not to be broken out by HUC.
pgm_sys VARCHAR2(10) Valid prg_sys_id from the program_systems table The program system of the data. NHD is a valid entry.
fcode_group VARCHAR2(50) Valid fcode group value from fcode_group table The grouping of FCODE based on the fcode grouping policy.
border_flag INTEGER 0, 1, or NULL 0 if row has no borders
1 if it has only borders
NULL if no border request was made
nhd_flag INTEGER 0, 1 Whether the row encompasses NHD-based data or non-nhd-based data

1 nhd-based data
0 custom-shaped data
feature_class VARCHAR2(1) l, a, or p Count of point events for the row
feature_total NUMBER positive NUMBER Sum of the linear features in METERS, or sum of area feature in SQ_KM, or number of point features for row.

Data Sources

Pre-computed data for point/line/area, both NHD and Event systems

Process Notes

Rolls up data from the pre-computed tables based on parameters.

The tw_user role must be granted to any user attempting to run the st_huc_fcode rollup portion of the TW Service.

PL/SQL
Example

This example returns a rollup of NHD, 303D, and 305B data for MD and VA, broken out by State, border waters, and HUC, and grouping by INT_PEREN fcode groupings. Note that the huc array is commented out.

DECLARE
v_pgm_sys_array SYS.GENSTRINGSEQUENCE := SYS.GENSTRINGSEQUENCE();
v_st_array SYS.GENSTRINGSEQUENCE := SYS.GENSTRINGSEQUENCE();
v_huc_array SYS.GENSTRINGSEQUENCE := SYS.GENSTRINGSEQUENCE();
v_border_policy INTEGER := 1;
v_grouping_name VARCHAR2(10) :='INT_PEREN';
v_nhd_custom INTEGER := 0;
v_state_grouping INTEGER := 1;
v_huc_grouping INTEGER := 0;
v_results_cursor SYS_REFCURSOR;
TYPE bb_results_rec IS RECORD (
st VARCHAR2(2),
nhd_st VARCHAR2(2),
huc VARCHAR2(8),
pgm_sys VARCHAR2(10),
fcode_group VARCHAR2(50),
border_flag INTEGER,
nhd_flag INTEGER,
feature_class VARCHAR2(1),
feature_total NUMBER
);
v_tw_return_rec bb_results_rec;

BEGIN

-- populate the program system array
v_pgm_sys_array.extend(3);
v_pgm_sys_array(1) := 'NHD';
v_pgm_sys_array(2) := '305B';
v_pgm_sys_array(3) := '303D';

-- populate the state array
v_st_array.extend(2);
v_st_array(1):='MD';
v_st_array(2):='VA';

-- populate the huc array
-- v_huc_array.extend(2);
-- v_huc_array(1):='01010101';
-- v_huc_array(2):='01010102';

-- run the procedure
BEGIN
ow_service.tw_state_huc_fcode.process_job(
v_pgm_sys_array,
v_st_array,
v_huc_array,
v_border_policy,
v_grouping_name,
v_nhd_custom,
v_state_grouping,
v_huc_grouping,
v_results_cursor
);
EXCEPTION
-- Handle Exceptions as your will
WHEN OTHERS
THEN
dbms_output.put_line('#### ERROR REPORTED ####');
dbms_output.put_line(DBMS_UTILITY.FORMAT_ERROR_STACK);
END;

LOOP
IF v_results_cursor%ISOPEN THEN
FETCH v_results_cursor INTO v_tw_return_rec;
EXIT WHEN v_results_cursor%NOTFOUND;
dbms_output.put_line(
' **ST = ' || v_tw_return_rec.st ||
' **NHD_ST = ' || v_tw_return_rec.nhd_st ||
' **HUC = ' || v_tw_return_rec.huc ||
' **PGM_SYS = ' || v_tw_return_rec.pgm_sys ||
' **FCODE_GROUP = ' || v_tw_return_rec.fcode_group ||
' **BORDER_FLAG = ' || v_tw_return_rec.border_flag ||
' **NHD_FLAG = ' || v_tw_return_rec.nhd_flag ||
' **FEATURE_CLASS = ' || v_tw_return_rec.feature_class ||
' **FEATURE_TOTAL = ' || v_tw_return_rec.feature_total);
ELSE
EXIT;
END IF;
END LOOP;

IF v_results_cursor%ISOPEN THEN
CLOSE v_results_cursor;
END IF;

END;
/

Usage Notes/Limitations

The data is only broken down by State Geography (with or without border waters), HUC, and/or FCODE. Custom event data summaries are currently not implemented.

Local Navigation

Jump to main content.