A method for generating engineering geological section diagrams using Excel files

Abstract

The present invention discloses a method for generating an engineering geological profile using an Excel file. It includes the following steps. Step 1), prepare a borehole logging data file in Excel format; for all boreholes completed in geotechnical engineering investigations, use Excel software to prepare a borehole logging data file in Excel format. Step 2), prepare a profile data file in Excel format; according to the requirements of geotechnical engineering investigations, for the required geological profiles, use Excel software to prepare a profile data file in Excel format. Step 3), run the Yangtze River Geotechnical Cloud Mapping Software in AutoCAD. Step 4), use the Yangtze River Geotechnical Cloud Mapping Software to read the data files in Steps 1) and 2) to generate an engineering geological profile. The present invention has the advantages of being simple, convenient, and highly versatile.

Description

Technical Field

[0001] The present invention relates to the field of geotechnical engineering investigation, and more specifically, it is a method for generating engineering geological profiles in AutoCAD using an Excel file as a data source. Background Art

[0002] During the process of geotechnical engineering investigation or other engineering geological investigations, making engineering geological profiles using AutoCAD software is an essential task in engineering investigation. Currently, the mapping methods mainly adopt the data formats and software developed by Lizheng Company or each company itself. Since the industries involved in geotechnical engineering are relatively extensive, in addition to industrial and civil construction, there are also industries such as railways, highways, water conservancy, railways, ports, etc. The main characteristics of the above-mentioned software are poor expandability, unable to meet the refined requirements of different industry fields, or being relatively complex, with unclear data formats and unfriendly interfaces for beginners. For example, for.txt format files, the input data needs to be strictly in the specified order, with high requirements for data formats and data types. It is extremely easy to make mistakes when inputting data, and there are few prompt messages, making it difficult to learn and inconvenient to use. Or when using special data formats such as Lizheng, due to the non-open data format, poor generality, and weak expandability. With the rise of big data applications, the application of three-dimensional geological information technology has increasingly complex requirements for original borehole data and profile data. Many currently popular data formats can no longer meet the needs of industry development and applications.

[0003] Therefore, it is of great significance to develop a simple, convenient, and highly general method for generating engineering geological profiles. Summary of the Invention

[0004] The purpose of the present invention is to provide a method for generating engineering geological profiles in AutoCAD using an Excel file as a data source, which is simple, convenient, and highly general. The proposed data file format follows the thinking of geological (geotechnical) engineers in understanding and processing geological problems. Starting from the actual process of geotechnical engineers in understanding the various attributes of geological bodies, using the simple and general characteristics of Excel software, a simple and easy-to-use borehole and profile diagram database is established according to the engineer's understanding of the various attributes of geological bodies, thereby providing a simple, convenient, and highly general method for generating geotechnical engineering geological profiles, so as to improve the efficiency of engineers in processing borehole and profile data.

[0005] In order to achieve the above purpose, the technical solution of the present invention is: a method for generating engineering geological profiles in AutoCAD using an Excel file as a data source, characterized by including the following steps,

[0006] Step 1), prepare a borehole logging data file in Excel format;

[0007] For all boreholes completed in geotechnical engineering investigations, use Excel software to compile an Excel-format borehole logging data file, which includes all data related to the borehole implementation process and the geological properties of the strata revealed by the borehole;

[0008] Step 2), compile an Excel-format cross-section data file;

[0009] According to the requirements of geotechnical engineering investigations, for the required geological cross-sections, use Excel software to compile an Excel-format cross-section data file, which includes all elements such as topography required for cross-section production and is associated with the borehole logging data file through appropriate annotations;

[0010] Step 3), run the Yangtze River Geotechnical Cloud Mapping software in AutoCAD software;

[0011] Step 4), use the Yangtze River Geotechnical Cloud Mapping software program to read the data file and generate a cross-section; after corresponding calculations, the Yangtze River Geotechnical Cloud Mapping software program calls the internal functions of AutoCAD to generate the engineering geological cross-section required for geotechnical engineering investigations (that is, use the Yangtze River Geotechnical Cloud Mapping software to read the data files in steps 1) and 2) to generate the engineering geological cross-section).

