Home / Uncategorized / SpatialBase/MineDB in Mineral resource evaluation

SpatialBase/MineDB in Mineral resource evaluation

INTRODUCTION

This white paper is aimed at comparing the current methodology in terms of Mineral Resource Evaluation, with regards to current technologies, and the introduced methodology by using a Spatially Enhanced Mining database, specifically SQL Server 2008/12 with MineDB, and the perceived benefits of such.
This paper is not a technical write-up of this particular procedure, but is rather aimed at understanding the advantages in using a Spatial Mining Database. in the above-mentioned approaches by bringing both of these methodologies down to its basic building blocks, and comparing them on that level.

HISTORY

Mineral Resource Evaluation within the mine in question has been done using a certain methodology with the current technologies that has been available for the past number of years.
Some of the processes have been identified to be non-optimum in terms of performance, hardware needed, etc. Looking at a newer technology might solve some of these non- optimum issues, and this paper is dedicated to a layman’s analysis of this technology as applied to these areas, and the result thereof.

CURRENT METHODOLOGY

The usual way of doing a Mineral Resource Evaluation consists of many different steps. Without going into too much technical detail on this procedure, the basic software applications steps applicable are the following:
 Elements are imported from CAD files.
 Polygon vertices for various geometric elements are read and written back to a SQL
Server database.
 ActiveX components created in Visual Basic 6 and Microsoft SQL Server stored
procedures to process the Batch.
 ActiveX components created in Visual Basic 6 to perform the Evaluation (ActiveX
executable applications calling an ActiveX Library)
 A standard C DLL is also wrapped in a COM library (created in Visual C++) to perform
the polygon intersection (CMMaths.dll wrapped by PolygonCutter.dll).
Summarily (for those of us who are not software developers or familiar with this area) this means that the different elements are stored as polygons (originally in CAD file format) with various attributes, then read into an application from a SQL Server database. From there it is evaluated by this application, and during this process, the Polygon Cutter does the intersections between various polygons. After the evaluation, this data is then stored back into a SQL Server database.

DOWN TO BASICS

To achieve a simplification and potential improvement of the currently used procedure, it was necessary to find the lowest common denominators between different batch processes. Two common denominators were found and the basic elements of the procedures are simply the following:
 Bulk intersections and differences are done between many polygons on one side and many other polygons on the other side. Whether it is intersecting 200 000 Survey polygons with 200 000 Krige Grid Square polygons or other similar data, the basic fact remains that all times, bulk intersections and differences are performed.
 Attributes are given to these polygons in groups, and upon intersection of groups of polygons, the result always has some combination of these attributes, per specified rules.

POTENTIAL AREAS OF IMPROVEMENT

The current methodology as described above had certain invariable results, and potential areas of improvement were identified as being the following:

PROCESSING TIME

The timing of these steps, specifically for the mine in question, took anywhere from half an hour to many hours, the time consuming steps being the following:
 Data being read from a SQL Server database into an application.
 Data being dealt with in code, and send back and forth to and from the polygon
cutter.
 Attributes being programmatically assigned to various sets of polygons.
 Actual intersection operations.
 Data being written back to the database.
 Data in the database being in the form of vertices, and being read as such. For
instance, an 88 000 vertex polygon would mean 88 000 records would be read,
instead of 1 record, since this is 1 object.
 Due to the fact that the application tied up a great deal of processing time on the
server machine, it would bring this machine to a halt during processing, causing other processes to be held up.

SIMPLICITY

Due to the fact that – in this particular Mineral Resource Evaluation procedure, the operations were not treated in its fundamental elements, which are the bulk intersections/differences and attributes given – many different operations were done in many different places and in many different varieties.
The amount of code needed for this evaluation procedure and all the steps in it, is approximately 70 000 lines of code.
This opened the door to a potential area of improvement in terms of simplicity, due to taking these operations down to their fundamentals.

AUDITABILITY

Due to various processes being handled inside the database, and then many other processes being handled outside the database, the auditability of these procedures were complicated by this fact.
Again, this opened the door to a potential improvement in the auditability of these procedures, should they be done in one place, rather than many, tracking through different sections of the software.

