1.- Original Request from Doug Given: ===================================== REQUEST #53: Add integer attribute for each channel to hold COSMOS "Table 6" value. Possible add to SimpleResponse COMMENT: This is required to distinguish freefield from structural sensors. This is needed by ShakeMap the the Gro. Mo. packet. REQUESTOR: Doug Given BACKGROUND: The COSMOS Table 6 maps an integer representing a V0 header value to the station type. Table 6 Code -- Freefield, Ground Response or Reference Stations 1 Small, fiberglass shelter (typically 1 m x 1 m x 1 m; e.g., T-hut). 2 Small, prefabricated metal building (typically 1-2 m x 1-2 m x 2 m high; e.g., Armco). 3 Sensors buried/set in ground (shallow, near surface). 4 Reference station (1-2 story, small, light building). 5 Base of building larger than above. : Instrumented Structures or Arrays 10 Building 11 Bridge 12 Dam : 20 Other structure : 50 Geotechnical array 51 Other array The Problem: There are several channel attitudes that are not accommodated by the current schema. Other new attributes will probably be found in the future. Adding additional attributes to channel data is time-consuming and difficult. Examples of new attributes include COSMOS style station type, clipping level, average noise level, etc. In addition, specific applications or groups of applications often need specialized channel lists. For, example what channels are available in a wavepool. PROPOSED SOLUTION: They're two basic solutions to this problem. 1) Add new columns to the channel data table. 2) Create extension tables to store the additional channels attributes (ala Simple_Response, and StaMapping). Changing the schema and adding columns to Channel_Data for each new attribute has proven impractical and would be never-ending. Therefore, creation of extension tables seems like the best solution. It also results in a highly normalized database. The primary disadvantage is that extracting several attributes would require a multi-table join. Here we call these extension tables "ChannelMap" tables because they are linked to the Channel_Data table via the Net/Sta/SeedChan/Location key (NSSL, aka SNCL). The table name would have the form ChannelMap and reflect the actual use of the table; for example ChannelMapExport. A ChannelMap table may contain multiple attributes if they are tightly related. If a ChannelMap's attribute is time varying ondate/offdata columns could be included. ChannelMap tables could even be created as views of other tables. What follows are some examples of ChannelMap extension tables that would solve some problems we have today. Example 1 - Cosmos Table 6 Station Type Purpose: hold Cosmos "Table 6" station type value. There would be a second table to hold a text description of each typecode. ChannelMapCosmosStationType Name Null? Type ----------------------------------------- -------- ------------------- NET NOT NULL VARCHAR2(8) STA NOT NULL VARCHAR2(6) SEEDCHAN NOT NULL VARCHAR2(3) LOCATION NOT NULL VARCHAR2(2) TypeCode NOT NULL NUMBER(8) CosmosStationTypeCode Name Null? Type ----------------------------------------- -------- ------------------- TypeCode NOT NULL NUMBER(8) DESCRIPTION NOT NULL VARCHAR2(80) ALTERNATE SOLUTION: Add new columns to the channel data table. ------------------------------------------------------------------------------------- 2.- Comments from Doug Neuhauser: ================================= 1. This is a per-channel attribute as you could have a station with sensors in different places, one of which may be "free-field", and one of which may not be. For example, we have a site that has both borehole and surface accelerometers, and these would have different values. 2. This may be more of a "site characteristic" than a "response value" but I'm not sure the distinction is very well delineated. The practical problem is that most of the IR and all of the HT tables don't really exist for many (most?) channels of interest to ShakeMap. 3. Doug Given proposed a separate table. See attached doc. 4. Should it have time values? I think so, for consistency with other tables and to cover the weird unanticipated situation. But I think its unlikely that a sensor's relationship to structures will change. 5. We also need a new table (or field) for each station that include the 5-digit COSMOS site id. Since it is COSMOS-related, I propose that we add this to the table. 6. Any other fields required? Is a COSMOS table the best way to do this? ------------------------------------------------------------------------------------- 3.- Other possible fields from Doug Neuhauser: ============================================== As part of a CISN Schema request change, I am looking not only at the request for the COSMOS Table 6 values (Station Type), but at any other attribute required to import or export COSMOS-format data. I have identified the following fields as being new fields that may be required. 1. COMSOS Table 6 Typecode. - per channel 2. COSMOS_TYPECODE table with text descritions of the codes. 3. COSMOS Station Number - 5 digits - per station ==> Already in the StaMapping table Other possible candidates: * Actual or assumed response. Q: Is it sufficient for us to assume actual response if the response is in our database? * COSMOS network name. Q: Where is this coming from now? ==> Right now for the Trinet and UCB MiniSeed data, this comes from the network code in the headers. * COSMOS channel number. Q: Where is this coming from now? The HW Tracking tables? Is this sufficient? ==> The HW tracking tables is where we get this from now. It is a rather complex query that Stephane provided and can be found in the create_v0xml.pl perl script. If you want to see it, I can post it to the group but it covers several outer joins from many tables. * Total number of channels in recorder. Q: Where is this coming from now? The HW Tracking tables? Is this sufficient? ==> The station_datalogger table, field named NB_PCHANNEL is where we get this number of channels. * Total number of channels at site. Q: Where is this coming from now? The HW Tracking tables? Is this sufficient? ==> This is not currently filled in in the Cosmos integer header from the ms_to_v0 code. * Full Scale output of sensor Q: I think this is currently in a comment field in the CISN schema. Is this satisfactory? ==> Correct, the full scale output of the sensor is coming from a comment in the d_abbreviation.description. The COSMOS format can be found at: http://www.cosmos-eq.org/cosmos_format_1_20.pdf -------------------------------------------------------------------------------------