Standardized extraction method for geotechnical engineering investigation data

By adopting standardized data extraction methods in urban rock and soil geological information systems, the problem of inconsistent data formats of different survey units is solved, and the rapid and accurate extraction and merging of data is achieved, the work efficiency is improved, and the standardization and consistency of data is ensured. , promotes the construction and application of the system.

CN118966172APending Publication Date: 2024-11-15TIANJIN SURVEY DESIGN INST GRP CO LTD
View PDF 0 Cites 2 Cited by

Patent Information

Application Number
CN202410989974.0
Authority / Receiving Office
CN · China
Patent Type
Applications(China)
Current Assignee / Owner
Filing Date
2024-07-23
Publication Date
2024-11-15

AI Technical Summary

Technical Problem

In the construction of urban geotechnical information systems, the huge amount of drilling holes in geotechnical engineering survey is inconsistent with the data format submitted by different survey units, resulting in increased difficulty in data integration and application, manual input is cumbersome, time-consuming and error-prone, reducing the problem of data restructuring. Work efficiency.

Method used

Adopt standardized extraction methods for geotechnical engineering survey data, including establishing a standard database, using AI intelligent tools to extract and convert text version reports, merging data tables through Excel smart office plug-in, and using SUMIF functions to match data to ensure data standardization and consistency .

Benefits of technology

It realizes the fast, convenient and accurate data extraction, reduces manual operations, improves data processing efficiency, ensures data accuracy and consistency, reduces design errors and potential security risks, and promotes the urban rock and land geological information system. Construction and application.

✦ Generated by Eureka AI based on patent content.

Smart Images

  • Figure CN118966172A_ABST
    Figure CN118966172A_ABST
Patent Text Reader

Abstract

The invention provides a geotechnical engineering investigation data standardization extraction method, belongs to the technical field of geotechnical engineering investigation, and is used for standardization of geotechnical engineering investigation data in an urban geotechnical geological information system. The method comprises the following steps: S1, establishing a geotechnical engineering investigation report standard database, and taking a data table as a database platform; s2, performing unified naming on the geotechnical engineering investigation report according to an engineering project name after text processing, wherein the naming format is an XX project investigation report; s3, converting survey report data into a module, exporting all attribute tables in the survey report processed in the step S2 into data table format files, and automatically naming the data table format files one by one; s4, merging the data table into the standard data document of the data table in the step S1; and S5, matching the survey data, and completing standardization and data extraction of the survey data. The method is simple in steps, convenient to use, high in engineering applicability, rapid and convenient in data extraction and high in precision.
Need to check novelty before this filing date? Find Prior Art

Description

Technical Field

[0001] The invention belongs to the technical field of geotechnical engineering investigation, and in particular relates to a method for standardizing and extracting geotechnical engineering investigation data. Background Art

[0002] Against the backdrop of the continuous development of computer graphics technology and geological research, the iterative innovation of related technologies and software has continuously promoted the in-depth development of 3D visualization of underground geotechnical information. In line with this trend, digital urban geotechnical information systems and related application platforms have gradually emerged, aiming to provide strong support for the efficient management and scientific decision-making of urban underground space. Since 2015, Tianjin has taken the lead and actively promoted the construction of urban geotechnical information systems, marking the entry of such technology applications into the practical stage.

[0003] However, as a megacity with complex geotechnical structures, Tianjin faces severe challenges in promoting the construction of an urban geotechnical geological information system. On the one hand, the huge amount of geotechnical engineering survey drilling data has greatly increased the difficulty of data integration and application. In addition, the data formats submitted by different survey units are different, which seriously hinders the establishment of a standardized geotechnical engineering survey database and becomes a key bottleneck restricting the in-depth development and wide application of geotechnical engineering information systems. On the other hand, the text formats of survey data submitted by different survey units are not the same, including word, PDF, WPS and other formats, making it difficult to efficiently integrate a large amount of scattered information into the information system platform. This situation forces engineering and technical personnel to adopt manual input, which is not only cumbersome and time-consuming, but also prone to errors, reducing work efficiency and increasing unnecessary workload.

[0004] In view of the above difficulties, it is particularly urgent to develop a standardized solution that can not only meet the requirements of urban geotechnical information systems but also automatically process geotechnical engineering survey data in different language versions. Summary of the invention

[0005] The problem to be solved by the present invention is to provide a standardized extraction method for geotechnical engineering survey data, which solves the problems in the above background technology, has concise steps, is easy to use, has strong engineering applicability, fast and convenient data extraction, and high accuracy. It is particularly suitable for standardization and extraction of survey report data in the construction of urban rock and soil geological information system.

[0006] In order to solve the above technical problems, the technical solution adopted by the present invention is: a method for standardizing and extracting geotechnical engineering survey data, which is used for standardizing geotechnical engineering survey data in an urban geotechnical geological information system, and comprises the following steps;

[0007] S1. Establish a standard database for geotechnical engineering investigation reports, with unique project stratum numbers and data tables as the database platform, and establish a database of project basic information tables, stratum basic information tables, foundation soil stratum physical and mechanical statistical index information tables and geotechnical engineering evaluation recommended parameter tables required by the urban geotechnical geological information system;

[0008] S2. Text processing of geotechnical engineering investigation report, in preparation for the unified data table later, extract the relevant table data in step S1 of the text version of geotechnical engineering investigation report, save it into a newly created word document, and uniformly name it according to the name of the engineering project, with the naming format of XX project investigation report;

