Underground pipeline whole-process multivariate database automatic conversion method and system

Abstract

The invention relates to the technical field of underground pipeline data conversion, in particular to an underground pipeline full-process multivariate database automatic conversion method and system, and the method comprises the following steps: S1, converting data in a field collection database into data in an interior processing database; s2, converting data in the interior work processing database into data in a file geographic database; and S3, converting the data in the interior work processing database into the data in the result table. The underground pipeline full-process multi-source database automatic conversion method provided by the invention has the beneficial effects that the problems that indoor and outdoor data formats and fields are inconsistent, and the efficiency of a traditional conversion mode is low are solved. An automatic conversion framework from DB to MDB to GDB to an achievement table is constructed by integrating three modules of field operation-interior operation conversion, interior operation-GIS conversion and achievement table generation.

Description

Technical Field

[0001] The present invention relates to underground pipeline data conversion, and in particular to an automatic conversion method and system for a full-process multivariate database of an underground pipeline. Background Art

[0002] With the continuous advancement of smart city construction and the deepening development of urban underground space resources, underground pipeline data has become a critical foundation for refined urban management and intelligent decision-making. To support the efficient application of underground pipeline data at all stages, including collection, processing, management, and output, the industry currently generally adopts a multi-source heterogeneous database architecture to organize and transfer data. The typical process involves translating data from an SQLite database (extension .db, hereinafter referred to as the "DB database") used for field collection, to a Microsoft Access database (extension .mdb, hereinafter referred to as the "MDB database") relied on for office editing, and then to a file geodatabase (extension .gdb, hereinafter referred to as the "GDB database") stored uniformly in the management platform. The final result is a table in formats such as Excel for archiving and reporting.

[0003] However, this multi-stage, multi-database conversion process has exposed numerous technical bottlenecks in actual engineering practice, severely restricting the efficiency and reliability of pipeline data management. Specifically, these bottlenecks are manifested in the following aspects: 1) There is field semantic heterogeneity between the field DB database and the office MDB database, resulting in a large workload for manual mapping: Due to the differences in field structure, naming rules, and data semantics defined by data acquisition systems from different manufacturers, the conversion process from the DB database to the MDB database usually requires manual field mapping and verification. On average, each project takes more than 40 working hours, which is inefficient and prone to consistency errors.

[0004] 2) The conversion from MDB database to GDB database relies on specific GIS software, and the conversion process is complex and error-prone: Currently, such conversion usually relies on professional software tools such as ArcGIS, and requires manual operation of multiple intermediate steps, including field matching, spatial projection verification, feature classification settings, etc. The process is cumbersome and has a high error rate, often resulting in attribute data loss or feature geometry distortion, and lacks automation and stability guarantees.

[0005] 3) The output table generation logic is tightly coupled to specific software templates, resulting in poor flexibility and prone to failure: Underground pipeline output tables are typically generated based on fixed templates that are deeply tied to specific mapping software. When project requirements change and adjustments to the template structure or fields are required, the existing system lacks a dynamic adaptation mechanism. Once the template version is updated, the original output logic is prone to failure, resulting in output errors or interruptions.

[0006] While existing ETL tools like FME attempt to provide some format conversion capabilities, they rely primarily on manually configured conversion rules, resulting in high maintenance costs and difficulty meeting the practical needs of dynamically updating underground pipeline data and frequently converting between multiple formats. Furthermore, most existing systems only support data format processing at specific stages and lack a unified processing framework covering the entire "DB→MDB→GDB→results table" process. This results in fragmented data flow between systems, long conversion chains, and prone to errors.

[0007] Therefore, there is an urgent need for a multi-source database intelligent conversion system with field semantic adaptation capabilities, format conversion automation capabilities, and output decoupling capabilities to achieve standardized, efficient, and robust processing of underground pipeline data in the multi-stage flow process, fundamentally solving the core problems of existing technologies such as high labor costs, complex processes, and poor output stability. Summary of the Invention

[0008] The technical problem to be solved by the present invention is to overcome the deficiencies in the prior art and provide a method and system for automatic conversion of a multivariate database of the entire process of an underground pipeline.