[0012] In the above technical solution, in step 1), the Excel-format borehole logging data file mainly includes basic data and extended data;

[0013] The basic data includes: project name, borehole number, borehole depth, X, Y, elevation at the hole opening, date of starting drilling, date of ending drilling, buried depth of the water level at the end of the hole, date of observing the water level at the end of the hole, drilling unit, recorder, cartographer, drawing number, checker, drill foreman, thickness of overburden, thickness of strongly weathered layer, thickness of weakly weathered layer, core description, borehole summary, borehole location, and purpose of drilling, a total of 23 items;

[0014] The extended data includes: stratigraphic division, borehole diameter, lithologic division, weathering zone division, recovery rate RQD, injection test, water pressure test, sonic logging, sampling, standard penetration test, and dynamic penetration test, a total of 9 items;

[0015] One Excel-format borehole logging data file is generated for each borehole's data.

[0016] In the above technical solution, in step 1), among the basic data, 6 items of data including project name, borehole number, borehole depth, X, Y, and elevation at the hole opening are required data, and the rest are optional data; all the extended data are optional data. Among them, the formation stratification data format includes 2 items of data, namely depth and age; the borehole diameter data format includes 2 items of data, namely depth and diameter; the lithologic stratification data format includes 2 items of data, namely depth and lithology; the weathering zone data format includes 2 items of data, namely depth and weathering; the recovery rate and RQD data format includes 4 items of data, namely depth, recovery rate, acquisition rate, and RQD; the water injection test data format includes 3 items of data, namely starting depth, ending depth, and value; the water pressure test data format includes 3 items of data, namely starting depth, ending depth, and value; the acoustic logging data format includes 2 items of data, namely depth and value; the sampling, standard penetration, and dynamic penetration data format includes 4 items of data, namely starting depth, ending depth, number or value, and category.

[0017] In the above technical solution, in step 2), the profile data file in Excel format mainly includes basic data and extended data;

[0018] The basic data includes: 8 items, namely map name, profile number, elevation name, maximum scale value, minimum scale value, horizontal scale, vertical scale, and whether it is reversed;

[0019] The extended data includes 1 item, namely the topographic line.

[0020] One Excel file is generated for each geological profile data, and this Excel file should be saved in the same directory as the Excel format borehole logging data file generated in step 1).

[0021] In the above technical solution, in step 2), all the basic data are required data, and all the extended data are required data;

[0022] In the extended data, the topographic line data format includes 5 items of data, namely cumulative distance, elevation, annotation / hole number, profile endpoint X (coordinate), and profile endpoint Y (coordinate). When the content of the annotation / hole number item is the hole number, its content should be the same as the name of the corresponding hole number's borehole data Excel file generated in step 1) (excluding the suffix). The profile endpoint X (coordinate) and profile endpoint Y (coordinate) are marked at the starting point and ending point of the profile, or at the turning points of the profile as needed, and the rest of the segments can be empty.

[0023] In the above technical solution, in step 4), when running the Yangtze River Geotechnical Cloud Geological Mapping Software, first select the profile data file generated in step 2). The Yangtze River Geotechnical Cloud Geological Mapping Software program will read the data in this file, as well as all the borehole data files generated in step 1) associated with this profile. The Yangtze River Geotechnical Cloud Geological Mapping Software program will perform data conversion and geometric operations on all the borehole data and profile data, so as to obtain the coordinates, lengths, widths, line types, sizes, fonts, colors, spacings, etc. of the line segments, texts, and filled patterns required for drawing in AutoCAD software. Then, call the internal functions of AutoCAD software to generate the engineering geological profile diagram required for geotechnical engineering investigation.

[0024] In the above technical solution, in step 4), when running the Yangtze River Geotechnical Cloud Geological Mapping Software, after the software program reads the data in this file, for the text, the preliminary work of the software is to extract the text content, specify the size, font, and alignment method of the text in the AutoCAD profile diagram, and calculate the corresponding coordinates in the profile diagram;