[0009] S3, survey report data conversion, export all attribute tables in the survey report processed in step S2 into data table format files, create a new separate table for each table, and automatically name them one by one, and ensure that all data in the survey report are generated into tables through loop traversal and conditional judgment;

[0010] S4, data table merging, merging all data table format files in a separate folder of a certain engineering survey report in step S3 into the data table standard data document in step S1;

[0011] S5. Survey data matching, matching operations on key fields, setting conditional areas and judgment conditions, setting summation areas, and specifying matching and summation, completing data merging and saving. In the merged table document, match the survey information in each individual table to the standard database of geotechnical engineering survey reports in step S1, completing the standardization of survey data and data extraction.

[0012] 4. The method for standardized extraction of geotechnical engineering survey data according to claim 1 is characterized in that: in step S1, an object identifier and an object number are set, linked to each city engineering geological information platform, and the information includes the following: (1) a basic information table of the project: including the borehole number, project name, construction unit, work number, and stratum name, and the information covers the context and background of the survey report;

[0013] (5) Basic characteristics information table of strata: including geological age and origin, color, moisture, state, density and compressibility, which is the basic description of geological structure in geotechnical engineering investigation;

[0014] (6) Information table of physical and mechanical statistical indicators of foundation soil layer: including 8 basic physical property indicators such as water content, density, porosity, 2 permeability coefficient indicators, 4 shear indicators, 3 standard penetration indicators and 2 in-situ test indicators, covering the foundation soil layer indicators in the survey report;

[0015] (7) Geotechnical engineering evaluation recommended parameter table: Contains 1 foundation soil bearing capacity index, 4 pile foundation indexes, and 5 foundation pit design indexes. These are the design parameters for selecting foundation treatment methods, and the design of pile foundations and foundation pit support projects; they ensure data standardization and consistency.

[0016] 5. The standardized extraction method for geotechnical engineering survey data according to claim 1 is characterized in that: in step S2, the geotechnical engineering survey report data table extraction adopts the "kimi" AI intelligent tool, opens a new session - uploads the survey report - inputs "traverse all tables in the file and extract all data in the table".

[0017] 4. The standardized extraction method for geotechnical engineering survey data according to claim 1 is characterized in that: in step S3, there is no need to pre-process the data or eliminate the data, and the conversion process includes: opening the word version of the XX project survey report in step S2 - opening Visual Basic - clicking to run the data conversion module to complete the operation of converting all tables in the word document into Excel.

[0018] 5. The standardized extraction method for geotechnical engineering survey data according to claim 1 is characterized in that: the data table is Excel, and in step S4, it is set to merge .xls or .xlsx format files, and the EXCEL Smart Office plug-in merge workbook function is used, and each .xls or .xlsx format file name is the name of each sheet of the merged EXCEL document.

[0019] 6. The method for standardizing and extracting geotechnical engineering survey data according to claim 1 is characterized in that: step S4 uses the Excel Wisdom Office plug-in to merge data, comprising the following steps: ① Start the Excel Wisdom Office plug-in: open the Excel application, load the Wisdom Office plug-in to enhance its data processing capability;

[0020] ② Load the standard database table: load the standard database Excel data table in step S1 into Excel and open it;

[0021] ③ Use the merge function: open the "Merge Workbooks" function provided by the Hui Office plug-in, and add the multiple Excel data tables converted in step S3;

[0022] ④ Set the merge parameters: Select "Name each merged sheet with the sheet name" and adjust the order to determine the arrangement and position of each sheet in the merged document;

[0023] ⑤Execute the merge operation: Click the "Start Merging" button, and the Hui Office plug-in will automatically complete the table merging work. The .xls or .xlsx format file names after merging are the names of the sheets in the merged EXCEL document;

[0024] ⑥ Check the merge results: After the merge is completed, check the completeness and accuracy of the data to ensure that all necessary data is included;

[0025] ⑦ Adjust the format and layout: According to the standard database format requirements, adjust the format and layout of the merged data to meet the requirements of the standard database;

[0026] ⑧Save the merged document: Save the merged data document as a new Excel file for use in subsequent steps.

[0027] 7. The method for standardized extraction of geotechnical engineering survey data according to claim 1 is characterized in that: in step S5, the data matching method adopts the SUMIF function provided by Excel, the condition area is the cell where "stratum, physical and mechanical statistical indicators and geotechnical engineering evaluation recommended parameters" are located, and the judgment condition is "stratum number".

[0028] 8. The method for standardizing and extracting geotechnical engineering survey data according to claim 1 is characterized in that: step S5 includes accurate matching of survey data, including the following steps: ① data preparation: loading and opening the data table merged in step S4; ② matching algorithm application: using Excel's SUMIF function to perform matching operations on key fields; ③ determining matching keys: determining key fields for matching, using "stratum number" as the basis for matching data in each sheet; ④ setting conditional areas and judgment conditions: defining conditional areas in Excel, the conditional areas are "stratum, physical and mechanical statistical indicators and geotechnical engineering evaluation construction"; ⑤ Set the summing area: according to the conditional area, set the data column associated with the conditional area; ⑥ Execute matching and summing: Excel will automatically execute the SUMIF function to sum the data corresponding to each key field that meets the conditions; ⑦ Data verification: verify the matching results to ensure the accuracy and consistency of the data; ⑧ Exception handling: identify and re-match the exceptions or error prompts that appear during the matching process; ⑨ Save the results: save the matching and summing results and update them to form a new standard stratum extraction database, which means that the standardized extraction of the survey report is completed.

