Query optimization-oriented lateral expansion method of distributed geological borehole database

2.1 Geological data element

Data model elements are defined as geological data elements in comprehensive geological data. When analyzing the data of geological and mineral resources, the geological data elements can be abstracted into five basic data according to the line classification method, namely, geological map, geological table, geological report, mineral resources and file classification. Among them, geological maps, geological documents, and geological forms are entities, which contain both entity data and attribute data, while geological reports and classifications are abstract concepts, which contain only attribute data and not entity data. These five data elements are the basic data units in geological and mineral data [15]. In order to describe them formally, the five data elements of mineral data can be divided into three categories: type identifier, attribute data, and entity data. Triples are mainly used to store sparse matrices as a compression method. This method can reduce the storage memory occupied by the computer, and shorten the access and operation time. In order to formalize their description, the five data are defined in the form of the triplet. The specific definitions are as follows:

where Type is a type identifier used to determine the type of geological data elements, corresponding to one of the five basic data units. For certain geological data, it can only be one kind of geological map, geological document, geological table, geological report, and classification, and has certainty [20].

MetaData is the attribute data of geological data elements, which describes the attribute information of geological data elements. EntiyData is the entity data of geological data. Geological maps, geological forms, and geological documents can contain entity data so they have value. For geological and mineral data with a wide variety and a large amount of data, the abstracted above five basic geological data elements can be used for data classification and description. These five geological data elements are the model elements of the integrated geological data model, which can be used to describe the static structure and dynamic operation of the model [21].

2.2 Integrated geological data tree

Mines and minefields comprise a broad range of geological data that play a key role in the prospecting and extraction of mineral deposits and sites, as well as in management decision-making. This study proposes a comprehensive geological data model, which facilitates the systematic management of geological data according to the model, and thus improves the efficiency of geological data utilization. The model is proposed as a geological data model (20) that organizes geological data in an orderly manner, enhancing the efficiency of the geological data use.

After a comprehensive geological data element, the static structure of the comprehensive geological data model can be described. Using an integrated geological data tree, the comprehensive geological data tree follows a tree data structure with the five basic geological data elements as nodes, which are used to detail the characteristics of geological and mineral resource information data as well as the mutual constraint relationship between them. Different from ordinary trees, a comprehensive geological data tree is actually a deformed tree structure, and its geological data elements are not only related to parent nodes and child nodes but also have the following three constraint relationships among geological data elements in a comprehensive geological data tree [14]:

1. As the entity data of the smallest unit of comprehensive geological data elements, geological maps, geological forms, and geological documents cannot be derived from other types of comprehensive geological data elements. If the geological data element is one of the types of geological maps, geological forms, and geological documents, it cannot have successors.

2. As two conceptual entities, geological reports and classification can have successors and precursors. If they have precursors, their precursors can only be classification, not other geological data elements.

3. If there is a successor to the geological report data element, the subsequent can only be three types, geological map, geological document, and geological table, instead of classification, and the successor to the classified data element can be any five geological data elements.

Figure 1 depicts the geological data tree before and after extension. Figure 1(a) illustrates the geological data tree, while Figure 1(b) illustrates the number of extended geological data instances.

Figure 1

Examples of geological data tree expansion before and after. (a) Example of a geological data tree and (b) an example of an extended geological data tree.

With the above three constraint relations, the formal definition of a comprehensive geological data tree can be given as

where D represents a dataset that combines geological data elements. R represents the relation set of D , which can be specifically expressed as:

1. If R is empty, D means there are no data, then the comprehensive geological data tree is an empty geological data tree;

2. If D has only one geological data element, then there is no definition of relation, then R is null;

3. If D contains two or more geological data elements, R = {H} exists, where H is the binary relationship in the following three comprehensive geological data tree cases:

D has one and only one geological element as the root node of the comprehensive geological data tree (there is no precursor for this geological data element). If the geological data element is a geological map, a geological table, or a geological document, it has no successor. If it is a geological report element, then its successor can only be a geological map, a geological document, or a geological table.

If D -{root} ≠ φ there is a division of D -{root} D 1 , D 2 , … , D m ( m > 0 ) for any j ≠ k ( 1 ≤ j , k ≤ m ) . Then, D j ∩ D k = φ and for any i ≠ k ( 1 ≤ i ≤ m ) , the only geologic data element x i ∈ D i has 〈 root, x i 〉 } ∈ H , where m is the number of tuples accessed, k is the number of nodes in the connection tree, and j is the node attribute coefficient.

