Live Oak Modeling Spreadsheets  

TOPIC: A little spreadsheet

== 1 of 7 ==
Date: Mon, Feb 11 2008 7:43 am


The huge girth live oaks that Larry Tuccei Jr. is measuring and that we hope to model set me to thinking about how far away Larry would need to be if using the principle of similar triangles to measure diameter (presumably at a point he can reach with a tape measure). The attached spreadsheet calculates the distance one needs to be back from the trunk to hold the error (unseen part of diamter) to a specified proportion of the entire diameter. The trunk is assumed to be round, so this approach is just to give one an idea of the relationship of diameter size to distance back from the trunk. For example, if one wants to hold the error down to 1% of the radius on a tree with a 2.5-foot radius, one needs to be back 15 feet. For a real behemoth with a 10-ft diameter, holding the error to 1.5% requires a distance of 24 feet. Reducing the error to 0.5% requires a distance of 45 feet.
The conclusion is that setting up the necessary similar triangles will not be a problem to get trunk diameter measurements provided we can devise a satisfactory method to form the similar triangles. Will has some excellent ideas on how to do that. We plan to cover the topic at the Black Mountain rendezvous.


== 2 of 7 ==
Date: Mon, Feb 11 2008 8:09 am
From: "Will Blozan"


Neat. I would have thought the measuring position to be farther away. I plan
to play around with some ideas later this week.


== 3 of 7 ==
Date: Mon, Feb 11 2008 9:35 am


I also thought the distance would be further. But, unless, I've made a mistake in the model, Larry would never have to be farther back than 45 feet to reduce the radial error to insignificance.

As we were discussing over the phone, I hope we can develop a measurement method with inexpensive measurement apparatus to allow folks who can't spring for the pricy toys a chance to do some serious modeling. The giants deserve more than the standard 3 measurements called for in the champion tree programs. I wonder if an L-square would work. You could sight down one leg and have the other leg at right angles and you could spot the point on it that is in alignment with the eye and the tangent point on the trunk. The sighting leg would be of known length - no errors there. And of course, the distance to the tangent point on that side of the trunk could be measured accurately. The error would be reduced to the sighting error on the other leg of the L-square. One eye would be used to do all the sighting, so one would not need to shift ones head to see the extremes of the trunk. The other challenge would mainly be to hold the L-square in a horizontal position, or in actuality, perpen
dicular to the axis of the trunk. Perhaps a small level taped or glued to the L-square could be placed so as to be visible to the eye. Just a crazy idea.


== 4 of 7 ==
Date: Mon, Feb 11 2008 9:48 am
From: "Will Blozan"

Wouldn't the scale be on the side- and not visible? Maybe I am not
envisioning how it is held.

== 5 of 7 ==
Date: Mon, Feb 11 2008 10:47 am


Yes, but the eye could spot where on the side of the L-square the line of sight touched and mark the top in that direction with a pencil. Holding the L-square steady might be too much, though.


      Please find the whole set of live oak modeling Excel workbooks attached. There are 7 workbooks althogether. LiveOakModelsTrunkLimbs.xls is the main workbook. I've already sent you a copy of it under an old name. It has the Ruskin Oak in it. The other 6 workbooks are limb length and thickness calculators. I enumerated them in a prior e-mail and have already sent a couple of them under slightly different names. However, the current naming scheme makes more sense.
      In each of the limb calculators, I've created enough space to enable you to do calculations on up to 600 limbs, 300 per spreadsheet (DataSheet1 and DataSheet2). These 6 subordinate workbooks are meant to feed the primary workbook, but the transfer of information will have to be manual for now. That is, you do the calculation of limb length or thickness in a subordinate spreadsheet and type the results into the primary sheet for the tree being modeled. If you calculate thickness, you can convert to circumference by multiplying thickness by pi. I should have included that as a column in the 2 thickness spreadsheets. Perhaps a fully automated system with plenty of bells and whistles will take shape in time.
      As I've previously noted, the main workbook allows you to volume model 10 trees. However, the main workbook can be expanded, or better yet, make a copy of it under a different name. This would allow for 10 additional oaks if you erase the data for the Ruskin oak in the copy. You could make multiple copies of the main workbook beforehand so that you'd have everything set up to model as many oaks as you desire, which gets me to a point.
       I don't know how much you know about Excel. I superficially protected all spreadsheets to prevent inadvertant overwriting of the cells that contain formulas, a common problem. However, if you work with Excel much, you can easily unprotect any of the spreadsheets that I am sending and expand the space as needed. To make the added space functional, you'd have to copy the formulas to all the blank cells that require formulas and at the end, you'd want to re-protect the worksheet(s). However, if you don't work with Excel in a design mode, I'll do all that stuff for you as needed. 
     In developing the volume, limb length, and limb thickness models, I've taken the absolute simplest approach, using the most basic assumptions about trunk and limb form. In some case, especially with limbs, we can clearly see the possibility of large errors. You can certainly break a long limb into segments. That would increase the workload, but is better than treaing a limb as straight when it clearly isn't. What we do have in the attachments is a quantum leap over anything else I've seen as an attempt to model forms like live oaks and will get us well into the ball park on the volume modeling. I've copied Will, Don, Lee, Dale, and Ed since any are all may have suggestions. 
     As a final comment, as we all know, most of volume calculations are done on trees with straight, gradually tapering trunks, i.e. timber trees. The dominance of trunk modeling for trees of high economic value has left a big whole for some of our most charismatic trees. Trying to apply forestry modeling to the live oak form is a big mistake. So we have to go it alone, as we should and we have you to thank for opening up the possibility of ENTS getting lots of good data on live oaks - a tree form that includes close to 50% of its volume in its limbs.