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 ............ |
|
| 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 ............ |
|
| 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 |
|
| 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 |
|
| 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 ................ |
|
| 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 |
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)
| 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 |