CyberShake DB Queries
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 | +--------+-----------+------------+--------------+-------------+------+---------------+--------------+----------+-------------+------------+-----------+------------+-------------+-----------+---------+-----------+
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 | ... +-----------------------+-----------------+---------+-------------+