[0009] The present invention is achieved through the following technical solutions: A method for automatically converting a multivariate database of the entire process of an underground pipeline, the method comprising the following steps: S1, converting the data in the field collection database into the data in the office processing database; S2, converting the data in the office processing database into data in a file geodatabase; S3, converts the data in the internal processing database into data in the result table.

[0010] Preferably, the S1 comprises the following steps: S11, generating an internal processing database template according to a preset pipeline classification standard, and generating an empty internal processing database according to the internal processing database template; S12, determining the organizational form of the field data collection database. If the organizational form of the field data collection database is multi-table, executing S13; if the organizational form of the field data collection database is single-table, executing S14; S13, converting the data in the field acquisition database into data that meets the requirements of the office processing database according to a preset field semantic mapping rule library, and writing the converted data into an empty office processing database; S14, separating the data in the field collection database to generate a multi-table intermediate file, converting the data in the intermediate file into data that meets the requirements of the internal processing database according to the preset field semantic mapping rule library, and entering the data into the empty internal processing database.

[0011] Preferably, said S2 comprises the following steps: S21, constructing pipe point geometric entities and pipeline geometric entities according to the data in the internal processing database; S22, creating corresponding coordinate system metadata according to the coordinate information of the pipe point geometric entity and the coordinate information of the pipeline geometric entity, and setting projection parameters that meet the requirements of the file geographic database; S23, configuring a dynamic projection mechanism according to the projection parameters; S24, creating a spatial index for the pipe point geometric entity and the pipeline geometric entity; S25, constructing a geometric topological relationship between the pipe point geometric entity and the pipeline geometric entity; S26, importing the processed data into the file geodatabase.

[0012] Preferably, the step S3 includes the following steps: S31, formulating an achievement table template, and generating an empty achievement table according to the achievement table template; S32, converting the data in the internal processing database according to the field requirements of the result table template; S33, writing the converted data into an empty result table template.

[0013] A system for automatically converting a multivariate database of the entire process of an underground pipeline comprises a control module configured as the method for automatically converting a multivariate database of the entire process of an underground pipeline.

[0014] The beneficial effects of the present invention are: The present invention provides a method for automatic conversion of multi-source databases throughout the entire process of underground pipelines, solving the problems of inconsistent formats and fields of field and field data, and low efficiency of traditional conversion methods. By integrating three modules: field-to-field conversion, field-to-GIS conversion, and result table generation, an automated conversion framework from "DB→MDB→GDB→result table" is constructed. A dynamic field mapping mechanism and a unified interface strategy are used to achieve efficient compatibility and batch processing of data from different collection tools. This method effectively improves the accuracy and efficiency of data conversion, reduces manual intervention time, and has good promotion prospects. It is widely applicable to fields such as urban planning, underground pipeline management, and smart city construction, providing strong technical support for the standardization and intelligentization of underground space data. BRIEF DESCRIPTION OF THE DRAWINGS

[0015] Figure 1 It is the water supply field collection DB database of the present invention.

[0016] Figure 2It is the communication and power field data collection DB database in the embodiment of the present invention.

[0017] Figure 3 It is the internal MDB database template in the embodiment of the present invention.

[0018] Figure 4 It is a multi-source database DB→MDB conversion system in an embodiment of the present invention.

[0019] Figure 5 It is a multi-source database MDB→GDB conversion system in an embodiment of the present invention.

[0020] Figure 6 This is a multi-source database MDB→results table conversion system in an embodiment of the present invention.

[0021] Figure 7 This is the GDB database that has been converted in the embodiment of the present invention.

[0022] Figure 8 It is a result table in the embodiment of the present invention. DETAILED DESCRIPTION

[0023] In order to enable those skilled in the art to better understand the technical solution of the present invention, the present invention is further described in detail below with reference to the accompanying drawings and the best embodiment. Based on the embodiments of the invention, all other embodiments obtained by those skilled in the art without making any creative work shall fall within the scope of protection of the invention.

[0024] The present invention provides a method for automatically converting a multivariate database of the entire process of an underground pipeline, the method comprising the following steps: S1. Convert the data in the field collection database into data in the office processing database. In this embodiment, the field collection database is a SQLite database with a .db extension, also referred to as the "DB database." The office processing database is a Microsoft Access database with a .mdb extension, also referred to as the "MDB database."