For D -{root} partition H - { 〈 root, x 1 〉 , … , 〈 root, x m 〉 } , there is a unique partition H 1 , H 2 , … , H m ( m > 0 ) , for any j ≠ k ( 1 ≤ j , k ≤ m ) , there is H j ∩ H k = φ , and for any i ( 1 ≤ i ≤ m ) , H i is a binary relation on D i , ( D i , { H i } ) is a comprehensive geological data tree in accordance with this definition, called the comprehensive geological data subtree of root . Thus, the construction of the distributed geological drilling data subtree is completed. Based on the above-mentioned database storage tree structure, the horizontal expansion method of the database is designed.

3.1 Principle of the lateral expansion method

According to different fields, different aspects of geological information are integrated to form a complete geological drilling database, which provides valuable reference data for various industries. Database expansion is the ability to continuously increase the system’s carrying capacity, and the horizontal expansion of the database is based on variables such as data volume and a data set of geological data road by read-write separation, vertical slice, and horizontal slice by putting different data in different nodes. This work thus calculates the spatial location parameter to come up with the efficiency of horizontal expansion.

The calculation process of horizontal expansion of the geological database is as follows:

Set the data quantity of the geological drilling database as w , the number of data attributes as g , and the geological drilling data set composed of all geological drilling databases is { e 1 , e 2 , … , e w } , where e i is the ith data in the geological drilling database. The data set composed of all geological drilling data attributes is { h 1 , h 2 , … , h g } , where h i is the j th data attribute in the geological drilling database, and the updating speed of geological drilling data is η . The spatial location selects the data nodes according to the load weights in a probabilistic manner and thus the optimal spatial location. The spatial location parameters of the extended geological borehole data can be calculated by using the following formula:

The lateral expansion efficiency of the geological drilling database can be calculated by using the following formula:

According to the method described above, the horizontal expansion of the geological drilling database can be carried out to provide data support for different industries.

3.2 Horizontal expansion optimization method of the distributed geological database

Using the traditional algorithm to optimize the horizontal expansion of the geological borehole database, it is impossible to avoid the defect of large differences in data attributes due to the large amount of data, which leads to a decrease of database query efficiency [23]. Therefore, a query optimization-oriented lateral expansion method of the distributed geological borehole database is proposed.

Building multiple constraint models. In the process of building the multi-constraint model, the diversity of borehole data attributes of the same geology is fully considered so the unnecessary cumbersome geological borehole data characteristics can be reduced. The steps are as follows: Pearson’s correlation coefficient used in this study to calculate the similarity of the data is calculated by dividing the covariance of the two variables by the product of their respective standard deviations, i.e. the similarity coefficient of the data in the Distributed Geological Borehole Database is calculated using the following formula:

Based on the method described above, the similarity degree of tedious and repeated data in the geological borehole database can be distinguished to describe the relevant data with strong similarity in the geological borehole database with the feature vector. The following formula can be used to calculate the variation parameters of the attribute characteristics of borehole data in different geology:

If the value of s is changed, the difference in data attribute characteristics is strong. Assuming s = 1 , the distributed geological borehole data attribute characteristic value obtained by the above formula is a negative number. Therefore, the following formula is used for calculation:

where y and z represent the characteristics of different data in the geological borehole database, and the distance between them can indicate the similarity between them:

where B is the non-negative definite matrix of data in the distributed geological borehole database.

Assuming B is the identity matrix, the above formula can be transformed into the following form:

Assuming B is a diagonal matrix, the above formula can be transformed into the following form:

Based on the above method, the data in the distributed geological borehole database can be processed, the data variables can be obtained, and more than 90% of the distributed geological borehole database information characteristics can be obtained [1]. The following formula can be used to establish a variety of constraint models, which can be used to optimize the lateral expansion of the distributed geological borehole database:

Model constraints are conditions that must be met in order for the model to work and are of two types: internal constraints, which refer to conditions that must be met in order for the equations to be solved; and external constraints, which are mainly conventions made to make the model reflect the real situation as closely as possible. The internal constraints established according to equation (11) are the value within the square root operation must not be less than 0 and the denominator must be meaningful, i.e. p is not equal to 1.

The external constraints are as follows:

1. Geological maps, geological tables, and geological documents, as the smallest unit of entity data in the integrated geological data element, cannot be derived from other types of integrated geological data units.

2. Geological reports and classifications are two conceptual entities with both successors and predecessors.

3. If the geological report data element has a successor, the successor can only be the geological maps, geological documents, and geological tables, and not classifications. The successor of the classification data element can be any five geological data elements.

According to the method described above, the data in the geological borehole database can be classified, and the information in the database can be divided into several different categories according to the characteristics of the data, which provides an accurate data basis for the expansion of the geological borehole database [1]. A variety of constraint models are established to judge the data categories that meet the query conditions, and the data query is carried out according to different data categories to realize the expansion and optimization of the geological borehole database.

3.3 Expansion and the optimization process of the distributed geological borehole database

