Using the Internet, Word, Excel &Arcmap

Recent Earthquakes in California

Using the Internet, Word, Excel &ArcMap

The objectives are to:

Capture a data set of up-to-the-minute earthquake information from the Internet
Format tabular data in Microsoft Word & Microsoft Excel
Prepare the data set and bring it into ArcMap as X,Y Data and save it as a shapefile.
Perform a spatial join of the new recent earthquake features with the counties of California and then classify the counties by the quantity of recent earthquakes (normalized by area).
Turn in an electronic copy of your layout as a PDF file into the “recentquakes” folder on the U drive.

Background:

Several web sites maintain up-to-date information on a variety of topics that reference geographic location on earth. Unfortunately, many times this data isn't available for download in a format that can be directly brought into ArcMap. Preparing the data for use is fairly easy using Microsoft Excel and Word (or NotePad or WordPad). Once this data is brought into a GIS you are able to display and query it. In the case of recent earthquakes in California, you will be able to query the location, magnitude, and depth of the most recent earthquakes in your area of interest.

Typically, projects involving technology such as GIS must use of a number of different programs for their various strengths. The ability and confidence to move back and forth between programs is very valuable and often essential. [Often referred to as interoperability - the ability of content, a subsystem or system to seamlessly work with other systems, subsystems or content via the use of agreed specifications/standards.]

The Recent Earthquake Project will be broken down into 4 parts. These are:

Capture data from the Internet & edit in Word.
Format data in Excel, calculate averages, make graphs, etc., and save as a .txt file.
Bring Earthquake data into ArcMap.
Perform a spatial join and symbolize the features.
Create a layout of your project.

Part I Instructions: Capture data from the Internet & Format

Note: You may help each other out for the editing in Word and in Excel. It is important that each of you do all these parts to be sure that you begin to develop some level of competency in using a variety of programs to edit and format data.

LOCATE AND CAPTURE DATA

Note: Using ArcCatalog make a new project folder called RecentEQ. In the project folder make a new folder called “XYdata”.

Conduct an Internet search for a “list of recent Earthquakes for California”, make sure you find the information in a tabular form, not just on a map. Ultimately you want to show at least 100 earthquakes on your map, I suggest you grab a lot then later perform a query later in ArcMap for the ones you want to display in your project.

Once you have found a site that lists the most recent earthquakes in a tabular format, be sure that it lists all of the following data for each earthquake event:

Location - latitude and longitude
Location - place name
Date
Magnitude
Depth

Write down the URL that you will use to gather data: URL: ______

Using your mouse, highlight the data in the table including the field headings, then from the Edit menu, choose Copy. (just ignore the automatic spell check message, if you get one.)

EDIT DATA USING MICROSOFT WORD

Open Microsoft Word and paste data into a new document. Save to the “XYdata” folder and name it "eqmetadata.doc".

Note: You could use WordPad or NotePad for this step instead of Word.

Next, do a "Save As" and save the file as EQ_edit1.doc. Change the page set-up to a landscape view so it is easier to view the data line by line.

Scan the information in your EQ_edit1.doc file. Then, delete all extraneous data and text except the field (column) headings and the earthquake data.(It should look like below.)

Check to see that you have a Field Headings for each column of data. It won't be lined up in Word, so don't worry about that. If a column of data didn't have a heading then put one in as a place holder. After all field headings, you must have some spaces. Double check to be sure all column headings have a space between them. Do a "Save As", and save as a “Plain Text (*.txt)” file in the XYdata folder. It should now be called: EQ_edit1.txt. Close the file then Exit out of Word. Don’t worry about the warning box if you get one.

FORMAT DATA IN EXCEL

Start Excel. From the File menu, choose Open. When the Open window comes to the screen, choose 'Text files” under the File Type drop down menu. Navigate to the XYdata folder and the file EQ_edit1.txt, which you saved in the last section.

When you open the EQ_edit.txt file in Excel, the "Text Import Wizard" is launched. It should default to "Fixed Width", import start at row 1, and File origin: Windows (ANSI) (If not, change it.)

Scan the data in the lower preview window. It should all be there, though the formatting may look awkward. Click on the NEXT button in the wizard.

READ the instructions at the top of the next window in the Wizard. You need to do the following (this will vary somewhat depending where you got your data).

If your Lat & Long came in with N & W, you need to add breaklines to separate the N & W into their own columns.

