Difference between revisions of "CyberShake DB Queries"

From SCECpedia
Jump to navigationJump to search
(Created page with '= 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 exp…')
 
Line 4: Line 4:
  
 
== Accessing ERF Info (rates, probabilities, site distances, etc) ==
 
== 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.
 
The "Ruptures" table contains information on each rupture in the Earthquake Rupture Forecasts (ERFs) used in CyberShake.
Line 54: Line 56:
 
|    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 |
 
|    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 |
 
+--------+-----------+------------+--------------+-------------+------+---------------+--------------+----------+-------------+------------+-----------+------------+-------------+-----------+---------+-----------+
 
+--------+-----------+------------+--------------+-------------+------+---------------+--------------+----------+-------------+------------+-----------+------------+-------------+-----------+---------+-----------+
 +
</pre>
 +
 +
=== 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 source distances.
 +
 +
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.
 +
 +
<pre>
 +
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    |      |
 +
+-------------------+------------------+------+-----+---------+-------+
 +
</pre>
 +
 +
For example, to find all ruptures, including distances, for site 18 (USC) and ERF 35:
 +
 +
<pre>
 +
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 |
 +
+------------+--------+-----------+------------+-------------+-------------------+
 
</pre>
 
</pre>

Revision as of 23:29, 11 June 2015

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 source distances.

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 |
+------------+--------+-----------+------------+-------------+-------------------+
Retrieved from "https://strike.scec.org/scecwiki/index.php?title=CyberShake_DB_Queries&oldid=13425"