civil engineering, geotechnical earthquakes
how to solve this using excel
Prof. Emel Seyhan
CE447 – Intro to Geotechnical Earthquake Engineering
CSULB – FALL 2020
HOMEWORK #3
Official due: Oct, 15th, 2020, 11.59PM
Penalty due (up to 20%): Up to Oct 22nd, 2020, 11.59PM
General note: Include all your plots with proper axes labels, title, legend etc. You are free to use Excel, Matlab etc.
Check out the lecture notes #12-13 and recording for details of the input files.
1. (20 pts) You will use the strong ground motion recordings flatfile
“NGA_West2_Flatfile_RotD50_d050_public_version.xslx” downloaded from
https://peer.berkeley.edu/research/data-sciences/databases. For the estimated ground shaking from
GMPEs, you will use the PEER tool “Five NGA-West2 horizontal GMPEs(locked version)”. Note: Don’t
forget to enable content to activate underlying VBA codes. You don’t need to unlock the file to use it.
a) Filter the flatfile and grab RSN760 and 797 recordings from Loma Prieta mainshock. Plot the response
spectra that is given in the file between Col EB-IK. Use PGA, PSA at spectral periods from 0.01 to
10sec. Explain why there are differences between two spectra. Also, explain how a 2-story and 20-
story building located on these stations would respond?
b) Identify all the recordings from Northridge mainshock with MW 6.69. Scatter plot the recordings
against Joyner-Boore distance (aka RJB in km) for PGA, T=0.2sec and T=1.0sec. Remove unknown input
variable values shown as -999. Explain the trends you observe with RJB and spectral period.
c) Now, you will use the PEER tool Main Sheet: (We will not compare predictions against observations
for a given event using this tool).
a. Use geometric GMPE averaging method
b. Use equal weights between ASK14, BSSA14, CB14 and CY14 NGA-West 2 GMPEs.
c. Use one standard deviation with 5% damping ratio
d. For blue colored cells, enter 999, so the codes get default values for unknown condition.
e. Calculate and plot median response spectra for MW7 strike-slip fault with 900 at a series of
site-to-source distances [1, 10, 30, 80, 200km] on VS30 = 400m/s. Use Ry0 = 999.
f. Compare the predicted PSa from the weighted average of GMPEs and explain the differences
in spectra trends.
2. (10 pts) You will use the USGS unified seismic hazard tool. Go to
https://earthquake.usgs.gov/hazards/interactive/. Select the latest update from USGS as Dynamic:
Conterminous U.S. 2014 (v4.2.0). Raw data format works well with Chrome for this edition. Enter station
lat/lon for San Francisco as 37.779, -122.411.
a) Calculate the location level hazard curves for 5-story building at 760 m/s and 259 m/s for 10% in 50
years return period. From Raw Data and plot the hazard curves for Total component for y-axis:
Annual Frequency of Exceedence vs x-axis: ground motion metric (g). Include the reference line for
the return period. Note: If USGS website is down due to an update to Dynamic option, use
Conterminous U.S. 2014 (v4.0.x) with 760 m/s instead. Open raw data in Firefox or JSON format.
b) Explain the trends between two curves. Which one shows higher seismic hazard and why?
c) Add two more location lat/lon for i) Los Angeles as 34.046, -118.251 and ii) Portland 45.508,
-122.657. Extract the accurate hazard values from Raw Data for the same 5-story building for 10% in
50 years return period, but on 760m/s only. Plot bar charts to show the differences. Which location
has higher seismic hazard? What would you expect to observe in trends if the building was taller?
https://peer.berkeley.edu/research/data-sciences/databases
http://apps.peer.berkeley.edu/ngawest2/wp-content/uploads/2016/02/NGAW2_GMPE_Spreadsheets_v5.7_041415_Protected.zip
https://earthquake.usgs.gov/hazards/interactive/