[0025] This step S1 is applicable to processing various underground pipeline data types, including but not limited to: water supply (JS), drainage (PS), gas (RQ), heating (RL), electricity (DL), communication (TX), industry (GY) and other (QT) common municipal and industrial underground pipelines.

[0026] Among them, the field collection database includes attribute information of underground pipelines (such as pipe diameter, ownership unit, etc.) and location information (such as spatial coordinates, burial depth, etc.) to support mapping and spatial analysis.

[0027] The step S1 specifically includes the following sub-steps S11-S14: S11: Generate an internal processing database template based on the preset pipeline classification standard. Based on this internal processing database template, an empty internal processing database is generated as the target database for data conversion, and then S12 is executed. Specifically, the preset pipeline classification standard includes the field structure of the pipeline point table and the line table. The field settings include, but are not limited to, the following: Point table field set: includes spatial coordinate fields (X coordinate, Y coordinate, ground elevation), topological attribute fields (geophysical point number, pipeline subclass), facility parameter fields (well bottom depth, manhole cover material) and management attribute fields (ownership unit, detection date), etc.

[0028] Line table field set: covers pipeline topology (starting point object number, ending point object number), physical parameters (burial depth, pipe diameter section, pressure), engineering properties (burial method, casing size, number of cables), etc.

[0029] S12: Determine the organizational format of the field data collection database. If the field data collection database is organized in a multi-table format, execute S13; if the field data collection database is organized in a single-table format, execute S14. A single-table field data collection database stores all point features (such as manhole covers and manholes) of all pipe types in a table named "point," and linear features (such as pipeline segments) in a table named "line," regardless of pipe type. A multi-table field data collection database sets up data tables based on pipe type, with point and line tables named JS_point, JS_line, PS_point, and PS_line, respectively.

[0030] S13, according to the preset field semantic mapping rule library, convert the data in the field collection database into data that meets the requirements of the internal processing database, and write the converted data into an empty internal processing database, thereby completing the data conversion.

[0031] S14: Data in the field collection database is segmented according to the pipe category codes in the database to generate a multi-table intermediate file. Subsequently, based on a pre-defined field semantic mapping rule library, the data in the intermediate file is converted to data that meets the requirements of the internal processing database and entered into the empty internal processing database, completing the data conversion.

[0032] Due to differences in field naming, field number, and data organization between the field collection database and the office processing database, a preset field semantic mapping rule library is used to establish field semantic correspondences to ensure accurate field matching and reliable automated processing. This preset field semantic mapping rule library is stored in a field mapping configuration file. Specifically, the field mapping file is constructed in a Notepad format (such as a .txt file) and contains a set of field comparison rules that indicate the correspondence between the fields in the field collection database and the office processing database. For example, if a field in the office processing database is named "X coordinate" and a field in the field collection database is named "X," the mapping file sets the corresponding relationship as: X = X coordinate. During data conversion, the system reads this field mapping file and, using the preset field semantic mapping rule library, automatically migrates field data from the field collection database to the corresponding fields in the empty office processing database, achieving field-level data reconstruction and semantic adaptation. This mechanism not only accommodates differences in database structures between different vendors but also significantly improves the efficiency and accuracy of converting field data to the office system.

[0033] S2, converting the data in the office processing database into data in a file geodatabase. In this embodiment, the file geodatabase has a .gdb extension and can be referred to as a GDB database. This step S2 specifically includes the following sub-steps S21-S26: S21: Construct pipe point geometric entities and pipeline geometric entities based on the data in the internal processing database. Specifically, traverse and read the data tables of various pipeline facilities in the internal processing database, including the point table and line table corresponding to each pipe type. Extract the pipe point attribute information and spatial coordinate information from the point table. The spatial coordinate information specifically includes the X coordinate, Y coordinate and ground elevation fields of the point position, and construct the corresponding pipe point geometric entity based on this information. At the same time, extract the pipeline attribute information and spatial coordinate information from the line table. Specifically, establish the topological connection relationship between points based on the starting point number and ending point number fields, and construct the pipeline geometric entity accordingly. Then execute S22.

