CE 111 Laboratory Laboratory 2 and 3 Computational Programs with Spreadsheets

TRAVERSE COMPUTATIONS AND ADJUSTMENTS

Objectives:Develop skills using spreadsheet programs and analyze basic surveying problems using student-developed program. Team instruction is to be modeled.

Background: Land surveys and construction control surveys require the surveyor to establish coordinates of defined points and verify the accuracy of the survey work. In land surveys the area within the boundaries is also needed. Potential angular and distance errors can be quickly checked and balanced prior to final use by using carefully constructed computer programs. In addition, circular and vertical curves can be readily analyzed using basic spreadsheet programs. Only the first of the programs will be started this week, but additional program will be developed later for circular and vertical curve details needed for route construction (highways, railroads, pipelines, etc).

The instructor will provide a base Excel program to start from, after the students demonstrate basic understanding of the computations the surveyor uses to adjust for angular error and correct for potential linear measurement error in surveying.

Base problem for Hand computation:

A four sided closed area was surveyed with the total station operator measuring lengths and angles by setting up at each corner and measuring the distances and angles. These measurements were averaged and only the average values were reported so we don’t know if the quality of measurement may have been different for any corner, so any angular adjustments will be distributed equally around the traverse. A general sketch is shown, but it is not to scale. The angular measurements and distances are provided in Table 1.

Table 1 Survey data for traverse

Station / Angle at Station / Distance between points(ft)
A / 66 0 22’ 03”
465.90
B / 1120 06’ 12
149.98
C / 1260 21’ 36”
514.60
D / 550 10’ 33”
630.86
A

We also know that the azimuth of line AB is 2070 06’ 20”, and the coordinates of A are:

600.00 E, 785.58 N.

Task 1: Work in two person teams to do the following computations using only your calculators, we will use a spreadsheet later. Additional guidance for some of these computations is provided in the e spreadsheet development section that may be of value here.

1) Check for angular error and adjust as need, assuming all corners have equal weight.

2) compute azimuths of the lines, in the direction of A-B-C-D –A.

3) Compute departures (eastings) and latitudes (northings) for each segment and check for any errors.

4) Use step three results to compute Total closure error and relative closure error. Does this survey meet the 1/5000 criteria expected of typical small area projects?

5) Use the compass rule strategy to balance the latitudes and departures.

6) Compute the final coordinates of all points based on your adjusted lats and deps.

7) Compute the inversed lengths and azimuths based on the adjusted latitudes and departures.

Note: the inversed azimuth for line AB may not equal the expected value, i.e. the known azimuth. The analyst can rotate the entire adjusted traverse, using principles from coordinate geometry. You do not have to do these computations at this time.

Traverse Computation Spreadsheet:

Given: Interior angles of a closed traverse, distances between stations, azimuth of a beginning line of the traverse, and coordinates of the originating station are obtained from data or survey plats.

Determine: Balanced network information and area of traverse

Working in two-person teams and assuring that all members understand the analysis and program steps, each team is to develop an Excel spreadsheet traverse program. The program is to be constructed in general format that could analyze any traverse with up to 12 sides (and easily expandable to any sized traverse). In particular, the program must:

1)check for angular closure and adjust the interior angles. Adjustments must consider the relative confidence the surveyor has in each angle. For example, if Angle A has twice as much variance as Angle B, Angle A should be adjusted the most. In general, surveyors assign a larger “weight” to a measure they are most confident in. In that case, any adjustments for angular closure should be inversely proportional to the weights of the angles. In this way the largest adjustments are made to the angles with the greatest variation.

2)calculate the azimuths of all lines given the first line has a known azimuth. The programmer is to calculate azimuths going counter-clockwise around the traverse. In this case the forward azimuth of all lines can be found by adding the interior angle at a corner to the back azimuth of the line coming from the previous corner. All azimuths must be checked and adjusted to be sure they are between 0 and 360 degrees. With care the program could be written even more generally so that if you wish to go clockwise around the traverse you identify a direction variable which has a value of either +1 counter clockwise, or -1 for clockwise movement around the traverse. The variable could be named DIR (direction or any other preferred name). The new forward azimuth would be

Forward Azimuth = Back azimuth + (DIR)*angle

3)compute the unadjusted latitudes and departures for each line

4)calculate the error in latitudes and departures, as well as the overall closure error of a traverse assuming the traverse closes on itself)

5)determine the accuracy ratio, as expressed by a surveyor, using the results from Step 4 and the total perimeter of the traverse.

6) apply appropriate corrections to the latitudes and departures using the Compass Rule to find the adjusted latitudes and departures.

7)calculate the final coordinates

8)determine the inversed length for each side (the calculated length based on the coordinates calculated in step 7

9)determine the inversed azimuth of each side, proceeding counter-clockwise around the traverse (the inversed azimuth is the azimuth calculated from the coordinates found in step 7). A strategy for doing this with a single spreadsheet equation has not been discussed in class at this time, but will be reviewed with you when you reach this stage in your programming.

10)use the coordinates from Step 7, and the coordinate method of area computation to calculate the area within the traverse boundary. Answers, with appropriate number of significant digits in square feet (or meters) and acres (or hectares) are to be provided. English units are all that are required here, but you can easily add the converted units.

Special spreadsheet features :

Special features or functions that will be helpful include:

  • COUNT function to automatically check the number of sides entered in the traverse
  • “select and drag” method to copy formulas down columns; simultaneously copying a formula and a blank cell from adjoining cells in a column will be found to be helpful
  • “Named” Cells as references within equations instead of referring to a cell by Cell number
  • “Degrees” and “Radians” functions. Summation of angles and distribution of angular error is more efficient using radians rather than using the degrees, minutes and seconds format. In addition, the trigonometric functions in Excel require the angles to be specified in Radians. However, the surveying instruments to measure angles (theodlites and total stations) measure in degrees, minutes and seconds. It is therefore necessary to move back and forth between these formats.
  • IF statements: =if(TEST, S1, S2) Used to TEST a condition,(if R2>D2),is true then complete statement S1. If the statement is not true, then complete statement S2).
  • “Integer” or “Round” function to help round the accuracy ratio to 100s.
  • “Hide” columns or rows to improve the final format. The programmer may wish to use a column to hold intermediate computations, but does not wish to show these in the final output. The columns may be hidden using Format – Columns from the toolbar. The instructor hides a column that was used to compute the back azimuths.
  • use of extra columns to compute intermediate products and their sum, in order to avoid long expressions requiring a Sum of products.

Required Output

Each team will provide the completed model to the instructor. A sample problem with its solution accompanies this lab. Your work will be checked by changing numbers and by testing it on a different data set.

A brief report to

  • explain the highlights of the project with special attention to the procedure for calculating inversed distances and azimuths
  • report each team member’s contribution. If a team member was especially helpful, acknowledge that special contribution with a written pat-on-the-back.
  • assess the value of the laboratory for increasing your understanding of the computational steps.
  • assess the value of the laboratory for increasing your programming knowledge

1