[0029] 9. The method for standardized extraction of geotechnical engineering survey data according to claim 8 is characterized in that: the data matching process follows the following principles: ① uniqueness principle: ensure the uniqueness of the matching key to achieve accurate data correspondence; ② format consistency principle: ensure that the data involved in the matching is consistent with the corresponding fields in the standard database in terms of format and data type; ③ integrity principle: ensure that the data fields involved in the matching process are comprehensive and without omissions. ④ accuracy principle: strictly check the matching results to ensure the accuracy of the data.

[0030] 10. The method for standardized extraction of geotechnical engineering survey data according to claim 8 is characterized in that the data matching process also includes the following steps: ① Error handling mechanism: timely respond to abnormal situations in the matching process and readjust the matching; ② Log recording: record operations and abnormalities in the matching process; ③ Data security: before performing data matching, back up the original data and database to ensure data security.

[0031] Compared with the prior art, the present invention has the following advantages and positive effects.

[0032] 1. The present invention establishes a standardized database and an automated data extraction process, which covers a wide range of accurate data, provides solid data support for engineering decision-making, reduces manual operations, improves accuracy, simplifies the processing process, and improves data processing efficiency. The steps are concise, easy to use, and highly applicable to engineering projects. The extraction of survey data is fast, convenient, and accurate. Accurate data is crucial for evaluating geological conditions, designing infrastructure, and ensuring engineering safety. Through automated data matching and merging technology, human errors are reduced, data accuracy is improved, and design errors and potential safety risks caused by inaccurate data are avoided.

[0033] 2. The present invention covers all the core data of the survey report. By using data tables as a unified database platform, a unified standard database for geotechnical engineering survey reports is established, which solves the problem of different data formats of different survey units, improves the compatibility and systematicness of the data, and is conducive to building a standardized geotechnical engineering information database.

[0034] 3. The present invention automatically extracts data through AI intelligent tools, converts it into a standard format, merges tables, sets algorithms, and matches by locking key fields to ensure the accuracy and consistency of data. Compared with manual operation and manual input, the workload of manual operation is reduced, the data processing time is greatly shortened, and the data processing time is significantly reduced. The speed and efficiency of data processing are significantly improved, and the workload and output are increased.

[0035] 4. The present invention is based on the widely used Excel platform, with concise operation steps, friendly interface, and low learning cost for engineering and technical personnel. It is a combination of conventional software and practical application, breaking through conventional limitations and realizing a new application of known software. Moreover, by combining with reality, locking keywords, and completing automatic data extraction and standardization, it is suitable for a wide range of geotechnical engineering survey projects and has the characteristics of practicality, ease of use and universality.

[0036] 5. The invention can provide comprehensive multi-source data, and the constructed database covers the key information usually required for three-dimensional visualization of strata, and adds 2 permeability coefficient indicators, 3 standard penetration indicators, 2 in-situ test indicators, 1 foundation soil bearing capacity indicator, 4 pile foundation indicators, 5 foundation pit design indicators, etc. These indicators basically cover all foundation soil layer indicators in the current domestic survey report, and are important design parameters for the selection of foundation treatment methods, pile foundation and foundation pit support engineering design, and can ensure the standardization and consistency of data, thereby facilitating data comparison and integration between different projects and survey units, and is used to construct an accurate digital model of underground geological structure, providing a solid data foundation for three-dimensional visualization of geological information, helping to improve the scientificity and efficiency of urban planning, construction and management, and promoting the construction and application of urban rock and soil geological information systems. It can be promoted and applied in the development of urban rock and soil geological information systems and application platforms, which is of great significance. BRIEF DESCRIPTION OF THE DRAWINGS

[0037] The accompanying drawings constituting a part of the present invention are used to provide a further understanding of the present invention. The exemplary embodiments of the present invention and their descriptions are used to explain the present invention and do not constitute an improper limitation of the present invention. In the accompanying drawings:

[0038] Figure 1 It is a flow chart of the method for standardizing and extracting geotechnical engineering survey data of the present invention;

[0039] Figure 2 It is a project information diagram of the method for standardizing the extraction of geotechnical engineering survey data of the present invention;

[0040] Figure 3 It is a basic information diagram of the stratum of the standardized extraction method of geotechnical engineering survey data of the present invention;

[0041] Figure 4 It is a statistical index information diagram of physical mechanics of foundation soil layer in the standardized extraction method of geotechnical engineering survey data of the present invention;

[0042] Figure 5 It is a geotechnical engineering evaluation suggested parameter diagram of the geotechnical engineering investigation data standardization extraction method of the present invention;

[0043] Figure 6It is a completed diagram of the text processing of the geotechnical engineering investigation report of the method for standardized extraction of geotechnical engineering investigation data of the present invention;

[0044] Figure 7 The table in the survey report of the geotechnical engineering survey data standardization extraction method of the present invention is converted into an Excel file result diagram;

[0045] Figure 8 It is a workbook merging result diagram of the geotechnical engineering investigation data standardization extraction method of the present invention;

[0046] Fig. 9 It is the final result diagram of the geotechnical engineering investigation data standardization extraction method of the present invention;

[0047] Fig.10 It is a logic diagram of the data conversion module of the present invention. DETAILED DESCRIPTION

[0048] It should be noted that, in the absence of conflict, the embodiments of the present invention and the features in the embodiments may be combined with each other.

[0049] In the description of the present invention, it should be understood that the terms "center", "longitudinal", "lateral", "up", "down", "front", "back", "left", "right", "vertical", "horizontal", "top", "bottom", "inside", "outside" and the like indicate positions or positional relationships based on the positions or positional relationships shown in the accompanying drawings, and are 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 cannot be understood as limiting the present invention. In addition, the terms "first", "second", and the like are only used for descriptive purposes, and cannot be understood as indicating or implying relative importance or implicitly indicating the number of technical features indicated. Thus, features defined as "first", "second", and the like may explicitly or implicitly include one or more of the features. In the description of the present invention, unless otherwise specified, "multiple" means two or more.

