5.1 Introduction |
Top |
In addition to the direct entry of advisory and monitoring data through the use of data entry screens, Beach Watch provides the local health agencies with the means to perform a "batch" upload of data into the system. The direct data entry procedure was described in the previous chapters. This chapter describes the batch upload process.
The Beach Watch system receives two types of data files through the batch upload process. They are:
There is a predefined file format which local users must follow to prepare the data file. The data file is a comma separated values text file with predefined record structure. Beach Watch does not have any restrictions about how the file is prepared by local agencies. The Beach Advisory database system developed by Southern County Coastal Water Research Project (SCCWRP) has the functionality to generate the data files in the required format. So the agencies that are using SCCWRP database (Southern California Counties currently) for tracking the coastline advisory data locally can use the functionality to generate these files from SCCWRP System. The agencies that do not have SCCWRP databases in use can use any text editor, spread sheet or database that is capable of producing the the output as comma separated text file.
The two data file upload processes are explained below, in terms of their data file formats and associated operational procedures.
5.2 Beach Advisory Data Upload |
Top |
Beach Watch will receive beach advisory data (Postings, Closures, Rain Advisories data) from local health agencies. For this process the Local Health Agencies must place the advisory into a predefined format. This section describes the format of this data file and the procedures to load the file into Beach Watch.
Some of the important characteristics of the data file format are:
The data
file uses identifiers or a valid list of values for many fields in the file.
Examples include beach identifier, station identifier, indicator codes, suspected
substances, and additional actions. The data file producers, local health agencies,
have to obtain these identifiers from Beach Watch in order to use them in the
data files.
The information in a record is organized into 5 groups. They include the following:
The Relationship between general and the other groups is one to many. For example,
an advisory will have only one instance of general details but can have multiple
instances of lengths, indicators, substances and additional actions taken. However,
there is no direct relationship between instances of one detail group to another
detail group.
In the data file, general details of the advisory are not repeated in the records
when there are multiple instances of lengths, indicators, substances and actions.
A typical advisory record with two affected lengths records, three indicators,
two substances and one additional action would look like the following table.
Table: Advisory Record Composition
| General Details Group | Affected Length Details Group | Indicator Details Group | Substance Details Group | Addl. Action Details Group |
| General - 1 | Length - 1.1 | Indicator 1.1 | Substance - 1.1 | Action - 1.1 |
| Length - 1.2 | Indicator 1.2 | Substance - 1.2 | ||
| Indicator 1.3 | ||||
| General - 2 | Length - 2.1 | Indicator 2.1 | Substance - 2.1 | Action - 2.1 |
| Length - 2.2 | Indicator 2.2 | Substance - 2.2 | ||
| Indicator 2.3 |
The following table details the data fields in a record and their definitions
in the data file.
|
Field Name |
Data Type |
Format/Mask |
Valid Values |
Required Field? |
Description |
|
Station Identifier |
String |
X(10) |
Yes |
Yes |
A unique identifier given to each sampling station
issued by Beach Watch. The sampling station identifiers will be made available
for download via Beach Watch. The system will also have an interface to
register the new beaches and obtain these identifiers. |
|
Station Name |
String |
X(50) |
No |
No |
Name of the sampling station. This value is not
used by Beach Watch identify the station. This is only for submitting
party’s (County) reference only. |
|
Activity Code |
String |
X(10) |
Yes |
Yes |
Advisory code. The value has to come from a list
of valid values. Examples include “Beach Posting”, “Beach Closure”, ”Rain
Advisory”, “Permanent Posting”. The complete list of activity codes will
be made available for download via Beach Watch |
|
Source Code |
String |
X(10) |
Yes |
Yes |
Source code of the activity. This has to come from
a list of valid values. Examples include””, “”, “”. The complete list
of source codes will be made available for download via Beach Watch. |
|
Cause Code |
String |
X(10) |
Yes |
Yes |
Cause code of the activity. This has to come from
a list of valid values. Examples include””, “”, “”. The complete list
of cause codes will be made available for download via Beach Watch. |
|
Start Date |
Date |
MM/DD/YYYY |
No |
Yes |
Start date of the activity. In other words the date
on which the beach is posted or closed. |
|
Start Time |
Time |
HH24:MI |
No |
No |
Start time of the activity. In other words the time
on which the beach is posted or closed. |
|
End Date |
Date |
MM/DD/YYYY |
No |
No |
End date of the activity. In other words the date
on which the beach is reopened. |
|
End Time |
Time |
HH24:MI |
No |
No |
End time of the activity. In other words the time
on which the beach is reopened. |
|
Location Latitude |
String |
S999.99999 |
No |
No |
Latitude of activity location. This is expressed
as degree, minutes and seconds with the direction. |
|
Location longitude |
String |
S999.99999 |
No |
No |
Longitude of activity location. This is expressed
as degree, minutes and seconds with the direction. |
|
Area Description |
String |
X(255) |
No |
No |
The narrative description
of the area where the activity took place. |
|
Affected date |
Date |
MM/DD/YYYY |
No |
No |
Starting date of the affect (of the length that
is reported) |
|
Affected length |
Number |
999.99 |
No |
No |
Affected length measurements. |
|
Length units code |
String |
X(10) |
No |
No |
Units in which length is
reported. This has to come from a list of valid values. Examples include
“Yards”, “Miles”. The complete list of length units will be made available
for down load via Beach Watch |
|
Indicator Code |
String |
X(10) |
Yes |
No |
The code of the indicator
that is used in issuing the advisory activity. This has to come from a
list of valid values. Examples include “E. Coli”, “Entercoccus”. The complete
list of indicators will be made available for down load via Beach Watch |
|
Substance Code |
String |
X(10) |
Yes |
No |
The code of the substance
that is suspected/found in the water. This has to come from a list of
valid values. Examples include “Primary”, “Secondary”. The complete list
of substances will be made available for down load via Beach Watch |
|
Substance Volume |
Number |
999.99 |
No |
No |
The volume measurement of
the substance suspected/found in water. |
|
Substance Units |
String |
X(10) |
Yes |
No |
Units in which substance
volume is measured. This has to come from a list of valid values. Examples
include “Gallons”, “CFT”. The complete list of volume units will be made
available for down load via Beach Watch |
|
Additional Action Code |
String |
X(10) |
Yes |
No |
Code of the additional actions
taken during the activity period. This has to come from a list of valid
values. Examples include “Radio announcement”, “Press release”. The complete
list of additional action codes will be made available for down load via
Beach Watch |
|
Action Date |
Date |
MM/DD/YYYY |
No |
No |
Date on which this particular
action is taken |
The following graphic presents a sample layout of the beach advisory data file, a comma-separated values file opened in Excel. The column headings are displayed for reference purpose and the actual data file shall not contain column headings in it. The Identifiers used in this sample layout are fictitious values for this example. The actual list of valid values will be made available for download from Beach Watch website.
Figure:
Advisory Activity Data File - Sample Layout
Click on “Advisory Activity Sample Data File” to see the sample data file opened in Excel.
Data
File Upload - File Selection
(A) Valid Data File and contents: If all the validations of the uploaded data file are successful, the following screen is displayed along with the uploaded data . Users are encouraged to review the data displayed on the screen.
Data File Upload - Data Submittal Successful Load
Or
Data
File Upload - Data Submittal Deletion
(B) Invalid Data File and contents: If all the validations of the uploaded data file are not successful, the following screen is displayed along with the uploaded data. The rows that failed the validations are highlighted in yellow and the data errors are highlighted in red. Carefully review all the errors displayed on the screen. A detailed error report generated as comma separated values text file is available for view through a link on top of the screen. You can download the file to review and assist with correcting the original data file before reloading. Beach Watch will not accept upload files that are not successfully validated.
Data
File Upload - Invalid Data
Data
File Upload - Data Submittal Deletion
5.3 Monitoring(Lab Results) Data Upload |
Top |
Beach Watch will receive beach-monitoring data (analysis of samples) from local health agencies. In this process the local health agencies must place beach monitoring data into a predefined format data file. This section describes the format of this data file and the procedures to load the contents into the Beach Watch database.
Some of the important characteristics of the data file format are:
The data
file uses identifiers or a valid list of values for many fields in the data
file. Examples include beach identifier, station identifier, indicator codes,
suspected substances, and additional actions. Local Health Agencies must obtain
these identifiers from the Beach Watch system in order to use them in the data
files.
The following table details the data fields in a record and their definitions
in the data file.
|
Field Name |
Date Type |
Format/ Mask |
Valid Values? |
Required field? |
Description |
|
Station Identifier |
String |
X(10) |
Yes |
Yes |
A unique identifier given to each sampling station
issued by Beach Watch. The sampling station identifiers will be made available
for download via Beach Watch. The system will also have an interface to
register the new beaches and obtain these identifiers. |
|
Station Name |
String |
X(50) |
No |
No |
Name of the station. This value is not used by Beach
Watch identify the station. This value is only for submitting party’s
(County) reference only. |
|
Sample Date |
Date |
MM/DD/YYYY |
No |
Yes |
The date of sampling activity. Typically three or
four analytical results are obtained from a sample collected in a sampling
activity. All the analytical results obtained from a sample collected
during a sampling activity will have the same sample date and time. |
|
Sample Time |
Time |
HH24:MI:SS |
No |
No |
The time of sampling activity. Typically three or
four analytical results are obtained from a sample collected in a sampling
activity. All the analytical results obtained from a sample collected
during a sampling activity will have the same sample date and time. |
|
Sample Type |
String |
X(10) |
Yes |
No |
Type of the sample that gives appropriate meaning
to the results. Examples include “Comparison”, “Duplicate”, “Results”,
“QC Check” etc. The complete list of valid values will be made available
for download via Beach Watch. |
|
Sample Depth in Inches |
Number |
N(6,2) |
No |
No |
Depth in inches at which the sample is collected.
If the depth is not reported, 6 inches is assumed as the default value.
|
|
Lab Identifier |
String |
X(10) |
Yes |
No |
A unique identifier given
to each of the laboratory that works for agencies. The complete list of
lab identifiers will be made available for download via Beach Watch. The system will also have
an interface to register the new laboratories and obtain these identifiers. |
|
Lab Process Start Date |
Date |
MM/DD/YYYY |
No |
Yes |
Date (start) of the analysis. |
|
Lab Process Start Time |
Time |
HH24:MI:SS |
No |
Yes |
Time (start) of the analysis. |
|
Analytical Procedure Identifier |
String |
X(10) |
Yes |
Yes |
A unique code for the analytical procedure used
to obtain the result. Examples include “EPA 1600”,”Colilert 18”. The complete
list of analytical procedure identifier will be made available for download
via Beach Watch. |
|
Parameter Identifier |
String |
X(20) |
Yes |
Yes |
Identifier of the parameter
or stressor measured or computed in the result. Examples include “E. Coli”,
”Enterococcus”, ”Fecal Coliforms”. The complete list of parameter identifiers
will be made available for download via Beach Watch. |
|
Result qualifier |
String |
X(05) |
Yes |
Yes |
Qualifier of the result.
The examples include “=”,”>”,”<“ etc. The complete list of qualifiers
will be made available for download via Beach Watch. |
|
Result Value |
Number |
9(13,2) |
No |
Yes |
The value of the result.
|
|
Result Units |
String |
X(10) |
Yes |
Yes |
Units in which the result
is measured or computed. Examples include “CFU/100ml” and “MPN/100ml”.
The complete list of result units will be made available for download
via Beach Watch. |
|
Statistic Method |
String |
X(10) |
Yes |
No |
The statistic method used
to compute the result. Examples include “Mean”, ”Median”. The complete
list of statistic method codes will be made available for download via
Beach Watch. |
|
Duration Basis |
String |
X(10) |
Yes |
No |
The period of time over
which a measurement was made. In some cases, it indicates an analytical
procedure that has a prescribed duration, while in other cases it indicates
a period of time within which a certain effect or result might occur.
This qualifier may also indicate a period of which over which a cumulative
or averaged measurement occurs. This code must come from a list of valid
values. Examples include 24 Hour, 96 Hour, 1 Day, 3 Day. The complete
list of the duration codes will be made available for download via Beach Watch. |
|
Result Comments |
String |
X(255) |
No |
No |
Any additional descriptive
comments about the result. |
The following presents a sample layout of the beach activity data file, a comma-separated values file opened in Excel. The column headings are displayed for reference purposes only and the actual data file should not contain column headings. This sample layout contains fictitious identities and values for exhibit purposes only. The actual list of valid values are available for download from Beach Watch.
Figure:
Advisory Activity Data File - Sample Layout
Click on “Beach Monitoring Sample Data File” to see the sample data file opened in Excel.
Data
File Upload - File Selection
(A) Valid Data File and contents: If all the validations of the uploaded data file are successful, the following screen is displayed along with the uploaded data. You are encouraged to review the data displayed on the screen.
Data
File Upload - Data Submittal Deletion
(B) Invalid Data File and contents: If all the validations of the uploaded data file are not successful, the following screen is displayed along with the uploaded data. The rows that failed the validations are identified in yellow and the data errors are shown in red. Carefully review all the errors displayed on the screen. A detailed error report generated as comma separated values text file is available through a link on top of the screen. You can down load the file to review and assist with correcting the data file before reloading. Beach Watch will not accept files that are not successfully validated.
Data
File Upload - Invalid Data
Data
File Upload - Data Submittal Deletion
