1:24000+ Scale Hydro Clearinghouse

System Documentation

The following document covers the system design, installation, and
operation of a clearinghouse for multiple scales of hydro data. The
initial data load consists of existing 1:100000 scale data, but this
data will be replaced with 1:24000 scale or better data by contributing
agencies. The 1:100,000 scale data is provided as a starting point, so
new data submissions can easily comply with the requirement to use the
same LLIDs (Unique feature ids based on latitude and longitude) and
optionally the same measures in the 1:24000 scale data to be submitted.

Project Requirements

Environmental Systems Research Institute, Inc. (ESRI), developed this
solution for a 1:24,000+ scale hydro clearinghouse for the Pacific
Northwest Division of the USDA--Forest Service\'s Regional Ecosystem
Office (REO). This clearinghouse supports multiple remote sites from
various organizations simultaneously editing watercourse routes & events
(WC), Waterbody regions (WB), waterbody shoreline routes & events (WS),
and waterbody points (WP).

Requirements for this clearinghouse as identified by REO and understood
by ESRI are as follows:

-   The desired application will be deployed on an Internet server to a
    client having a web browser and a set of ArcInfo AMLs and menus to
    support checking out and checking in data.

```{=html}
<!-- -->
```
-   The client and server mix should be able to deliver hydro data and
    the related unique ID when requested for a geographic area.

```{=html}
<!-- -->
```
-   The system needs to support different scales of hydro data Note that
    the same ID for a given stream is intended to be used at various
    scales (i.e., the Columbia River will be assigned the same ID at
    1:100,000 as at 1:24,000). Initial priority for layers is focused on
    1:24,000 hydro data. Route measures will not necessarily correspond
    between 1:100000 and 1:24000+ scale data, but LLIDs should match.

```{=html}
<!-- -->
```
-   The application should be able to keep track of areas where editing
    or new data submission is being done by cooperating agencies, and
    prevent editing conflicts by allowing hydro data in a given area to
    be edited by only one agency at a time.

```{=html}
<!-- -->
```
-   Cooperating agencies may have slow network connections, so a
    checkout-check-in method must be used instead of direct on-line
    editing.

```{=html}
<!-- -->
```
-   Editing will be done by cooperating agencies using existing
    workstation ArcInfo methods and applications, so the data must be
    provided to the cooperating agencies in coverage format.

```{=html}
<!-- -->
```
-   The system can be used by interested parties to determine if hydro
    data in a specified geographic extent is \"checked out\" for
    work/editing and who is doing the work.

```{=html}
<!-- -->
```
-   The check-in process will include basic QA checks.

```{=html}
<!-- -->
```
-   Changes to the database are subject to review and quality assurance
    checks by the clearinghouse manager before final inclusion in the
    database

    Deliverables

Deliverables for the project include the following:

**Server**

-   SDE software and licensing.

```{=html}
<!-- -->
```
-   ArcIms software and licensing; Also MO IMS if necessary.

```{=html}
<!-- -->
```
-   Clearinghouse software to manage transaction control for hydro
    edits/update.

```{=html}
<!-- -->
```
-   Quality check software to aid the clearinghouse manager.

```{=html}
<!-- -->
```
-   Clearinghouse manager software to facilitate moving approved
    submissions from holding area to clearinghouse SDE layer.

```{=html}
<!-- -->
```
-   Software scripts necessary to generate initial tables and other
    database objects on REO SDE server.

    Client

```{=html}
<!-- -->
```
-   Client application to facilitate query and retrieval from
    clearinghouse SDE server.

```{=html}
<!-- -->
```
-   The client application will format information retrieved from SDE
    clearinghouse into a set of coverages and event tables ready for use
    by existing conflation tools.

```{=html}
<!-- -->
```
-   The client will have a copy of the quality check software to perform
    many of the same QA/QC tests as the clearinghouse manager, thus
    reducing the probability of a rejected data submission.

    Overall Architecture

The server component consists of a Spatial Database Engine™ (ArcSDE™)
server, ArcInfo 8.x, an ArcIMS server, and related support software. A
dll from MO IMS is used to fetch transaction ids. Client software
consists of a Java-enabled web browser for data viewing and certain
check-in/check-out operations. ArcInfo 7.2.x or 8.x can be used for
editing, QA of the checked-out data, and other check-in/check-out
operations.

The server tier stores a set of multi-scale Hydro layers. A "checkout
status tracking" layer tracks areas checked out by clearinghouse
editors. If an area is checked out, all topologies (point, route,
region) and associated events for that area are checked out. This
implementation meets the requirement of being able to store multiple
scales of hydro data with the unique ID for a given feature being unique
within each layer. In addition, a temporary holding area for submissions
from client sites will be maintained. The clearinghouse server manager
has the ultimate responsibility for approval or rejection of all
submissions requested for inclusion in the official clearinghouse ArcSDE
layers.

# Data translation between Client and Server

> The following hydro data is managed by the clearinghouse**:**

-   wc -Watercourse routes (e.g. streams)

-   Events on watercourses

-   Lookup tables

-   wb -Waterbody areas (e.g. lakes) stored as regions

-   Lookup tables

-   ws - Waterbody shorelines stored as routes

-   Events on waterbody routes

-   Lookup tables

-   wp -Water points (e.g. springs)

-   Relate table for water points

-   Lookup tables

Refer to "Oregon and Washington State Framework; Clearinghouse
Hydrography Data Dictionary; Physical Data Model" for details

# 

# The data is stored in ArcSDE on the server, but delivered to the user in coverage format. This translation is done according to the following table:

+-----------------------------------+-----------------------------------+
| ### Coverage feature class        | ### SDE layer                     |
+-----------------------------------+-----------------------------------+
| wp (point)                        | wp (point)                        |
+-----------------------------------+-----------------------------------+
| wc (route.wc)                     | wc (measured shape)               |
+-----------------------------------+-----------------------------------+
| wb (region.wb)                    | wb_area (area)                    |
+-----------------------------------+-----------------------------------+
| ws (route.ws)                     | ws_route (measured shape)         |
+-----------------------------------+-----------------------------------+

-   Event tables on the client are INFO tables; on the server they are
    Oracle tables.

```{=html}
<!-- -->
```
-   Domain lookup tables are stored as a single set of Oracle tables on
    the server, but these tables will be redundantly copied out as INFO
    tables into each coverage to which they apply. That way, the lookup
    tables are always available to the user of a coverage extracted from
    the clearinghouse.

-   Sections of checked-in coverages are stored as event tables in
    ArcSDE. They are used to ensure that section breaks in the output
    coverage correspond to section breaks in the input coverage. The
    user never sees these tables in the extracted coverages. They are
    created on the fly when data is loaded, and deleted after the
    post-processing step when data is extracted. They do reside in the
    ArcSDE database, however. They are named wc_evt_section and
    wb_evt_section.

-   There is an issue to be aware of with section event tables: Any
    editing of measures done directly on the ArcSDE data will be lost
    unless the section event table is also updated to reflect these
    changes. In ArcSDE, measures are stored on each vertex, and all
    measures not represented in the section event table are lost when
    data are extracted from the clearinghouse. As direct editing of the
    clearinghouse server is not currently supported, this is not
    currently a problem.

-   In the future, things may change, and it may be desirable to convert
    from coverage editing to geodatabase editing. At that point, a
    decision must be made about section event tables. Options are:

-   No longer use them. Dissolve sections using the methods prototyped
    in the utilities directory, subdirectory: section_dissolve. There
    will be many sections that cannot be dissolved because the single
    precision storage of measures in a coverage may produce too much
    rounding error to effectively discriminate legitimate measure/length
    ratio breakpoints.

-   Create them on the fly in the geodatabase. . Write a program to
    operate against the more precisely stored geodatabase measures to
    detect measure/length ratio breakpoints and generate the section
    event table on the fly as routes are extracted to coverage format.

-   See the word doc: "utilities\\section_dissolve\\tech_meeting notes
    1_4 and 1_17_01.doc" for further discussion on this issue.

-   Coordinate storage precision

> \<\<\<\<\<\<\<\<Fill in: spatial reference, single precision measures,
> long route issue,(precision, long checkout (snake ate a possum) use
> for now, split if it just won't work, yadda yadda yadda

Server design

The first part of this section is devoted to "housekeeping" issues, and
the last part covers the transaction process flow.

**Server logging**

The server keeps a log of all significant events. This log is a valuable
resource for monitoring server activity and troubleshooting. All lines
are flagged with date, time and message type. Message types are:

User A message logged by the clearinghouse manager, using the "Server
Manager" menu.

Info An informational message from the server

Warn A warning from the server

Err An error in data or server operation

Perr A program logic error or other improper condition detected by the
server code

The log is a text file named:

\\chse_exe\\chse_server\\etc\\chse_server_log

**Server Parameters**

The server parameters are currently set in the class_initialize
procedure of the ChseServer class in the manager.mxd ArcMap document.

As a stub for future enhancements, an empty parameter file has been
created in

\\chse_exe\\chse_server\\etc\\chse_server_log

**Restarting the server after a crash**

When the clearinghouse server is started by the clearinghouse manager, a
lockfile is created to keep a second server from being started. This
lockfile is located in:

\\chse_exe\\chse_server\\lockfiles\\chse_server_lock.txt

If the server crashes, the lockfile may be still present. You will get
as message to that effect. If so, delete it and restart.

**Killing a hung server**

Checkins and checkouts can take a long time to complete, so do not be
surprised if the "Server Manager" menu interface appears to be hung. It
is probably busy servicing a transaction. If you want to make sure the
server stops at a the next good stopping point, and the "Server Manager"
menu is not responding, make a file named:

> \\chse_exe\\chse_server\\lockfiles\\chse_server_request_shutdown.txt

The server checks for this file at every iteration, and stops itself if
the file is present.

**Internet map server setup**

Refer to the following file:

chse_exe\\chse_server\\doc\\web_client\\install_notes.txt

**FTP setup**

An FTP server must be installed for the clearinghouse server to work.
The data contributors can all connect with the same username and
password. Currently this is hydroftp/hydroftp. Anonymous FTP is not
recommended for the hydro transaction incoming FTP directories, to
discourage hackers. Individual user accounts are not necessary, since
the incoming FTP directories are regularly cleaned out while the server
is running. There is little chance that data contributors will
inadvertently "step on" each others' incoming data due to timing and
naming conventions, and malicious behavior on the part of data
contributors is not anticipated.

The FTP root directory can be anywhere on the system, as long as the NT
account which runs the clearinghouse server software can create, delete,
and copy all files and folders in its subdirectory. This subdirectory
must be named chse_ftp.

These directories should be read/write to the hydroftp user, and
invisible to anonymous:

chse_ftp\\sc24000\\incoming\\checkin_covs

Used for checkin data submission

chse_ftp\\sc24000\\incoming\\checkout_polys

Used for checkout polygon submission

This directory should be readonly to hydroftp and invisible to
anonymous:

chse_ftp\\sc24000\\outgoing\\checkout_covs

Used for checkout data download

In a future enhancement, the checkout status map coverage and the hydro
data will be posted on an FTP directory for download by anyone. These
directories should be readonly by hydroftp and anonymous FTP. The
current plan for the location of these directories is:

chse_ftp\\sc24000\\outgoing\\hydro_data

Used for readonly download of latest hydro data

chse_ftp\\sc24000\\outgoing\\statusmap

Used for readonly download of the STATUSMAP coverage, showing the

current status of checkouts

**Email setup**

The clearinghouse server sends mail to the clearinghouse manager and to
users who contribute data. There is an enhancement request to send
automatic email to users when checkout or checkin activity occurs in
their area of interest. The clearinghouse server does not receive any
mail. All incoming mail goes to the clearinghouse manager.

The clearinghouse manager's email address is the return address of all
mail sent by the clearinghouse server. This address is set as a server
parameter. See the " Server Parameters" section of this document for
more information.

Any mail protocol that supports MAPI should work. The server was written
and tested against Microsoft Outlook using an Exchange server.

If Outlook is not running when the server starts, MAPI will
automatically connect. This requires the operator to click OK to a
"connect" messagebox popped up by Outlook.

**Registering clearinghouse clients**

A client must be registered in the EDITORS table to do any checkouts or
checkins. The REO clearinghouse manager is responsible for maintaining
this table, using the "Editor Manager" menu. The clients do not need
their own Oracle or NT accounts, as they are not required to connect
directly. If they do have Oracle or NT accounts, they MUST NOT have
write access to any clearinghouse tables, ArcSDE layers, folders, or
files, except as outlined in the "FTP Setup" section of this document.
These data should be modified ONLY by the clearinghouse server software,
or corrupt transactions may result!

All modifications to clearinghouse data must be done by the
clearinghouse server, to maintain "long transaction" integrity. If users
have write permission, they might inadvertently connect directly and
corrupt the database.

**Table EDITORS:**

**Name Null? Type**

**\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\--
\-\-\-\-\-\-\--**

**EDITOR_ID NOT NULL NUMBER(6)**

**USERNAME NOT NULL VARCHAR2(30)**

**PASSWORD NOT NULL VARCHAR2(30)**

**FULL_NAME VARCHAR2(25)**

**ORGANIZATION VARCHAR2(20)**

**ADDRESS1 VARCHAR2(50)**

**ADDRESS2 VARCHAR2(50)**

**ADDRESS3 VARCHAR2(50)**

**PHONE VARCHAR2(20)**

**EXTENSION VARCHAR2(6)**

**FAX VARCHAR2(20)**

**EMAIL NOT NULL VARCHAR2(50**)

**Tracking transactions**

As transactions progress through the workflow, they are tracked in a
series of Oracle tables. Major steps are also logged to the Server Log,
described in the "Server Logging" section of this document.

Each transaction consists roughly of a checkout, data download, edit,
data upload, and checkin or abort. Each transaction gets one row in the
TRANSACTION table.

**Table: TRANSACTION**

**Name Null? Type**

**\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\--
\-\-\-\-\-\-\--**

**TRANSACTION_ID NOT NULL NUMBER(10)**

**EDITOR_ID NUMBER(6)**

**TRANSACTION_TYPE VARCHAR2(10)**

**START_TIME DATE**

**DEADLINE DATE**

**FINISH_TIME DATE**

**TRANSACTION_STEP VARCHAR2(20)**

**TRANSACTION_VERIFY NUMBER(8)**

As the transaction progresses, entries are added to the
TRANSACTION_HISTORY table. This table is used to record the progress of
the transaction, to ensure that steps are not omitted or repeated, even
though the server may have been stopped and restarted. This table can
also be used for general transaction status information.

**Table: TRANSACTION_HISTORY**

> **Name Null? Type**
>
> **\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\--
> \-\-\-\-\-\-\-- \-\-\-\-\-\-\-\-\--**
>
> **TRANSACTION_STEP_ID NOT NULL NUMBER(10)**
>
> **TRANSACTION_ID NUMBER(10)**
>
> **SUBMISSION_ID VARCHAR2(14)**
>
> **TRANSACTION_STEP VARCHAR2(20)**
>
> **STEP_DONE_TIME DATE**
>
> **STEP_COMMENTS VARCHAR2(250)**

The TRANSACTION_STEP column in the TRANSACTION_HISTORY table records
events as the transaction progresses. These events are defined in the
TRANSACTION_STEP_DESC table:

**Table: TRANSACTION_STEP_DESC**

**Name Null? Type**

**\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\--
\-\-\-\-\-\-\-- \-\-\-\-\-\-\-\-\-\--**

**STEP_ORDER NUMBER(10,6)**

**TRANSACTION_STEP NOT NULL VARCHAR2(20)**

**STEP_DESCRIPTION VARCHAR2(250)**

**Contents of Table: TRANSACTION_STEP_DESC**

**STEP_ORDER TRANSACTION_STEP**

**\-\-\-\-\-\-\-\-\-- \-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\--**

**STEP_DESCRIPTION**

**\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\--**

**1 TRANS_START**

**Transaction was started and a transaction number assigned**

**2 CKOUT_POLY_RECV**

**Checkout polygon received**

**9 CKOUT_POLY_REJECT**

**Checkout polygon was rejected because it overlapped an already
checked-out area; mail sent to user**

**3 CKOUT_DATA_POST**

**Checkout data posted to outgoing ftp dir and mail sent to user**

**4 CKIN_DATA_RECV**

**Checkin data received at incoming FTP dir**

**5 CKIN_PROCESSED**

**Checkin processed**

**6 CKIN_REJECT_AUTO**

**Checkin rejected automatically by server, mail sent to user**

**6 CKIN_REJECT_MANAGER**

**Checkin rejected after review by manager, mail sent to user**

**7 CKIN_DATA_POST**

**Checkin data posted to the database**

**9 CKIN_ACCEPT**

**Checkin accepted, mail sent to user**

**9 ABORT_BY_USER**

**Transaction was aborted by the user, mail sent to user**

**9 ABORT_BY_MGR**

**Transaction was aborted by clearinghouse manager, mail sent to user**

A view named TRANSACTION_VIEW joins the transaction tracking tables for
convenience.

**View: TRANSACTION_VIEW**

**Name Null? Type**

**\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\--
\-\-\-\-\-\-\-- \-\-\-\-\-\-\-\-\--**

**TRANSACTION_ID NOT NULL NUMBER(10)**

**EDITOR_ID NUMBER(6)**

**TRANSACTION_TYPE VARCHAR2(10)**

**START_TIME DATE**

**DEADLINE DATE**

**FINISH_TIME DATE**

**TRANSACTION_STEP VARCHAR2(20)**

**STEP_ORDER NUMBER(10,6)**

The checkout polygons are kept in an ArcSDE polygon layer named
STATUSMAP. When an area is checked-out, a polygon is added to this layer
to lock the checked-out area. When the transaction is checked-in or
aborted, this polygon is dated and flagged as inactive. Inactive
polygons can overlap. Active polygons cannot overlap. The client can
point at an existing polygon to see who has the area checked-out, when
it was checked-out, etc.

This layer joins to the TRANSACTION_VIEW view to associate the area
being checked out to the status of the transaction. Only polygons with a
TRANSACTION_STEP value less than 9 lock areas from edits by other users.
Other polygons represent completed or aborted transactions from the
past.

**ArcSDE Layer: STATUSMAP**

**-Registered with the geodatabase, NOT Registered as versioned**

**Name Null? Type**

**\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\--
\-\-\-\-\-\-\-- \-\-\-\-\-\-\-\-\--**

**TRANSACTION_ID NUMBER(10)**

**SHAPE NUMBER(9)**

**OBJECTID NOT NULL NUMBER(9)**

A script creates these tables, along with constraints and sequences.
This script is named:

\\chse_exe\\chse_server\\sde_scripts\\define_trans_tables18.sql

Note: use the highest number. The "18" in the name indicates that it is
the seventeenth version of this script. If a more recent version is
available, use it!

This script also creates four tables that keep track of LLIDs

They are:

**wb_llid**

**ws_llid**

**wc_llid**

**wp_llid**

All four tables have nearly identical formats.

In the SQL script, they are created using these SQL commands:

create table wb_llid(TRANSACTION_ID NUMBER(10), wb_llid_nr
varchar2(13));

create table ws_llid(TRANSACTION_ID NUMBER(10), ws_llid_nr
varchar2(13));

create table wc_llid(TRANSACTION_ID NUMBER(10), wc_llid_nr
varchar2(13));

create table wp_llid(TRANSACTION_ID NUMBER(10), wp_llid_nr
varchar2(13));

Then you must use ArcCatalog to register these four tables with the
geodatabase, and register them as versioned. This adds a column called
OBJECTID to each table.

Do NOT register the other transaction tables with the geodatabase!

**ArcSDE data loads**

The server needs a set of ArcSDE layers and tables, comprising the Hydro
Framework database design. These layers and tables include:

The STATUSMAP layer. This layer is a polygon layer with one user-defined
field named TRANSACTION_ID defined as double with a precision of 10 and
a scale of 0.

This layer should be registered with the geodatabase, but NOT registered
as versioned.

The four Data layers: WC, WS, WB, and WP

The event tables

The relate table

The lookup tables

The crossreference tables

For a complete list, refer to: "Oregon and Washington State Framework;
Clearinghouse Hydrography Data Dictionary; Physical Data Model"

These data loads should be done using the ArcCatalog GUI, using the same
format as the development server, Moose.

The above layers and tables, with the exception of the STATUSMAP layer
should be registered with the geodatabase and registered as versioned.

The spatial reference for all five Arcsde layers should be as follows,
except that only the WS and WC layers should have M (measure) units.

![](media/image1.png){width="2.50625in" height="3.423611111111111in"}

![](media/image2.png){width="2.623611111111111in"
height="3.439583333333333in"}

![](media/image3.png){width="2.623611111111111in"
height="3.5034722222222223in"}

**Relationship Classes**

A relationship class must be created for each relate table and LLID
table.

The following list shows the tables and their corresponding relationship
classes.

![](media/image4.png){width="2.8048611111111112in"
height="8.439583333333333in"}

The relationship classes follow two patterns:

-   event/relate table

-   LLID table

Here is an example of an event/relate table's relationship class:

![](media/image5.png){width="3.1354166666666665in" height="3.54375in"}

![](media/image6.png){width="3.1354166666666665in"
height="3.567361111111111in"}

Here is an example of an LLID table's relationship class:

![](media/image7.png){width="3.1354166666666665in"
height="3.567361111111111in"}

![](media/image8.png){width="3.1354166666666665in"
height="3.5034722222222223in"}

**Client transaction process**

The process flow for data checkout, editing, and checkin is described by
tracking a typical transaction. This process involves actions by the
client, and responses by the server. The overall transaction process
consists of the following major steps:

-   User identifies self and starts a transaction

-   User submits a checkout polygon covering desired area of work

-   Server locks the area

-   Server verifies checkout and extracts data to edit

-   User copies data from server and edits the data

-   User tests data

-   User submits data

-   Server recieves and tests data

-   Clearinghouse Manager accepts submission and posts changes, or
    rejects submission, in anticipation of a re-submission by the user,
    or aborts whole transaction.

-   Server releases the lock on the area

These basic steps are detailed in the following sample transaction flow
between a clearinghouse client and the clearinghouse server. Some of the
steps are performed using a web browser as the client, and some are
performed using a client-side ArcInfo AML application supplied as part
of this contract.

**User identifies self and starts a transaction**

-   The user connects a Java-enabled web browser (Internet Explorer or
    Netscape) to the clearinghouse web site.

![](media/image9.png){width="6.239583333333333in"
height="4.679861111111111in"}

-   The user selects "Select Watershed(s) on Map" or "Get FTP
    Transaction Number"

**If the user selected "Select Watershed(s) on Map":**

-   The following page is displayed:

> ![](media/image10.png){width="6.239583333333333in"
> height="4.679861111111111in"}

-   The user hits the "Select" button, then clicks on one watershed that
    is not red. Red indicates a checked-out area.

-   If the user selects a second point, a line is drawn.

-   If the user selects three or more points, a polygon is drawn.

-   All watersheds under the point, line, or polygon are selected.

-   The user hits the "submit" button

-   A form pops up requiring the user to enter a username and password.

-   The user is notified on the screen and by email of the transaction
    id.

```{=html}
<!-- -->
```
-   The watersheds are merged into a single polygon. This polygon is
    written to a coverage, named c\<transaction_id\> (example:
    transaction 356's checkout polygon coverage would be named c356, and
    its export file would be named c356.e00). This coverage is exported,
    and placed in the hydro_ftp\\sc24000\\checkout_covs directory.

-   An "end flag" file is then placed in the
    hydro_ftp\\sc24000\\checkout_covs directory. This is a signal file
    signifying that the file transfer of the main file is complete. The
    end flag file is named e\<transaction_id\>.txt (e.g. e356.txt)

```{=html}
<!-- -->
```
-   This file will be processed by the Clearinghouse Server to check out
    data, as described in the step after next.

**If the user selected "Get FTP Transaction Number":**

-   The user enters a username and password, and clicks "GO!".

-   The user will use ArcInfo offline to create a polygon coverage
    containing one polygon defining the area the user wants to edit.
    This coverage must be clean, without label errors, containing one
    real polygon and the universe polygon. It must be double precision,
    and stored in decimal degrees, NAD83. It must have a valid PRJ file.
    Any attributes will be ignored. Then the user will export and FTP
    this polygon coverage to the server.

```{=html}
<!-- -->
```
-   The name of the coverage must be c\<transaction_id\> and it's export
    file must be named c\<transaction_id\>.e00.

-   The user is instructed to then FTP an "end flag" file to the
    hydro_ftp\\sc24000\\checkout_covs directory. This is an empty file
    which signifies that the file transfer of the main file is complete.
    The end flag file is named e\<transaction_id\>.txt (e.g. e356.txt)

-   An AML named FTP_CHECKOUT_POLY.AML is provided to automate this
    process.

**Server verifies checkout and extracts data to edit**

-   In either case, an export file and an "end flag" file show up in the
    hydro_ftp\\sc24000\\incoming\\checkout_polys directory. This is the
    queue of pending checkouts. The server periodically scans this
    directory, and looks for files that have finished uploading, as
    signified by the presence of an "end flag" file.

-   A directory, named t\<transaction_id\> (e.g. t356), is created in
    the hydro_server\\sc24000\\transactions directory. Only the server
    has access to this directory.

-   This event is logged in the transaction table.

-   The export file is imported to a coverage named edit_boundary and
    validated:

-   One contiguous polygon

-   The export file name is the same name as transaction to which it
    applies

-   The server compares the polygon to the checkout polygon layer.

-   If there is no overlap with active transaction polygons, then this
    polygon is added to the STATUSMAP layer, with an attribute
    identifying its transaction_id

-   If it fails, mail is sent to the client, explaining the problem. The
    transaction is aborted. This event is logged in TRANSACTION_HISTORY
    as CKOUT_POLY_REJECT, and the transaction is closed with a
    disposition of CKOUT_POLY_REJECT in the TRANSACTION table.

-   The user must now go back to the web site and start a totally new
    transaction

-   The transaction is closed

-   If the checkout succeeded, the following sequence of events occurs:

-   The checkout polygon is inserted into the statusmap layer

-   In a future version, a new statusmap coverage is extracted to the
    hydro_server\\sc24000\\statusmap directory, showing the areas for
    active transactions only. These polygons are non-overlapping. This
    coverage is exported and placed in the
    hydro_server\\sc24000\\statusmap directory. The readme.txt file is
    placed in the hydro_server\\sc24000\\statusmap directory, indicating
    that the new statusmap.e00 is ready for download. These readme.txt
    files are used in several places in this system. A readme.txt file
    in this system serves two purposes:

-   It documents the purpose of the data in its directory, as well as
    contextual information such as date/time, transaction id, and
    username.

-   Its presence serves as a signal that the other data in the directory
    is not in the process of being copied, because the readme.txt file
    is always copied into the directory last.

-   The directory
    hydro_server\\sc24000\\transactions\\t\<transaction_id\>\\checked_out
    is populated with checked-out data for this transaction. The
    following coverages and tables are extracted from the SDE database
    and stored here:

-   wc -- watercourse routes are extracted if they fall completely
    within the checkout polygon. Event table rows are extracted if the
    LLID matches the extracted stream route.

-   wb - waterbody regions are extracted if they fall completely within
    the checkout polygon.

-   ws - waterbody shoreline routes are extracted if they fall
    completely within the checkout polygon. Event table rows are
    extracted if their LLIDs match the extracted waterbody shoreline
    routes' LLIDs.

-   Note: Event tables have same root name as the coverage, so they copy
    with the coverage.

-   wp - Water Points

-   Rows from the water point relate table are extracted if their LLIDs
    match extracted water points

-   Now we have four coverages of extracted data, and one coverage
    containing the checkout polygon.

-   Now waterbodies, shorelines, and watercourses that touch but are not
    completely within the checkout polygon are exported as background
    coverages named wb_back, ws_back, and wc_back, respectively. No
    background coverage is needed for water points, since a point has
    zero dimensions and physically cannot fall partially within the
    checkout polygon.

-   When the user sends back edits, we need all 4 export files back,
    even if changes were not made. The contents of these coverages will
    totally replace the extracted features, regardless of how few, if
    any, edits were made.

-   LLID tables are populated with ids of all features and event/relate
    rows checked out.

-   hydroqa.aml (quality assurance) is run against the checked-out data.
    If errors are detected, the transaction proceeds anyway. Data will
    be checked on insert, so errors in the database are not anticipated.
    However, if the database is populated using a batch load, or if the
    clearinghouse manager chooses to load data with known errors, or if
    additional QA/QC tests are implemented in the future, it is
    conceivable that there will be data in the server that does not pass
    the QA/QC tests. This step helps the prospective editor be aware of
    problems that may be encountered.

-   The error report is generated as a file named
    hydro_server\\sc24000\\transactions\\t\<transaction_id\>\\checked_out\\checkout_errors.txt

-   This error report file is copied to
    hydro_ftp\\sc24000\\outgoing\\checkout_covs\\t\<transaction_id\>\\checkout_errors.txt
    for the user to download.

-   This event logged to transactions table

-   The eight coverages that were extracted are exported directly to the
    hydro_ftp\\sc24000\\outgoing\\checkout_covs\\t\<transaction_id\>
    directory. Once the last coverage has been exported to this
    directory, a readme.txt file is placed there, to signal the user
    that the files are ready to download.

-   Mail sent to client that the checked-out data is ready to download

-   CHECKOUT_DATA_POST is logged to the TRANSACTION_HISTORY table

### User copies data from server and edits the data

-   The user gets email that the data for their transaction is ready to
    download.

-   The user runs hydrocheckout.aml, and enters the transaction id (e.g.
    t356) and FTP password. This AML does these things:

-   Creates a subdirectory named the transaction id (e.g.t356)

-   FTPs the eight .e00 files into this directory

-   Imports the .e00 files

-   Leaves the .e00 files in place in case the coverages are
    inadvertently corrupted by the user

-   FTPs the checkout_errors.txt file into this directory, which is an
    error report on the checked-out data. This error report was
    generated by the server when the data was extracted. The user can
    create a similar report by running hydroqa on the checked-out data.
    It is provided as a reference to compare against the error report
    that will be generated by hydroqa.aml immediately prior to checkin.

```{=html}
<!-- -->
```
-   The user edits the wc, wp, ws, and wb coverages, using the
    edit_boundary, wb_back, ws_back, and wc_back coverages as background
    coverages.

-   The user can employ any tools at their disposal to edit these
    coverages. Examples are SRT and STEVE. These tools are being
    modified to comply with data model changes resulting from the
    integration of data from Oregon framework partners, Washington
    framework partners, and Federal partners.

-   The coverages can be edited in place, or copied for editing in
    another workspace. The only requirement is that when the edits are
    ready to be checked in, the final changed coverages must copied back
    to this workspace under the original names of wc_checkin,
    wp_checkin, ws_checkin, and wb_checkin. This directory is the
    staging area for final QA/QC and checkin.

-   All edits must fall within the edit_boundary coverage

-   The user should not modify the edit_boundary, wc_back, ws_back and
    wb_back coverages.

-   The user is responsible for making sure the features line up
    properly between the wc, wp, and wb coverages

-   The user is responsible for ensuring that features snap properly to
    the background coverages.

-   The user is also responsible for ensuring that wb, wp, ws, and wc
    features line up properly with each other.

-   LLID is an id value that is an encoded long/lat value for a point on
    each feature. The decoded long/lat for all LLID values created for
    new features must fall within the edit_boundary polygon. This
    ensures that two editors do not simultaneously create the same LLID
    for different features.

-   LLID values do not need to be unique between the wc, wb, ws, and wp
    coverages. LLIDs must be unique within each coverage.

-   Other QA rules are documented below. The above rules are listed here
    to clarify the roles of the edit_boundary, wc_back, ws_back, and
    wb_back coverages.

**User tests data**

-   The client runs hydroqa.aml on the edited data, and corrects any
    errors detected.

-   The reason for this step is to avoid frustration and wasted time
    from multiple submissions and rejections. The user can test the data
    locally instead. Since the data will be clean before it is sent to
    the clearinghouse, rejected submissions should not be common.

**User submits data**

-   Now client FTPs edits back to the server by running
    hydrocheckin.aml. This AML exports the four edit coverages only. The
    other coverages are not supposed to be edited anyway, so there is no
    point in sending them back to the server.

-   An error report is run immediately prior to FTPing the export files
    to the server. Depending on the severity of the errors, and whether
    these errors are problems which were inherited when the data was
    checked out in the first place, the clearinghouse manager may or may
    not decide to allow this data back into the clearinghouse.

-   The error report is written to a file called checkin_errors.txt. If
    the checkin is aborted and retried later, the error report will be
    overwritten by the next checkin. There will be two error reports in
    the directory:

-   Checkout_errors.txt -- downloaded error report on checked-out data

-   Checkin_errors.txt -- locally generated error report on data for
    last attempted checkin.

-   Note: the user can run hydroqa.aml anytime they want, and name the
    error files anything they want. These two files are just the ones
    that automatically show up when the user checks out or checks in
    data. Their purpose is to report the error status of the data that
    was checked out and checked back in.

**Server receives and tests data**

-   The server is constantly polling the
    hydro_ftp\\sc24000\\incoming\\checkin_covs directory. When it
    encounters a subdirectory called t\<transaction_id\> (e.g. t356), it
    checks in that directory for a file named readme.txt. This is a
    signal file whose presence indicates that the client has finished
    populating this directory.

-   The server now checks the transaction table and that the
    transaction_id refers to an open transaction that is ready for data
    submission. If not, then:

```{=html}
<!-- -->
```
-   If any data shows up in the checkout_polys or checkin_covs
    directories that refers to prevoiusly active, currently dead
    transaction ids, (e.g. if the transaction id can be found in the
    transaction table), it is archived in
    hydro_server\\sc24000\\transactions\\t\<trans_id\>\\rejected\\\<date_time\>,
    and mail is sent to the owner of the dead transaction.

-   If any data shows up in the checkout_polys or checkin_covs
    directories that refers to nonexistent transaction ids, (e.g. if the
    transaction id *cannot* be found in the transaction table), it is
    stored in hydro_ftp\\sc24000\\wastebasket, and mail is sent to ther
    clearinghouse manager. This directory can be cleaned up by the
    clearinghouse manager as desired.

```{=html}
<!-- -->
```
-   Files older than a month may be deleted from the wastebasket
    directory. The only reason the files are not deleted outright is to
    provide a mechanism for recovering from an error in transaction
    management that resulted in the submitted data being thrown away.

-   If the transaction is valid:

-   the server moves the export files to a \<date_time\> directory in
    the
    hydro_server\\sc24000\\transactions\\t\<transaction_id\>\\submitted
    directory, then it deletes the
    hydro_ftp\\sc24000\\incoming\\checkin_covs\\ t\<transaction_id\>
    directory.

-   A CKIN_DATA_RECV code is added to the transaction_history table.

-   Mail is sent to the client confirming receipt of the data.

-   The server runs hydroqa.aml, and posts the report in
    hydro_server\\sc24000\\transactions\\t\<transaction_id\>\\submitted\\\<date_time\>checkin_errors.txt

-   Mail is sent to the clearinghouse manager, informing him that data
    has been submitted.

-   A CKIN_PROCESSED code is added to the transaction_history table.

Clearinghouse Manager accepts submission and posts changes, or rejects
submission, in anticipation of a re-submission by the user, or aborts
whole transaction.

-   The clearinghouse manager reviews the checkin_errors.txt file,
    displays the data on the screen for a quick check, and decides
    whether to accept or reject the submission. Comments are added to
    the checkin_errors.txt file, if desired.

-   If the submission is rejected:

-   Mail is automatically sent to the user explaining why the submission
    was rejected.

-   The data is moved to
    hydro_server\\sc24000\\transactions\\t\<transaction_id\>\\rejected\\\<date_time\>

-   A CKIN_REJECT code is added to the transaction_history table

-   The clearinghouse manager should generally contact the user
    directly, and explain the problem.

-   If the submission is accepted:

-   Mail is automatically sent to the user verifying that the submission
    was accepted.

-   A CKIN_ACCEPT code is added to the transaction_history table

```{=html}
<!-- -->
```
-   An SDE version named \<transaction_id\> is created. Database changes
    are made to this version.

-   The SDE logfile created at checkout time is used to delete all
    features or rows that were checked out.

-   The checked-in features and rows are inserted into this version.

-   This version is reconciled against the default version. No conflicts
    should occur. If so, urgent error mail is sent to the clearinghouse
    manager.

-   This version is posted to the default version

-   The checkout polygon is marked and dated as retired from the
    statusmap layer. This area is now ready for new edits.

-   A CKIN_ACCEPT code is added to the transaction_history table

**Quality assurance tests**

An AML named hydroqa.aml is provided to perform quality assurance of the
data. It calls other AMLs. These AMLs do these things:

**Overall functionality:**

-   Print the version when hydroqa is run without arguments

-   Print out usage when hydroqa is run without arguments

-   Save any &amlpath and &menupath settings and reset for this
    application, then restore on exit.

-   Check to see that all component parts for the full QA are present.
    If not, print message and bail out. No report is created with this.

-   Print the version of hydroqa.aml on the report, so discrepancies
    which are due to different versions of hydroqa.aml can be
    reconciled. This could be important if a user gets a clean report
    from his version of hydroqa.aml, but the clearinghouse manager's
    report shows errors.

-   The report tags error lines:

-   Prefix lines with \$\$ as a special character so the report can be
    parsed by a program.

-   Next char is F = fatal, E = error, W = warning, or M = Message

-   \<err_no\> = error number (a lookup table of Error descriptions is
    provided)

-   \<obj_type\> = the type of object having the problem (event, wc
    route, wp point, relate table row, etc)

-   \<feature id\> if applicable

-   description of the problem (from the lookup table of error numbers)

Specific tests:

-   Directory structure

-   Coverage and subclass names

-   Topology

-   Precision

-   Projection

-   Table names

-   Table definitions

-   Item names

-   Event table/relate table names and definitions

```{=html}
<!-- -->
```
-   All features fall inside checked-out boundary.

-   Unique LLID

-   A point made from the decoded LLID falls in the checkout

-   LLIDs only have to be unique within a coverage type (WC, WB, WS,
    WP).

-   All arcs must be represented in a route. This is handled
    automatically by the server, so it does not need to be verified

-   All WC arcs run downstream (i.e. the opposite direction from the
    route) -- This is also handled automatically by the server, so it is
    not tested.

**Special handling of domain lookup tables**

Every domain consisting of a set of discrete values such as FTR_SRC_CD
will be copied out to the user as a lookup table.

These domain lookup tables are not to be modified by the user: changes
to these tables will not be loaded back into the clearinghouse. Changes
to these tables can only be made directly by the clearinghouse manager.
Furthermore, codes should not be changed or deleted by the clearinghouse
manager, because that would invalidate existing data that uses these
codes. Codes should only be added to domain lookup tables.

These tables can be used by the QA software to ensure that invalid codes
are not entered. When run on the client, there is no way to ensure that
the tables have not been modified. So final QA on the server will be run
using domain lookup tables supplied by the server, not the ones that
were sent back with the checkin coverages.

The tables are stored in one place on the clearinghouse DBMS server, but
they are copied out using the above format and exported with the
coverages as they are checked out.

**General operating rules**

-   All mail from client goes to clearinghouse manager. The server only
    sends mail. It does not receive mail.

-   The server constantly polls the
    hydro_ftp\\sc24000\\incoming\\checkout_polys directory and the
    hydro_ftp\\sc24000\\incoming\\checkin_covs directory.

-   If any data shows up in the checkout_polys or checkin_covs
    directories that refers to prevoiusly active, currently dead
    transaction ids, (e.g. if the transaction id can be found in the
    transaction table), it is archived in
    hydro_server\\sc24000\\transactions\\t\<trans_id\>\\rejected\\\<date_time\>,
    and mail is sent to the owner of the dead transaction.

-   If any data shows up in the checkout_polys or checkin_covs
    directories that refers to nonexistent transaction ids, (e.g. if the
    transaction id *cannot* be found in the transaction table), it is
    stored in hydro_ftp\\sc24000\\wastebasket, and mail is sent to ther
    clearinghouse manager. This directory can be cleaned up by the
    clearinghouse manager as desired.

```{=html}
<!-- -->
```
-   The event tables are named \<cov\>.\<table\>\_evt in the
    coverage/info format, but these names are translated to remove the
    dot(".") before insertion into SDE, to comply with ORACLE naming
    rules

    Directory structure

The following section presents a layout of the directory structure of a
clearinghouse for 1:24000 and other scale hydro data. Example
transaction 356 is shown. Note that no single transaction could actually
have files in all these locations at the same time. This diagram shows
the directory layout, and is not an illustration of the transaction
process.

FTP Directory:

-   chse_ftp

-   sc24000

-   incoming

-   checkin_covs

-   t356

-   checkin_errors.txt

-   wc_checkin.e00

-   wb_checkin.e00

-   ws_checkin.e00

-   wp_checkin.e00

-   readme.txt

-   checkout_polys

-   c356.e00

-   c356.txt

-   outgoing

-   checkout_covs

-   t356

-   wc.e00

-   wb.e00

-   ws.e00

-   wp.e00

-   wc_back.e00

-   wb_back.e00

-   ws_back.e00

-   edit_boundary.e00

-   checkout_errors.txt

-   readme.txt

-   statusmap

-   statusmap.e00

-   readme.txt

-   hydro_data

-   wc.e00

-   wb.e00

-   ws.e00

-   wp.e00

-   readme.txt

-   aml

-   unix

-   hydroqa.tar

-   nt

-   hydroqa.zip

-   wastebasket

-   20010209110748

-   20010208124429

```{=html}
<!-- -->
```
-   

Clearinghouse server directory:

-   chse_exe

-   chse_server

-   aml

-   help

-   reference

-   temporary_covs

-   bin

-   managerxxx.mxd

-   doc

-   overviewxx.doc

-   notes -- assorted notes, not directly related

-   web_client -- readme & help files for web interface

-   etc

-   chse.defs

-   chse_server_log.txt

-   internet

-   cov

-   huc

-   info

-   readme.txt

-   MO_web

-   Temp

-   checkoutpoly.aml

-   test_checkoutpoly.aml

-   lockfiles

-   chse_server_lock.txt

-   to_start_server_after_a_crash.txt

-   to_stop_server.txt

-   sde_scripts

-   Define_trans_tables18.sql

-   SDE_layer_parameters.txt

-   sde_setup.txt

-   utilities

-   Useful things not used by server

-   sc24000

-   statusmap

-   statusmap (future version)

-   info

-   transactions

-   t356

-   checked_out

-   t356

```{=html}
<!-- -->
```
-   wc

-   wb

-   ws

-   wp

-   wc_back

-   wb_back

-   ws_back

```{=html}
<!-- -->
```
-   edit_boundary

-   info

-   c356.e00

-   checkout_errors.txt

-   readme.txt

-   submitted

-   20010210345426

-   t356

-   edit_boundary

-   wc

-   wb

-   ws

-   wp

-   edit_boundary.e00

-   wc.e00

-   wb.e00

-   ws.e00

-   wp.e00

-   info

-   client_checkin_errors.txt

-   server_checkin_errors.txt

-   readme.txt

-   20010209223213

-   t356

-   edit_boundary

-   wc

-   wb

-   ws

-   wp

-   edit_boundary.e00

-   wc.e00

-   wb.e00

-   ws.e00

-   wp.e00

-   info

-   client_checkin_errors.txt

-   server_checkin_errors.txt

-   readme.txt

SDE layers and tables

-   statusmap

-   huc

-   wc

-   wb

-   ws

-   wp

-   event tables

-   relate tables

-   lookup tables

-   transactions table

-   editors table

-   transaction_history table

```{=html}
<!-- -->
```
-   

**Enhancements**

> With any system, there are opportunities for improvement or desired
> enhancements. The following list is provided to assist REO in planning
> for ongoing improvement of the Clearinghouse server.

-   Additional quality assurance tests. This version of the server and
    client comes with basic QA to determine that all coverages are
    present, and that they have the correct precision, projection, and
    topology. Additionally, the attribute tables, relate tables, and
    event tables must have the correct layout and item definitions. The
    QA tests further verify that features all fall completely inside the
    edit boundary polygon. Furthermore, it determines that all LLID
    values are unique within the checkout polygon, and the positions
    represented by decoded LLID values all fall inside the checkout
    polygon. A framework for adding additional QA routines is provided.
    Numerous QA AMLs are available from the hydro partners, including
    Washington DNR (Travis Butcher, Washington Dept of Ecology (Dan
    Saul), REO
    (http://[www.reo.gov/hydro/cgm](http://www.reo.gov/hydro/cgm)) and
    others. The code for many of these tests has already been written.
    Two approaches can be taken for incorporating these programs:
    Standardize them into the clearinghouse AML system, or simply run
    them and incorporate their reports by concatenating them to the main
    report. The first approach would result in a more standardized
    report that would be easier to interpret. The second method would be
    cheaper to implement, and easier to incorporate updates from
    contributing agencies. QA enhancement requests are:

```{=html}
<!-- -->
```
-   QA requiring the comparison of "before" and "after" coverages.

-   Verify that no streams were deleted in the editing process. This
    request is from the BLM (Dan Wickwire). It has not been determined
    if the same test is required for the other hydro layers.

-   Verify that the route measures at stream confluences have not
    shifted during editing. This requirement is optional for conversion
    from 1:100000 data to 1:24000+ data, but it is not optional for the
    editing of 1:24000+ scale data. An untested AML has been written by
    Washington DNR (Charlie Ware) to do this test. It is named
    CHECK_MEAS.AML. A copy has been included in the AML directory.

-   Verify that section event tables extracted from the clearinghouse
    cover all portions of routes to be extracted. This test would be run
    on the server only. If the stored section event tables are
    incomplete for some reason, routes or portions of routes would be
    lost in the extracted data. Routes are rebuilt from section events.
    Any routes or portions of routes not covered by section events would
    not get rebuilt. Two cases that could result in missing section
    events are:

-   Route editing or creation without using the clearinghouse server
    application to check in or check out data.

-   A bug or crash of the clearinghouse server application that results
    in section event records not being stored.

-   QA that can be done on a single coverage. Verify that:

```{=html}
<!-- -->
```
-   All code values are present in the corresponding lookup tables

-   All required fields are populated

-   All required event records are present

-   Features edgematch to the background coverage

-   No watercourse (WC) routes cross each other except canals

-   Braids may touch at the upstream ends, but they do not cross

-   The downstream end of each watercourse route must connect cleanly.
    No over- or under- shoots, within a tolerance.

-   All watercourse routes have ascending measures upstream

-   All route measures are unique within shoreline (WS) and watercourse
    (WS) routes, and monotonically increasing

-   Events fall at least partly on a route. An event may have a measure
    value of 9999, meaning that the event goes to end of a route, even
    if the route is extended later.

-   There is at least one metadata event for each route, each time it is
    edited

-   Waterbody (WB) areas must be single-part and non-overlapping

-   Shoreline (WS) routes having a default shoreline code of "Y" must be
    coincident with the boundary of one or more waterbody regions

-   Each waterbody region must be routed with shoreline routes having a
    default shoreline code of "Y". These routes cannot overlap with
    other default shoreline routes.

-   There is no restriction on the placement of shoreline routes having
    a default shoreline code of "N"

```{=html}
<!-- -->
```
-   Extensive documentation. This system comes with a system design,
    installation, and operation document, help files, and code comments.
    There are other forms of documentation that may be useful:

-   Data submittal guide. During the data design meetings, the need for
    a data submittal guide was discussed to help participants create
    data that adheres to the standards. This guide would include such
    topics as routing rules, feature-level metadata standards, etc

-   User guide for the QA AMLs, web interface, and FTP client.

-   System operations manual

-   Process flow diagrams

-   System architecture diagrams

-   Program architecture diagrams

-   Detailed installation guide

-   Bulletproof the server. The server logs all significant events both
    to transaction tables and to a log file. It keeps copies of all
    extracted and submitted data. It stores the data in Oracle. It uses
    a temporary ArcSDE version during checkin to avoid creating
    incomplete and corrupt data during a failed checkin. Server errors
    are reported to the clearinghouse manager via email. Still, more
    bulletproofing can be done. The ChseServer class has two methods
    named BeginTrans and EndTrans. These methods are called before and
    after each major transaction processing step. Currently, these
    methods do nothing. The plan is that BeginTrans will create a
    persistent record that a transaction step has been started, and
    EndTrans will remove this persistent record when the step finishes.
    When the server starts up, it will check for a crash by looking for
    data created by BeginTrans. If it finds this data, it will know the
    transaction number and transaction step that failed. It will enter
    recovery mode and redo or undo the step that failed. After coding
    these methods, the server should be tested by deliberately crashing
    it during various transaction steps to verify that it can
    successfully recover.

-   Enhanced security features.

-   Each transaction comes with a validation code. This code is a random
    number which could be used to ensure that data submitted to the
    incoming FTP site actually originated with the owner of the
    transaction. Currently, only the transaction id is used for this
    purpose, and the transaction ids are public. To implement this
    feature, the incoming transactions would be required to have the
    validation code in their readme.txt files.

-   Encrypt passwords. Currently, editor passwords are kept in an
    unencrytped column in the EDITORS table. These passwords are only
    used by the Clearinghouse server. They are not OS or database
    passwords, so they do not represent a security hole in that regard.
    The risk is that someone could masquerade as a data editor if they
    got a password. They would probably be detected quickly, as Email is
    sent to each editor whenever they check in or check out data.

-   Data browsing website. The web pages delivered with this application
    have limited usefulness to people who are not involved in the
    editing process. A website that showcases the clearinghouse data
    could be a valuable contribution to data dissemination by REO as a
    node in the Geography Network.

-   Event data display. Display of event data at this website is also an
    enhancement to be considered. A method of pre-calculating event
    locations would make events display quickly.

-   Data download server. Various approaches could be used here,
    including nightly exports of the data, either as a single huge
    export file, or broken down by watershed. An on-demand database
    query and sownload would also be an option here. Some users may want
    to download a copy of some data for a project, but there is a danger
    here that they might try to submit changes to this data, thus
    potentially stepping on someone else's edits. This "readonly" web
    site should be carefully positioned to data editors so they do not
    "end run" the transaction process by editing data they copied from
    this "readonly" web site.

-   Some of these "readonly" pages would be useful to editors as well as
    browsers:

-   Checked-out areas

-   Query of editing history by location -- just the checkout boundary
    polygons

-   Note about feature history: We are currently keeping a separate
    version for each completed transaction. These versions can be used
    to display historical data. As these transactions can have a lot of
    data, the system performance may degrade over time, necessitating a
    policy change whereby old versions are deleted. Therefore, it would
    be unwise to promise the ability to display and query historical
    data until the system has been in operation long enough to determine
    the feasibility.

-   A feature to detect non-unique LLIDs even if they are inaccurately
    placed. The system currently checks for unique LLIDs by determining
    if the LLIDs are unique within the checkout polygon, and verifying
    that each LLID is derived from a Longitude, Latitude value that
    falls inside the checkout polygon. This method works if the data are
    perfect. An enhancement is proposed to make it work with imperfect
    data. Two forms of imperfect data are anticipated:

-   1\. LLIDs in the 1:100,000 data were generated from features in the
    NAD27 datum, even though the features themselves are stored in
    Decimal Degrees in the NAD83 datum. In this clearinghouse server
    implementation, this was used as the standard, so all LLIDs are
    projected from NAD27 to a temporary point coverage in the NAD83
    datum for testing to verify that they fall inside the checkout
    polygon. This projection results in a shift of about 100 meters. If
    data are submitted with LLIDs generated in the NAD83 datum, they may
    fall outside the checkout polygon in some cases, resulting in the
    detection of a spurious data error by the server. If LLIDs of
    features in bordering data describe points that fall inside the
    checkout polygon, there is a small probability that a legitimate
    feature created inside the checkout polygon will have this same
    LLID, thus resulting in a condition of non-unique LLIDs in the
    database.

-   2\. As data are conflated from 1:100000 to 1:24000+, a feature may
    move away from the point represented by its LLID. The same alpha and
    beta errors described above may occur in this case.

-   A solution to this problem could involve the checking out of a
    buffer zone around the checkout polygon, and the generation of a
    separate list of LLIDs for features in this buffer zone. No features
    could be added that fall inside this buffer zone. It would be for
    LLID uniqueness only. LLIDs would need to:

-   Be within a tolerance of their respective features.

-   Be unique within the checkout polygon AND the buffer zone.

-   Eliminate the need for the storage of section event tables. The
    system currently stores the sections of the two route layers (WS and
    WC) as events. This is done so that the same sections will result in
    data checked out as existed on the data that were checked in. The
    default condition is that a separate section results between each
    pair of vertices. Attempts to dissolve sections in the coverage data
    model will not work without using these section event tables due to
    precision issues. Routes longer than 1000 km are anticipated with
    vertex spacing as close as 1 m are anticipated. Measures are stored
    in the coverage as single precision floating point numbers. Any
    reasonable "length-measure ratio" section dissolve tolerance on this
    data would be subject to too much rounding error to be effective.
    The measures are stored much more precisely in ArcSDE, however. The
    section breakpoints could be derived in ArcSDE, before the coverage
    is exported. A section event table could be derived on the fly as
    the data are extracted, then used the same way section event tables
    are currently used to process output data. The advantage here is
    that geodatabase tools could be used to edit routes directly. Under
    the current system, only coverage editing can be done, because of
    the need to preserve section breakpoints as section event tables.

```{=html}
<!-- -->
```
-   Submit planned checkin date at checkout time, and implement a system
    of email reminders. Currently, the checkout date and a deadline date
    are stored. The deadline is calculated as checkout date + one week.
    The two specific enhancements are a flexible system for assigning
    deadlines, and a system of email reminders for overdue transactions.

-   Add a comment box on checkout polygons so people can query to find
    out why an edit is being done. This would require the addition of a
    comment field to the transaction table, modification of the three
    user interfaces that create transactions, and an enhancement to the
    web interface for displaying checked out areas. This enhancement was
    requested by Andrew Kinney of the Thurston Geodata Center.

-   Set up a system whereby stakeholders can be automatically notified
    by email when transaction activity occurs in their areas of
    interest. This could be a useful tool for multiple data custodians
    to keep track of activity in their overlapping areas of
    responsibility. This could be implemented with a multipart polygon
    layer with 1 row for each person to be notified. The multipart
    polygon would cover their area of interest. Each major transaction
    event such as checkout, data submission, transaction post, or
    transaction abort would generate email when it happened in their
    area of interest. It would probably be simplest to join this layer
    to the EDITORS table, and add a field indicating the editor's role
    (editor, data custodian, interested party, etc.) only certain roles
    could actually edit data. This enhancement was requested by Diane
    Ransford of the Siauislaw national forest.

```{=html}
<!-- -->
```
-   Add the ability to check out portions of long routes that fall
    outside the checkout polygon. Currently, all features to be edited
    must fall inside the checkout polygon. To edit a small area with a
    1000 km route passing through it, the user has two choices:

-   Don't edit it

-   Create a checkout polygon that includes the area of interest with
    long, skinny protrusions that surrounds the long route. This polygon
    could be created by buffering the long river, and merging the buffer
    polygon with the area of interest polygon. The resulting checkout
    polygon would resemble a snake that ate a rabbit.

One proposed method is to split features that cross the checkout
polygon, give the parts to different editors, and merge the parts when
the data are checked in. This method will take a considerable amount of
programming effort, because it lowers transaction granularity from the
feature level to the sub-feature level. It also poses unsolved technical
issues. For example, what happens to the events when two people are
editing the same long route? Events often span long portions of routes,
and changes to event measures can cause the events to change position
along the route, sliding them into the other editor's territory. Also,
it is unclear how to assign events to different editors using this
method. For example, WC_EVT_NAME typically spans the entire route. In a
split feature scenario, which editor is allowed to edit this event? It
would be very difficult to allow two people to work on the same long
route without the possibility of edits to the measures causing conflicts
on the event the data, and it is unclear how events spanning long
reaches of the route can be shared between different editors.

Here is an alternate approach for long route management that only allows
one editor on each route: The user checks out a reasonably sized
polygonal area of data using the standard method. Then, in the same
transaction, the user types in the LLIDs of one or more long routes that
pass through the polygonal checkout area. These LLIDs are added to a
list of a special checked out long routes, so that nobody else will edit
them. This list of LLIDs and user IDs of people editing them would be
maintained on the server as an Oracle table. When the user checks in his
data, an integrity check is done to verify that no geometry changes were
made to the long route except where the route falls inside the checkout
polygon. Thus, features edited in other transactions can still snap to
the long route in a background coverage. The user can make any event
changes necessary. This appears to be the only practical way to
guarantee that editors do not conflict with each other on a long route
without checking out a polygonal area that completely surrounds the
route. It works because only one person is editing a route at a time,
and they are not making geometry changes that would cause snapping
problems for other editors.