[0050] In the description of the present invention, it should be noted that, unless otherwise clearly specified and limited, the terms "installed", "connected", and "connected" should be understood in a broad sense, for example, it can be a fixed connection, a detachable connection, or an integral connection; it can be a mechanical connection or an electrical connection; it can be a direct connection, or it can be indirectly connected through an intermediate medium, or it can be the internal communication of two components. For ordinary technicians in this field, the specific meanings of the above terms in the present invention can be understood by specific circumstances.

[0051] The specific embodiments of the present invention are described in detail below with reference to the accompanying drawings.

[0052] The method for standardizing and extracting geotechnical engineering survey data is used for standardizing geotechnical engineering survey data in an urban geotechnical geological information system, and comprises the following steps;

[0053] S1. Establish a standard database for geotechnical engineering investigation reports, using data tables as the database platform, and establish a database of basic project information tables, basic stratum information tables, physical and mechanical statistical index information tables of foundation soil layers, and geotechnical engineering evaluation recommended parameter tables required for the urban geotechnical geological information system;

[0054] S2. Text processing of geotechnical engineering investigation report, in preparation for the unified data table later, extract the relevant table data in step S1 of the text version of geotechnical engineering investigation report (pdf, word, wps and other common text formats), save it to a newly created word document, and uniformly name it according to the name of the engineering project, with the naming format of XX project investigation report;

[0055] S3. Survey report data conversion: Based on the VBA language in the text version of the survey report, write a data conversion module to export all attribute tables in the survey report processed in step S2 into data table format files, and automatically name them one by one;

[0056] S4, data table merging, merging all data table format files in a separate folder of a certain engineering survey report in step S3 into the data table standard data document in step S1;

[0057] S5. Survey data matching: In the merged table document, the survey information in each individual table is matched to the standard database of geotechnical engineering survey report in step S1 to complete the standardization and data extraction of survey data.

[0058] Preferably, the text version is in common text formats such as pdf, word, and wps. In step S2, the data table extraction of the geotechnical engineering investigation report adopts the "kimi" AI intelligent tool, and the steps are: open a new session - upload the investigation report - enter "traverse all tables in the file and extract all data in the table". Other intelligent tools that can realize table traversal and data extraction in the file are also available. The purpose is to prepare for the subsequent unification of data tables, so that the text content of the text version of the geotechnical engineering investigation report is deleted, and only the tables and their data in the report are retained.

[0059] The advantages of the automation process of this patent are as follows: (1) Data identification and extraction: Use the "kimi" AI intelligent tool to automatically identify and extract table data in PDF, Word, WPS and other format documents. (2) Data conversion: Through the data conversion module in VBA, the identified data is converted into a standardized Excel format, and a separate workbook is automatically created for each table. (3) Data merging: Use the "Merge Workbooks" function of the Excel Smart Office plug-in to merge multiple Excel workbooks into a unified data document. (4) Data matching: Apply Excel's SUMIF function matching algorithm to match key fields to ensure data accuracy and consistency. Compared with manual input, the invention greatly shortens the data processing time; previously, manually processing 10 survey reports involved about 100 tables, requiring 5 people, each working 8 hours a day, and a total of about 10 days to complete data input and verification. In contrast, the automated process of the present invention also processes about 100 tables of 10 survey reports, and only requires 2 people to perform data input and final verification respectively, and the entire process can be completed within 2 hours. This significantly reduces data processing time, improves efficiency, and increases workload and output. In addition, the data matching and verification performed by the automated process of the present invention reduces data entry errors caused by human operation and ensures the accuracy and consistency of the input data. There are more or less input errors in the survey report data entered manually. Usually, the number of data errors manually entered in each engineering survey report is about 5. For the construction of an urban geological information system that requires nearly a thousand engineering survey reports, a large number of data entry errors have a huge impact on the data security, data analysis and data decision-making of the subsequent urban geotechnical information system. For example, errors in key indicators such as the physical and mechanical properties of the strata and water content may lead to incorrect assessments of geological conditions, thereby affecting the safety of engineering design and construction; decision-making based on erroneous data may lead to waste of resources, project delays, and even safety accidents. The method provided by the present invention ensures the accuracy and consistency of the input data, and can provide solid and reliable data support for the construction and application of urban geotechnical information systems, which is of great significance.

[0060] Preferably, the data table is Excel, and in step S4, it is set to merge files in .xls or .xlsx format, and the workbook merge function of the EXCEL Smart Office plug-in is used. Each .xls or .xlsx format file name is the name of each sheet of the merged EXCEL document. Other plug-ins or software that can merge Line 4 tables can be used without specific limitation, and the main function of completing the table merging is to complete it.

[0061] Preferably, in step S5, the data matching method uses the SUMIF function provided in Excel, the condition area is the cell where the "stratum, physical and mechanical statistical indicators and geotechnical engineering evaluation recommended parameters" are located, and the judgment condition is the "stratum number".

[0062] Preferably, in step S1, it involves establishing a standard database for geotechnical engineering investigation reports. This database is based on an EXCEL data table and contains the following types of information:

[0063] (1) Project basic information table: including basic information such as drilling number, project name, construction unit, work number, and formation name. This information covers the context and background of the survey report.

