Accessing CyberShake Database Data - Revision history

Scottcal at 19:54, 28 October 2021

2021-10-28T19:54:17Z

Scottcal at 20:45, 13 February 2020

2020-02-13T20:45:41Z

Scottcal at 19:36, 12 October 2017

2017-10-12T19:36:59Z

Scottcal at 18:30, 20 October 2016

2016-10-20T18:30:48Z

Scottcal at 18:30, 20 October 2016

2016-10-20T18:30:31Z

Scottcal at 19:10, 30 September 2016

2016-09-30T19:10:04Z

Scottcal at 19:02, 30 September 2016

2016-09-30T19:02:20Z

Scottcal at 19:00, 30 September 2016

2016-09-30T19:00:28Z

Scottcal: 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..."

2016-09-30T18:33:02Z

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..."

New page

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

So if you'd like to get all the PSA data for LADT for 3 seconds, 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=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".
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:

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

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=35 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=35
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.

@@ Line 29: / Line 29: @@
   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
+  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=21" means 3 second SA geometric mean period.  This mapping is stored in the IM_Types table, but here are the values of most interest:
+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
@@ Line 55: / Line 44: @@
   RotD50, Period = 0.2 sec, IM_Type_ID=174
   RotD50, Period = 0.1 sec, IM_Type_ID=175
   RotD100, Period = 10 sec, IM_Type_ID=136
@@ Line 65: / Line 56: @@
   RotD100, Period = 0.1 sec, IM_Type_ID=172
-Duration data also has different IM Types:
+We also have geometric mean data:
   acceleration significant duration, 5% to 75%, X component, IM_Type_ID=176
@@ Line 78: / Line 80: @@
 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 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 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

@@ Line 16: / Line 16: @@
 *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.

@@ Line 62: / Line 62: @@
   RotD100, Period = 0.2 sec, IM_Type_ID=171
   RotD100, Period = 0.1 sec, IM_Type_ID=172
 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.

@@ Line 63: / Line 63: @@
   RotD100, Period = 0.1 sec, IM_Type_ID=172
-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.
+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 0 as your largest PSA at 3 seconds.  If you wanted to learn more about this rupture, you could run the following query:

@@ Line 32: / Line 32: @@
 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:
+The "P.IM_Type_ID=21" means 3 second SA geometric mean 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
@@ Line 43: / Line 43: @@
   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.
+RotD 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:
 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.

@@ 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:
-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.
@@ Line 56: / Line 56: @@
 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.

@@ 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:
-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.)
+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:

@@ Line 1: / Line 1: @@
 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.
@@ Line 11: / Line 11: @@
 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
-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 Study has the information - SGT ID, Rupture_Variation_Scenario_ID, etc. - which you need for
-select P.Source_ID, P.Rupture_ID, P.Rup_Var_ID, P.IM_Value
+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:
-The "R.Velocity_Model_ID=1" part means CVM-S; if you want CVM-H instead, you should use "R.Velocity_Model_ID=2".
+ Period = 10 sec, IM_Type_ID=1
-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:
+ Period = 5 sec, IM_Type_ID=11
-Period = 10 sec, IM_Type_ID=1
+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.