|
==============================================================================
TOPIC: A little spreadsheet
http://groups.google.com/group/entstrees/browse_thread/thread/ba020e39e9a2ba29?hl=en
==============================================================================
== 1 of 7 ==
Date: Mon, Feb 11 2008 7:43 am
From: dbhguru@comcast.net
ENTS,
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.
Bob
== 2 of 7 ==
Date: Mon, Feb 11 2008 8:09 am
From: "Will Blozan"
Bob,
Neat. I would have thought the measuring position to be farther
away. I plan
to play around with some ideas later this week.
Will
== 3 of 7 ==
Date: Mon, Feb 11 2008 9:35 am
From: dbhguru@comcast.net
Will,
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.
Bob
== 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
From: dbhguru@comcast.net
Will,
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.
Bob
Larry,
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.
Bob
projects/liveoak_project/liveoakspread/LiveOakLimbLengthsDownDown.xls
projects/liveoak_project/liveoakspread/LiveOakLimbLengthsDownUp.xls
projects/liveoak_project/liveoakspread/LiveOakLimbLengthsUpDown.xls
projects/liveoak_project/liveoakspread/LiveOakLimbLengthsUpUp.xls
projects/liveoak_project/liveoakspread/LiveOakLimbThicknessAngle.xls
projects/liveoak_project/liveoakspread/LiveOakLimbThicknessHorizontal.xls
projects/liveoak_project/liveoakspread/LiveOakModelsTrunkLimbs.xls
|