Schemas EarthWorm vs. NCEDC --------------------------- This document tries to outline the mapping of fields between the EarthWorm and NCEDC schemas as well as how to move real-time information between the two schemas. I.- Fields mapping: ------------------- This paragraph addresses the mapping of the fields in the EarthWorm tables to the fields in the NCEDC schema. When a '???' is specified, it means that no such field was found in the NCEDC schema that corresponds to the EarthWorm one or that the mapping is unclear. +----------------------------+ | 1.- Parametric Information | +----------------------------+ 1.1.- Event: ------------ idEvent --> Event.evid tiEventType --> Event.etype iDubiocity --> ??? (Indicated whether an event is bogus, or potentially the likelihood that an event is bogus) idComment --> Event.commid 1.2.- Bind: ----------- idBind : Unique record identifier #idEvent : Indicates the event to which the core record is to be bound tiCore : Indicates the entity type (DBtable) to bind to the event idCore : Indicates the record from tiCore to bind to the event identified by idEvent 1.3.- Origin: ------------- idOrigin --> Origin.orid #idSource --> Origin.auth (String) tOrigin --> Origin.datetime dLat --> Origin.lat dLon --> Origin.lon dDepth --> Origin.depth iGap --> Origin.gap dDmin --> Origin.distance dRms --> Origin.wrms iAssocRd --> Origin.totalarr + Origin.totalamp (???) (Number of readings (introduced for USNSN use that were associated with this origin) iAssocPh --> Origin.totalarr iUsedRd --> ??? (Number of readings that were actually used to calculate parameters for this origin) iUsedPh --> Origin.ndef iFixedDepth --> Origin.fdepth (Char) iE0Azm --> Origin_Error.azismall iE0Dip --> Origin_Error.dipsmall dE0 --> Origin_Error.magsmall iE1Azm --> Origin_Error.aziinter iE1Dip --> Origin_Error.dipinter dE1 --> Origin_Error.maginter iE2Azm --> Origin_Error.azilarge iE2Dip --> Origin_Error.diplarge dE2 --> Origin_Error.maglarge dErLat --> Origin.erlat dErLon --> Origin.erlon dErZ --> Origin.sdep tMCI --> Origin.stime (???) (A time confidence interval) idComment --> Origin.commid 1.4.- Magnitude: ---------------- idMag --> Netmag.magid #idSource --> Netmag.auth (String) dMagAvg --> Netmag.magnitude iNumMags --> Netmag.nsta dMagErr --> Netmag.uncertainty #iMagType --> Netmag.magtype (String) #idOrigin --> Netmag.orid idComment --> Netmag.commid 1.5.- OriginPick: ----------------- idOriginPick --> ??? (Unique record identifier) #idOrigin --> AssocArO.orid #idPick --> AssocArO.arid sPhase --> AssocArO.iphase tPhase --> Arrival.datetime + Arrival.timeres (???) dWeight --> AssocArO.wgt dDist --> AssocArO.delta dAzm --> AssocArO.seaz dTakeOff --> Arrival.ema tResPick --> Arrival.timeres 1.6.- MagLink: -------------- Amplitude Coda ---------------------------- idMagLink --> ??? ??? (Unique record identifier) #idMag --> AssocAmM.magid AssocCoM.magid idDatum --> AssocAmM.ampid AssocCoM.coid dMag --> AssocAmM.mag AssocCoM.mag dWeight --> AssocAmM.weight AssocCoM.weight 1.7.- MagType: Read Only Table -------------- iMagType : Magnitude type identifier sMagAbbrev : Code or abbreviation for the magnitude sMagName : Long-hand name of the magnitude tiMagType : Table identifier for the datum elements of the magnitude, such as PeakAmp and CodaDur. 1.8.- CodaDur: Associates Pick & Coda Termination -------------- #idCodaDur : Unique Record Identifier #idTCoda : Identifier of the TCoda to which the CodaDur is related #idPick : Identifier of the Pick to which the CodaDur is related tCodaDurObs : Time between the Pick time and the Observed Coda Termination time tCodaDurXtp : Time between the Pick time and the Calculated Coda Termination time 1.9.- Pick: ----------- idPick --> Arrival.arid #idChan --> Arrival.[sta, net, seedchan, location] (String) #idSource --> Arrival.auth (String) SPhase --> Arrival.iphase tPhase --> Arrival.datetime cMotion --> Arrival.fm cOnset --> Arrival.qual dSigma --> Arrival.deltim (???) (Quality of the phase in terms of possible error deviation (seconds)) 1.10.- TCoda: ------------- idTCoda --> Coda.coid #idChan --> Coda.[sta, net, seedchan, location] (String) tCodaTermObs --> Coda.datetime (???) (Observed time of the coda termination) tCodaTermXtp --> ??? (Calculated time of the coda termination calculated by amplitude measurements and decay algorithm) 1.11.- CodaAmp: --------------- idCodaAmp --> Coda.coid #idChan --> Coda.[sta, net, seedchan, location] (String) #idTCoda --> Coda.coid (???) (Identifier of the coda termination (TCoda) record that this record supports. More than one amplitude measurements can be used to calculate a coda termination time) ton --> Coda.time[1, 2, 3, 4, 5, 6] (???) (Start time of the average amplitude measurement) toff --> ??? (End time of the average amplitude measurement) dAvgAmp --> Coda.amp[1, 2, 3, 4, 5, 6] (???) (Average amplitude between tOn and tOff) 1.12.- PeakAmp: --------------- idPeakAmp --> Amp.ampid #idChan --> Amp.[sta, net, seedchan, location] (String) dPeakAmp1 --> amp.amplitude tAmp1 --> amp.wstart (???) (Time of the amplitude measurement) tPeriod1 --> amp.per dPeakAmp2 --> ??? tAmp2 --> ??? tPeriod2 --> ??? #iMagType --> ??? (Netmag.magtype where Netmag.magid = AssocAmM.magid AND amp.ampid = AssocAmM.ampid) +--------------+ | 2.- External | +--------------+ 2.1.- Prefer: ------------- *idPrefer --> ??? (Unique Record Identifier) #idPrefOrigin --> Event.prefor #idPrefMag --> Event.prefmag #idPrefMech --> Event.prefmec #idEvent --> Event.evid 2.2.- Source: ------------- idSource : Unique Record Identifier *sSource : The text string identifying the source sHumanReadable : The name of the source, in a format that is intelligible to humans idComment : Link to a possible comment for the event 2.3.- ExternalEvent: -------------------- *idExternalEvent : Unique Record Identifier #idSource : Identifier of the source of the event that is to be linked to a DB Event sSourceEventID : The event identifier given to the event by the external source idComment : Link to a possible comment for the event +-------------------+ | 3.- Strong Motion | +-------------------+ 3.1.- SMMessage: Associated to an event by the bind table ---------------- idSMMessage : Unique Record Identifier #idChan : Identifier of the channel from which this message came tMotion : Time the author of the message assigns to it tLoad : Time the message was first loaded into an EW DB tAlt : Alternate time assigned to the message by some entity iAltCode : Code specifying the source of tAlt tPGA : Time of the Peak Ground Acceleration for this message tPGV : Time of the Peak Ground Velocity for this message tPGD : Time of the Peak Ground Displacement for this message 3.2.- SMMotion: --------------- *idSMMotion : Unique Record Identifier #idSMMessage : Identifier of the message from which this motion measurement came iMotionType : Type of motion the measurement is for dPeriod : Period for a response spectral acceleration measurement #idChan : Identifier of the channel from which this motion measurement came dMeasurement : The size of the motion +--------------+ | 4.- Waveform | +--------------+ 4.1.- WaveformDesc: Associated to an event by the bind table ------------------- idWaveform --> Waveform.wfid #idChan --> Waveform.[sta, net, seedchan, location] (String) tStart --> Waveform.datetime_on tEnd --> Waveform.datetime_off iDataFormat --> ??? (Data format of the waveform snippet) iByteLen --> Waveform.nbytes iStorageType --> ??? (Method used to store the waveform snippet in conjunction with the DB) sStorageInfo --> ??? (Additional information about the storage of the waveform snippet) 4.2.- Waveform: --------------- #idWaveform : Unique Waveform Record Identifier binSnippet : Actual binary waveform snippet (long raw 4Gb Max.) +----------+ | 5.- Meta | +----------+ 5.1.- Comments: --------------- idComment --> Remark.commid sComment --> Remark.remark 5.2.- EWDB_TableList: --------------------- *idTable : Unique Record Identifier sTableName : Table name +---------------------------+ | 6.- Cooked Infrastructure | +---------------------------+ 6.1.- Site: ----------- *idSite --> Primary Key sSta --> Station_Data.sta sNet --> Station_Data.net idComment --> NULL 6.2.- SiteT: ------------ *idSiteT --> Primary Key #idSite --> Foreign Key tOff --> Station_Data.offdate (DATE) tOn --> Station_Data.ondate (DATE) dLat --> Station_Data.lat dLon --> Station_Data.lon dElev --> Station_Data.elev idComment --> Station_Comment.comment_id (Abbreviation Dictionary) 6.3.- Comp: ----------- idComp --> Primary Key #idSite --> Foreign Key sComp --> Channel_Data.seedchan sLoc --> Channel_Data.location idComment --> NULL 6.4. CompT: ----------- *idCompT --> Primary Key #idComp --> Foreign Key tOff --> Channel_Data.offdate (DATE) tOn --> Channel_Data.ondate (DATE) sSta --> Channel_Data.sta sComp --> Channel_Data.seedchan sNet --> Channel_Data.net sLoc --> Channel_Data.location dLat --> Channel_Data.lat dLon --> Channel_Data.lon dElev --> Channel_Data.elev dAzm --> Channel_Data.azimuth dDip --> Channel_Data.dip idComment --> Channel_Comment.comment_id (Abbreviation Dictionary) 6.5.- Chan: ----------- idChan --> Primary Key idComment --> NULL 6.6.- ChanT: ------------ *idChanT --> Unique Key #idChan --> Primary Key tOff --> Channel_Data.offdate (DATE) tOn --> Channel_Data.ondate (DATE) #idCompT --> Foreign Key #(#idDeviceSlot, iPlexor) --> NULL idComment --> NULL 6.7.- ChanCTF: -------------- *idChanCTF --> Unique Key #idChanT --> Primary Key #idCTF --> Foreign Key dSampRate --> Channel_Data.samprate dGain --> Sensitivity.sensitivity (stage_seq = 0) 6.8.- CookedTF: --------------- idCTF --> Primary Key sFuncDesc --> PZ.name 6.9.- PolesAndZeroes: --------------------- #idCTF --> Foreign Key idPZ --> Primary Key cPZType --> PZ_Data.type dReal --> PZ_Data.r_value dImaginary --> PZ_Data.i_value 6.10.- Station_2_idChan_External: --------------------------------- *idChan --> Unique Key StationID --> Primary Key 6.11.- Station_External: ------------------------ StationID --> Primary Key Sta --> Channel_Data.sta Chan --> Channel_Data.seedchan Net --> Channel_Data.net Loc --> Channel_Data.location Lat --> Channel_Data.lat Lon --> Channel_Data.lon Elev --> Channel_Data.elev Description --> NULL II.- Issues: ------------ 1.- Create Bind and EWDB_TableList tables for the NCEDC schema (NCEDC_Bind and NCEDC_EWDB_TableList). NCEDC_Bind is a temporary Bind table; procedures analyze rows, take specific actions and delete those rows from it. 2.- EarthWorm EWDB_TableList and MagType tables are needed to be able to go from NCEDC to EarthWorm schema. 3.- Procedures that: - Retrieves sSource from Source table with idSource. - Retrieves sMagAbbrev from MagType table with iMagType. - Retrieves net, sta, chan, loc from Station_External table with idChan. - Splits remarks in multiple lines in Comments. 4.- Lookup tables for EarthWorm & NCEDC identifiers mapping (needed to move from NCEDC schema to EarthWorm schema). 5.- No origin association with codas and amplitudes in the EarthWorm schema! 6.- Program that generates the Cooked Infrastructure schema from the Instrument Responses one and use the cooked infrastructure APIs from the EarthWorm system. 7.- Use the alarm APIs from the EarthWorm system. 8.- We have to relax/remove some constraints in the NCEDC schema to allow for triggered population. For example, in the NCEDC schema, an event has to be created before an origin (evid field in Origin table cannot be NULL). 9.- The EarthWorm schema has the ability to make associations that don't make sense. For example: Event1 <------ Origin ^ | | Event2 <------ Magnitude A Magnitude can be associated to an Origin via the idOrigin in the Magnitude table. This origin can be bound to an Event1 via the Bind table and this magnitude could be bound to an Event2 via the Bind table. There is a conflict since this Magnitude is now bound to two different events. 10.- Will we ever need to go from NCEDC schema to EarthWorm schema? If so, what rules should be used for creation of the entries in the Bind table. III.- Triggers: --------------- Oracle allows you to define procedures that are implicitly executed when an INSERT, UPDATE, or DELETE statement is issued against the associated table. A trigger can include SQL and PL/SQL statements to execute as a unit and can invoke stored procedures. Triggers can be defined only on tables, not on views. However, triggers on the base table(s) of a view are fired if an INSERT, UPDATE, or DELETE statement is issued against a view. IV.- Identifiers Mapping Table: ------------------------------- Table Definition: EWNC_Id_Mapping: | EWId Int EarthWorm Identifier | EWTable String EarthWorm Table Name | NCId Int NCEDC Identifier | NCTable String NCEDC Table Name The following table outlines what entries should be populated in the EWNC_Id_Mapping whenever a row is inserted into an EarthWorm table. INSERT INTO | EWId EWTable NCId NCTable -------------+---------------------------------------------------------- Event | idEvent ,'Event' ,evid ,'Event' Origin | idOrigin ,'Origin' ,orid ,'Origin' Origin | idOrigin ,'Origin' ,orid ,'Origin_Error' Magnitude | idMag ,'Magnitude' ,magid ,'Netmag' Pick | idPick ,'Pick' ,arid ,'Arrival' TCoda | idTCoda ,'TCoda' ,coid ,'Coda' CodaAmp | idCodaAmp ,'CodaAmp' ,coid ,'Coda' PeakAmp | idPeakAmp ,'PeakAmp' ,ampid ,'Amp' WaveformDesc | idWaveform ,'WaveformDesc' ,wfid ,'Waveform' Comments | idComment ,'Comments' ,commid ,'Remark' V.- Bind Table: --------------- Event Table can be bound with the following tables: (ExternalEvent) Origin (MechFM) Same as magnitude (Origin Id in MechFM Table) Magnitude Pick TCoda WaveformDesc (SMMessage) Put Event Id in SM Table --> Same as Origin (PeakAmp?) Same as TCoda ==> INSERT/UPDATE Bind: tiCore: Origin Action: UPDATE Origin SET Origin.evid = Bind.idEvent WHERE Origin.orid = Bind.idCore DELETE ROW FROM Bind tiCore: Magnitude Action: SELECT orid INTO OId FROM Netmag WHERE Netmag.magid = Bind.idCore IF OId == NULL SELECT OId = BOGUS origin identifier FOR EVENT Bind.IdEvent UPDATE Netmag SET Netmag.orid = OId WHERE Netmag.magid = Bind.IdCore DELETE ROW FROM Bind tiCore: Pick Action: SELECT orid INTO OId FROM AssocArO WHERE AssocArO.arid = Bind.idCore IF OId == NULL SELECT OId = BOGUS origin identifier FOR EVENT Bind.IdEvent INSERT INTO AssocArO VALUES (orid, arid) (OId, Bind.IdCore) DELETE ROW FROM Bind tiCore: TCoda Action: SELECT orid INTO OId FROM Netmag WHERE Netmag.magid = SELECT magid FROM AssocCoM WHERE AssocCoM.coid = Bind.idCore IF OId == NULL SELECT OId = BOGUS origin identifier FOR EVENT Bind.IdEvent INSERT INTO AssocCoO VALUES (orid, coid) (OId, Bind.IdCore) DELETE ROW FROM Bind tiCore: WaveformDesc Action: INSERT INTO AssocWaE VALUES (wfid, evid) wfid = Bind.idCore, evid = Bind.IdEvent ==> INSERT RESCAN Bind Table BASED ON tiCore, IdCore ==> UPDATE RESCAN AND MAYBE DELETE BOGUS ORIGIN