[0034] S22: Based on the coordinate information of the pipe point and pipeline geometric entities, corresponding coordinate system metadata is created and projection parameters are set that meet the requirements of the file geodatabase to ensure spatial data consistency. Preferably, the coordinate system includes the WGS84 coordinate system or the CGCS2000 coordinate system, but another system can be selected based on the actual acquisition standard. S23 is then executed.

[0035] S23, configuring a dynamic projection mechanism based on the projection parameters to achieve unified and standardized processing of the spatial references of the pipe point geometric entities and the pipeline geometric entities, ensuring the consistency of the pipe points and pipelines in the target coordinate system. Then, S24 is executed.

[0036] S24: Create a spatial index for the pipe point geometric entities and the pipeline geometric entities to improve the efficiency of subsequent retrieval and processing. Then, S25 is executed.

[0037] S25: Construct geometric topological relationships between the pipe point geometric entities and the pipeline geometric entities to describe their connectivity, adjacency, and intersection relationships, ensuring the spatial topological integrity and logical consistency of the data. S26 is then executed.

[0038] S26, importing the processed data into a file geodatabase to complete the data conversion.

[0039] S3, converting the data in the internal processing database into data in the results table. In this embodiment, the results table is Excel. This step S3 specifically includes the following sub-steps S31-S33: S31. Create a results table template and generate an empty results table based on it. This template includes the following attributes: Spatial attributes: These include X- and Y-coordinates, ground elevation, and bottomhole depth, which accurately describe the spatial location and vertical orientation of pipeline points. Topological attributes: These include point numbers (unique identifiers), connection point numbers, and feature point markers (used to distinguish turning points or branching points) to express the topological structure of the pipeline network. Engineering attributes: These include pipe diameters, compatible with various cross-sectional formats, such as circular DN300 and rectangular 600×800. Material information: These use enumerated values ​​to represent common materials such as PVC, cast iron, and concrete. Pressure / voltage levels: These use a graded approach, such as 0.4 MPa and 10 kV. Burial methods: These include direct burial, pipe corridor, or overhead. Management attributes: These include ownership units, uniquely identified by mapping to a unified social credit code. Construction date: These are stored in the ISO8601 standard format. The number of cables is defined as an integer constraint greater than or equal to 1. Pipe hole usage: These reflect actual usage by calculating the ratio of used to unused pipe holes. Then execute S32.

[0040] S32: Convert the data in the internal processing database according to the field requirements of the results table template. Furthermore, during the conversion process, establish a connection between the data in the internal processing database and the fields in the results table to ensure accurate data mapping and targeted conversion. Then, execute S33.

[0041] S33, writing the converted data into an empty result table template to achieve standardized output of pipeline engineering data.

[0042] The following uses underground pipeline data as an example to illustrate this method. Field data collection includes three types of pipelines: water supply, communication, and electricity. The water supply pipeline data is stored in a DB database with a multi-table structure. Among them: the JS_POINT table records water supply pipeline point information, such as manhole covers and valves; the JS_LINE table records pipeline connection information, including start point, end point, pipe diameter, material, and other attributes. Figure 1 Communication and power pipeline data are also stored in the form of a DB database, but the structure is a single table: datapoint table: unified storage of communication and power point information; dataline table: unified storage of communication and power line information, recording their starting and ending points, types, materials, etc. Figure 2 .

[0043] During the internal mapping process, data of different structures need to be converted to a standard MDB format database. The database structure is that point information is uniformly stored in the point table, and line information is uniformly stored in the line table. Figure 3 The specific field correspondence is shown in the following table:

[0044] Considering that there are two organizational forms of the acquisition database, namely: multi-table structure and single-table structure. Therefore, two different field mapping configuration files (such as .txt files in Notepad format) need to be set up separately for the system to identify and call, so as to realize automatic matching of fields and data conversion. The single-table DB database is the field mapping file for converting the communication and power database to the MDB database, specifically: Category = Pipeline Category, Point Name = Geophysical Point Number, North Coordinate = X Coordinate, East Coordinate = Y Coordinate, Starting Point Number = Starting Point Number, End Point Number = End Point Number, Starting Burial Depth = Starting Burial Depth, End Burial Depth = Ending Burial Depth, Material = Pipeline Material. The multi-table DB database is the field mapping file for converting the water supply database to the MDB database, specifically: Pipeline Subcategory = Pipeline Category, X Coordinate = X Coordinate, Y Coordinate = Y Coordinate, Starting Object Number = Starting Point Number, Ending Object Number = Ending Point Number. The mapping file contains the field equal sign. The left column is the DB database field, and the right column is the MDB database field. Refer to it when converting the communication and power DB database. Figure 4 , select single table, select the field mapping file, the system will recognize the field connection, and complete the programmatic data migration and conversion. When converting the corresponding water supply DB database, select multi-table, the system will recognize the field connection, and complete the conversion.

