Regression Analysis.

[therock003]

I want to make kind of a 3d regression and from a set of 3d points (XYZ - Coords) i want to calculate the interpolated plane,that fits best between the set of points.

So far I've only thought of making 3 linear regressions,one on each plane (XY,XZ,YZ) and combining them to create the plane.I don't know if there's an easier,more accurate and better way to do this.

Also what's the precision of the regression equation.it seems like it provides 4 decimals for the a coefficient and 2 for the b constant.Is that good,can i make it provide more accurate equation for the regression it draws?I mean does it use this exact equation or does it display an approximation of the regression it drew?

 

[therock003]

Yep i know,that is the one i get as well.

Sorry i didnt mean to say edges, imeant to vertices.I limit the plane as part of a plane so i can draw it and then project these given points to it,so they can have 2d coordinates.

Let me explain.These are points surveyed on the filed.

X,Y represent their location on the xy plane and Z is therir elevation.Everythin is in meters (m).

XY should be considered as their location,and if that's the case,the plane sould be regressed somewhere between the z elevations.And that should be somewhere around their values,which they are near 499.

 

[pgc01]

XY should be considered as their location,and if that's the case,the plane sould be regressed somewhere between the z elevations.And that should be somewhere around their values,which they are near 499.

That's exactly what happened. For the (x,y) locations you have now the regressed values on the interpolation plane that are the values in column D in my example. These regressed values are all around 499, as expected.

I'm afraid I can't help you further. I don't understand the method you want to use to get the 2D coordinates.

 

[therock003]

That's exactly what happened. For the (x,y) locations you have now the regressed values on the interpolation plane that are the values in column D in my example. These regressed values are all around 499, as expected.

Using the regresed equation i dont get values near 499?

X Y Output Z (not even near to 499)
504,0678459 504,7305128 -508,729723
504,0678459 529,9595136 -508,9021821

514,8537169 504,7305128 -508,8232729
514,8537169 529,9595136 -508,995732

 

[therock003]

Oh wait sorry i messed with the equation.So after i finally get the correct interpolated z of the points that will mean that every xy point to be on the smae plane?

 

[pgc01]

Good idea to post the points, this way we can check thee result.

I see you chose the 4 points (X min/max, Y min/max)

To calculate the regressed Z value on the interpolated plan I used:

1st point: Z = 0.0068358 * 504.0678459 - 0.0086734*504.7305128 + 500.907540006
2nd point: Z = 0.0068358 * 504.0678459 - 0.0086734*529.9595136 + 500.907540006
3rd point: Z = 0.0068358 * 514.8537169 - 0.0086734*504.7305128 + 500.907540006
4th point: Z = 0.0068358 * 514.8537169 - 0.0086734*529.9595136 + 500.907540006

And got the values:

1st point: Z = 499.9755065
2nd point: Z = 499.75668596
3rd point: Z = 500.0492361
4th point: Z = 499.8304155

Which seem OK.

Please confirm

 

[pgc01]

Oh! we cossed the posts.

I'm glad you got the correct values now.

So after i finally get the correct interpolated z of the points that will mean that every xy point to be on the smae plane?

No, it means that all the X,Y,Zint (Z interpolated) 3Dpoints are on the same 3D plane.


The for points (X1,Y1,Z1), ... (X4,Y4,Z4) are now the 4 vertices of the projection of the rectangle in the XY plan with vertices in (X1,Y1)...(X4,Y4) onto the interpolated plan.

What you have now is a problem of descriptive geometry.

 

[therock003]

Yep my friends these are the values.

504,0678459 504,7305128 499,97550654
504,0678459 529,9595136 499,75668595

514,8537169 504,7305128 500,04923605
514,8537169 529,9595136 499,83041546


So that finally means that everything is coplanar now right?

Ok the final piece of my puzzle is to project every 3d value on this new created plane,so i can get the new corrected x,y values.

Any idea on how i can project each point on this plane?



Explanation.The whole point of this task was that since the oints i surveyed out on the field where not coplanra i needed some way to project them on a plane so i can get their x,y values like they were actually supposed to be located on a plane surface.

Thanks for this method you sugggested and for all of your support my friend.

 

[pgc01]

So that finally means that everything is coplanar now right?

Yes, all the points from my last table with the X, Y in columns A:B and the interpolated Z values calculated in column D are coplanar. Also the four vertices you calculated are coplanar and define a quadrilateral in the interpolated plan that has inside all the other points.

Now your next problem is to get the XY values on that plane. I can't help you on that, at least not today as I'm leaving. I guess however that you can use the 2 slopes you got from the regression, as they are also the tangent of the angles between the interpolated plan and the XZ axis and the YZ axis. Anyway I hope you get it soon.

Cheers.

 

[therock003]

Is there any information as to what the precision of this is?Like when you make a regression on a chart it displays the eqation and the r-coefficient.

How can i calculate a coefficient for this interpolation?So i will know the certainty on a % percentage.

 

[pgc01]

Hi

Please check the help on LINEST().

If you set the fourth parameter to True, Linest() returns you a number of statistics in 5 rows * (1+number of independent variables) columns.The R2 value is returned on the cell (3,1) (see help).

You can either return all the values by entering the function as an array function or use Index() to get a specific value.

In this case, to get just the value of R2 (with the layout of the table I posted), you can use the formula:

=INDEX(LINEST(C2:C15,A2:B15,,TRUE),3,1)

I got the value 0.96165582 for the example posted, which seems very good.