Difference between revisions of "Accessing CyberShake Database Data"

From SCECpedia
Jump to navigationJump to search
(Created page with "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: Cy...")
 
 
(7 intermediate revisions by the same user not shown)
Line 1: Line 1:
 
To query the CyberShake database, you can use the following login information with a MySQL client:
 
To query the CyberShake database, you can use the following login information with a MySQL client:
  
−
host: focal.usc.edu
+
host: focal.usc.edu
−
user: cybershk_ro
+
user: cybershk_ro
−
password: CyberShake2007
+
password: CyberShake2007
−
database: CyberShake
+
database: CyberShake
  
 
This username/password is read-only, so you don't need to worry about accidentally changing the database.
 
This username/password is read-only, so you don't need to worry about accidentally changing the database.
Line 10: Line 10:
 
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:
 
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_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.)
+
*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.
+
*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
+
*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
+
*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
+
*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
  
−
So if you'd like to get all the PSA data for LADT for 3 seconds, you'd want to run the following query:
+
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.
  
−
select P.Source_ID, P.Rupture_ID, P.Rup_Var_ID, P.IM_Value
+
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:
−
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=35 and R.Status="Verified" and R.SGT_Variation_ID=5 and R.Velocity_Model_ID=1
 
−
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=1" part means CVM-S; if you want CVM-H instead, you should use "R.Velocity_Model_ID=2".
+
select P.Source_ID, P.Rupture_ID, P.Rup_Var_ID, P.IM_Value
−
The "P.IM_Type_ID=21" means 3 second period. This mapping is stored in the IM_Types table, but here are the values you'd want:
+
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;
  
−
Period = 10 sec, IM_Type_ID=1
+
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.
−
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
 
  
−
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.
+
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:
  
−
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:
+
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
  
−
select * from Ruptures
+
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:
−
where ERF_ID=35 and Source_ID=244 and Rupture_ID=12;
+
 +
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.
 
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 51: Line 89:
 
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.

Latest revision as of 19:54, 28 October 2021

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.

Retrieved from "https://strike.scec.org/scecwiki/index.php?title=Accessing_CyberShake_Database_Data&oldid=26144"