Version 2
Copyright © 2005-2012 Delaware Environmental Observing System
Published: March 10th 2005
| Revision History | ||
|---|---|---|
| Revision 1.1 | 2005/03/10 | GEQ |
| Initial version | ||
| Revision 1.2 | 2005/03/14 | GEQ |
| Outlined the data format and database tables used. Added units command options. | ||
| Revision 1.3 | 2005/03/15 | GEQ |
| Clarified the required and optional command line options. | ||
| Revision 1.4 | 2005/03/17 | GEQ |
| Clarified the start and end time format options (YYYY-MM-DD HH:MM:SS is allowed) and specified that the end time must be later than the start time. | ||
| Revision 1.5 | 2005/09/15 | GEQ |
| Added the time zone options. | ||
| Revision 1.6 | 2005/10/20 | GEQ |
| Added the caution at the top of the output file section. | ||
| Revision 1.7 | 2005/11/30 | GEQ |
| Clarified the data types option as in requiring numbers. | ||
| Revision 1.8 | 2006/01/20 | GEQ |
| Fixed print history. | ||
| Revision 1.9 | 2006/04/17 | GEQ |
| Added table describing data types and their IDs. | ||
| Revision 2.1 | 2006/09/26 | GEQ |
| Incremented version number of this document to 2. | ||
| Revision 2.2 | 2006/12/07 | GEQ |
| Added abbreviation command line option for specifying data types. Revised data type table to include abbreviations. | ||
| Revision 2.3 | 2007/01/04 | GEQ |
| Added an ID for the data types section. Added abbreviations to data types table. | ||
| Revision 2.1.4 | 2007/10/30 | GEQ |
| Revised data types table. | ||
Abstract
This technical note describes the DEOS extract program, and the options for its use.
Table of Contents
The DEOS extract function provides the functionality to retrieve and format data stored in the DEOS database.
The program is executed by the command extract from the command line.
extract { -u user-name | --user user-name } { -p password | --password password } { -d database | --database database } { -n network | --network-namenetwork } { -S station | --station-namestation } [--start-timetime string] [--end-timetime string] [ --local | --utc | --UTC ] [--filenamename] {--data-typeslist} [--dump-type[csv]] [ --english | --English | --si | --SI ] [ -vvalue | --verbosevalue ] [ -h | --help ]
The extract program uses database configuration parameters to connect to the database storing the data for extraction.
The specification for the user option is:
{ -u username | --user username | --user=username }
This option will allow the program to connect to the database using the username provided. The user must already exist, and have sufficient permissions to access the database specified. This item is required.
The specification for the password option is:
{ -p password | --password password | --password=password }
This option in combination with the username allows the program to connect to the database using the username provided. This item is required.
The specification for the database option is:
{ -d database | --database database | --database=database }
This option will specify which database the program should use as a source of data, configuration items and a destination for any event logging items. This item is required.
The specification for the network name option is:
{ -n network name | --network-name network name | --network-name=network name }
This option specifies the network name and is required.
The specification for the station name option is:
{ -S name | --station-name name | --station-name=name }
This option specifies the station name and is required.
The specification for the start time option is:
[ --start-time time string | --start-time=time string ]
This option specifies a timestamp for data extraction to begin. The time string format can be either YYYYMMDDHHMMSS or YYYY-MM-DD HH:MM:SS. If the latter is used, the string must be placed in quotes.
The default is 24 hours prior to the end time. The time string, if provided, must contain at least the year, month and day of month. If the hours, minutes and seconds entries are missing, the default is midnight.
The specification for the end time option is:
[ --end-time time string | --end-time=time string ]
This option specifies a timestamp for data extraction to end. The time string format can be YYYYMMDDHHMMSS or YYYY-MM-DD HH:MM:SS. If the latter is used, the string must be placed in quotes.
The default is the time the program is run. The time string, if provided, must contain at least the year, month and day of month. If the hours, minutes and seconds entries are missing, the default is midnight.
The end time must be later, i.e. more recent, than the start time.
The specification for the time zone for output is:
[ --local | --utc | --UTC ]
This option specifies which time zone will be used in the output data and which time zone other time related command options are interpreted to be. The local option results in the usage of the default time zone for the station as specified in the station metadata. Specifying either utc or UTC will use UTC time in the output. The default value is local.
The specification for the filename option is:
[ --filename name | --filename=name ]
This option will specify which file name the data will be placed in. If a value isn't
provided, the default
value will be /tmp/YYYYMMDDHHMMSS.DUMP-TYPE where the time string is
generated from the time the file was generated and DUMP-TYPE is the argument given to the command
line option of the same name.
The specification for the data types option is:
{ --data-types items | --data-types=items }
This option specifies which data types will be extracted and placed into the output file. The 'items' entry must be a comma-separated list of numeric data type IDs or alphabetic abbreviations for extraction. It is not possible to mix numeric and alphabetic IDs. No spaces are allowed in the list or an error will result, even if the list is enclosed in quotes.
The specification for the dump file type is:
[ --dump-type [csv] | --dump-type=[csv] ]
This option will specify which file name the data will be placed in. The default value is 'csv', comma-separated values.
The specification for the output units is:
[ --english | --English | --si | --SI ]
This option will specify which units will be used on output. If this option isn't supplied, English units are assumed.
The specification for the verbose option is:
[ -v value | --verbose value ]
The value provided for the verbose setting (a digit between 0 and 5) indicates how much output is generated, 0 being minimal and 5 being extremely verbose. Positive values of setting send data to the ELF, negative value also print the message to the screen. If no value is provided, the setting is assumed to be 1.
The following database tables provide input for the extract program.
This table contains metadata about the various data types DEOS handles.
The available data types are described in the following table.
It should be noted that data for all data types may not be available from every station and that data for some data types are derived from other data and so may not be immediately available.
Table 1. Data Types
| ID | Abbreviation | Description |
|---|---|---|
| 0 | RRH | Radar Precipitation (60) |
| 1 | RFH | Gage Precipitation (60) |
| 2 | AT | Air Temperature |
| 3 | DP | Dew Point Temperature |
| 4 | WS | Wind Speed |
| 5 | WGH | Wind Gust Speed (60) |
| 6 | WD | Wind Direction |
| 7 | BP | Barometric Pressure |
| 8 | SR | Solar Radiation |
| 9 | WG5 | Wind Gust Speed (5) |
| 10 | RF5 | Gage Precipitation (5) |
| 11 | RH | Relative humidity |
| 12 | HI | Heat Index |
| 13 | WC | Wind Chill |
| 14 | ST | Soil Temperature |
| 15 | V | Voltage |
| 16 | RHA | Bias-adjusted Gage Precipitation (60) |
| 17 | R5A | Bias-adjusted Gage Precipitation (5) |
| 18 | VWC | Volumetric Water Content |
| 19 | SDR | Raw Snow Depth |
| 20 | RR6 | Radar Precipitation (6) |
| 21 | LF | Leaf wetness |
| 22 | VB | Visibility |
| 23 | GH | Gage Height |
| 24 | RFD | Precip (daily) |
| 25 | DSC | Discharge |
| 26 | WT | Water Temperature |
| 27 | SV | Stream Velocity |
| 28 | RFQ | Gage Precipitation (15) |
| 29 | PH | pH |
| 30 | SC | Conductance |
| 31 | PO2 | Dissolved Oxygen %saturation |
| 32 | DO2 | Dissolved Oxygen Volume |
| 33 | TBB | Turbidity (BB) |
| 34 | WH | Significant Wave Height |
| 35 | WPD | Wave Period (Dominant) |
| 36 | WPA | Wave Period (Average) |
| 37 | WDA | Wave Direction (Average) |
| 38 | BPT | Barometric Pressure Trend |
| 39 | TD | Tide (Deviation) |
| 40 | FT | Fuel temperature |
| 41 | FM | Fuel moisture |
| 42 | WGI | Wind Gust Direction |
| 43 | TMD | Mean Daily Temp. |
| 44 | TXD | Max Daily Temp. |
| 45 | TND | Min Daily Temp. |
| 46 | HDD | Heating Degree Days |
| 47 | CDD | Cooling Degree Days |
| 48 | WSD | Mean Wind Speed |
| 49 | WDD | Mean Wind Direction |
| 50 | WGD | Peak Wind Gust Speed (Daily) |
| 51 | RFE | Gage Precipitation (Daily) |
| 52 | SFD | Daily Snow Fall |
| 53 | TMM | MTD Mean Temp. |
| 54 | TXM | MTD Max Temp. |
| 55 | TNM | MTD Min Temp. |
| 56 | RFM | MTD Total Rainfall. |
| 57 | SFM | MTD Snow Fall |
| 58 | WSM | MTD Mean Wind Speed |
| 59 | WDM | MTD Mean Wind Direction |
| 60 | WGM | Peak Wind Gust Speed (MTD) |
| 61 | HDM | MTD Heating Degree Days |
| 62 | CDM | MTD Cooling Degree Days |
| 63 | RXM | MTD Max Gage Rainfall |
| 64 | SXM | MTD Max Snow Fall |
| 65 | RFY | YTD Total Rainfall. |
| 66 | SFY | YTD Snow Fall |
| 67 | HDY | YTD Heating Degree Days |
| 68 | CDY | YTD Cooling Degree Days |
| 69 | GHD | Mean Daily Gage Height |
| 70 | GHX | Max Daily Gage Height. |
| 71 | GHN | Min Daily Gage Height. |
| 72 | DCD | Mean Daily Discharge |
| 73 | DCX | Max Daily Discharge |
| 74 | DCN | Min Daily Discharge |
| 75 | GHM | MTD Avg Gage Height. |
| 76 | GXD | MTD Max Gage Height. |
| 77 | GND | MTD Min Gage Height. |
| 78 | DCM | MTD Mean Discharge |
| 79 | DXM | MTD Max Discharge |
| 80 | DNM | MTD Min Discharge |
| 81 | VM | MTD Volume |
| 82 | VY | YTD Volume. |
| 83 | WTD | Mean Water Temp |
| 84 | WHD | Mean Wave Ht |
| 85 | WHX | Max Wave Ht |
| 86 | WHN | Mean Wave Period |
| 87 | TDM | MTD Mean Water Temp |
| 88 | THD | MTD Mean Wave Ht |
| 89 | VHX | MTD Max Wave Ht |
| 90 | VHN | MTD Mean Wave Period (Dominant) |
| 91 | SF5 | Snow Fall (5) |
| 92 | SFH | Snow Fall (60) |
| 93 | WGX | Wind Gust Speed (10) |
| 94 | RFX | Gage Precipitation (10) |
| 95 | TM | Turbidity (MC) |
| 96 | WDH | Mean Hourly Wind Dir |
| 97 | DPD | Mean Daily Dew Point Temperature |
| 98 | RHD | Mean Daily Relative Humidity> |
| 99 | ATH | Mean Hourly Air Temperature |
| 100 | DPH | Mean Hourly Dew Point Temperature |
| 101 | RHH | Mean Hourly Relative Humidity |
| 102 | GXH | Maximum Hourly Wind Gust |
| 103 | BPH | Mean Hourly Barometric Pressure |
| 104 | SRH | Mean Hourly Solar Radiation |
| 105 | WSH | Mean Hourly Wind Speed |
| 106 | HIH | Hourly Maximum Heat Index |
| 107 | HID | Daily Maximum Heat Index |
| 108 | HIM | Monthly Maximum Heat Index |
| 109 | WCH | Hourly Minimum Wind Chill |
| 110 | WCD | Daily Minimum Wind Chill |
| 111 | WCN | Monthly Minimum Wind Chill |
| 112 | WL | Well Water Level |
| 113 | WLD | Mean Daily Well Water Level |
| 114 | SAL | Concentration of dissolved salts |
| 115 | WXH | Maximum Hourly Wave Height |
| 116 | SWH | Average Hourly Wave Height |
| 117 | SPH | Significant Hourly Wave Period |
| 118 | SH | Wave Direction Spread |
| 119 | SMD | Daily Mean Specific Conductance |
| 120 | AMD | Daily Mean Concentration of Dissolved Salts |
| 121 | SVD | Daily Mean Dissolved O2 %Saturation |
| 122 | DMD | Daily Mean Dissolved Oxygen Volume |
| 123 | PXD | Daily Mean Hydrogen Ion Concentration |
| 124 | BXD | Daily Maximum Turbidity (Broad Band) |
| 125 | BMD | Daily Mean Turbidity (Broad Band) |
| 126 | SMM | MTD Mean Conductance |
| 127 | AMM | MTD Mean Dissolved Salt Conc. |
| 128 | SVM | MTD Mean Oxygen %saturation |
| 129 | DMM | MTD Mean Oxygen Volume |
| 130 | PXM | MTD Mean pH |
| 131 | BXM | MTD Maximum Turbidity (BB) |
| 132 | BMM | MTD Mean Turbidity (BB) |
| 133 | HMD | Daily Mean Wave Height |
| 134 | HXD | Daily Maximum Wave Height |
| 135 | PMD | Daily Mean Wave Period |
| 136 | WMD | Daily Mean Wave Direction |
| 137 | HMM | MTD Mean Wave Height |
| 138 | HXM | MTD Maximum Wave Height |
| 139 | PMM | MTD Mean Wave Period |
| 140 | WMM | MTD Mean Wave Direction |
| 141 | WXD | Daily Maximum Water Temperature |
| 142 | WND | Daily Minimum Water Temperature |
| 143 | WXM | MTD Maximum Water Temperature |
| 144 | WNM | MTD Minimum Water Temperature |
| 145 | AXD | Daily Maximum Dissolved Salt Conc. |
| 146 | AND | Daily Minimum Dissolved Salt Conc. |
| 147 | SXD | Daily Maximum Oxygen %saturation |
| 148 | SND | Daily Minimum Oxygen %saturation |
| 149 | 1XD | Daily Maximum pH |
| 150 | 1ND | Daily Minimum pH |
| 151 | AXM | MTD Maximum Dissolved Salt Conc. |
| 152 | ANM | MTD Minimum Dissolved Salt Conc. |
| 153 | SXX | MTD Maximum Oxygen %saturation |
| 154 | SNX | MTD Minimum Oxygen %saturation |
| 155 | 1XM | MTD Maximum pH |
| 156 | 1NM | MTD Minimum pH |
| 157 | WDP | Avg Water Depth |
| 158 | DDD | Daily Mean Water Depth |
| 159 | DXD | Daily Max Water Depth |
| 160 | DND | Daily Min Water Depth |
| 161 | DDM | MTD Mean Water Depth |
| 162 | 2XM | MTD Max Water Depth |
| 163 | 2NM | MTD Min Water Depth |
| 164 | 3DD | Daily Avg Hourly Wave Height |
| 165 | 3XD | Daily Max Hourly Wave Height |
| 166 | 3DM | MTD Avg Hourly Wave Height |
| 167 | 3XM | MTD Max Hourly Wave Height |
| 168 | PT1 | Potential Evapotranspiration (Hamon 1963) |
| 169 | PT2 | Potential Evapotranspiration (Hargreaves 1985) |
| 170 | PT | Potential Temperature |
| 171 | EPT | Equivalent Potential Temperature |
| 172 | ET | Equivalent Temperature |
| 173 | ED | Energy Density |
| 174 | RFE | Reference Evapotranspiration |
| 175 | SDG | Snow Depth |
| 176 | PAR | Photosynthetically Active Radiation |
| 177 | 48A | 48-Hour Snow Accumulation |
| 178 | SDN | Raw Snow Depth (Natural) |
| 179 | NDG | Snow Depth (Natural) |
| 180 | GDD | Growing Degree Days |
| 181 | DMR | Daily Avg RH |
| 182 | DXR | Daily Max RH |
| 183 | DNR | Daily Min RH |
| 184 | DMS | Daily Avg ST |
| 185 | DXS | Daily Max ST |
| 186 | DNS | Daily Min ST |
| 187 | DMV | Daily Avg VWC |
| 188 | DXV | Daily Max VWC |
| 189 | DNV | Daily Min VWC |
| 190 | MMR | MTD Avg RH |
| 191 | MMI | MTD Avg Soil Temp |
| 192 | MXI | MTD Max Soil Temp |
| 193 | MNI | MTD Min Soil Temp |
| 194 | MMV | MTD Avg VWC |
| 195 | MXV | MTD Max VWC |
| 196 | MNV | MTD Min VWC |
| 197 | DSE | Daily Solar |
| 198 | MDG | MTD GDD |
| 199 | YDG | YTD GDD |
| 200 | MDR | MTD RET |
| 201 | YDR | YTD RET |
| 202 | MSE | MTD Avg Solar |
| 203 | LCC | Lowest Cloud Cover Layer |
| 204 | HCC | Highest Cloud Cover Layer |
| 205 | CWL | Water Level |
| 206 | PWL | Predicted Water Level |
| 207 | WLA | Water Level Anomaly |
| 208 | SA5 | Snow Accumulation |
| 209 | HPF | Snow Depth (HPF) |
| 210 | SST | Snow Storm Total |
| 211 | NNN | Nutr-Nitrogen as N |
| 212 | PPP | Phosphate-Phosphorus as P |
| 213 | INN | Inorg Nitrogen as N |
| 214 | ENT | Enterococcus (Bacteria) |
| 215 | HOX | Historical Maximum Monthly Dissolved Oxygen |
| 216 | HON | Historical Minimum Monthly Dissolved Oxygen |
| 217 | HOA | Historical Average Monthly Dissolved Oxygen |
| 218 | HBX | Historical Maximum Monthly Enterococcus |
| 219 | HBN | Historical Minimum Monthly Enterococcus |
| 220 | HBA | Historical Average Monthly Enterococcus |
| 221 | HNX | Historical Maximum Monthly Nutrient-Nitrogen as N |
| 222 | HNN | Historical Minimum Monthly Nutrient-Nitrogen as N |
| 223 | HNA | Historical Average Monthly Nutrient-Nitrogen as N |
| 224 | HIX | Historical Maximum Monthly Inorganic Nitrogen (nitrite and nitrate) as N |
| 225 | HIN | Historical Minimum Monthly Inorganic Nitrogen (nitrite and nitrate) as N |
| 226 | HIA | Historical Average Monthly Inorganic Nitrogen (nitrite and nitrate) as N |
| 227 | HHX | Historical Maximum Monthly pH |
| 228 | HHN | Historical Minimum Monthly pH |
| 229 | HHA | Historical Average Monthly pH |
| 230 | HPX | Historical Maximum Monthly Phosphate-Phosphorus as P |
| 231 | HPN | Historical Minimum Monthly Phosphate-Phosphorus as P |
| 232 | HPA | Historical Average Monthly Phosphate-Phosphorus as P |
This table contains metadata about the various unit types DEOS handles. In particular, which units the DEOS system uses by default.
This table contains metadata about the various schema types DEOS handles the two principal being SI and English. This table enables a mapping between the DEOS internal units and the user selected schema. uses by default.
Further processing of large amounts of data following generation by the extract program, particularly when using English units may generate rounding errors. This has been particularly apparent during testing when performing additional processing of precipitation data from DEOS stations (due principally to the number of data points) in English units. If such processing is required, it is strongly advised the user retrieve data in SI units and convert to English as a final step.
The extract program generates files in the following formats.
The first line of the file is a header describing the data columns and will contain the data described below in comma separated form.
Time
Data item header #1 with units in parentheses
Other header items in the same format
The second and subsequent lines of the file contain comma-separated data each applicable for the time indicated.
Time string in YYYY-MM-DD HH:MM:SS (UTC SX), where SX is the timezone offset
Data item #1
Other data items in the same format
The extract program serves as the back-end portion of the HDRS system. The front end is a set of web pages that allow the user to select the data types, time range and geographical area of interest for which they want data. The web front end writes the request data to the database. A PL/SQL package copntrols the queuing system and runs the extract program as needed to fulfill the data request.
The following database tables provide configuration options for the cfgen program.
Table 2. cfgen Database Controls
| Name | Description |
|---|---|
| NUM_PROCESSES | Maximum number of extract processes to run. |
| DELAY_BETWEEN_RUNS | The time delay in minutes between runs. |
| RETENSION_TIME | The time delay before the data files are deleted. |
| EXTRACTION_LIMIT | The maximum number of data items a user can request. |
| BASE_DIR | The directory when output files are stored. |
| ENABLED | Indicates whether the HDRS system is enabled. |
| EXEC | The full path to the executable file. |
| USERNAME | The name of the user account under which to run the extract process. |
| PASSWORD | The password of the user account under which to run the extract process. |
| URL | The URL that gets written to the top of the metadata file. |
| ADDRESS | The USPS address that gets written to the top of the metadata file. |
| ADMIN_EMAIL | The email address of the person receiving error reports. |