• Author / Uploaded
• Ismael

Citation preview

Ultimate Pit Limit Design Using ~icrosoft' Excel Spreadsheet David ~ r e w and ' Ernest ~aafi'

ABSTRACT Microsoft Excel based ultimate pit limit (UPL) optimiser has been developed to determine the greatest economic value of an orebody. UPL manipulates a given geological block model to define an ultimate pit limit using either the moving cone, method or Korobov algorithm o r Lerchs-Grossman technique. UPL is designed to be an adjunct to M S Excel; the cells in Excel are excellent analogue to the blocks in a geological model, providing X-Y dimensions in an easily viewable and editable state. The use of individual worksheets within an Excel workbook provides the necessary depth to a 3-D block model. INTRODUCTION The determination of the ultimate pit limit is vital to all open pit mining operations. The developments of computer programs to determine pit limits were some of the very first application of computers within the mining industry, and have remained at the forefront of mine design since its inception. There are various packages including WHITTLE 4-X and Datamine pit optimizer, which are used to generate these ultimate pit limits, using a variety of algorithms. UPL was primarily designed as a teaching tool to meet the needs of undergraduate mining engineering students. UPL is freely available and easy to use, and can perform both large and small pit optimisations with accuracy (Drew, 2000). UPL makes use of the moving cone method (Wright, 1990). the Korobov algorithm (Dowd and Onur, 1993), and the Lerchs-Grossman dynamic programming technique (Lerchs and Grossman, 1965). Undergraduate students at University of Wollongong use UPL to evaluate the strengths and weaknesses of each of the three algorithms, as well as learn about ultimate pit limit design concepts. Instead of being a stand-alone program, UPL runs as M S Excel add-in by being loaded temporarily Into Excel with the functionalities of Excel (Figure 1). UPL uses M S Excel as the basis for the following reasons: Ideal data format Market 'standard' Powerful file and data UO Visual Basic capabilities DESIGN OF UPL The cells in a spreadsheet are an excellent analogue to the blocks in a geological block model, providing X-Y dimensions in an easily viewable and editable state. The use of individual worksheets within Excel workbook prbvides the necessary depth to a three-dimensional block model. In addition, the size of Excel sheets is sufficient for a reasonably size block model to be analysed.

I

Hoslangs Resource Management, Level 1, I Swan Road., Taringa, Qld 4068, Australia

Faculty of Engineering, University of Wollongong, Wollongong, NSW 2522. Australia

'

'

The program U P L requires a geological block model to generate an ultimate pit. In a typical regular fixed block model the orebody is divided into mining blocks of equal size. Each of the blocks is assigned a grade determined from sample grades. Once the block grade data have been loaded into UPL, the user can edit each block data and make any changes (Figure 2). Since the geological model is in the form of selective mining blocks, UPL shows the data in a similar "cell-block" format. MS Excel cells are typically in a rectangular aspect ratio that is approximately 2 !h : 1. Unless the block sizes are in these same ratios, they are displayed incorrectly. The following subroutine, ResizeCells(), was used to resize the cells in Excel to the ratio that suits the block sizes. Sub ResizeCells() ' This subroutine changes the size of the cells. IfgdXDimBlock = 0 Then gdXDimBlock = I IfgdYDirnBlock = 0 Then gdYDimBlock = I IfgdZDimBlock = 0 Then gdZDimBlock = I For lSheet = I To glSheets Worksheets(lSheet).Activate Rows("1:" & glRows).Column Width = 4.5 * (gdXDimBlock/gdYDimBlock) Rows("1:" & glRows).RowHeight = 22.5 * (gdYDimBlock/ gdXDimBlock) Rows("I: & glRows).HorizontalAlignment = xlCenter Rows("I:" & glRows).VerticalAlignment = xlCenter Next lSheet '

'

"

OPERATION OF UPL UPL is divided up into four sections. These are: Pit Limit Generators Data Transformation Sensitivity Analysis Options and User functions The pit limit generators are the moving cone, Korobov and LG algorithms; moving cone algorithm was selected for its speed and simplicity, and for its readily identifiable errors. 'The Korobov algorithm was chosen to demonstrate a 'modified' form of the moving cone method, and as an example of an "optimal" heuristic algorithm. The LG method is a rigorous algorithm, which always guarantees an optimal pit.

