adjustment of survey measurements with solver add-in...

Adjustment of Survey Measurements with Solver Add-in By: zainul_ulum

A. BACKGROUND

Included with standard Excel is an add-in, called Solver, which is tool for optimization and solving equations. The

Standard Solver was developed by Frontline System; Inc. Frontline System also has developed several more

powerful solver packages for Excel. One of these, called Premium Solver which is not included in standard excel.

To check or to activate Standard Solver add-In on Excel 2007, follow these steps:

1. Click the office button, then click [Excel Options].

2. On the [Excel Options] dialog box, Select [Add-Ins] Group on the Left Panel

3. , on the Manage selection box, choose, [Excel add-

Ins], then click [Go].

4. , Check [Solver add-In], then click [OK]

Once the Solver Add-In is activated or installed, it is ready for optimization and solving equation.

In this article, Solver Add-in is implemented for optimizing or adjusting on survey measurements as an alternative

method instead of least square adjustment using matrices.

Basically, least square adjustment is a method of how to analyze and adjust survey measurements in order to

obtain minimum corrections on survey measurements. Since the corrections could result in minus (negative) or

plus (positive), they have to be squared to obtain positive value. It can be concluded that the good observation will

have a minimum sum of squared corrections. If V1, V2, V3, V4,….,Vn are identified as correction for observation

number 1, 2, 3, 4, …,n, then the criterion for least square is as follow:

Φ = 𝑉12 + 𝑉22 + 𝑉32 + 𝑉42 + ⋯ . +𝑉𝑛2 == 𝑚𝑖𝑛𝑖𝑚𝑢𝑚

For uncorrelated observations of unequal precision, the criterion is

Φ = 𝑊1𝑉12 + 𝑊2𝑉22 + 𝑊3𝑉32 + 𝑊4𝑉42 + ⋯ . +𝑊𝑛𝑉𝑛2 == 𝑚𝑖𝑛𝑖𝑚𝑢𝑚

In which W1, W2, W3, W4,….Wn are the weight of the corresponding observations.

Three methods of least square have been applied on survey measurement:

1. Adjustment of observations only, also known as condition method

2. Adjustment of indirect observations, also known as parameter method

3. Adjustment of combined parameter and observations

All methods require advanced knowledge on matrices operation such as inverse, transpose & derivative and

calculus such as linearization and derivatives.