[0025] For numerical values, when they need to be expressed as line segments in the profile diagram, the preliminary work of the software is to calculate the starting and ending coordinates of the line segments and specify the corresponding colors, line types, and widths;

[0026] When they need to be expressed as numerical values, convert them into text and process them according to the above text processing process;

[0027] For filled patterns, the preliminary work of the software is to search for the corresponding filled pattern names in the software database, specify the corresponding filling ratios and angles, and calculate the coordinates of the filling boundaries;

[0028] For other data with mutual relationships, the preliminary work of the software is to calculate the lengths or angles expressing the mutual relationships, convert them into line segments and texts, and process them according to the above text and numerical value processing processes;

[0029] After the above processing, the line segments, texts, filled patterns required for drawing the profile diagram, and their related attributes are obtained. These attributes all belong to the attributes that can be recognized by AutoCAD software; then the Yangtze River Geotechnical Cloud Geological Mapping Software program will transfer the above attribute values to AutoCAD software, call the corresponding internal functions of AutoCAD software, and express these attributes in AutoCAD to complete the drawing of the profile diagram.

[0030] The advantages of the present invention are as follows:

[0031] (1) Utilize the simple and general characteristics of Excel software to generate borehole and profile data files, which are concise, efficient, highly versatile, and have a high correct rate, and are extremely conducive to beginners quickly getting started and using;

[0032] (2) The data file format of the present invention follows the thinking of geological (geotechnical) engineers in understanding and dealing with geological problems. Starting from the real process of engineers' understanding of various attributes of geological bodies, it can simplify complex problems with a clear and concise idea.

[0033] (3) This data file has strong scalability. According to the characteristics of each geotechnical engineering investigation industry, the data in it can be freely combined and utilized, or data with special requirements can be added, and it has good compatibility in the later stage.

[0034] (4) When using big data and 3D geological information technology, the borehole logging data file in Excel format and the cross-section diagram data file in Excel format of the present invention have strong versatility and high processing efficiency. No special processing software is required, and it is suitable for wide promotion and application. BRIEF DESCRIPTION OF THE DRAWINGS

[0035] Figure 1 It is a schematic flow chart of a method for generating an engineering geological cross-section diagram using an Excel file proposed by the present invention.

[0036] Figure 2 It is a schematic diagram of the data structure of the borehole logging data file made using Excel software in a method for generating an engineering geological cross-section diagram using an Excel file proposed by the present invention.

[0037] Figure 3 It is a schematic diagram of the data structure of the cross-section diagram logging data file made using Excel software in a method for generating an engineering geological cross-section diagram using an Excel file proposed by the present invention. DETAILED DESCRIPTION OF THE EMBODIMENTS

[0038] To make the objectives, technical solutions, and advantages of the embodiments of the present invention clearer, the technical solutions in the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings in the embodiments of the present invention. Obviously, the described embodiments are some, but not all, of the embodiments of the present invention. All other embodiments obtained by those of ordinary skill in the art without making creative efforts based on the embodiments of the present invention belong to the scope of protection of the present invention.

[0039] In the description of the present invention, it should be understood that the orientation or positional relationship indicated by the terms "length", "width", "upper", "lower", "front", "rear", "left", "right", "vertical", "horizontal", "top", "bottom", "inner", "outer", etc. is based on the orientation or positional relationship shown in the drawings, and is only for the convenience of describing the present invention and simplifying the description, rather than indicating or implying that the device or element referred to must have a specific orientation, be constructed and operated in a specific orientation, and therefore should not be construed as a limitation of the invention.

[0040] The Yangtze River Geotechnical Cloud Mapping Software is a software applied for by the applicant, and the software registration certificate number is: Software Copyright Registration No. 9446490.

