by M.W.Taylor » Mon Jan 23, 2012 2:05 pm
Bob,
I was surprised at how few lines it took to do the task. Most of the code is for other tasks. The volume solver piece of code contains a quintuple nested "do loop". That's a first in my limited code programming experience. The volume solver is the last subroutine on the list. The first macro is "deleterows" which is a range noise filter utility. To use set xmin-xmax,ymin-yman,zmin-zmax and then run the macro to delete all rows of the XYZ cloud table that have a point outside that range. You can also draw a chart of the points....colume A vs. B for overhead view or plot B vs. C for side trunk view. Use mouse button to hover over outlier point you see outside trunk until arrows appear. Then drag the point away from the cloud from into a far out of rage "cage" area for deletion during the "Deleterows" subroutine. You can manually delete these outliers from the tables, but I find it much faster to drag the outlier points with mouse into an "out of range cage" area then manually find and delete.
The "addrows" subroutine extracts cookies from the trunk form at desired height intervals. To use this utility set the Zcookie text box to desired height and Thickness box should also have a value. I recommend using the preloaded default example for the given cloud set from Redwood Creek Giant. Press Control-C. The cookie will then be extracted and displayed on the lower chart.
Also remember this spreadsheet from my Excel97 and only allows 65,000 columns so I am only using about 1/3 of the available cloud points for the Redwood Creek Giant cloud set. If you have Excel2007 or later you can use 1 million rows so that increases the potential # of clouds points processed by 15x. If you have Excel2007 or later I would recommend extracting the other 130k XYZ cloud points from the PLY graphics file I attached. This will make the extracted cookies 3 times denser and hence volume calculation even better.
The "adjustxyz" macro recenters the X,Y,Z axis. There is no input box for this. You'll need to manually edit the Visual Basic macro and change the X,Y and Z values from 0 to desired off-set. Then run macro from tools menu. If cloud sets are imported with origin not at 0,0,0 you will need to re-center cloud set with "adjustxyz" or redefine origin in text boxes, otherwise calculation will be 100% wrong.
The "Volume" macro is the volume solver. In a nutshell here is how it works:
1)Extracts cookies from starting height point to ending point from the entire cloud set, but bound by range set in text boxes.
2) Rayangle is set and the Min and Max horizontal distance magnitude between origin and each XYZ point is calculated. Each Min and Mac point within the range is accepted and stored into radmin(10000) and radmax(10000). For default example the ray angle is set to 1 degree so this means the first scan ray finds all point furthest and closest to user define origin (default 0,0,0) for each 1 degrees from 0...all the way to 360. That would be 360 scan rays to find points. If scan ray is 10 degrees then that wouuld be 36 rays per cookie. The finer the scan ray, the finer the volume calculation.
3) once the rays are constructed between origin and inside points and outside points, the software then constructs all those ray projections into triangles with sides S1,S2 and S3 just like in your polygon area formula. Using the formula from your spreadsheet, I calculate all the little sub-triangle areas per cookie. The process can be seen in the output boxes. For a ray angle of 1, the polygon that forms the cookie would be partitioned into 360 small triangles. For ray angle 10 degree the polygon would be fitted with 36 triangles. The smallest ray angle this software version will accept is 1 degree.
Millions of processes are taking place in this code. It's slow but it works. I coded the routines so that the output goes into the cells for each working variable. This is for testing to determine how well the software is modeling the polygon. It works well for dense clouds (hundreds-thousands or more per cookie). If your cloud set is not dense enough the sub-areas get choppy and volume over-estimation may occur.
Michael