[0064] (2) Basic characteristics information table of strata: including geological age and origin, color, humidity, state, density, compressibility and other information, which are the basic descriptions of geological structure in geotechnical engineering survey.

[0065] (3) Information table of physical and mechanical statistical indicators of foundation soil layers: It includes eight basic physical property indicators such as water content, density, porosity, two permeability coefficient indicators, four shear indicators, three standard penetration indicators and two in-situ test indicators. These indicators basically cover all foundation soil layer indicators in the survey report and are crucial for evaluating the engineering performance of the soil layer.

[0066] (4) Geotechnical engineering evaluation recommended parameter table: It includes 1 foundation soil bearing capacity index, 4 pile foundation indexes, and 5 foundation pit design indexes. These indexes are important design parameters for the selection of foundation treatment methods, pile foundation and foundation pit support engineering design, and are of great significance to ensuring the quality and safety of engineering results.

[0067] The contents of the four tables basically contain the basic data in the current domestic survey reports, and can ensure the standardization and consistency of the data, so as to facilitate the comparison and integration of data between different projects and survey units. In general, the standard database has the following advantages: (1) Consistency: The database can ensure that all data are entered in a unified format and standard to facilitate data comparison and analysis. (2) Scalability: The standard database design can add columns to the above four tables separately to provide interfaces for new parameters or new data types that may be added in the future. (3) Ease of use: The standard database is easy for engineering and technical personnel to use, with a friendly interface and simple operation. (4) Accuracy: The overall design architecture of the database is based on the basic information table. Each project stratum number is unique, which can ensure the accuracy of each row of data and reduce input errors. (5) Innovation: Compared with conventional geotechnical databases, this database has added object identifiers and object numbers, which can be directly linked to various urban engineering geological information platforms, facilitating the construction of urban geotechnical geological information systems. (6) Extensiveness: The indicators of this database basically cover all foundation soil layer indicators in geotechnical survey reports, meet the basic requirements for the construction of urban geotechnical geological information systems at home and abroad, and can be widely promoted and applied.

[0068] Preferably, in step S2, the data table in the survey report does not need to be adjusted in style. The survey reports and data table styles of different survey units are different. They only need to contain content related to the table in step S1, which greatly improves the speed of standardized extraction of survey data. Regardless of whether the data table style deviates from the table in step S1, data matching can be performed. The matching method is detailed in step S5.

[0069] Preferably, in step S3, the conversion process mainly includes: opening the word version of the XX project investigation report in step S2 - opening Visual Basic - clicking to run the data conversion module - running the sub-process / user form, so as to complete the operation of converting all tables in the word document into excel, and create a new separate Excel workbook for each table in the word file; through loop traversal, conditional judgment and other condition settings, the table data in all documents can be fully extracted and saved. Other modules that can realize this function can also be used without specific limitation. The logic diagram of the data conversion module is as follows: Fig.10 shown.

[0070] The conversion process does not require pre-processing of data or elimination of data. The conversion process mainly involves basic file operations. The following principles are followed as a whole: (1) Automation: The entire process is basically automated, and data extraction does not involve manual intervention. (2) Accuracy: Ensure the accuracy and integrity of data during the conversion process. (3) Consistency: Maintain the format and relationship of the original data.

[0071] Preferably, step S4 involves the step of using the Hui Office plug-in of Excel to merge data. The following is a detailed description of this step and the handling of special cases: ① Start the Excel Hui Office plug-in: Open the Excel application and load the Hui Office plug-in to enhance its data processing capabilities. ② Load the standard database table: Load the standard database Excel data table (.xls or .xlsx file) in step S1 into Excel to open. ③ Use the merge function: Open the "Merge Workbook" function provided by the Hui Office plug-in, and add multiple Excel data tables (.xls or .xlsx files) converted in step S3. ④ Set the merge parameters: Select "Name each merged sheet with the sheet name" and adjust the order to determine the arrangement and position of each table in the merged document. ⑤ Perform the merge operation: Click the "Start Merge" button, the Hui Office plug-in will automatically complete the merge of the tables, and the merged .xls or .xlsx format file names are the names of each sheet of the merged EXCEL document. ⑥ Check the merge results: After the merge is completed, check the integrity and accuracy of the data to ensure that all necessary data are included. ⑦ Adjust the format and layout: According to the standard database format, adjust the format and layout of the merged data to meet the requirements of the standard database. ⑧ Save the merged document: Save the merged data document as a new Excel file for use in subsequent steps.

[0072] Preferably, step S5 involves the precise matching process of the survey data, which specifically includes the following key steps, matching principles and special considerations: (1) Description of key steps in the matching process: ① Data preparation: Load and open the data table merged in step S4. ② Matching algorithm application: Use Excel's SUMIF function to perform matching operations on key fields. ③ Determine the matching key: Determine the key field used for matching, and use "stratum number" as the basis for matching the data in each sheet. ④ Set the conditional area and judgment condition: Define the conditional area in Excel, the conditional area is the cell where "stratum, physical and mechanical statistical indicators and geotechnical engineering evaluation recommended parameters" are located, and the judgment condition is "stratum number". ⑤ Set the summation area: According to the conditional area, set the data column associated with the conditional area, such as the conditional area is "geological age and genesis, color, humidity, state, density, and compressibility index of standard stratum 1-1" in sheet 1, and the summation area is "geological age and genesis, color, humidity, state, density, and compressibility index of standard stratum 1-1 in the standard database sheet". ⑥ Execute matching and summing: Excel will automatically execute the SUMIF function to sum the data corresponding to each key field that meets the conditions. ⑦ Data verification: Verify the matching results to ensure the accuracy and consistency of the data. ⑧ Exception handling: Identify and re-match the exceptions or error prompts that appear during the matching process. ⑨ Save the results: Save and update the matching and summing results to form a new standard formation extraction database, which means that the standardized extraction of the survey report is completed. (2) Data matching principles: ① Uniqueness principle: Ensure the uniqueness of the matching key to achieve accurate data correspondence. ② Format consistency principle: Ensure that the data involved in the matching is consistent with the corresponding fields in the standard database in terms of format and data type. ③ Integrity principle: Ensure that the data fields involved in the matching process are comprehensive and without omissions. ④ Accuracy principle: Strictly verify the matching results to ensure the accuracy of the data. (3) Special considerations: ① Error handling mechanism: Respond to abnormal situations in the matching process in a timely manner and readjust the matching. ② Log recording: Record operations and exceptions in the matching process to facilitate problem tracking and system maintenance. ③Data security: Before data matching, back up the original data and database to ensure data security.

