UPL Paper [PDF]

Citation preview

Implementation of Ultimate Pit Limit Algorithms with Microsoft Excel D Drew1 and E Baafi2 ABSTRACT



The determination of the ultimate pit limit is of vital importance to all open pit mining endeavours. The calculation of the ultimate pit limit is essentially a determination of the greatest economic value of an orebody. There are several commercial programs used by the industry to determine ultimate pit limits; these include the popular Whittle Four-X. In this paper the program UPL is discussed; UPL is designed primarily for undergraduate students as a learning tool. The program is designed to be an adjunct to Microsoft Excel. It has the advantages of its simplicity and low cost, whilst still retaining the ability to calculate the ultimate pit limit with good accuracy for large-sized geological block models. INTRODUCTION There are several commercially available ultimate pit design programs on the market. These include Whittle Four-X and Datamine pit optimiser. These packages are designed to be all-in-one solutions to solve ultimate pit design and production scheduling related problems. Some of these packages have an enormously steep learning curve, especially for undergraduate students. A Microsoft Excel based ultimate pit limit (UPL) program has been primarily developed to meet the needs of undergraduate mining students; UPL makes use of the three main algorithms commonly used to determine the ultimate pit limit. UPL manipulates a given geological block model to define an ultimate pit limit using either the moving cone method (Wright, 1999) or the Korobov algorithm or the popular Lerchs-Grossman (LG) technique (Lerchs and Grossman, 1965). Students can use UPL to evaluate the strengths and weaknesses of each of the three algorithms. It was decided to use Microsoft Excel as the basis of UPL for the following reasons:



• • • •



ideal data format, market ‘standard’, powerful file and data I/O, and visual basic capabilities.



The cells in Excel spreadsheet are 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 an Excel workbook provides the necessary depth to a 3D block model. In addition, the size of Excel sheets is sufficient for a reasonably large block model to be analysed.



DESIGN OF UPL



assigned a grade usually determined from sample grades. Once the block grade data have been loaded into the program, the user can edit each block data and make any changes. Since the geological model is in the form of selective mining blocks, the program UPL shows the data in a similar format. Excel cells are typically in a rectangular aspect ratio that is approximately 2.5:1. Unless the block sizes are in these same ratios, they will look incorrect. Thus, a subroutine was developed that could resize the cells in Excel to the ratio that suits the block sizes. Output from UPL must readily identify the ultimate pit. UPL allows the user to view the optimal pit from different views, and allows the user to view each section in the model independently. The moving cone algorithm was selected for its speed and simplicity, and for its readily identifiable errors. The Korobov algorithm was chosen to demonstrate a ‘corrected’ 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. Because many of mine planning programs which create geological block models do not output into a format that can be imported directly to Excel, UPL also contains functions which can translate raw output data from such programs into a data format compatible with UPL. UPL also has many options allowing the translation of many different data types. UPL can export source or result data, so that other application programs, such as Surfer can read. In order to validate UPL’s operation, UPL has been tested against many two-dimensional and three-dimensional pit models, with up to 180 thousand blocks, and compared with other pit-generating programs, in order to ensure that its results are accurate, and that it always generates the ultimate pit, within the limitations of the model.



The program UPL uses a geological block model to generate an ultimate pit. In a typical fixed block model the orebody is divided into mining blocks of equal size. Each of the blocks is



1.



Faculty of Engineering, University of Wollongong, Wollongong NSW 2522. E-mail: [email protected]



2.



MAusIMM, Faculty of Engineering, University of Wollongong, Wollongong NSW 2522. E-mail: [email protected]



AusIMM Youth Congress



OPERATION OF UPL Before UPL can be loaded properly, Excel requests the user to enable macros; the user must enable macros. Once the program is loaded UPL displays its initial option window (Figure 1). The load in data option opens Excel open dialog, which allows the user to load an input data in any Excel-readable format. The start new workbook option opens an empty workbook; the close window option closes the option window. Once UPL is loaded, it



Brisbane, Qld, 2 - 6 May 2001



1



D DREW and E BAAFI



adds an additional selection function to Excel menu bar as add-in (Figure 2). It is from this menu that UPL’s various functionalities can be selected and executed. The main menu in UPL has the following options:



from other data formats, into a format compatible with UPL. The data translation submenu has two primary options (Figure 3).



• The first three options, Lerchs-Grossman, Moving Cone and Korobov are the three ultimate pit limit determination algorithms. Selecting each of these will run that algorithm on the orebody model which is displayed in the main Excel window.



