Difference between revisions of "CyberShake DB Queries"
From SCECpedia
Jump to navigationJump to search| (One intermediate revision by the same user not shown) | |||
| Line 60: | Line 60: | ||
=== Rupture_Variations Table === | === Rupture_Variations Table === | ||
| − | The "Rupture_Variations" table contains information about each rupture variation - each realization created with the rupture generator, with a hypocenter and slip distribution. Linking this table with the Ruptures table allows you to select a specific variation of a rupture with the hypocenter in a certain location. | + | The "Rupture_Variations" table contains information about each rupture variation - each realization created with the rupture generator, with a hypocenter and slip distribution. Linking this table with the Ruptures table allows you to select a specific variation of a rupture with the hypocenter in a certain location. This is dependent on the specific rupture generator used, identified by the Rup_Var_Scenario_ID. |
| + | |||
| + | Rup_Var_Scenario_ID=3: Graves & Pitarka (2007), used in preliminary testing and Study 1.0. | ||
| + | |||
| + | Rup_Var_Scenario_ID=4: Graves & Pitarka (2010), used in Study 1.1, 1.3, 1.4, 2.2, 13.4, and 14.2. | ||
| + | |||
| + | Rup_Var_Scenario_ID=5: Graves & Pitarka (2014) with random hypocenter spacing, only used in testing. | ||
| + | |||
| + | Rup_Var_Scenario_ID=6: Graves & Pitarka (2014) with uniform hypocenter spacing, used in Study 15.4 and 15.12. | ||
| + | |||
<pre> | <pre> | ||
| Line 79: | Line 88: | ||
</pre> | </pre> | ||
| − | For example, to look at information | + | For example, to look at information for ERF 36, source 0, rupture 0: |
| + | <pre> | ||
| + | mysql> SELECT * FROM Rupture_Variations WHERE ERF_ID=36 AND RUP_VAR_SCENARIO_ID=6 AND SOURCE_ID=0 AND RUPTURE_ID=0 limit 5; | ||
| + | +------------+---------------------+--------+-----------+------------+-----------------------------------+----------------+----------------+------------------+ | ||
| + | | Rup_Var_ID | Rup_Var_Scenario_ID | ERF_ID | Source_ID | Rupture_ID | Rup_Var_LFN | Hypocenter_Lat | Hypocenter_Lon | Hypocenter_Depth | | ||
| + | +------------+---------------------+--------+-----------+------------+-----------------------------------+----------------+----------------+------------------+ | ||
| + | | 0 | 6 | 36 | 0 | 0 | e36_rv6_0_0.txt.variation-r000000 | 37.4274 | -121.7895 | 4.88 | | ||
| + | | 1 | 6 | 36 | 0 | 0 | e36_rv6_0_0.txt.variation-r000001 | 37.3946 | -121.7577 | 5.48 | | ||
| + | | 2 | 6 | 36 | 0 | 0 | e36_rv6_0_0.txt.variation-r000002 | 37.3615 | -121.7303 | 5.68 | | ||
| + | | 3 | 6 | 36 | 0 | 0 | e36_rv6_0_0.txt.variation-r000003 | 37.3278 | -121.7042 | 5.28 | | ||
| + | | 4 | 6 | 36 | 0 | 0 | e36_rv6_0_0.txt.variation-r000004 | 37.2917 | -121.6788 | 5.28 | | ||
| + | +------------+---------------------+--------+-----------+------------+-----------------------------------+----------------+----------------+------------------+ | ||
| + | </pre> | ||
=== CyberShake_Site_Ruptures Table === | === CyberShake_Site_Ruptures Table === | ||
Latest revision as of 20:14, 7 April 2016
Contents
CyberShake Database Queries
This page contains various CyberShake database queries which may be be of use to users. We suggest that you use a graphical SQL interface for exploring the CyberShake database.
Accessing ERF Info (rates, probabilities, site distances, etc)
Ruptures Table
The "Ruptures" table contains information on each rupture in the Earthquake Rupture Forecasts (ERFs) used in CyberShake.
Current ERFs in use: ERF_ID=35: Standard UCREF2, 1.0 km rupture surface discretization ERF_ID=36: Same as 35 but 200 m rupture surface discretization for 1hz runs
Fields:
mysql> describe Ruptures; +--------------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+------------------+------+-----+---------+-------+ | ERF_ID | int(10) unsigned | NO | PRI | NULL | | | Source_ID | int(10) unsigned | NO | PRI | NULL | | | Rupture_ID | int(10) unsigned | NO | PRI | NULL | | | Source_Name | varchar(100) | NO | | NULL | | | Source_Type | varchar(20) | YES | | NULL | | | Mag | double | NO | | NULL | | | Prob | double | NO | | NULL | | | Grid_Spacing | double | NO | | NULL | | | Num_Rows | int(11) | NO | | NULL | | | Num_Columns | int(11) | NO | | NULL | | | Num_Points | int(11) | NO | | NULL | | | Start_Lat | double | NO | | NULL | | | Start_Lon | double | NO | | NULL | | | Start_Depth | double | NO | | NULL | | | End_Lat | double | NO | | NULL | | | End_Lon | double | NO | | NULL | | | End_Depth | double | NO | | NULL | | +--------------+------------------+------+-----+---------+-------+
For example, to see all available information on Source 0:
mysql> SELECT * FROM Ruptures WHERE ERF_ID=35 AND Source_ID=0; +--------+-----------+------------+--------------+-------------+------+---------------+--------------+----------+-------------+------------+-----------+------------+-------------+-----------+---------+-----------+ | ERF_ID | Source_ID | Rupture_ID | Source_Name | Source_Type | Mag | Prob | Grid_Spacing | Num_Rows | Num_Columns | Num_Points | Start_Lat | Start_Lon | Start_Depth | End_Lat | End_Lon | End_Depth | +--------+-----------+------------+--------------+-------------+------+---------------+--------------+----------+-------------+------------+-----------+------------+-------------+-----------+---------+-----------+ | 35 | 0 | 0 | Calaveras;CC | null | 5.95 | 0.00030383517 | 1 | 4 | 59 | 236 | 37.441475 | -121.80358 | 4.68 | 36.992928 | -121.47 | 4.68 | | 35 | 0 | 1 | Calaveras;CC | null | 6.05 | 0.0010007863 | 1 | 4 | 59 | 236 | 37.441475 | -121.80358 | 4.68 | 36.992928 | -121.47 | 4.68 | | 35 | 0 | 2 | Calaveras;CC | null | 6.15 | 0.0017895743 | 1 | 4 | 59 | 236 | 37.441475 | -121.80358 | 4.68 | 36.992928 | -121.47 | 4.68 | | 35 | 0 | 3 | Calaveras;CC | null | 6.25 | 0.0018958241 | 1 | 4 | 59 | 236 | 37.441475 | -121.80358 | 4.68 | 36.992928 | -121.47 | 4.68 | | 35 | 0 | 4 | Calaveras;CC | null | 6.35 | 0.0015825889 | 1 | 4 | 59 | 236 | 37.441475 | -121.80358 | 4.68 | 36.992928 | -121.47 | 4.68 | | 35 | 0 | 5 | Calaveras;CC | null | 6.45 | 0.0010841207 | 1 | 4 | 59 | 236 | 37.441475 | -121.80358 | 4.68 | 36.992928 | -121.47 | 4.68 | | 35 | 0 | 6 | Calaveras;CC | null | 6.55 | 0.0004575977 | 1 | 4 | 59 | 236 | 37.441475 | -121.80358 | 4.68 | 36.992928 | -121.47 | 4.68 | | 35 | 0 | 7 | Calaveras;CC | null | 6.65 | 0.00010773571 | 1 | 4 | 59 | 236 | 37.441475 | -121.80358 | 4.68 | 36.992928 | -121.47 | 4.68 | +--------+-----------+------------+--------------+-------------+------+---------------+--------------+----------+-------------+------------+-----------+------------+-------------+-----------+---------+-----------+
Rupture_Variations Table
The "Rupture_Variations" table contains information about each rupture variation - each realization created with the rupture generator, with a hypocenter and slip distribution. Linking this table with the Ruptures table allows you to select a specific variation of a rupture with the hypocenter in a certain location. This is dependent on the specific rupture generator used, identified by the Rup_Var_Scenario_ID.
Rup_Var_Scenario_ID=3: Graves & Pitarka (2007), used in preliminary testing and Study 1.0.
Rup_Var_Scenario_ID=4: Graves & Pitarka (2010), used in Study 1.1, 1.3, 1.4, 2.2, 13.4, and 14.2.
Rup_Var_Scenario_ID=5: Graves & Pitarka (2014) with random hypocenter spacing, only used in testing.
Rup_Var_Scenario_ID=6: Graves & Pitarka (2014) with uniform hypocenter spacing, used in Study 15.4 and 15.12.
mysql> describe Rupture_Variations; +---------------------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------------+---------------------+------+-----+---------+-------+ | Rup_Var_ID | bigint(20) unsigned | NO | PRI | NULL | | | Rup_Var_Scenario_ID | int(10) unsigned | NO | PRI | NULL | | | ERF_ID | int(10) unsigned | NO | PRI | NULL | | | Source_ID | int(10) unsigned | NO | PRI | NULL | | | Rupture_ID | int(10) unsigned | NO | PRI | NULL | | | Rup_Var_LFN | varchar(50) | NO | | NULL | | | Hypocenter_Lat | double | YES | | NULL | | | Hypocenter_Lon | double | YES | | NULL | | | Hypocenter_Depth | double | YES | | NULL | | +---------------------+---------------------+------+-----+---------+-------+
For example, to look at information for ERF 36, source 0, rupture 0:
mysql> SELECT * FROM Rupture_Variations WHERE ERF_ID=36 AND RUP_VAR_SCENARIO_ID=6 AND SOURCE_ID=0 AND RUPTURE_ID=0 limit 5; +------------+---------------------+--------+-----------+------------+-----------------------------------+----------------+----------------+------------------+ | Rup_Var_ID | Rup_Var_Scenario_ID | ERF_ID | Source_ID | Rupture_ID | Rup_Var_LFN | Hypocenter_Lat | Hypocenter_Lon | Hypocenter_Depth | +------------+---------------------+--------+-----------+------------+-----------------------------------+----------------+----------------+------------------+ | 0 | 6 | 36 | 0 | 0 | e36_rv6_0_0.txt.variation-r000000 | 37.4274 | -121.7895 | 4.88 | | 1 | 6 | 36 | 0 | 0 | e36_rv6_0_0.txt.variation-r000001 | 37.3946 | -121.7577 | 5.48 | | 2 | 6 | 36 | 0 | 0 | e36_rv6_0_0.txt.variation-r000002 | 37.3615 | -121.7303 | 5.68 | | 3 | 6 | 36 | 0 | 0 | e36_rv6_0_0.txt.variation-r000003 | 37.3278 | -121.7042 | 5.28 | | 4 | 6 | 36 | 0 | 0 | e36_rv6_0_0.txt.variation-r000004 | 37.2917 | -121.6788 | 5.28 | +------------+---------------------+--------+-----------+------------+-----------------------------------+----------------+----------------+------------------+
CyberShake_Site_Ruptures Table
the "CyberShake_Site_Ruptures" table maps CyberShake sites to the Ruptures within a given cutoff distance. This can be used to find all ruptures for which CyberShake has been computed for a given site. It also contains the minimum 3D distance between each site and rupture surface.
NOTE: Some distances are currently (as of 6/11/2015) excluded for ERF 36. You can use distances for ERF 35 in their place as they are equivalent.
mysql> describe CyberShake_Site_Ruptures; +-------------------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------------+------------------+------+-----+---------+-------+ | CS_Site_ID | int(10) unsigned | NO | PRI | NULL | | | ERF_ID | int(10) unsigned | NO | PRI | NULL | | | Source_ID | int(10) unsigned | NO | PRI | NULL | | | Rupture_ID | int(10) unsigned | NO | PRI | NULL | | | Cutoff_Dist | double | NO | PRI | NULL | | | Site_Rupture_Dist | double | YES | | NULL | | +-------------------+------------------+------+-----+---------+-------+
For example, to find all ruptures, including distances, for site 18 (USC) and ERF 35:
mysql> select * from CyberShake_Site_Ruptures where CS_Site_ID=18 and ERF_ID=35 limit 5; +------------+--------+-----------+------------+-------------+-------------------+ | CS_Site_ID | ERF_ID | Source_ID | Rupture_ID | Cutoff_Dist | Site_Rupture_Dist | +------------+--------+-----------+------------+-------------+-------------------+ | 18 | 35 | 7 | 0 | 200 | 68.19 | | 18 | 35 | 7 | 1 | 200 | 68.19 | | 18 | 35 | 7 | 2 | 200 | 68.19 | | 18 | 35 | 7 | 3 | 200 | 68.19 | | 18 | 35 | 7 | 4 | 200 | 68.19 | +------------+--------+-----------+------------+-------------+-------------------+
Accessing Pre-Computed Hazard Curves
CyberShake hazard curves are stored in the "Hazard_Curves" and "Hazard_Curve_Points" tables.
The Hazard_Curves table contains metadata for each hazard curve:
mysql> describe Hazard_Curves; +-------------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------------+------------------+------+-----+---------+----------------+ | Hazard_Curve_ID | int(10) unsigned | NO | PRI | NULL | auto_increment | | Run_ID | int(10) unsigned | NO | MUL | NULL | | | Curve_Date | date | YES | | NULL | | | IM_Type_ID | int(10) unsigned | NO | | NULL | | | Hazard_Dataset_ID | int(10) unsigned | YES | MUL | NULL | | +-------------------+------------------+------+-----+---------+----------------+
The Hazard_Curve_ID field from the Hazard_Curves table can be used to find the actual hazard curve points in the Hazard_Curve_Points table. Additionally, the Hazard_Dataset_ID table can be used to quickly find all curves for a given study. Study 15.4 curves all have Hazard_Dataset_ID=57, and the Hazard_Datasets table contains additional metadata.
mysql> describe Hazard_Curve_Points; +-----------------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------------------+------------------+------+-----+---------+----------------+ | Hazard_Curve_Point_ID | int(10) unsigned | NO | PRI | NULL | auto_increment | | Hazard_Curve_ID | int(10) unsigned | NO | MUL | NULL | | | X_Value | float | NO | | NULL | | | Y_Value | float | NO | | NULL | | +-----------------------+------------------+------+-----+---------+----------------+
For example, to access 3 second SA (geometric mean) curves (IM_Type_ID=21) for Run 3970 (Site USC from Study 15.4):
mysql> select * from Hazard_Curves where Run_ID=3970 and IM_Type_ID=21; +-----------------+--------+------------+------------+-------------------+ | Hazard_Curve_ID | Run_ID | Curve_Date | IM_Type_ID | Hazard_Dataset_ID | +-----------------+--------+------------+------------+-------------------+ | 20681 | 3970 | 2015-04-26 | 21 | 57 | +-----------------+--------+------------+------------+-------------------+
Then using that hazard curve ID, fetch the actual curve points where X_Value is the intensity measure in g's and Y_Value is the annual probability of exceedance:
mysql> select * from Hazard_Curve_Points where Hazard_Curve_ID=20681; +-----------------------+-----------------+---------+-------------+ | Hazard_Curve_Point_ID | Hazard_Curve_ID | X_Value | Y_Value | +-----------------------+-----------------+---------+-------------+ | 1082250 | 20681 | 0.0001 | 0.0972944 | | 1082251 | 20681 | 0.00013 | 0.0972944 | | 1082252 | 20681 | 0.00016 | 0.0972944 | | 1082253 | 20681 | 0.0002 | 0.0972944 | ... +-----------------------+-----------------+---------+-------------+
