Database Definitions

tables | definitions | SQL syntax

Some of the TABLE and fieldnames used in our database are rather obvious (like STATION or CHANNEL) but several are not (LOOKUP_GA or TEXT). Here is a list of TABLE and fieldnames and their definitions and relationships to Blockettes in the SEED data format.

KEY
BOLD CAPS = TABLE name PLAIN CAPS = fieldname
[# - bold] = SEED blockette # {# - not bold} = SEED blockette field #

B30 | B31 | B32 | B33 | B34 | CHANNEL | STATION | EVENT | CHANNEL_SUMMARY
Temporary Network's Table Definitions

B30 SEED blockette [30]- Data Format Dictionary Blockette
All volumes, no matter what type, must have a blockette [30]. Each Channel Identifier Blockette [52] has a reference back to a blockette [30], so that SEED reading programs will know how to decode data for the channels. Because every kind of data format requires an entry in the blockette [30] each recording network will have at least one, maybe more, entries.
ID data format identifier code [4] - a cross reference number used in later blockettes to indicate this particular dictionary entry
FAMILY data family type [5] - this field tells potential decoder program what general algorithm to use to decode the associated data
NUMKEYS number of decoder keys [6]
NAME short descriptive name [3] - a short name describing the data type (see Appendix D of the SEED manual)

SQL and output EXAMPLE:

select network, station, channel, name, sample_rate, starttime, endtime from
b30, channel where 
channel.lookup_dfd = b30.id and channel.station = 'ANMO' and channel.channel = 'BHZ';


NE STATI CHA NAME                              SAMPLE_RATE STARTTIME           ENDTIME
-- ----- --- --------------------------------- ----------- ------------------- -------------------
IU ANMO  BHZ Steim1 Integer Compression Format          20 08/30/1989 00:00:00 01/23/1991 22:25:00
IU ANMO  BHZ Steim1 Integer Compression Format          20 01/23/1991 22:25:00 02/11/1991 20:48:00
IU ANMO  BHZ Steim1 Integer Compression Format          20 02/11/1991 20:48:00 02/01/1995 00:00:00
IU ANMO  BHZ Steim1 Integer Compression Format          20 02/01/1995 00:00:00 03/21/1995 17:16:00
IU ANMO  BHZ Steim1 Integer Compression Format          20 03/21/1995 17:16:00 07/14/1995 00:00:00
IU ANMO  BHZ Steim2 Integer Compression Format          20 07/14/1995 00:00:00 12/31/2599 23:59:59
............

Return to Top

B31 SEED blockette [31] - Comment Description Blockette
Station operators, data collection centers, and data management centers can add descriptive comments to data to indicate problems encountered or special situations.
ID comment code key [3] - used to uniquely identify comments
CLASS comment class code [4]
TEXT description of comment [5]
B34_ID refers to blockette [34] fieldname [ID] - unit lookup code

SQL and output EXAMPLE:

select * from B31;

ID C TEXT                                       B34_ID
-- - ------------------------------------------ ----------
 1 C Channel data quality is fair.
 3 C Channel data quality is very poor.
 4 C Channel has frequent negative pulses.
 5 C Channel has frequent spiking.
 7 C Channel has high sensitivity.              12 
............

Return to Top

B32 SEED blockette [32] - Cited Source Dictionary Blockette
This blockette identifies the contributing institution that provides the hypocenter and magnitude information. This blockette is usually used only in event oriented network volumes.
ID source lookup code [3]
NAME name of publication /author [4]
DATE_PUB date published/catalog [5]
PUBLISHER publisher name [6]

SQL and output EXAMPLE:

select * from B32;

 ID NAME                                                         DATE_PUB   PUBLISHER
--- ------------------------------------------------------------ ---------  -------------------------------------
  7 Washington Regional Seismograph Network.                     WRSN        WRSN,University of Washington
  8 UW, pseudo-Wood-Anderson                                     a           University of Washington 
  1 Preliminary determination of epicenters (monthly listing).   1991        USGS/NEIC
  9 UW, Wood-Anderson                                            b           University	of Washington
 10 UW, Coda Duration                                            c           University	of Washington 
 11 Pacific Geoscience Center, Victoria                          p           Pacific Geoscience Center, Victoria 
 12 USGS, Newport                                                n           USGS,NEIC 
 13 Preliminary determination of epicenters (monthly listing).   u           USGS/NEIC
 14 Phase pick by U.W. data analyst                              UW          University	of Washington

Return to Top

B33 SEED blockette [33] - Generic Abbreviation Blockette
Abbreviated descriptions of instruments or channels in the channel header [52] and of the network or owner in the station header [50]
ID abbreviation lookup code [3] - used in blockette [50] and [52]
TEXT abbreviation description [4] - the descriptive text for the network or owner of the station (ie. instrument type for CHANNEL and network affiliation for STATION)

SQL and output EXAMPLE:

select network, station, text from b33, station
where b33.id = station.lookup_ga and station.station = 'ANMO';

NE STATI TEXT
-- ----- --------------------------------------------------
IU ANMO  (GSN) Global Seismograph Network (IRIS/USGS)

select network, station, channel, text from b33, channel where
channel.lookup_ga = b33.id and channel.network = 'IU'
and channel.channel like 'BH_' and channel.station = 'ANMO';

NE STATI CHA TEXT
-- ----- --- ----------------------------------------------
IU ANMO  BHE Geotech KS-36000-I Borehole Seismometer
IU ANMO  BHN Geotech KS-54000 Borehole Seismometer
IU ANMO  BHU Guralp CMG3-T Seismometer (borehole)
IU ANMO  BHV Guralp CMG3-T Seismometer (borehole)
IU ANMO  BHW Guralp CMG3-T Seismometer (borehole)
IU ANMO  BHZ Geotech KS-54000 Borehole Seismometer

Return to Top

B34 SEED blockette [34] - Units Abbreviations Blockette
This blockette defines the units of measurement in a standard, repeatable way. Mention each unit of measurement only once.
ID unit lookup code [3] - used in blockette [52]
NAME unit name [4]
TEXT unit description [5]

SQL and output EXAMPLE:

select * from B34;

 ID  NAME                 TEXT
---- -------------------- --------------------------------------------------
 1   A                    Amperes
 2   B                    Boolean
 3   C                    Degrees Centigrade
 4   COUNTS               Digital Counts
 5   D                    Degrees 0-360 (direction)
 6   M                    Earth Displacement in Meters
 7   M/M                  strain
.............

OR more specifically:
select network, station, starttime, endtime, name, text from
b34, channel where
b34.id = channel.lookup_units_in and channel.station = 'SUR';

NE STATI STARTTIME           ENDTIME             NAME            TEXT
-- ----- ------------------- ------------------- --------------- --------------------------------------
II SUR   01/23/1996 00:00:00 12/31/2599 23:59:59 M/S             Velocity in Meters Per Second
II SUR   10/30/1990 00:00:00 08/04/1992 00:00:00 M/S             Velocity in Meters Per Second
II SUR   08/04/1992 00:00:00 01/23/1996 00:00:00 M/S             Velocity in Meters Per Second
................

Return to Top

CHANNEL This TABLE is related to Blockette [52] - Channel Identifier Blockette
NOTE: Not all of the available fieldnames for this TABLE are listed below. Please view the Oracle Tables page to see a list of all available fieldnames.
STARTTIME start date [22] - the earliest known date that information in this blockette is correct
ENDTIME end date [23] - the latest date when this information is correct
LOOKUP_DFD data format identifier code [16] - lookup key referring to blockette [30] fieldname [ID] (data format dictionary)
LOOKUP_GA instrument identifier [6] - lookup code referring to blockette [33] fieldname [ID] (generic abbreviation)
LOCATION location identifier [3] - describes the individual sites on an array station operated by the same network operator
SUBCHANNEL subchannel identifier [5] - used for a multiplexed data channel
COMMENTS optional comment [7] - optional comment given to an instrument
FLAGS channel flag [21] - a series of channel type flags (see SEED manual, Chapter 6, blockette [52] for details)
LOOKUP_UNITS_IN units of calibration input [9] - a unit lookup key that refers to blockette [34] fieldname [ID] for the units of calibration input, usually volts or amps
LOOKUP_UNITS_OUT units of signal response [8] - a unit lookup key that refers to blockette [34] fieldname [ID] for the signal response of the instrument
UPDATE_FLAG update flag [24] - indicate to what data the update records refer

Return to Top

STATION This TABLE is related to Blockette [50] - Station Identifier Blockette
NOTE : Not all of the available fieldnames for this TABLE are listed below. Please view the Oracle Tables page to see a list of all available fieldnames.
SITE site name [9] - the station site, usually as "local town/city, major political subdivision, country/territory
STARTTIME start effective date [13] - the earliest known date that information in this header record is correct
ENDTIME end effective date [14] - the latest date when this information is correct
LOOKUP_GA network identifier code [10] - lookup code referring to blockette [33] fieldname [ID] (generic abbreviation)
UPDATE_FLAG update flag [15] - the update flag indicates to what the data update records refer (see SEED manual, Chapter 6, blockette [50] for details)

Return to Top

EVENT this TABLE consists of event information from 1964 to present.
ID link to MAG TABLE (because there can be more than one reported magnitude per event)
SOURCE the pulisher of a given loaded catalog (currently we use NEIC and ISC)
TIME time of event in YYYY-MM-DD HH:MM:SS
TIME_MS the milliseconds recorded as ####
LATITUDE latitude of event
LONGITUDE longitude of event
DEPTH depth of event in meters
CATALOG the name of the catalog (i.e. MHDF - monthly hypocenter data file, WHDF - weekly hypocenter data file, etc.)
CONTRIBUTOR what organization contributed hypocenter information to a catalog.
LOOKUP_CSD Cited Source Definition lookup - linked to B32 TABLE (B32.ID = EVENT.LOOKUP_CSD)
REGION id used to link to REGION TABLE code field representing Flinn-Engdahl regions

SQL output and example:

select time, time_ms, latitude, longitude, depth, file_fmt, magnitude, type, region.code, seismic, region.region
from event, mag, region
where event.region = region.code and event.id = mag.id and
mag.magnitude > 4 and region.region like '%ALABAMA%';

TIME                 TIME_MS   LATITUDE  LONGITUDE   DEPTH CATALOG  MAGNITUDE TYP   CODE  SEISMIC_REGION REGION_TEXT
------------------- -------- ---------- ---------- ------- -------- ---------- --- ------ -------------- -----------
10/24/1997 08:35:17     8000     31.118 -87.338997      10 WHDF            4.2 MS     507             34 ALABAMA
10/24/1997 08:35:17     8000     31.118 -87.338997      10 WHDF            4.8 MB     507             34 ALABAMA
10/24/1997 08:35:17     8000     31.118 -87.338997      10 WHDF            4.9 LG     507             34 ALABAMA


MAG this TABLE contains the magnitude information for the events and is linked to the EVENT TABLE.
(MAG.ID = EVENT.ID)
ID link to EVENT TABLE
MAGNITUDE reported magnitude in #.#
TYPE type of magnitude (i.e. Mw, Ms, Mb etc.)
CONTRIBUTOR the source agency from which the magnitude was reported - not to be confused with the EVENT.CONTRIBUTOR field. (Entry examples are HRV for Harvard, BRK for Berkeley, NEIC, GS, etc.)


REGION this TABLE contains the Flinn-Engdahl region information and is linked to the EVENT TABLE.
(REGION.CODE = EVENT.REGION)
CODE Flinn-Engdahl region code - number between 1-729 (subgroups of seismic code)
SEISMIC_REGION Flinn-Engdahl seismic code - number between 1-50
REGION_TEXT Flinn-Engdahl region text - directly related to region code (i.e. region code #1 = Central Alaska)

Return to Top

CHANNEL_SUMMARY this table is a compilation of information from several tables. It is designed to inform the user of the earliest and latest dates for which data has been archived for any given network, station, channel or location code.
NETWORK one or two digit alphanumeric code representing the network operator
STATION three or four digit alphanumeric code representing a station
LOCATION location identifier - describes the individual sites on an array station operated by the same network operator
CHANNEL three digit alphanumeric code representing a data acquisition channel
EARLIEST the earliest date for which data has been archived
LATEST the last date for which data has been archived