[0073] The effects are as follows: (1) Unified data platform: By using data tables as a unified database platform, the present invention establishes a standardized storage and processing method for various types of geotechnical survey information required by the urban geotechnical geological information system. (2) Contains necessary information: The standard database includes a basic project information table, a basic stratum information table, a foundation soil stratum physical and mechanical statistical index information table, and a geotechnical engineering evaluation recommended parameter table, which almost covers all the core data of the survey report. (3) Compatibility and systematicity: By establishing a unified standard, the compatibility of data is improved, so that data from different sources and formats can be systematically integrated and analyzed, ensuring that data submitted by different survey units can be used in the construction of the geological platform information system, solving the problem of different data formats (word, WPS, PDF, etc.) of different units and different paper documents. (4) Scalability: Since the database design of the present invention is scalable, it can not only support the processing of word, WPS, PDF and other formats, but also support the import of data in other formats by adding corresponding data conversion modules or plug-ins, further improving the versatility and applicability of the database.

[0074] In view of the importance of geotechnical engineering survey data for urban planning and construction, the automation and standardization method provided by the present invention not only improves the efficiency and accuracy of data processing, but also provides solid data support for engineering decision-making. The specific effects are described as follows: (1) Concise steps and easy use: The present invention reduces the cumbersome manual input and data processing steps in traditional methods by establishing a standardized database and an automated data extraction process, thereby simplifying the entire survey data processing process. (2) Strong engineering applicability: The standardized database and automated process are particularly suitable for the construction of urban geotechnical geological information systems, and can meet the needs of rapid processing and analysis of survey data. (3) Fast and convenient data extraction: Using automated tools and methods, such as VBA programming and Excel functions, data can be quickly extracted from survey reports in various formats, which greatly shortens the data processing time compared to manual input. Previously, manual data processing may take several days, while the automated process of the present invention can complete the same work in a few minutes or hours, greatly improving work efficiency. (4) High accuracy: In geotechnical engineering, accurate data is crucial for evaluating geological conditions, designing infrastructure, and ensuring engineering safety. The present invention reduces human errors and improves data accuracy through automated data matching and merging technology. For example, using the SUMIF function for data matching ensures accurate correspondence and high consistency of data. Design errors and potential safety risks caused by inaccurate data can be avoided.

[0075] This example performs standardized extraction of geotechnical engineering survey data based on the geological conditions of a specific area in Tianjin. The geological structure of this area is complex, containing multiple layers of strata of different ages. This article takes the data extraction of the K2019-0127 survey results report as an example. The specific implementation steps are as follows:

[0076] 1. Establish a standard database for geotechnical engineering investigation reports, use Excel data tables as the database platform, and establish the basic project information tables required for the urban geotechnical information system (see Figure 2 ), basic information table of strata (see Figure 3 ), the physical and mechanical statistical index information table of the foundation soil layer (see Figure 4 ) and the recommended parameters table for geotechnical engineering evaluation (see Figure 5 ) database;

[0077] 2. Text processing of geotechnical engineering investigation report, extract relevant table data from the text version of geotechnical engineering investigation report (K2019-0127 Investigation Results Report.pdf), save it to a newly created word document, and name it uniformly according to the name of the engineering project. The naming format is K2019-0127 Project Investigation Report; the extraction process is as follows: open the "kimi" AI smart tool - start a new session - upload the investigation report - enter "traverse all tables in the file and extract all data in the table" - copy and save to the new word.

[0078] 3. Survey report data conversion. The specific steps are to open the K2019-0127 project survey report completed in the previous step - open Visual Basic - write a data conversion module - click Run - Run the sub-process / user form to complete the conversion of all tables in the word document to Excel, and create a new separate Excel workbook for each table in the word file, and automatically name them one by one; the data conversion module involves basic programming logic such as loop traversal and conditional judgment. The VBA language conversion structure model is shown in Figure 6 ; The results of converting the tables in the survey report into excel files with automatic naming are shown in Figure 7 .

[0079] 4. Data merging: Use the "Merge Workbooks" function of the Excel Hui Office plug-in to merge all workbooks into a unified data document. The specific steps are as follows: ① Start the Excel Hui Office plug-in: Open the Excel application and load the Hui Office plug-in. ② Load the standard database table: Load the standard database Excel data table (.xlsx file) in step 1 into Excel to open. ③ Use the merge function: Open the "Merge Workbooks" function provided by the Hui Office plug-in, and add the 14 Excel data tables (.xlsx files) converted in step 3. ④ Set the merge parameters: Select "Name each merged sheet with the sheet name" and adjust the order. ⑤ Perform the merge operation: Click the "Start Merging" button, and the Hui Office plug-in will automatically complete the table merging work. The merged .xlsx format file names are the names of the sheets of the merged EXCEL document. ⑥ Check the merge results: Check the integrity and accuracy of the merged data to ensure that all necessary data is included. ⑦ Adjust the format and layout: According to the standard database format requirements, adjust the merged data format and layout to meet the requirements of the standard database. ⑧Save the merged document: Save the merged data document. The workbook result after data merging and saving is shown in Figure 8 .