[0041] The present invention is characterized by being simple, convenient and highly versatile. The present invention uses an Excel file to generate an engineering geological profile. The order of data input is not restricted, the operation is simple, the work efficiency is improved, and it is suitable for any operator to use. The correct profile can be generated by inputting data in any order (the data needs to be complete); it overcomes the defect of the prior art that a.txt format file must be input in the order of data formation to ensure the generation of a correct profile and has high requirements for the operator, seriously affecting the work efficiency. The present invention uses an Excel file to generate an engineering geological profile, has good interactivity, has prompts and fixed formats in each input unit, and also has prompt functions such as generating modification marks, avoiding input errors, being simple to operate, improving work efficiency, and being suitable for any operator to use; it overcomes the defect of the prior art that a.txt format file does not have a prompt function and cannot detect input errors in time, affecting the work efficiency.

[0042] The present invention uses an Excel file to generate an engineering geological profile, which can meet the requirements of any data format for the development and application of various industries, has strong versatility and expandability, and the generated engineering geological profile can meet the usage requirements.

[0043] Such as Figure 1 shown, a method for generating an engineering geological profile by using an Excel file according to the present invention includes the following steps:

[0044] Step 1), according to the results of geotechnical engineering surveys, for all the boreholes completed for the project, use Excel software to compile an Excel-format borehole logging data file, the main contents of which include basic data and extended data. The basic data includes: project name, borehole number, borehole depth, X, Y, elevation of the hole opening, date of starting the hole, date of ending the hole, buried depth of the water level at the end of the hole, date of observing the water level at the end of the hole, drilling unit, record, mapping, drawing number, checking, drill foreman, thickness of overburden, thickness of strong weathering, thickness of weak weathering, core description, borehole summary, borehole location and drilling purpose, a total of 23 items. The extended data includes: stratigraphic division, borehole diameter, lithologic division, weathering zone division, recovery rate RQD, injection test, water pressure test, acoustic logging, sampling, standard penetration test and dynamic penetration test, a total of 9 items. One file is generated for the data of each borehole, and the specific format of the file is shown in the appendix Figure 2 。

[0045] Among the basic data, six items of data including project name, borehole number, borehole depth, X, Y, and elevation at the hole opening are required data, and the rest are optional data. All the extended data are optional data. Among them, the format of formation stratification data includes two items of data: depth and age; the format of borehole diameter data includes two items of data: depth and diameter; the format of lithological stratification data includes two items of data: depth and lithology; the format of weathering zoning data includes two items of data: depth and weathering; the format of recovery rate, acquisition rate, and RQD data includes four items of data: depth, recovery rate, acquisition rate, and RQD; the format of water injection test data includes three items of data: starting depth, ending depth, and value; the format of water pressure test data includes three items of data: starting depth, ending depth, and value; the format of sonic logging data includes two items of data: depth and value; the format of sampling, standard penetration test, and dynamic penetration test items includes four items of data: starting depth, ending depth, number or value, and category.

[0046] In the data items of recovery rate, acquisition rate, and RQD, the data values of recovery rate, acquisition rate, and RQD are all percentage values.

[0047] In the data item of sampling, standard penetration test, and dynamic penetration test, the data values of the category include five categories: rock sample, soil sample, disturbed sample, standard penetration test, and dynamic penetration test.

[0048] Step 2), according to the needs of geotechnical engineering investigation, for the required geological profiles, use Excel software to compile Excel format profile data files, the main content of which includes basic data and extended data. The basic data includes: map name, profile number, elevation name, maximum scale value, minimum scale value, horizontal scale, vertical scale, and whether it is reversed, a total of 8 items. The extended data includes topographic line, a total of 1 item. One Excel file is generated for each profile data, and this file should be saved in the same directory as the Excel file generated in step 1). For the specific file format, see the attachment Figure 3 。

[0049] All the basic data are required data, and all the extended data are required data. In the extended data, the format of topographic line data includes five items of data: cumulative distance, elevation, annotation / hole number, profile endpoint X (coordinate), and profile endpoint Y (coordinate). When the content of the annotation / hole number item is the hole number, its content should be the same as the name of the corresponding hole number's borehole data Excel file generated in step 1) (excluding the suffix). The profile endpoint X (coordinate) and profile endpoint Y (coordinate) are marked at the starting and ending points of the profile or the turning points of the profile as needed, and the rest of the segments can be empty.