• Data Translation option runs the transformational subroutine on the data that is currently loaded in the primary window. This option enables the user to translate the data



FIG 1 - UPL start option window.



FIG 3 - UPL data translation submenu.



FIG 2 - UPL’s main menu as Excel add-in.



2



Brisbane, Qld, 2 - 6 May 2001



AusIMM Youth Congress



IMPLEMENTATION OF ULTIMATE PIT LIMIT ALGORITHMS WITH MICROSOFT EXCEL



The first option asks the user to select between the ‘economic value format’ and the ‘grade value format’ for the geological block data. A file with grade value format may be converted into the economic value format using this option. To further calculate the block economic value of each block, the ‘economic data’ tab in the translation options is used (Figure 4).



FIG 5 - Economic data submenu.



FIG 4 - Economic data submenu.



The primary limitation of the transform algorithm is the input data. Excel can only handle 65 536 rows in any one spreadsheet. This implies when a text file in opened, Excel cannot open it if it contains more than 65 536 rows. Because each rows represents a block in the block model, this would place the ultimate number of blocks which can be input at any one time to 65 536. The next option on the ‘block data’ tab deals with the format of a block location. Instead of using i-j-k values to specify the index of a block, the model may use coordinates based on the blocks’ relative x-y-z locations. In the case of x-y-z location of a block additional information shown in Figure 5 is required:



• transform option provides a list of data type options; • resize cells is an option used to display different block sizes in UPL. Instead of using the Excel’s standard cell orientation, resize cells changes the size of the cells;



• general option allows the user to select between 2D and 3D modes, and enter the block size information; and



• about option displays the about box shown in Figure 6. READING DATA Once the data is in the Excel spreadsheet, one of the most important functions is to read the data from the spreadsheet into memory. UPL does not operate in the manner of a normal



AusIMM Youth Congress



FIG 6 - The about box of UPL.



spreadsheet, applying formulas to the contents of cells. Instead, the spreadsheet’s cells are only used to display the input and the output data. All calculations are performed wholly within program memory. In order to read the spreadsheet into memory, UPL must first determine how big the spreadsheet is. It uses an algorithm to determine the size of the used area of the spreadsheet, for each sheet, and also counts the number of sheets. This determines the size of the memory array. This is vitally important. If the program uses a fixed size for the memory array, its execution will be very slow. Once the array size is determined, UPL then goes to each cell in the spreadsheet, reads its value, and writes this value to the memory array. This is done for every cell in the spreadsheet which forms the block model.



Brisbane, Qld, 2 - 6 May 2001



3



D DREW and E BAAFI



Once this block model is loaded into the memory array, UPL determines the ultimate pit limit. Performing calculations in memory is much faster than performing them on a spreadsheet. If a cell is null, that is, it has no value, UPL assigns this cell a value (by default, Excel gives this cell a value of zero when reading it). A cell that contains a null value is given an extremely negative value, to ensure that it will never be mined. UPL also remembers the location of these null blocks, so when writing the block values, it returns the block null values, instead of highly negative values.



CONE CREATION The most difficult part of the Korobov and the Moving Cone algorithms is the creation of the cone for each of the positively valued blocks. The cone creation process is done level by level. On each level, the two-step process is employed. Firstly, the algorithm determines a ‘working area’. This is an area of cells which is a subset of the cross-section of the block model. The cone’s blocks can only belong to this area, though not all blocks in this area belong to the cone. This is done to speed up calculation.



Block model area Working area



Cone cross section



FIG 7 - Determination of cone, plan view.



FIG 8 - A plan view of the pit cone.



4



Brisbane, Qld, 2 - 6 May 2001



AusIMM Youth Congress



IMPLEMENTATION OF ULTIMATE PIT LIMIT ALGORITHMS WITH MICROSOFT EXCEL



The working area is determined using the pit slope angle, the block size and the height of that section above the initial block. In all calculations, determinations are made from the centre of mass of all blocks. These results in a ‘step out’ value, indicating by how many units the algorithm should step outwards from the centre block. For a 45° slope, the step-out distance is one. That is, for each level progressed upwards, the working area steps out by one unit in all directions. Once this working area is determined, the algorithm, in its second step, needs to determine what blocks actually lie within the cone. The projection of a cone onto a plane results into a circle, so the equation of a circle x2 + y2 = r2 is used. However, since we are trying to find an area, all blocks are accepted if they fulfil the inequality x2 + y2 ≤ r2. The x and y values are the distance from the centre of the circle for each block. The radius of the circle is determined using the height above the initial block, the block size and the pit slope angle. Figure 8 shows a plan view of a cone, determined for a pit, when the block sizes are unequal.



