SO503 Honors Modern Methods

Spring 2013

Lab 1: The Spreadsheet, Filtering, GPS

  Lab Objectives:

1.  Learn how to import an ASCII text file into the spreadsheet and use it for numerical calculations. Understand the two ways ASCII files can be arranged (fixed width and delimited).

2.  Know how to use if and trig functions (radians and quadrants for inverse functions) in the spreadsheet.

3. Get time into a useful format for numerical computations.

4. Understand one dimensional numerical filtering.

5. Consider how directional data is different from other numerical data.

6. Learn something about GPS data and the Mercator projection.

 The data files you need are called norm_gps_1.txt and norm_gps_2.txt.

Excel Tips

These two files are taken with a small data logger which we are evaluating for use in a research project tracking wildlife.  We want to answer the following questions:

• What units does the logger use for recording speed?

• Does the unit record speed with sufficient accuracy, or will we have to compute it from the raw data?

• If we have to compute speed from the raw data, how noisy is the data and will we have to filter it?

• Does the heading follow math or compass conventions?

We will turn in this assignment in two parts.  This lab will be individual effort; you will document on the writeup any assistance you receive (if none, explicitly state).

Due Tues 24 Jan at 1330 hours in the Blackboard assignment dropbox (e.g. YourName_Lab1_gps_speed.doc):

• Graph(s) with the speed of the unit as a function of time, and with a filter that averages 3, 5, and 7 readings, for both files, and  a median filter with what you consider to be the best number of terms. You can do this as one single graph for both data sets or two separate graphs.  You might also want to blow up part of the time axis so that you can see what the filters do.

• Table for 20 readings with Time (center of interval, think about the best units), Duration of Time interval, Distance covered in interval (meters), Speed during interval (meters per second),  Averaged speed over 3, 5, and 7 time intervals centered on the desired time, and your median filter.  This will be for the first 20 readings of the gps_1 file.

• The table and graph will be included in your Word document,  labeled in accord with the department style manual, and referenced in your discussion.

• Discuss answers to the following questions, as a coherent discussion and not a simple question/answer cut and paste job.

  • How does filtering affect the graph of the speed?  What size filter do you think best reflects the true speed, and why? How do the median and averaging filters differ?

• Graph of the track of the unit (strictly speaking you should make sure that the physical dimensions of the x and y axes are the same, and then you would have a Mercator map projection, but you can ignore this minor point) for both data files.  You can do this as one single graph or two separate graphs.

• Graph of the heading of the unit (compass degrees) as a function of time for the two files.

• Discuss how  averaging or filtering the azimuth data would be different than the same operations on the speed data? (While you are not required to do this, you may want to actually try this to help in answering this question.)

General guidance:

1.  These directions are not a cookbook for how to do the lab.  Learning to think is probably the most important thing you will get from your education here.  If you have problems, see me before you spin you wheels forever.

2.  The discussion is designed to make you think.  Your thought process is as important as the "correct" answer, which we will go over eventually.

Science Background

A map projection is a mathematical set of equations for taking a position on the three dimensional earth onto a flat map.  For the Mercator projection, this keeps parallels and meridians perpendicular.  The only way to do this is to increase the scale in north-south direction because the meridians should be converging toward the poles.  To keep the angles and local distances on the map correct, the east-west dimensions increase by the same amount.  This scaling factor increases from 1 at the equator to ¥ at the poles (which can thus never appear on a Mercator map).  The x and y coordinates on the map can be plotted like a regular graph.  They may or may not actually appear on the map—for ground maps used by the Army and Marines they are the preferred way to report positions, although with a UTM projection and  not with a Mercator projection.  The actual equations, especially for an elliptical earth, can be quite messy.

• Projection(lat, long, earth radius)  à Map(x,y)

• InverseProjection(Map x,y) à Earth(lat, long, earth radius)