A NEW WAY OF THINKING

To change the paradigm of the current evaluation procedure, it was necessary to find a new methodology that fit the criteria for a workable solution.
What was wrong with the old procedures?
Well, certainly the time taken to import data back and from the database is an important factor in terms of time consumption. As is the processing time holding up the entire machine, and certainly sending data to a polygon cutter and receiving it back.
If all these processes could be done in one place, and this place where the new processing was happening did not hold up the entire machine while it was operating, bringing other processes to their knees – well that might be a workable solution.

WHAT IS SpatialBase/MINEDB?
MineDB is a 2D and 3D spatial mining extension to SQL Server 2008 or above. For the more technical orientated audience, it uses the CLR extensions of SQL Server 2008 to add extra spatial capability in terms of types, operations and predicates, augmenting the current 2D support to fully fledged 3D support as well as support for mining objects like boreholes, pit benches and stopes etcetera.
For the less technical orientated audience, MineDB causes SQL Server 2008 to be able to recognize and work with 3D mining spatial types. The database now “knows” what a linestring, polygon, point, box, surface, borehole is etc. And the database can do intersections, differences, area calculations, borehole de-surveying and so forth as well on these spatial objects.

APPLYING THE TECHNOLOGY TO THE PROBLEM

The following were done, using MineDB:
 An intersection-join operation was created in the database as a stored procedure. This operation is unique to MineDB and allows processing of huge datasets with minimum resource utilization This operation would take many objects (polygons) from one side (the left) and intersect them or difference them with many objects (polygons) from the other side (the right).
 The same operation also included merging the various attributes of these objects, per the pre-defined rules of the mine in question.
 By using this operation, one could get the remainders, the differences, or the unions between left and right, and this was the most fundamental procedure in the evaluation process.
This was all that needed to be done to solve the entire evaluation procedure.

BENEFITS

There were many benefits to using this methodology to the business problem.
In terms of the potential areas of improvement the following were improved upon.

PROCESSING TIME

In the below paragraphs, improvements are set out in italics.
The timing of these steps, specifically for the mine in question, used to take anywhere from
half an hour to many hours, now took only 5 minutes approximately.
The time consuming steps which used to be the following, were now changed in the
following ways:
 Data being read from a SQL Server database into an application. This was now not read into the application anymore, but this data was kept in a SQL Server database, and dealt with right there.
 Data being dealt with in code, and send back and forth to and from the polygon
cutter. The polygon intersection and differencing now happened directly in the database, and data doesn’t need to be sent back and forth anymore.
 Attributes being programmatically assigned to various sets of polygons. These attributes now were being assigned right inside the database, not programmatically.

 Actual intersection operations.

These operations happen directly in the database.
 Data being written back to the database.

There is no writing back and forth. Data is
held inside the database.

 Data in the database being in the form of vertices, and being read as such. Data now
resided as objects in the database, and by using proper object formats, such as polygons, this slow down was eliminated.
 Due to the fact that the application tied up a great deal of processing time on the server machine, it would bring this machine to a halt during processing, causing other processes to be held up. SQL Server can be set to use only a certain percentage of processing. By actual test, even while running large operations, the server machine performed fine when other work needed to be done on the same machine.

SIMPLICITY

Operations were now treated as fundamentally as possible. Many different operations no longer needed to be done in many different places or in different varieties.
The amount of code in the stored procedures that represented the various steps done in a single evaluation, which used to be about 70 000 lines of code, came down to only 10 procedures and approximately 1000 lines of code. This is much simpler than before.

AUDITABILITY

Due to all processes being handled inside the database, the auditability is much easier and simpler.

CONCLUSION

This white paper set out to explain, how using a spatially enhanced database technology, especially with regards to SpatialBase/MineDB with SQL Server, assisted in improving various areas with regards to the Mineral Resource Evaluation procedure for a particular mine.
The improvements in terms of performance, simplicity and audibility are tremendous and this technology is being established as a workable solution and is applicable to other areas with similar potential areas of improvement, as it is a widely applicable technology that could undercut many different areas.

Top