If the location for each row (record) is broken up into several columns (fields), then you need to delete some breaklines to have the location appear together (double click to remove breaks). Look at the original data to see where the breaks are if you are not sure. Scroll all the way to the right to make sure you see everything.

Click NEXT. In this part of the Wizard, you will skip columns that you don't need as suggested below:

Any extraneous fields such as "Map”, the "N" and "W" fields. Select the field that you want to skip then click in the circle at the top of the wizard for: "Do not import column (skip)". Note: you had to create break lines in the previous step to delete the “map, N and W” columns in this step.

NOTE: Don't worry about the field headings being out of alignment. You will fix them in the next step.

Click FINISH when you are done. Look over the data in Excel. Widen the field display width by clicking your mouse between the field names at the top of the sheet and dragging it to widen or by double clicking between the field columns. If there are any obvious errors in the table correct them, for example if a letter is inserted into a field that should just have numbers - correct it.

Adjust field headings, if necessary (i.e. if a letter is cut off add it, if one appears where it should not, delete it.)

From the File menu, choose “Save As”. Navigate to the XYdata folder and save it as EQ_format.xls.

Note: You will continue to format this data in the next part.

Part II: Using Excel to format the Earthquake data

Background:

In this part you will format the data that you brought into Excel in Part I. Next, you will save the Excel spreadsheet as a.txt file. In the Excel spreadsheet you will use the formula function to calculate statistics (optional). Then you will bring this data into ArcMap (Part III).

Part II Instructions:

Open the Excel file you saved in Part I, if it is not open already.

Note: Notice that the numbers in the columns for Latitude and Longitude are positive numbers. In reality, the Latitudes are positive as they are north of the equator in the Northern Hemisphere. However, the Longitudes should be negative numbers as they are west of Greenwich. In ArcMap (and most other GIS programs) this matters in order for your data to appear in the correct hemisphere.

To change the Longitude field to negative numbers: first you need a new column. Click on the column heading to the right of the Longitude column. From the Insert menu, select Columns. A new column will appear to the right of the Longitude column. Type in the heading: Longneg.

Click in the blank cell below the Longneg heading. Then click the curser in the formula area above the worksheet (has an fxsign to the left of the typing area). Type in an "=" sign followed by the cell location of the first Longitude data, then “*-1” then, press the enter key. (It should look something like the following: =E2*-1) You should end up with the negative value of the longitude in your new Longneg column.

The beauty of a spreadsheet is the ability to do many calculations very quickly. To calculate the values of the rest of the negative longitude numbers, you will use the copy paste command. First click on the negative longitude cell that you just calculated. Then from the Edit menu, choose Copy.

Then click on the empty cell below the negative longitude cell and from the Edit menu, choose Paste. Thendrag the box down (from the lower right-hand corner of the box) until you reach the end of the line of data for the other columns. You should now have a column of negative Longitudes whose values look identical to the longitude column next to it, except for the negative sign. SAVE

Next, you need to be sure the numeric fields have been formatted for the necessary number of decimal places. Click on the Letter for the column above a field that should be numeric, (such as Magnitude). Then holding down your Ctrl key click on any other column letters which will require the same number of decimal places (such as Depth). Only these two fields should now be highlighted.

From the Format menu, choose Cells. When the Cell Format Window appears, click on Number, then set the number of decimal places that your original data came in (such as 1). Click OK when you are done. You also need to format the date and time, make sure you choose a type that matches the data format that you have.

Repeat the following with any other numeric fields that require a different number of decimal places (such as the Latitude, Longitude and Longneg fields). SAVE when you are done.

Next, sort the data by Magnitude in descending order (from the Data dropdown menu select “Sort”) and take a look at your range of values.

Next, highlight all the records in the spreadsheet you want to save (at least 100), then do a SAVE AS, and save as “Text (tab delimited) .txt” with the name EQ.txt When you click save it will ask you if you want to keep the workbook in this format, say yes. Do a SAVE AS, again and save as “DBF 4 (dBASE IV)(*.dbf)” with the name EQ.dbf When you click save it will ask you if you want to keep the workbook in this format, say yes. Then close the file, if it asks you to replace the existing file, say no.

OPTIONAL (Do later if you have time) –GET EXPERIENCE USING FORMULAS IN EXCEL

From the File menu open the Excel version of the data.

At the bottom of your spreadsheet, a couple of lines below your Earthquake data, put the following in Column A:

MAGNITUDE:

AVERAGE

Click in the cell to the right of the Average cell (you will actually need to insert a new blank column before you do this. Click on the column heading to the right of the needed Column. From the Insert menu, select Columns. You can remove this column later). Then click on the function button in the Excel GUI (graphical user interface) (fx). This brings up the Insert Function dialog box. Click on Statistical for function type, then Average. When the next dialog box comes to the screen, next to number 1, type in the cell range for your magnitude column of data (For example: A2:A333). Click OK when you are done. The result of your formula should appear in the cell to the right of the AVERAGE cell.

Repeat the above for Earthquake Depth as Well. SAVE

OPTIONAL – TO GET EXPERIENCE USING CHARTS IN EXCEL

Select the data in the magnitude column. Use the CTL key to also select the data in the depth column. Click on the CHART button in Excel GUI. Select a XY (Scatter) graph. Follow the chart wizard. Remember to label the chart and the X and Y axes. After you finish close the program and exit Excel.

Part III: Bringing the Earthquake data into ArcMap

Background:

In Part I and II you captured earthquake epicenter data from the web and used Word and Excel to format the data. In this activity, you will bring the .txt file that you saved in Excel into ArcMap.

Part III Instructions:

Create a new data folder in your RecentEQ folder. Using ArcCatalog copy and paste your California state file, California Counties file, California Cities file, and California faults file into you data folder. Also grab the projected file of all the states and place it in your data folder. All your files should have the same projection.

Open a new project in ArcMap and call it NewQuakes. Create a map with all the counties and cities in California. Use an appropriate projection. Save your projection and make it transportable.

Add the EQ.txt file to the ArcMap project (you do this the same way you would add a shapefile). It is very common to get errors here- read the error message and go back and make the necessary changes in Excel and try again. The errors generally refer to a space or inappropriate character in one of the field headings. If you still get errors try working with the EQ.dbf file.

Now you are ready to display the recent earthquake data. Click on the “Tools” menu and select “Add X,Y Data”. Browse to find the EQ.txt file that you formatted and saved in Excel and click “Add”. Next, specify the field for the X and Y coordinates (make sure you pick the right ones – think about it). Leave the Spatial Reference System as Unknown for now. You will change this later. Hit OK.

Hopefully your X,Y data table is now an “events” layer composed of many points each representing a recent earthquake epicenter. Once everything comes through okay, open the attribute table for the EQ layer to be sure it has everything that you expect, such as all the field names across the top and all the earthquake data in the proper field. Close the table when you are done checking it.

Nextright click on the new events layer and select “Data” > “Export”. Choose “Use the coordinate system the same as the data frame” assuming your project has an appropriate projection. Make sure your new data file is going into an appropriate folder and rename the file “recentquakes” and hit OK. This new shape file will be much more stable for you to work with when you symbolize the data; event files are not very robust.

Remove the “events” layer from the project. SAVE PROJECT.

Use the distance tool to check the distance from 5 of the earthquakes to the nearby cities, listed with the earthquake data. To get the proper distance measurement open the Layerproperties by double clicking on Layers, select the General tab and under Units change the Display to kilometers. Turn on the labels for the cities to make it easier to find the cities. Use the Identity tool to see the attributes for any given earthquake.

City and distance in DatabaseMeasured distance in ArcMap.

______
______
______
______
______

Part IV: Symbolizing the counties by the number of recent quakes.

Background:

Now that you have new point features that represent the locations of recent earthquakes in California you will use the spatial relationships of the recentquakes (points) to the counties in California (polygons) to perform a spatial join.

Part IV Instructions:

Use “Select by location” to remove the recent quakes that are not in California.

Read pages 271-272 in GTKArcGIS and join the attributes of the recent quakes to the countries so you have a count of the total number of recent quakes in each county.

Symbolize the counties by the number of quakes (count). Add a second data frame and copy all your data layers from the first data frame over to the second data frame. This time normalize the quakes by area for comparison.

Produce a layout of your project, include a legend, your name, MPC GIS Lab, projection & datum, and date and export it as a PDF (put it in your Map_Exports folder).

Place a copy of your PDF in the RecentQuakes folder on the server. You file name should be your last name_recent.pdf

Developed by Ann Johnson, Community College Coordinator, ESRI- 4/99, Modified by Chris Crown and Deidre Sullivan