The condition method or adjustment of observations only (method #1) is implemented in Solver Model. With this

Solver Add-In, there is no requirement of knowledge for matrices, inverse matrices, derivative and linearization.

Steps for least square adjustment with solver add-in

Basic steps to perform adjustment with Solver Add-In according to least square method #1 are:

1. Defining number of observations and its redundancy

2. Defining condition equations & constraints

3. Set target parameter to be minimums.

A.1.1 DEFINING NUMBER OF OBSERVATIONS AND ITS REDUNDANCY

Redundancy (r) is defined as excess measurement compared to required minimum number of measurements. For

example measurement of inner angle of triangles of S1, S2 and S3 will require 2 (two) minimum measurements.

The third measurement is not mathematically necessary since it can be derived from equation 180-(S1+S2). If n

identified as number of observations, u number of minimum observations, the equation of redundancy is:

𝑟 = 𝑛 − 𝑢

A.1.2 DEFINING CONDITIONAL EQUATIONS & CONSTRAINTS

Condition equations, also known as independent equations, are one or more mathematical models consisting of

observations to meet a real condition or mathematically proved as TRUE. Example:

• The sum of three inner angles of triangle is 180

• The sum of a looped difference heights is 0

After conditions equations are modeled, the constraints can be identified. The constraints from example above

are 180 and 0

Number of equation equals to number of redundancy.

A.1.3 SET TARGET PARAMETER

Target parameter is cell on excel that is set to be minimum. According to least square method, the cell contains

formula of sum of squared corrections.

A.2 SOLVER MODEL

Solver Model represents least square equations which consist of observations, corrections, weight and adjusted

observations. The sum of corrections which is set to be minimum are shown in Solver Model

Solver model of measurements on three inner angles on triangle S1, S2 and S3 with no weight:

Number of measurements (n)=3

Number of minimum measurements (u)=2

Redundancy (r=n-u)=1

S1+V1+S2+V2+S3+V3=180

Number of measurements (n) 3

Number of minimum measurements (u) 2

Redundancy (r=n-u) 1

Conditions equations S1+V1+S2+V2+S3+V3=180

Constraints [E5]=180

Target to minimum [D5]

By Changing cells [C2:C4]

Solver add-in will calculate values on range [C2:C4] iteratively until minimum value on cell [D5] is obtained.

Setting on solver parameters:

Solver parameters can be set by activating the solver program with the following steps:

1. On Excel 2007, select [Data] ribbon

2. In the group of [Analysis], click [Solver]

B. MORE SAMPLES ON NETWORK ADJUSTMENT

B.1 ADJUSTMENT ON LEVELING NETWORK

1. Number of Observations and Redundancy

Number of Observations (n) : 6

Minimum Required Observations (u) : 3

Minimum 3 (three) observations are required to

calculation elevations on B, C and D (number of red

circles)

Redundancy (r=n-u) : 3

Thus, three (3) conditional equations are required to adjust six (6) observations.

2. Solver Model, Conditional Equations and Constraint

CELL FORMULA REMARK

C37 =D21 Copy formula on C37 down to C42

D37 =1/E21 Copy formula on D37 down to D42

F37 =E37^2*D37 Copy formula on F37 down to F42

G37 =C37+E37 Copy formula on G37 down to G42

F44 =SUM(F37:F42) Target Parameter on Solver

Conditional equations can be written clearer by naming cells (called range name) rather than using cell reference.

For example, formula/equation in cell F48 is more comprehensive expressed by =La_1-La_3+La_2 instead of

=G37-G39+G38. Individual cell on G37:G42 can be named according to list on H37:H42 with the following:

• Select / block range G37:H42

• Click [Formula] ribbon

• In the group [Defined Names], click [Create from Selection]

• On the options [Create name from values], check only at [Right column]

• Click [OK]

After naming cells, the following formula can be applied:

CELL FORMULA REMARK

F48 =La_1-La_3+La_2

F49 =La_2+La_4-La_5

F50 =La_3+La_6-La_5

G48:G50 Constraints

3. Solver Parameters

• Select [Data] ribbon

• In the group of [Analysis], click [Solver]

• Set parameters as shown below

• Click [Solve]

• “Solver found a solution” means all constraints and conditions are correct.

• click [OK]

Solver model after optimization:

4. Statistical Testing

After optimization, sample variance (s2) can be obtained from target cell (F44) with the equation s2 = sum(V2W)/(n-

u) = F44/3. Statically, the value of sample variance (s2) can be tested with Chi-Square distribution using 95%

Confidence Interval or 5% significant level.

𝜒2𝑐𝑎𝑙𝑐𝑢𝑙𝑎𝑡𝑒𝑑 = (𝑛−𝑢)𝑠2

𝜎2= 𝑠𝑢𝑚(𝑉2𝑊)

𝜎2 if population variance (σ2) =1, 𝜒2𝑐𝑎𝑙𝑐𝑢𝑙𝑎𝑡𝑒𝑑 = 𝑠𝑢𝑚(𝑉2𝑊)

CELL FORMULA NOTE

F53 =F44

F54 =CHIINV(0.05/2, E32)

F55 =IF(F53 < F54, "Pass at the 5% significant level",

"Fail at the 5% significant level")

B.2 ADJUSTMENT ON TRIANGULATION




Minimum 4 (four) observations are required to

calculation coordinates on B and C (number of yellow

squares)


2. Solver Model, Conditional Equations and Constraints

CELL FORMULA REMARK

C40 =F21

D40 =1/(G21^2) Copy formula on C40:D40 down to C47:D47

F40 =E40^2*D40

G40 =C40+(E40/3600) Copy formula on F40:G40 down to F47:G47

I40 =TRUNC(G40)

J40 =TRUNC((G40-I40)*60)

K40 =3600*(G40-I40-J40/60) Copy formula on I40:K40 down to I47:K47

*Formula at H54:H57 contains range name of every cells on G40:G47.

H54 =La_1+La_2+La_3+La_8

H55 =La_4+La_5+La_6+La_7

H56 =La_1+La_6+La_7+La_8

H57 =La_1+La_2+La_3+La_4+La_5+La_6+La_7+La_8

J54 =(I54-H54)*3600 Copy formula on J54 down to J57


Optimization result:


B.3 ADJUSTMENT ON TRILATERATION






CELL FORMULA REMARK

C39 =C20

D39 =D20

E39 =1/(D39^2) Copy formula on C39:E39 down to C48:E48

G39 =F39^2*E39

H39 =C39+F39 Copy formula on G39:H39 down to G48:H48

E54 =VLOOKUP(B54,$B$39:$H$48,7,0)

F54 =VLOOKUP(C54,$B$39:$H$48,7,0)

G54 =VLOOKUP(D54,$B$39:$H$48,7,0)

H54 =F54^2+G54^2-E54^2

I54 =2*F54*G54

J54 =H54/I54

K54 =DEGREES(ACOS(J54)) Copy formula on E54:K54 down to E58:K58

H64 =s_1+s_2+s_3+s_4+s_5

J64 =(I64-H64)*3600 Constraint set to 0 (zero)




B.4 ADJUSTMENT ON TRAVERSE






Traverse surveying observes/collects two types of observation; they are horizontal angle and distance observations

thereby traverse surveying obtains 2 (two) metric units: “decimal degree” for horizontal angle and “meter” for

distance. Considering that optimization process (process to minimize corrections) involves two different units, the

ρ number is required to convert seconds to radians on standard deviation of horizontal angles. The ρ calculated

from =2*PI()/(360*60*60).

As depicted on sample of traverse net, the net can be divided into 2 (two) loops named as Loop#1 and Loop#2.

Loop#1 follows on stations BM.1, BM.2 POL.1, POL.2, BM.5 and BM.6 while for Loop#2 follows BM.1, BM.2, POL.3,

POL.4, POL.5, BM.5 and BM.6.

Solver add-in will optimize or adjust Loop#1 and Loop#2 in one process. Coordinates on Loop#1 are not derived

from Loop#2 or Loop#2 is derived conversely from Loop#1.

CELL FORMULA REMARK

L40 =IF(I40="A",K40*$C$60,K40) Standard deviation is multiplied by $C$60 (ρ) for Angle

Observation

M40 =1/(L40^2) Copy formula on L40:M40 down to L54:M57

O40 =N40^2*M40

P40 =IF(I40="A",N40/$C$60,N40) Correction is divided by $C$60 (ρ) for angle

observations. Unit for angle correction is second and for

distance is meter

Q40 =J40+IF(I40="A",P40/3600,P40) Adjusted observations.. Unit for adjusted observations

is degree and for distance is meter.

R40 =IF(I40="A",

TEXT(Q40/24,"[h]° mm\' ss.00\"""),Q40)

Format angle display as dd° mm' ss.00"

Copy formula on O40:R40 down to O57:R57

O58 =SUM(O40:O57) Set target to minimum

CELL FORMULA REMARK

Q70 =DEGREES(ATAN2((P71-P69),(O71-

O69)))+IF(ATAN2((P71-P69),(O71-O69))<0,360)

Calculate azimuth from BM.1 to BM.2

I70 =Q70

I72 =I70+G71-180+IF(I70+G71-180<0,360,IF(I70+G71-

180>360,-360))

Calculate azimuth base on previous azimuth

and measured horizontal angle

Copy formula I72 to I74, I76 and I78

J72 =$H72*SIN(RADIANS($I72))

K72 =$H72*COS(RADIANS($I72)) Copy formula J72:K72 to J74:K74 and

J76:K76

L71 =O71

M71 =P71

L73 =L71+J72

M73 =M71+K72 Copy formula L73:M73 to L75:M75 and

L77:M77

The same method is applied on Loop#1


adjustment of survey measurements with solver add-in...

Documents