[0050] Step 3), run the Yangtze River Geotechnical Cloud Mapping Software in AutoCAD software; use the Yangtze River Geotechnical Cloud Mapping Software to read the data file and generate a profile diagram;

[0051] When running the Yangtze River Geotechnical Cloud Geological Mapping software program, first select the profile data file generated in step 2). The Yangtze River Geotechnical Cloud Geological Mapping software program will read the data in this file, as well as all the borehole data files generated in step 1) associated with this profile. The Yangtze River Geotechnical Cloud Geological Mapping software program will perform data conversion and geometric operations on all the borehole data and profile data, so as to obtain parameters such as coordinates, lengths, widths, line types, sizes, fonts, colors, and spacings of the line segments, texts, and filling patterns required for mapping in AutoCAD software. Then, it will call the internal functions of AutoCAD software to generate the engineering geological profile diagrams required for geotechnical engineering investigation.

[0052] The data file format proposed by this method follows the thinking of geological (geotechnical) engineers in understanding and dealing with geological problems. Starting from the real process of engineers' understanding of various attributes of geological bodies, it can simplify complex problems, with a clear and concise idea, which is conducive to beginners quickly getting started and using it; this data file has strong scalability and is suitable for use in various fields related to geotechnical engineering, such as geotechnical engineering investigations in industries like construction, water conservancy, railways, and highways. It has strong versatility, rich expression content, and can also be directly used in big data and three-dimensional geological information technologies, enabling the efficiency of geological data processing in geotechnical engineering construction to be greatly improved. It is simple to operate and easy to learn. Generally, a newly recruited intern can master the use of this method in only 2 - 3 hours.

[0053] The above description is only a preferred embodiment of the present invention and is not intended to limit the present invention. Any modifications, equivalent replacements, improvements, etc. made within the spirit and principle of the present invention shall be included within the protection scope of the present invention.

[0054] Other parts not described are all prior arts.

Claims

1. A method for generating an engineering geological profile using an Excel file, characterized in that: It includes the following steps: Step 1), prepare a borehole logging data file in Excel format; For all boreholes completed in geotechnical engineering investigations, use Excel software to prepare a borehole logging data file in Excel format, which includes all data related to the borehole implementation process and the geological properties of the strata revealed by the boreholes; Step 2), prepare a cross-section data file in Excel format; According to the requirements of geotechnical engineering investigations, for the required geological cross-sections, use Excel software to prepare a cross-section data file in Excel format, which includes all elements of the topography required for cross-section production and is associated with the borehole logging data file through appropriate annotations; Step 3), run the Yangtze River Geotechnical Cloud Ground Mapping Software in AutoCAD software; Step 4), use the Yangtze River Geotechnical Cloud Ground Mapping Software to read the data file and generate a cross-section; The program of the Yangtze River Geotechnical Cloud Ground Mapping Software calls the internal functions of AutoCAD after corresponding operations, so as to generate the engineering geological cross-section required for geotechnical engineering investigations; In Step 1), the borehole logging data file in Excel format includes basic data and extended data; One Excel-format borehole logging data file is generated for each borehole data; In Step 2), the cross-section data file in Excel format includes basic data and extended data; One Excel file is generated for each geological cross-section data, and this Excel file is saved in the same directory as the Excel-format borehole logging data file generated in Step 1); In Step 4), when running the Yangtze River Geotechnical Cloud Ground Mapping Software, first select the cross-section data file generated in Step 2). The program of the Yangtze River Geotechnical Cloud Ground Mapping Software will read the data in this file, as well as all the borehole data files generated in Step 1) associated with this cross-section. The program of the Yangtze River Geotechnical Cloud Ground Mapping Software will perform data conversion and geometric operations on all the borehole data, so as to obtain the coordinates, lengths, widths, line types, sizes, fonts, colors, and spacing parameters of the line segments, text, and filling patterns required for AutoCAD software mapping, and then call the internal functions of AutoCAD software, so as to generate the engineering geological cross-section required for geotechnical engineering investigations; In Step 4), when running the Yangtze River Geotechnical Cloud Ground Mapping Software, after the software program reads the data in this file, for the text, the preliminary work of the software is to extract the text content, specify the size, font, and alignment method of the text in the AutoCAD cross-section, and calculate the corresponding coordinates in the cross-section; For numerical values, when they need to be expressed as line segments in the cross-section, the preliminary work of the software is to calculate the starting and ending coordinates of the line segment and specify the corresponding color, line type, and width; When it needs to be expressed as a numerical value, it is converted into text and processed according to the above text processing process; For filling patterns, the preliminary work of the software is to search for the corresponding filling pattern name in the software database, specify the corresponding filling ratio and angle, and calculate the coordinates of the filling boundary; For data with other interrelationships, the initial software work is to calculate the lengths or angles representing the interrelationships, convert them into line segments and text, and process them according to the above text and numerical processing procedures; After the above processing, the line segments, text, filling patterns required for cross-section drawing, and their related attributes are obtained, all of which belong to the attributes recognizable by AutoCAD software; then the Yangtze River Geotechnical Cloud Ground Drawing Software program transfers the above attribute values to AutoCAD software, calls the corresponding internal functions of AutoCAD software, and expresses these attributes in AutoCAD, thus completing the cross-section drawing.