[0080] 5. Survey data matching: In the table document merged in step 4, match the survey information in all sheets to the standard database of geotechnical engineering survey report. The key steps of the matching process are as follows: ① Data preparation: Open the data table merged in step 4. ② Matching algorithm application: Use Excel's SUMIF function to match the key fields. ③ Determine the matching key: Use "stratum number" as the basis for matching the data in each sheet. ④ Set the conditional area and judgment condition: Define the conditional area in Excel. The conditional area is the cell where "stratum, physical and mechanical statistical indicators and geotechnical engineering evaluation recommended parameters" are located, and the judgment condition is "stratum number". ⑤ Set the summation area: According to the conditional area, set the data column associated with the conditional area. For example, if the conditional area is "geological age and genesis, color, humidity, state, density, and compressibility index of standard stratum 1-1" in sheet 1, the summation area is "geological age and genesis, color, humidity, state, density, and compressibility index of standard stratum 1-1 in the standard database sheet". ⑥ Execute matching and summing: Excel will automatically execute the SUMIF function to sum the data corresponding to each key field that meets the conditions. ⑦ Data verification: Verify the matching results to ensure the accuracy and consistency of the data. ⑧ Exception handling: Identify and re-match the exceptions or error prompts that appear during the matching process. ⑨ Save results: Save and update the matching and summing results to form a new standard stratum extraction database, which means that the standardized extraction of the survey report is completed. The final results of the extraction of this project are shown in Fig. 9 .

[0081] The invention effectively promotes the construction and application of urban rock and soil geological information systems, provides a solid data foundation for the three-dimensional visualization of geological information, helps to improve the scientificity and efficiency of urban planning, construction and management, and can be promoted and applied in the development of urban rock and soil geological information systems and application platforms. The three-dimensional visualization of strata is a complex process, which requires the integration of multi-source data to construct an accurate digital model of underground geological structures. The key information usually required for three-dimensional visualization of strata is: (1) Stratigraphic distribution data: the spatial distribution of different strata. In three-dimensional information visualization, the distribution is generally determined by whether a certain soil layer exists at the drilling point. (2) Geological age and genesis: the age and genetic mechanism of stratum formation, which helps to understand the nature of the stratum. (3) Stratigraphic lithology: the rock and soil type of the stratum, such as mixed fill, plain fill, etc. Different lithologies affect the physical and mechanical properties of the stratum. (4) Stratigraphic physical property parameters: including density, porosity, water content, compressibility, etc. These parameters affect the engineering properties of the stratum. (5) Stratigraphic mechanical parameters: such as compression modulus, shear strength, etc. These parameters are very important for engineering structure design.

[0082] The database constructed by the present invention covers all the above information, and adds 2 permeability coefficient indicators, 3 standard penetration indicators, 2 in-situ test indicators, 1 foundation soil bearing capacity indicator, 4 pile foundation indicators, 5 foundation pit design indicators, etc. These indicators basically cover all foundation soil layer indicators in the current domestic survey report, and are important design parameters for the selection of foundation treatment methods, pile foundation and foundation pit support engineering design, and can ensure the standardization and consistency of data, thereby facilitating data comparison and integration between different projects and survey units, which is of great significance to the construction and application of urban rock and soil geological information systems.

[0083] The above is a detailed description of an embodiment of the present invention, but the content is only a preferred embodiment of the present invention and cannot be considered to limit the scope of implementation of the present invention. All equivalent changes and improvements made within the scope of the present invention should still fall within the scope of the patent coverage of the present invention.

Claims

1. A method for standardizing and extracting geotechnical engineering survey data, which is used for standardizing geotechnical engineering survey data in an urban geotechnical geological information system, and is characterized by: The steps include: S1. Establish a standard database for geotechnical engineering investigation reports, with unique project stratum numbers and data tables as the database platform, and establish a database of project basic information tables, stratum basic information tables, foundation soil stratum physical and mechanical statistical index information tables and geotechnical engineering evaluation recommended parameter tables required by the urban geotechnical geological information system; S2. Text processing of geotechnical engineering investigation report, in preparation for the subsequent unified data table, extract the relevant table data in step S1 of the text version of geotechnical engineering investigation report, save it to the newly created document, and uniformly name it according to the name of the engineering project, with the naming format of XX project investigation report; S3, survey report data conversion, export all attribute tables in the survey report processed in step S2 into data table format files, create a new separate table for each table, and automatically name them one by one, and ensure that all data in the survey report are generated into tables through loop traversal and conditional judgment; S4, data table merging, merging all data table format files in a separate folder of a certain engineering survey report in step S3 into the data table standard data document in step S1; S5. Survey data matching, matching operations on key fields, setting conditional areas and judgment conditions, setting summation areas, and specifying matching and summation, completing data merging and saving. In the merged table document, match the survey information in each individual table to the standard database of geotechnical engineering survey reports in step S1, completing the standardization of survey data and data extraction.