In the constructed sub-tree structure of distributed geological drilling data, based on the tree structure of database storage, this work designs a method of database horizontal expansion and extended optimization. The extended optimization process of the distributed geological drilling database refers to the process of generating a query execution plan (QEP), in which the QEP should minimize the objective function, which is the time required for query execution in a distributed environment. This work focuses on joint operations. The query optimizer of the distributed geological borehole database consists of three parts: search scope of the geological database, generation of the horizontal expansion model of the geological borehole database, and query rules of the geological borehole database, as shown in Figure 2.

Figure 2

Expansion optimization of the distributed geological database.

Search scope of the distributed geological borehole database: A collection of alternative execution plans for query requests that can produce the same results. The execution sequence unique to each query plan, and the different ways in which various operations are implemented can result in different performances of execution plans. Execution plans are generally abstracted into tree of operations, where the nodes are operations and the shape of the tree determines the execution order of the operations. These operation trees are generated by the transformation rules of the query requests. These query operation trees are equivalent in that they can produce the same result set [3]. The horizontal expansion model of the distributed geological borehole database is generated. The cost model of the query optimizer includes cost function, data statistics information, intermediate result set estimation tool, etc. The query execution time is the primary benchmark for calculating the execution cost. For the distributed geological drilling database rules, the cost model is used to detect the execution plan. In the search space, the use of dynamic programming or simulated annealing strategy filter does not produce the optimal solution of the execution plan. It is required to check the plan in accordance with the price model to forecast the response time, and considering all the execution plans, finally, the best execution plan is determined.

The centralization or decentralization of the distributed geological borehole database expansion need not be taken into account during the search space stage since the execution plan is developed in accordance with specific transformation rules that are applicable under all circumstances.

4.1 Capacity of the distributed geological borehole database after lateral expansion

The expansion of database capacity is to ensure that it can be smooth, continue to provide external services, and ensure the availability of geological database services. In order to verify the effect of horizontal database expansion under different methods, the method in previous studies [11–13] and the method in this study are used to detect the database capacity after horizontal expansion of the geological borehole database, and the results are shown in Figure 2. For ease of understanding and differentiation, the three methods mentioned above and the one in this study will be referred to as Methods A [12], B [13], C [11], and D (database horizontal expansion methods), respectively.

It can be seen from the analysis of Figure 3 that the lateral expansion capacity of geological databases under different methods is different. With the increase of the geological data capacity, the lateral expansion capacity of the geological database of this research method is always much higher than that of other methods and shows a stable upward trend [11,12,13]. If the geological data capacity is 60 GB, the maximum expansion capacity is approximately 46 × 105 TB. It can be seen that the horizontal expansion method of the database always has a high capacity after the horizontal expansion of the geological database, and the horizontal expansion effect is good.

Figure 3

Capacity of the geological database after lateral expansion under different methods.

4.2 Retrieval efficiency of geological borehole database

Retrieval efficiency refers to the efficiency of database retrieval of the specified data. In this study, the difference is the amount of retrieved data, the retrieval time of the test method, and the automatic output of the detection efficiency value. In order to verify the retrieval efficiency of the geological borehole database after lateral expansion under different methods, this study uses Ping’s method [11], Mamoru’s method [13], Guo et al.’s method [12], and the method of this study to test the retrieval efficiency after lateral expansion of the geological borehole database, and the experimental results are shown in Table 1.

Analysis of Table 1 shows that the retrieval efficiency is different after horizontal expansion under different geological database storage capacities. With the increase in the storage capacity of geological databases, the retrieval efficiency of geological databases using this research method is mostly stable at over 90%, while the retrieval efficiency of geological databases using other methods is in the range of 40–65% [11,12,13]. It can be seen that after the horizontal expansion of geological databases, the retrieval efficiency of this research method is always higher than that of other methods, and it has a very good advantage in retrieval efficiency.

4.3 System resource occupancy rate after geological drilling database expansion

Resource usage is the usage of memory. The lower the resource occupancy rate, the better the application effect of the database expansion method. In order to verify the system resource occupancy rate after the horizontal expansion of the database under different methods, the research methods of Ping [11], Guo [12] and Mamoru [13] are adopted in this study. The occupancy rate of system resources is determined.

Analysis of Table 2 shows that there are differences in the system resource utilization rate after horizontal expansion under different methods. As the number of iterations increases, the resource utilization rate of the system using this research method remains stable within 15%, while the resource utilization rate of the system using other methods varies from 40 to 80% [11,12,13]. It can be seen that after the expansion of the geological drilling database using this research method, the resource utilization rate of the system decreases significantly, indicating that the lateral expansion effect of the geological drilling database using this method is good. According to SPSS 23.0 analysis, P < 0.05, which is statistically significant.