Difference between revisions of "Accessing CyberShake Database Data"

From SCECpedia
Jump to navigationJump to search
Line 49: Line 49:
 
If you run this query, you should get source 244, rupture 12, rupture variation 0 as your largest PSA at 3 seconds.  If you wanted to learn more about this rupture, you could run the following query:
 
If you run this query, you should get source 244, rupture 12, rupture variation 0 as your largest PSA at 3 seconds.  If you wanted to learn more about this rupture, you could run the following query:
  
select * from Ruptures
+
select * from Ruptures
where ERF_ID=35 and Source_ID=244 and Rupture_ID=12;
+
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.
 
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.
Line 56: Line 56:
 
If you wanted to get the distance from LADT to this rupture, you could run the following query:
 
If you wanted to get the distance from LADT to this rupture, you could run the following query:
  
select SR.Site_Rupture_Dist
+
select SR.Site_Rupture_Dist
from CyberShake_Site_Ruptures SR, CyberShake_Sites S
+
from CyberShake_Site_Ruptures SR, CyberShake_Sites S
where S.CS_Short_Name="LADT"
+
where S.CS_Short_Name="LADT"
and S.CS_Site_ID=SR.CS_Site_ID
+
and S.CS_Site_ID=SR.CS_Site_ID
and SR.ERF_ID=35
+
and SR.ERF_ID=36
and SR.Source_ID=244
+
and SR.Source_ID=244
and SR.Rupture_ID=12;
+
and SR.Rupture_ID=12;
 
+
 
This will give you the shortest distance from the site to any part of the rupture surface, in km.
 
This will give you the shortest distance from the site to any part of the rupture surface, in km.

Revision as of 19:10, 30 September 2016

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

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=21
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=21" means 3 second SA period. This mapping is stored in the IM_Types table, but here are the values of most interest:

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

RotD data has different IM Types; you would want to search the IM_Types table to identify the type IDs you want.

In this 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 0 as your largest 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.