Figure 2 - Typical block model worksheet DATA ENTRY MODULE Because UPL is part of Excel, the data input phase is handled via Excel. Excel's ability to read and import files is very extensive, and it can translate a wide variety of formats into its own. This makes it more likely that most data formats can be used with UPL. All undergraduate mining students are familiar with Excel and this greatly reduces the time needed to learn UPL. Excel's vast data manipulation abilities can be used to either create a new block model, or alter a previously created model, especially for students.

U P L does not perform calculations within the spreadsheet itself, or use in-cell formulas. It extracts data from Excel's interface into its own program memory and'performs all calculations within memory. In reading any cell from a spreadsheet, the following statement is used to read the cell value directly: ExanzpleVariable = Worksheets("Sheet1").Cells(3.4).Value The above statement reads the value of the cell in row 3, column 4 from the worksheet called Sheet 1 into the variable called ExampleVariable: UPL needs to perform this task many times in reading an entire block model. U P L performsthis process in two basic steps. Firstly, the sizeof the block model is determined. Using an algorithm suggested by Walkenbach (1999) the locations of both the last row and last column used in a worksheet can be determined. 'This algorithm is applied over all worksheets in the workbook to determine maximum ranges for all dimensions. Secondly, a data array has to be initialized. The data array is a four-dimensional array in which the block value, and other cell data are stored. The maximum size of the data array is based on the maximum size of the spreadsheet. This concept allows for efficient memory usage, and avoids having a data array that is either too large (slowing down calculations) or too small. The first three elements of the data array correspond to the block's i-j-k location within the block model. The fourth is used to store intermediate results, program's flag and other cell descriptors. Table 1 summarises the structure of the fourth element of the data array. The null flag is to counter a problem that occurs when using data arrays with Excel. By default, when a data array is created, all values within an array are filled with zero. A null cell in Excel is a cell that has no data, and matches only "". In a block model, this would correspond to lack of data. When this null cell is read into a data array, the original zero value is retained which is not a desired result. When the determination of the ultimate pit limit is performed, a block that has a zero value will be mined if circumstances permit.

Number

I Type

Table 1- The Fourth Element of Data Array

I Data Stored I Example

Meaning

I

Number

Block Value

a(1,2,3,1)=56

Block (1.2.3)'s value is 56

2

Boolean

Is Null?.

a( 1,2,3,2)=0

Block was not originally null

3

Boolean

Mineable?

a(1,2,3,3)= 1

Block forms part of ultimate pit

1

Number

I

Intermediate Result

I

a(1,2,3,4)= 129

1

Intermediate Result for block (1.2.3) is 129

However, because a null cell has no data, it could have any possible range of values, and would mostly likely be negative, thus causing an erroneous pit limit model should the block be selected. To counter this problem, if a null cell is encountered, the data array location for that block is,assigned a highly negative dollar value. This null cell is noted in the second of the fourth data array locations, so that the negative value can be removed, and the null value replaced, when the results are written back into the spreadsheet. Once the data array is intialised, and the workbook size determined, the cells can be read into the data array. This is done using three nested for loops: For z = I to M d h e e t s For \I = I to MaxRows For x = I to MaxColumns DataArray(x,y,z,I ) = Worksheets(z).Cells(y,x).Value Next x Next Next z >J

.By attaching UPL to Excel, it creates an additional menu in the menu bar. From UPL's menu the user can select the algorithm of pit optimiser, or data entry forms. In Visual Basic, reading and storing these values is achieved by the statement: Example Variable= ExampleForm. TextBoxOne.Value Forms were created using Visual Basic forms; when data entry form is initialised, entry boxes are filled in using the values already stored in the program's memory. These are either the standard values read during startup, or the user's values read from a file. Any of the values in the form may be changed be the user. When the form is closed, UPL reads these values and stores them for immediate or later use in other subroutines.

TRASLATION OF DATA Not all data that is can be read into Excel are in a form of UPL's i-j-k data input format Since, some block modeling programs output data in an x-y-z grade format a translating algorithms were required to convert the data formats into a form usable by UPL. This is done through forms shown in Figure 3.

Figure 3 - Data translation forms In Figure 3, the user selects the format the input data, and enters economic or other data as required by UPL. Once this data is entered, the user can initiate the data transformation function, and the program will output a block model in the format that the pit-generating algorithm requires. In calculating the block value, UPL makes use of the following formula:

Block Value = (Amount of Metal * Recovery Amount of Rock *Rock Mining Cost

* Metal

Price - Amount of ore

* Ore mining

cost) -