USER MODE As the program is designed to be easy for students to use, a variable user mode has been set up. This has two options. The user may select from either ‘beginner’ or ‘advanced’ user modes. When in beginner mode, UPL provides the user with regular help boxes, to suggest a course of action to the user, or to make sure that the user has the correct data for the method chosen. For instance, if the user goes to the ‘transform options’ dialog box, and selects the XYZ data format option, a dialog box (Figure 9) will pop up and remind the user that the block size and the working area coordinates must be entered. This helps the user to remember the next step, and prevent errors caused by uncertainty or forgetfulness.



PROGRAM OUTPUT Once the block economic data has been manually input, or transformed from a grade data file, the ultimate pit limit can then be determined using one of the algorithms provided (Figure 2).



FIG 9 - A help dialog box.



FIG 10 - A section of an ‘optimal’ pit.



AusIMM Youth Congress



Brisbane, Qld, 2 - 6 May 2001



5



D DREW and E BAAFI



FIG 11 - A 3D representation of an ‘optimal’ pit using Surfer.



All the algorithms of UPL use an identical input format, and produce an identical output format. Note that the three algorithms will not necessarily produce the same ultimate pit for a given economic block model, due to the variations within the algorithms themselves. All of the blocks which lie within the ultimate pit limit will be highlighted. In addition, UPL will provide a summary of the pit, showing the number of blocks in the block model, the number of blocks that lie within the ultimate pit, and the value of that ultimate pit. If desired, this result summary can be exported to a separate file. If a three-dimensional pit is used in the program, UPL will output both horizontal and vertical sections of the block model, each highlighted with the ultimate pit for that section.



Currently, there are two factors for which the user can perform a sensitivity analysis. The first is metal price. The second is pit slope angle. For either of the options, the user has to input a high value, a low value and an incremental value. When a sensitivity analysis is started, the program will calculate the optimum pit for the smallest value, then increment up to the largest. This can be a lengthy process, depending on the amount of time required to calculate each optimal pit, and the number of iterations devised by the user. Once all pits have been calculated, UPL outputs the results to a new spreadsheet. It lists the iteration’s value, the pit size, the pit’s value, the amount of metal in the pit and the amount of waste that needs to be extracted for that pit.



EXPORTING DATA



CONCLUSION



Since Excel has a poor three-dimensional graphing capabilities, the user may wish to export the output values from UPL to an external graphing program. In order to accommodate this, UPL has an export function, which will allow the ultimate pit to be opened by other programs. To do this, UPL creates a new file in Excel. It then writes the pit data to the new file. It does this by writing the x-location, the y-location, and the pit depth at that point. It does this for all surface locations. Once this file is written, Excel can then save the file in a variety of formats, such as a comma separated value text file.



UPL has been primarily designed for the use of undergraduate mining engineering students. The current limitations include:



SENSITIVITY ANALYSIS



UPL was developed by the first author (Drew, 2000) in partial fulfilment of requirements for an award of BE (Mining) Hons degree from University of Wollongong.



Sensitivity analysis is used to determine how susceptible certain pit outputs are to certain inputs. For instance, the sensitivity analysis can be used to determine how the size of the pit, and the pit’s value will change with a change in the price of metal. In order for a sensitivity analysis to be performed, the block model must be used as input data, where the block values show their grade, instead of the usual economic value format. This is due to the fact that in a sensitivity analysis, the economic value of the blocks change.



6



• UPL is limited to Excel’s maximum working area size of 256 columns by 65 536 rows, by over 1000 sheets;



• UPL does not have an algorithm which is mathematically rigorous in three dimensions in its solution of the ultimate pit; and



• UPL does not allow for pit roads, or other scheduling capabilities. These may be considered in future work, but should be considered a low priority.



REFERENCES Drew, D, 2000. Ultimate Pit Limit Design Using Microsoft Excel, BE Thesis, University of Wollongong, 164 p. Lerchs, H and Grossman, I F, 1965. Optimum Design of Open-pit Mines, Trans Can Inst Min Metall, Vol LXVII, pp 17-24. Wright, E A, 1990. Open Pit Mine Design Models: An Introduction With FORTRAN/77 Programs (Trans Tech Publications: Germany).



Brisbane, Qld, 2 - 6 May 2001



AusIMM Youth Congress