View source for Accessing CyberShake Database Data

To query the CyberShake database, you can use the following login information with a MySQL client:

 host: focal.usc.edu
 user: cybershk_ro
 password: CyberShake2007
 database: CyberShake

This username/password is read-only, so you don't need to worry about accidentally changing the database.

We have quite a few tables and quite a lot of information in the database, but there are only a few tables that you'll need to use.  They are:

*CyberShake_Sites - used to look up the unique site ID from the name of the site (like "LADT").
*CyberShake_Runs - used to look up the unique run ID using a site ID and other run parameters (like the velocity model, broadband, etc.)
*Ruptures - list of ruptures from UCERF used in CyberShake, along with their magnitude, name, and probability.
*CyberShake_Site_Ruptures - list of ruptures used for each site, including the distance to the rupture
*Rupture_Variations - list of rupture variations (realizations with different hypocenters and slip distributions) for each rupture
*PeakAmplitudes - used to get the PSA values, given the run ID and a specific rupture, rupture variation, and period
*Hazard_Curves - contains the run id and IM type for each hazard curve
*Hazard_Curve_Points - the X and Y values describing each hazard curve

The wiki page for each CyberShake study has the information - SGT ID, Rupture_Variation_Scenario_ID, etc. - which you need to retrieve the data from a specific study.

So if you'd like to get all the PSA data for LADT for 3 seconds from Study 15.12, you'd want to run the following query:

 select P.Source_ID, P.Rupture_ID, P.Rup_Var_ID, P.IM_Value
 from CyberShake_Sites S, CyberShake_Runs R, PeakAmplitudes P
 where S.CS_Short_Name="LADT"
 and S.CS_Site_ID=R.Site_ID
 and R.Max_Frequency=10.0 and R.ERF_ID=36 and R.Status="Verified" and R.SGT_Variation_ID=8 and R.Velocity_Model_ID=5 and R.Rup_Var_Scenario_ID=6
 and P.Run_ID=R.Run_ID
 and P.IM_Type_ID=162
 order by P.IM_Value desc limit 100;

The "R.Velocity_Model_ID=5" part means CVM-S4.26.  The "R.Rup_Var_Scenario_ID=6" part means Graves & Pitarka (2014) rupture variations, with uniform hypocenter spacing.

The "P.IM_Type_ID=162" means 3 second period for RotD50.  This mapping is stored in the IM_Types table, but here are the values of most interest:

 RotD50, Period = 10 sec, IM_Type_ID=152
 RotD50, Period = 5 sec, IM_Type_ID=158
 RotD50, Period = 3 sec, IM_Type_ID=162
 RotD50, Period = 2 sec, IM_Type_ID=167
 RotD50, Period = 1 sec, IM_Type_ID=169
 RotD50, Period = 0.5 sec, IM_Type_ID=173
 RotD50, Period = 0.2 sec, IM_Type_ID=174
 RotD50, Period = 0.1 sec, IM_Type_ID=175

RotD100 data has different IM Types; you could search the IM_Types table to identify the type IDs you want.  Here are the values of most interest:
 
 RotD100, Period = 10 sec, IM_Type_ID=136
 RotD100, Period = 5 sec, IM_Type_ID=142
 RotD100, Period = 3 sec, IM_Type_ID=146
 RotD100, Period = 2 sec, IM_Type_ID=151
 RotD100, Period = 1 sec, IM_Type_ID=168
 RotD100, Period = 0.5 sec, IM_Type_ID=170
 RotD100, Period = 0.2 sec, IM_Type_ID=171
 RotD100, Period = 0.1 sec, IM_Type_ID=172

We also have geometric mean data:

 Period = 10 sec, IM_Type_ID=1
 Period = 5 sec, IM_Type_ID=11
 Period = 3 sec, IM_Type_ID=21
 Period = 2 sec, IM_Type_ID=26
 Period = 1 sec, IM_Type_ID=86
 Period = 0.5 sec, IM_Type_ID=88
 Period = 0.2 sec, IM_Type_ID=94
 Period = 0.1 sec, IM_Type_ID=99

Duration data has different IM Types:

 acceleration significant duration, 5% to 75%, X component, IM_Type_ID=176
 acceleration significant duration, 5% to 95%, X component, IM_Type_ID=177
 velocity significant duration, 5% to 75%, X component, IM_Type_ID=178
 velocity significant duration, 5% to 95%, X component, IM_Type_ID=179
 acceleration significant duration, 5% to 75%, X component, IM_Type_ID=180
 acceleration significant duration, 5% to 95%, X component, IM_Type_ID=181
 velocity significant duration, 5% to 75%, X component, IM_Type_ID=182
 velocity significant duration, 5% to 95%, X component, IM_Type_ID=183

In the above example, I have asked MySQL to order them in decreasing IM_Value, so the largest PSA values are at the top.  Since the query would return about 400,000 values, I've also added the 'limit 100' so I only get the top 100 values.

If you run this query, you should get source 244, rupture 12, rupture variation 25 as your largest RotD50 PSA at 3 seconds.  If you wanted to learn more about this rupture, you could run the following query:

 select * from Ruptures
 where ERF_ID=36 and Source_ID=244 and Rupture_ID=12;

You'll see it's a Puente Hills rupture, with magnitude 7.25 and probability ~1.97e-06.  The probability of the rupture is divided evenly among its rupture variations.

If you wanted to get the distance from LADT to this rupture, you could run the following query:

 select SR.Site_Rupture_Dist
 from CyberShake_Site_Ruptures SR, CyberShake_Sites S
 where S.CS_Short_Name="LADT"
 and S.CS_Site_ID=SR.CS_Site_ID
 and SR.ERF_ID=36
 and SR.Source_ID=244
 and SR.Rupture_ID=12;
 
This will give you the shortest distance from the site to any part of the rupture surface, in km.