UPL OUTPUT Program output is normally achieved by displaying information to the user interface, and by writing to a file. Because UPL uses Excel's capabilities, its output is done differently. In all the writing operations in Excel, a new spreadsheet is created to avoid overwriting other data that the user may wish to keep. The output data is then written to a new worksheet by a direct reversal of the reading procedure, i.e.

Worksheets(z).Cells(y,x).Value = DataArray(x,y,z,l) If the pit limit has been calculated in three dimensions, then UPL writes not only cross-sections of the model, but also plan views as well. UPL provides an additional series of orthogonal cross-sections. This allows to user to view the output in all major directions. In order to make the ultimate pit distinctive, UPL highlights the pit by changing the colour of the cells that define the ultimate pit (Figure 4). To provide additional information to the user, UPL also displays the final pit parameters such as the ultimate pi't value, number of blocks within the pit, and the computation time. Since Excel has extremely weak three-dimensional graphing capabilities, the user may export the pit data into other application programs for further analysis. For these reasons, UPL writes the final pit shape to a spreadsheet, which can then be saved using one of Excel's numerous file output formats. A typical UPL pit exported to Surfer and Vulcan are shown in Figure 5.

In the positive moving cone and Korobov algorithms, the cones are used to determine the mining area for each positively valued block, and thence the value of that cone. In order to determine which blocks are within a given cone, the equation of a circle is used to determine which of the blocks on the current level fit within this cone. The area of the circle is the projection of the cone onto that level, allowing the radius to be determined for given block sizes and pit slope angles. Certain blocks (these blocks are selected by a quick heuristic algorithm) on the level are checked against the inequality x2 + y 2 5 r2.If it fits within this inequality, then the block lies within the cone. If the equation for an ellipsoid is used, then two slope angles may be used. Later versions of UPL use this equation for improved usefulness of the program.

(a) UPL-generated pit displayed by Surfer

(b) UPL-generated pit displayed by Vulcan

Figure 5 - UPL generated pit displayed by Surfer and Vulcan Software

CONCLUDING REMARKS UPL was designed to implement a wide range of pit limit methods. It uses MS Excel as the basis for its data handling. UPL outputs results in.a clear and distinct format, without altering original data. UPL also contains useful routines for translating and manipulating data from geological modeling programs. UPL has been validated to work accurately, and provides consistent results for all block models tested. The program UPL has an option to perform sensitivity analysis. For each of the selected parameters, the user inputs high and low values, and an incremental value. UPL steps through these values, creating economic block models using the grade block model and input values, and generate the ultimate pit limit for each of the input parameters.

In addit~onto a beginner tutorial, UPL has two user modes, beginner and advanced. When in the beginner mode, UPL displays help boxes at regular intervals. These help boxes make suggestions as to the kind of data required and the subsequent steps. The current version of UPL and its comprehensive web-based manual can be downloaded freely from the site / I ~ ~ ? : / / M ~ \ V M ~ . ~ ~ ~ N ~ W I J S ~ . ( ~ O I I I . I J I I / ( ~ ~ Z I ~ ~ ( UPL is limited to MS Excel's maximum working area size of 256 columns by 65,536 rows, by over 1000 sheets. A cube-shaped block model of 256*256*256 (Excel's smallest dimension) contains over 16 million blocks.

REFERENCES Dowd, P A and Onur, A H 1993. Open-Pit Optimisation - Part 1: Optimal Open-Pit Design. Transactions of The Institution of Mining and Metallurgy (Section A: Mining Industry), The Institution of Mining and Metallurgy, London, vo1.102: pp. A95-A104. Drew, D. 2000. Ultimate Pit Limit Using MS Excel. BE(Hon) Thesis, University of Wollongong, Wollongong, Australia, 164pp. Lerchs, H and Grossman, I. F. 1965. Optimum Design of Open-pit Mines. Transactions of the Canadian Institute of Mining and Metallurgy, Vol. LXVII, pp. 17-24 Walkenbach, J 1999. Microsoft Excel 2000 Formulas, M&T Books, pp 615-616. Whittle, J. and Whittle D. 1999. Optimization in Mine Design Manual, pp 61-62 Wright, E. A. 1990. Open Pit Mine Design Models : An Introduction with FORTRAN/77 Programs. Trans Tech Publications, Germany, 187pp.