CyberShake DB Queries

From SCECpedia
Revision as of 20:14, 7 April 2016 by Scottcal (talk | contribs) (→‎Rupture_Variations Table)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigationJump to search

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 |
...
+-----------------------+-----------------+---------+-------------+
Retrieved from "https://strike.scec.org/scecwiki/index.php?title=CyberShake_DB_Queries&oldid=16043"