[0045] The fully converted MDB database contains six tables: JS_POINT, JS_LINE, DL_POINT, DL_LINE, TX_POINT, and TX_LINE. Figure 3 Keep the field attributes in the original table, read the coordinate information, and identify the coordinate system as CGCS2000. Figure 5 , set the MDB database coordinate system to realize the GIS storage of pipeline data. The GDB database includes: three point feature files: JS_POINT, DL_POINT, and TX_POINT; three line feature files: JS_LINE, DL_LINE, and TX_LINE. The fields are kept consistent with the MDB. Figure 6 .

[0046] Based on the converted MDB database, refer to Figure 7 The system identifies the corresponding relationship between the results table fields and the MDB database fields. The results table template field information is as follows:

[0047] Output of water supply, communication and electricity results table files for each category, see Figure 8 .

[0048] The automatic conversion system for multi-source databases of the entire underground pipeline process includes a processor and a memory. The processor is used to implement various instructions; the memory can set a storage path for the above-mentioned conversion data.

[0049] The above is only a preferred embodiment of the present invention. It should be pointed out that for ordinary technicians in this technical field, several improvements and modifications can be made without departing from the principles of the present invention. These improvements and modifications should also be regarded as within the scope of protection of the present invention.

Claims

1. A method for automatic conversion of multivariate database of the whole process of underground pipelines, characterized by: The following steps are involved: S1, converting the data in the field collection database into the data in the office processing database; S2, converting the data in the office processing database into data in a file geodatabase; S3, converts the data in the internal processing database into data in the result table.

2. The method for automatic conversion of a multivariate database of the entire underground pipeline process according to claim 1 is characterized in that: Said S1 comprises the following steps: S11, generating an internal processing database template according to a preset pipeline classification standard, and generating an empty internal processing database according to the internal processing database template; S12, determining the organizational form of the field data collection database. If the organizational form of the field data collection database is multi-table, executing S13; if the organizational form of the field data collection database is single-table, executing S14; S13, converting the data in the field acquisition database into data that meets the requirements of the office processing database according to a preset field semantic mapping rule library, and writing the converted data into an empty office processing database; S14, separating the data in the field collection database to generate a multi-table intermediate file, converting the data in the intermediate file into data that meets the requirements of the internal processing database according to the preset field semantic mapping rule library, and entering the data into the empty internal processing database.

3. The method for automatic conversion of a multivariate database of the entire underground pipeline process according to claim 2 is characterized in that: The S2 comprises the following steps: S21, constructing pipe point geometric entities and pipeline geometric entities according to the data in the internal processing database; S22, creating corresponding coordinate system metadata according to the coordinate information of the pipe point geometric entity and the coordinate information of the pipeline geometric entity, and setting projection parameters that meet the requirements of the file geographic database; S23, configuring a dynamic projection mechanism according to the projection parameters; S24, creating a spatial index for the pipe point geometric entity and the pipeline geometric entity; S25, constructing a geometric topological relationship between the pipe point geometric entity and the pipeline geometric entity; S26, importing the processed data into the file geodatabase.

4. The method for automatic conversion of a multivariate database of the entire underground pipeline process according to claim 1 is characterized in that: The S3 includes the following steps: S31, formulating an achievement table template, and generating an empty achievement table according to the achievement table template; S32, converting the data in the internal processing database according to the field requirements of the result table template; S33, writing the converted data into an empty result table template.

5. An automatic conversion system for multivariate databases of the entire underground pipeline process, characterized in that: It comprises a control module, which is configured as the automatic conversion method for the full-process multivariate database of an underground pipeline according to any one of claims 1-4.

Images