2. The method for standardized extraction of geotechnical engineering survey data according to claim 1, characterized in that: In step S1, the object identifier and object number are set and linked to the engineering geological information platform of each city. The information includes the following: (1) Basic project information table: including drilling number, project name, construction unit, construction number, stratum name, and the information covers the context and background of the survey report; (2) Basic characteristics information table of strata: including geological age and origin, color, moisture, state, density and compressibility, which is the basic description of geological structure in geotechnical engineering investigation; (3) Information table of physical and mechanical statistical indicators of foundation soil layer: including 8 basic physical property indicators such as water content, density, porosity, 2 permeability coefficient indicators, 4 shear indicators, 3 standard penetration indicators and 2 in-situ test indicators, covering the foundation soil layer indicators in the survey report; (4) Geotechnical engineering evaluation recommended parameter table: Contains 1 foundation soil bearing capacity index, 4 pile foundation indexes, and 5 foundation pit design indexes. These are the design parameters for selecting foundation treatment methods, pile foundations, and foundation pit support engineering designs; they ensure data standardization and consistency.

3. The method for standardized extraction of geotechnical engineering survey data according to claim 1, characterized in that: In step S2, the data table extraction of the geotechnical engineering investigation report adopts the "kimi" AI intelligent tool, opening a new session - uploading the investigation report - entering "traverse all tables in the file and extract all data in the table".

4. The method for standardized extraction of geotechnical engineering survey data according to claim 1, characterized in that: In step S3, there is no need to pre-process the data or eliminate the data. The conversion process includes: opening the word version of the XX project investigation report in step S2 - opening Visual Basic - clicking to run the data conversion module to complete the operation of converting all tables in the word document into Excel.

5. The method for standardized extraction of geotechnical engineering survey data according to claim 1, characterized in that: The data table is Excel. In step S4, it is set to merge files in .xls or .xlsx format, and the workbook merge function of the EXCEL Wisdom Office plug-in is used. The file names of the .xls or .xlsx format files are the names of the sheets of the merged EXCEL document.

6. The method for standardized extraction of geotechnical engineering survey data according to claim 1, characterized in that: Step S4 uses the Excel Hui Office plug-in to merge data, including the following steps: ① Start the Excel Hui Office plug-in: open the Excel application and load the Hui Office plug-in to enhance its data processing capabilities; ② Load the standard database table: load the standard database Excel data table in step S1 into Excel and open it; ③ Use the merge function: open the "Merge Workbooks" function provided by the Hui Office plug-in, and add the multiple Excel data tables converted in step S3; ④ Set the merge parameters: Select "Name each merged sheet with the sheet name" and adjust the order to determine the arrangement and position of each sheet in the merged document; ⑤Execute the merge operation: Click the "Start Merge" button, and the Hui Office plug-in will automatically complete the table merging work. The .xls or .xlsx format file names after merging are the names of the sheets in the merged EXCEL document; ⑥ Check the merge results: After the merge is completed, check the completeness and accuracy of the data to ensure that all necessary data is included; ⑦ Adjust the format and layout: According to the standard database format requirements, adjust the format and layout of the merged data to meet the requirements of the standard database; ⑧Save the merged document: Save the merged data document as a new Excel file for use in subsequent steps.

7. The method for standardized extraction of geotechnical engineering survey data according to claim 1, characterized in that: In step S5, the data matching method uses the SUMIF function provided by Excel, the condition area is the cell where "stratum, physical and mechanical statistical indicators and geotechnical engineering evaluation recommended parameters" are located, and the judgment condition is "stratum number".

8. The method for standardized extraction of geotechnical engineering survey data according to claim 1, characterized in that: Step S5 includes accurate matching of survey data, including the following steps: ① Data preparation: loading and opening the data table merged in step S4; ② Matching algorithm application: Use Excel's SUMIF function to match key fields; ③ Determine the matching key: determine the key field used for matching, and use "stratum number" as the basis for matching the data in each sheet; ④ Set the conditional area and judgment condition: define the conditional area in Excel, the conditional area is the cell where "stratum, physical and mechanical statistical indicators and geotechnical engineering evaluation recommended parameters" are located, and the judgment condition is "stratum number"; ⑤ Set the summation area: according to the conditional area, set the data column associated with the conditional area; ⑥ Execute matching and summation: Excel will automatically execute the SUMIF function to sum the data corresponding to each key field that meets the conditions; ⑦ Data verification: verify the matching results to ensure the accuracy and consistency of the data; ⑧ Exception handling: identify and re-match the exceptions or error prompts that occur during the matching process; ⑨ Save the results: save and update the results of matching and summation to form a new standard stratum extraction database, which means that the standardized extraction of the survey report is completed.

9. The method for standardized extraction of geotechnical engineering survey data according to claim 8, characterized in that: The data matching process follows the following principles: ① Uniqueness principle: ensure the uniqueness of the matching key to achieve accurate data correspondence; ② Format consistency principle: ensure that the data involved in the matching is consistent with the corresponding fields in the standard database in format and data type; ③ Integrity principle: ensure that the data fields involved in the matching process are comprehensive and without omissions; ④ Accuracy principle: strictly verify the matching results to ensure the accuracy of the data.

10. The method for standardized extraction of geotechnical engineering survey data according to claim 8, characterized in that: The data matching process also includes the following steps: ① Error handling mechanism: timely respond to abnormal situations in the matching process and readjust the matching; ② Log recording: record operations and exceptions in the matching process; ③ Data security: before data matching, back up the original data and database to ensure data security.

Citation Information

Cited By

  • Mineral exploration drilling electronic map element sorting method, system and equipment and medium

    CN119810864A

  • Physical exploration workload statistical method and system

    CN119886899A