A filter is a mathematical procedure to change the values in a series (1D case) or an image (2D case).  Filters can be designed in hardware in the data capture phase; you will discuss high-pass, low-pass, and band-pass filters in either your electrical engineering or weapons classes.  The filter can be designed to eliminate or enhance aspects of the data, to smooth or sharpen the resulting data stream.  For this lab we will run an averaging filter, using a running average of various numbers of data points (generally an odd number, such as 3, 5, 7, 9 or 11).  The filter will be unweighted, with all the points weighted equally.  We could also weight the filter, generally applying a larger weight to the central observation.  The sum of the weights should equal 1, so that the overall mean of the data stream is not affected.  The smoothing filter will even out natural irregularities in the data or in the sampling hardware, and can provide a more realistic picture of the true data signal in the absence of noise.

The diagram below shows the first six steps of filtering a series.  The filter is the top line in each step, the input series the middle line, and the output the third line.  In each case the terms in the filter are multiplied with the terms in the series below them and them summed to give the output.  Note that the first term in the input series has no match in the outputf, and that the same thing will happen at the end of the series.

Computer Common Sense:

·  Look at the first few results and see if they make sense.

·  Save your results often.  It may happen that you so thoroughly mess up the spreadsheet that you would like to return to where you were 10 minutes ago.  If you had saved it, you could be much happier.   You can even save multiple sequential versions, and then clean up when you are done.

·  If given the choice, use more spreadsheet columns rather than cramming everything into one monstrous formula.  Step by step calculations are much easier to debug.

·  Think about the units you want to use early on.

·  Check your data set before you apply the formulas, and resolve any problems then.  Formulas copied to regions of erroneous or missing data can cause errors or unreasonable values.

Importing an ASCII data file into the Spreadsheet.

• Insure file is ASCII: if in doubt, try to import it into NOTEPAD.  If you do not see a lot of happy faces, and can read what is in the file, you should be set.  If the file is coming from WordPerfect, you must insure it was not saved with the Word Perfect formatting commands.   Luckily we no longer use WordPerfect.
• Select the text file and go through the import Wizard.
  • The data will be in fixed width or delimited fields.
  • Check the row on which the data actually starts
  • For fixed width fields:
    • Carefully check the field widths; pay particular attention to LAT and LONG fields, because the minus signs are very important.
    • Be careful if the number of characters changes in the file, so you do not leave out the part of numbers.
    • Feld boundaries should generally be on the right side of the values, and blank columns go with the field on their right.
• Check the resulting import
  • See if there is extraneous text at the bottom of the spreadsheet and delete it
  • See if there are entire columns which you can delete. If you don't understand or want a column, you can probably delete it.
  • Your import might have missing values, which may or may not be allowed

Scientific Graphing From the spreadsheet

1.  You almost always want an "XY" graph, which you must pick manually.  The most frequently seen problem comes from a "Line" graph, which assumes that each point in equally spaced on the x axis and that the x series contains text labels rather than a scaled value.  If the x axis labels all overprint on your graph, you have a line graph.

2. The general consensus among graphics designers is that 3D graphs do not improve the presentation of your data.  They should be avoided (notice their absence in scientific journals).

3. Be very careful in the use of color or shaded backgrounds or patterns.  These (particularly fine dot patterns) may not reproduce well on the Xerox machine and thus should be used with caution.

Significant Digits in the spreadsheet:

1.  Be careful about the number of significant digits you use in calculations.  Small changes in values can lead to large errors if you are near the limits of the significant digits.  For this lab, you must do the dx and dy subtractions before you apply the scaling factor. 

  For the heading of the YP, remember the following: the computer uses radians, math angles start at east and run counterclockwise, and some arc tangent functions will only return values in two quadrants.  We want compass degrees.  You may have to do an IF comparison to figure out the correct quadrant, and some DEGREES, RADIANS, and ATAN or ATAN2 in your formulas.   If you have questions about these functions, you should resort to using the spreadsheet's help file.