2. The method for generating an engineering geological profile using an Excel file according to claim 1, wherein: In step 1), the basic data includes: project name, borehole number, borehole depth, X, Y, elevation at the hole opening, drilling start date, drilling end date, buried depth of the water level at the end of the hole, observation date of the water level at the end of the hole, drilling unit, recorder, drafter, drawing number, checker, drill foreman, thickness of overburden, thickness of strongly weathered layer, thickness of weakly weathered layer, core description, borehole summary, borehole location, and drilling purpose, a total of 23 items; The extended data includes: stratigraphic division, borehole diameter, lithologic division, weathering zone division, recovery rate, RQD, injection test, water pressure test, sonic logging, sampling, standard penetration test, and dynamic penetration test, a total of 9 items.

3. The method for generating an engineering geological profile using an Excel file according to claim 2, wherein: In step 1), among the basic data, 6 items of data including project name, borehole number, borehole depth, X, Y, and elevation at the hole opening are required data, and the rest are optional data; all the extended data are optional data. Among them, the data format of stratigraphic division includes 2 items of data: depth and age, the data format of borehole diameter includes 2 items of data: depth and diameter, the data format of lithologic division includes 2 items of data: depth and lithology, the data format of weathering zone division includes 2 items of data: depth and weathering, the data format of recovery rate and RQD includes 4 items of data: depth, recovery rate, acquisition rate, and RQD, the data format of injection test includes 3 items of data: starting depth, ending depth, and value, the data format of water pressure test includes 3 items of data: starting depth, ending depth, and value, the data format of sonic logging includes 2 items of data: depth and value, and the data format of sampling, standard penetration test, and dynamic penetration test includes 4 items of data: starting depth, ending depth, number or value, and category.

4. The method for generating an engineering geological profile using an Excel file according to claim 3, characterized in that: In step 2), the basic data includes: drawing name, section number, elevation name, maximum scale value, minimum scale value, horizontal scale, vertical scale, and whether it is reversed, a total of 8 items; The extended data includes topographic line, a total of 1 item.

5. The method for generating an engineering geological profile using an Excel file according to claim 4, wherein: In step 2), all the basic data are required data, and all the extended data are required data; In the extended data, the data format of the topographic line includes 5 items of data: cumulative distance, elevation, annotation / hole number, X coordinate of the section endpoint, and Y coordinate of the section endpoint. When the content of the annotation / hole number item is the hole number, its content should be the same as the name of the Excel file of the corresponding hole number's drilling data generated in step 1). The X coordinate and Y coordinate of the section endpoint are marked at the start and end points of the section, or at the turning points of the section as needed.

Images