A device time sequence data storage model construction method and system suitable for coal mine multi-disaster fusion analysis

Abstract

The present application relates to a kind of equipment time series data storage model construction method and system suitable for coal mine multi-disaster fusion analysis, belong to coal mine safety disaster analysis technical field, to solve the problem of data effectiveness, history deficiency in prior art.The method first abstracts equipment time series data into point type data mode and line type data mode, and constructs the data storage conceptual model including monitoring time series data model, alarm time series data model, personnel positioning monitoring data model, monitoring time series slice data model and alarm time series slice data model.Database system using NoSQL technology, design the database table into conceptual model, and create the corresponding wide table structure, take column-based storage mode, set the compression strategy of column, realize long-term online storage and efficient reading of data.The equipment time series data storage model constructed by the present application can effectively reduce the complexity of data access, reduce data load and operation amount.

Description

Technical Field

[0001] This invention belongs to the field of coal mine safety disaster analysis technology, and relates to a method and system for constructing a time-series data storage model for equipment suitable for multi-hazard fusion analysis in coal mines. Background Technology

[0002] The underground environment of coal mines is complex, with diverse types of hazards (such as gas outbursts, water hazards, and dust explosions). Multi-hazard fusion analysis is crucial for ensuring safe production. This analysis relies on time-series data generated by underground equipment and must meet requirements such as real-time performance, multi-factor correlation, and long-term historical backtracking. However, existing coal mine data storage technologies suffer from the following bottlenecks:

[0003] Data dispersion and heterogeneity: Monitoring data is stored in different vendors' private systems and various databases (such as MySQL, SQL Server, etc.) with inconsistent formats, which makes it difficult to integrate data across disaster types and makes it impossible to quickly respond to real-time analysis needs.

[0004] Historical data is stored offline: Existing technologies use a tiered storage strategy of cold, warm and hot data. Historical data is often archived offline. When it is retrieved, it needs to be loaded and converted, which significantly increases the time overhead and makes it difficult to support real-time prediction and retrospective analysis with a response time of seconds.

[0005] Insufficient storage efficiency and query performance: Traditional row-oriented databases suffer from high disk space consumption and low data compression rate when dealing with high-frequency time-series data. Furthermore, complex join queries require operations across multiple tables, resulting in low computational efficiency.

[0006] While existing data fusion technologies in non-coal mining sectors (such as publication number CN111930723A) propose data integration methods, they are not optimized for the spatial characteristics of coal mine time-series data (such as equipment geographic coordinates), high-density monitoring items (such as gas concentration and equipment operating parameters), and the need for multi-hazard correlation analysis. For example, the performance violation processing system in publication number CN102236875A focuses on structured transaction data and lacks support for fragmented statistical analysis and compressed storage of time-series data, thus failing to meet the real-time requirements of coal mine safety analysis.

[0007] Therefore, there is an urgent need for a dedicated time-series data storage model for multi-hazard fusion analysis in coal mines. This model should address the problems of data dispersion, inefficient querying, and high long-term storage costs through unified data abstraction, fragmented statistical pre-computation, and columnar compression storage technologies, providing reliable data support for real-time disaster early warning and historical retrospective analysis. Summary of the Invention

[0008] In view of this, the purpose of the present invention is to provide a method and system for constructing a time-series data storage model for equipment suitable for multi-hazard fusion analysis in coal mines.

[0009] To achieve the above objectives, the present invention provides the following technical solution:

[0010] A method for constructing a time-series data storage model for equipment suitable for multi-hazard fusion analysis in coal mines includes the following steps:

[0011] S1. Construct an abstract model of equipment data: Classify the time-series data generated by underground coal mine equipment into personnel monitoring, equipment monitoring, production environment monitoring and geological environment monitoring according to function, and abstract it into a point-type data pattern or a line-type data pattern containing spatial features α, time-series features β and monitoring data items γ.

[0012] S2. Construct a data storage conceptual model: Based on the device data abstract model, design a time-series detailed data conceptual model and a time-series segmented statistical data conceptual model; wherein, the time-series detailed data conceptual model includes a monitoring time-series data model, an alarm time-series data model, and a personnel positioning monitoring data model, and the time-series segmented statistical data conceptual model includes a monitoring time-series segmented data model and an alarm time-series segmented data model;

[0013] S3. Construct the physical database: Using columnar compressed storage NoSQL database technology, the conceptual model of time-series detailed data and the conceptual model of time-series sharded statistical data are mapped to a physical wide table structure, and a data compression strategy is configured.

[0014] Furthermore, in S1, the point-type data pattern is defined as:

[0015] Α=α,β,γ1,γ2,...,γn|γ=<k,v> ,1≤n<50}

[0016] In the formula, A represents the set of dimensions of the data generated by the device at a certain point in time, α is the spatial feature value, β is the temporal feature value, and γ1, γ2, ..., γ n Multiple monitoring data items are generated for the device, where n is the number of monitoring data items; the monitoring data item γ consists of the monitoring item name k and the monitoring item value v, and is used to represent the multi-parameter time-series data generated by the device.

[0017] Furthermore, in S1, the linear data pattern is defined as:

[0018] Β={α,β,γ1,γ2|γ=<k,v>}

[0019] In the formula, B represents the set of all dimensions of the data generated by the linear device at a certain time; α is the spatial feature value, β is the temporal feature value, γ1 is the content of the data generated by the device, and γ2 is the segmentation information of the data generated by the device; the monitoring data item γ consists of the monitoring item name k and the monitoring item value v; k of γ1 is a fixed value "data", and v is an array composed of all monitoring parameter values, consisting of the number of segments and the segment length; k of γ2 is a fixed value "segment".

[0020] Furthermore, in S2, the time-series detailed data conceptual model further includes a common dimension data model J, an equipment dimension data model K, a monitoring data item model L, an alarm dimension data model M, and a personnel positioning dimension data model N, and the data models of each dimension are combined into the monitoring time-series data model, the alarm time-series data model, and the personnel positioning monitoring data model through association relationships.

[0021] Furthermore, in S2, the time-series segmented statistical data conceptual model is based on a fixed time slice unit and stores statistical data of the monitored values, including the maximum value, minimum value, average value and their corresponding timestamps.

[0022] Furthermore, in S3, the NoSQL database is a ClickHouse database, and the fields of the physical wide table structure are stored in columnar format and configured with LZ4 or ZSTD compression algorithms.

[0023] A time-series data storage system for equipment suitable for multi-hazard fusion analysis in coal mines includes:

[0024] Module A: Equipment Data Abstraction Module, used to classify and abstract the time-series data generated by underground coal mine equipment into point-type or line-type data patterns;

[0025] Module B: Conceptual model building module, used to generate conceptual models of time-series detailed data and time-series sharded statistical data;

[0026] Module C: Physical Database Module, which builds a columnar compressed physical wide table based on NoSQL database technology and configures data compression strategies;

[0027] The output of module A is connected to module B, and the output of module B is connected to module C.

[0028] Furthermore, module B includes:

[0029] Submodule B1 is used to construct a monitoring time series data model based on common dimensions, equipment dimensions, and monitoring data item dimensions;

[0030] Submodule B2 is used to build an alarm timing data model based on common dimensions, device dimensions, and alarm dimensions.

[0031] Submodule B3 is used to build a personnel location monitoring data model based on public dimensions, equipment dimensions, and personnel location dimensions.

[0032] Furthermore, in the physical database module, the fields of the physical wide table include spatial coordinates, device attributes, monitoring values, alarm status, and shard statistics, and unique association is achieved through data number primary keys.

[0033] Furthermore, the system also includes a data writing module, which is used to write time-series data into the physical wide table through ETL tools or programs, and connects to the coal mine multi-hazard fusion analysis platform.

[0034] The beneficial effects of this invention are as follows:

[0035] (1) Use three wide table models to store the data of the original dozens of tables, reducing the complexity of access and the time overhead of data association loading.

[0036] (2) By using fragmented statistical data storage, a large number of analyses do not need to be recalculated from detailed big data, reducing the amount of data loading and computation, and improving analysis efficiency.

[0037] (3) The columnar compression storage saves 1x the disk space while improving the data reading speed, and can provide long-term online storage and use of coal mine safety analysis source data for more than 10 years.

[0038] Other advantages, objectives, and features of the invention will be set forth in part in the description which follows, and in part will be apparent to those skilled in the art from the following examination, or may be learned from practice of the invention. The objectives and other advantages of the invention can be realized and obtained through the following description. Attached Figure Description

[0039] To make the objectives, technical solutions, and advantages of the present invention clearer, the preferred embodiments of the present invention will be described in detail below with reference to the accompanying drawings, wherein:

[0040] Figure 1 The process of constructing a time-series data storage model for equipment in coal mine multi-hazard fusion analysis. Detailed Implementation

[0041] The following specific examples illustrate the implementation of the present invention. Those skilled in the art can easily understand other advantages and effects of the present invention from the content disclosed in this specification. The present invention can also be implemented or applied through other different specific embodiments, and various details in this specification can be modified or changed based on different viewpoints and applications without departing from the spirit of the present invention. It should be noted that the illustrations provided in the following embodiments are only schematic representations of the basic concept of the present invention. Unless otherwise specified, the following embodiments and features can be combined with each other.

[0042] The accompanying drawings are for illustrative purposes only and are schematic diagrams, not actual pictures. They should not be construed as limiting the invention. To better illustrate the embodiments of the invention, some parts in the drawings may be omitted, enlarged, or reduced, and do not represent the actual product dimensions. It is understandable to those skilled in the art that some well-known structures and their descriptions may be omitted in the drawings.

[0043] In the accompanying drawings of the embodiments of the present invention, the same or similar reference numerals correspond to the same or similar components. In the description of the present invention, it should be understood that if terms such as "upper," "lower," "left," "right," "front," and "rear" indicate the orientation or positional relationship based on the orientation or positional relationship shown in the drawings, they are only for the convenience of describing the present invention and simplifying the description, and do not indicate or imply that the device or element referred to must have a specific orientation, or be constructed and operated in a specific orientation. Therefore, the terms used to describe positional relationships in the drawings are only for illustrative purposes and should not be construed as limiting the present invention. For those skilled in the art, the specific meaning of the above terms can be understood according to the specific circumstances.

[0044] like Figure 1 As shown, a method for constructing a time-series data storage model for equipment suitable for multi-hazard fusion analysis in coal mines is provided, including:

[0045] I. Constructing an abstract model of equipment data

[0046] 1. Summarize and classify equipment data

[0047] The time-series data for multi-hazard fusion analysis in coal mines mainly comes from underground safety equipment. Based on the equipment's function, the data is divided into four categories:

[0048] (1) Personnel monitoring

[0049] Monitoring data that provides real-time location information of personnel through positioning devices.

[0050] (2) Equipment monitoring category

[0051] The monitoring data, including equipment health status, operating parameters, and working conditions, are uploaded by intelligent devices through the underground ring network. For example, dust suppression spray equipment uploads the spray start / stop status and spray duration, while fans upload operating voltage and operating power.

[0052] (3) Production Environment Monitoring

[0053] Monitoring data on gas composition, temperature, humidity, and dust particles provided by the monitoring system are the main data source for safety analysis. Examples include monitored values ​​for gas concentration, dust concentration, wind speed, and temperature.

[0054] (4) Geological Environment Monitoring

[0055] Data is generated by monitoring the physical properties of coal seams and rock strata, such as their composition and hydrological structure.

[0056] 2. Abstract Device Data Model

[0057] The equipment that generates time-series monitoring data is installed at a specific location underground in the coal mine, with precise geographical coordinates on an underground coal mine map, possessing spatial characteristics α. Simultaneously, the equipment generates data based on time pulses, possessing temporal characteristics β.

[0058] Each monitoring data item γ generated by the device consists of the monitoring item name k and the monitoring item value v.

[0059] γ=<k,v>

[0060] The data generated by the above devices can be summarized into two data modes:

[0061] (1) Point-type data pattern

[0062] Α={α,β,γ1,γ2,...,γn|γ=<k,v> ,1≤n<50}

[0063] In the formula, A represents the set of dimensions of the data generated by a device at a certain point in time. Where α is the spatial feature value, β is the temporal feature value, and γ1, γ2, ..., γ n Multiple monitoring data items are generated for the device, where n is the number of monitoring data items. Each monitoring data item γ consists of a monitoring item name k and a monitoring item value v, and is used to represent the multi-parameter time-series data generated by the device.

[0064] In this mode, the equipment monitors one or more parameters and generates corresponding parameter monitoring values ​​representing a location in the mine in a time sequence. There are two scenarios: First, the equipment generates data on one or more environmental parameters for the location. Second, the equipment generates data on multiple parameters it monitors. Generally, the number of monitored parameters (n) is less than 50. In this case, γ represents the corresponding multiple monitoring data items.

[0065] The data generated by most monitoring devices in most coal mine production environments follows this pattern. For example, dust concentration sensors generate monitored values ​​for dust concentration.

[0066] (2) Linear data mode

[0067] Β={α,β,γ1,γ2|γ=<k,v>}

[0068] In the formula, B represents the set of dimensions of the data generated by the linear device at a certain moment. α is the spatial characteristic value, β is the temporal characteristic value, γ1 is the content of the data generated by the device, and γ2 is the segmentation information of the data generated by the device. Each monitoring data item γ consists of the monitoring item name k and the monitoring item value v. For γ1, k is a fixed value "data", and v is an array composed of all monitoring parameter values. For γ2, k is a fixed value "segment", and v is an array composed of the number of segments and the segment length.

[0069] In this mode, the equipment monitors a single parameter and generates corresponding monitoring values ​​representing multiple points within a certain distance between two locations underground, in a time sequence. The monitoring data generated by the equipment is represented by γ1 and γ2, which are 2 monitoring data items.

[0070] A typical example is fiber optic temperature measurement equipment, where a single fiber optic cable is several kilometers long, generating one temperature monitoring value per meter, and the temperature monitoring values ​​within every 100 meters form a data segment.

[0071] Since B∈A, mode A is defined as an abstract model of the timing data generated by the device.

[0072] II. Constructing a Data Storage Conceptual Model

[0073] Multi-hazard fusion analysis requires the following data usage:

[0074] First, we need to focus on analyzing abnormal alarm data, which comes from raw monitoring data and needs to be stored separately.

[0075] Secondly, the time-series data aggregation results based on time periods are the basis for statistical analysis, and it is necessary to provide monitoring and alarm aggregation data based on time slices.

[0076] Third, multi-hazard fusion analysis typically involves numerous dimensions, such as underground area, hazard type, and equipment type. The abstract model of equipment data only provides time and spatial dimension information and monitoring values. To avoid retrieving these dimensional data from other tables during analysis, the storage model design provides complete dimensional information related to all monitoring data dimensions.

[0077] In summary, five relational data models were designed to store the data required for multi-hazard fusion analysis.

[0078] 1. Conceptual Model of Time-Series Detailed Data

[0079] The following three data concept models store all the raw time-series data records generated by the device:

[0080] (1) Monitoring time series data model

[0081] C=J∪K∪L

[0082] (2) Alarm timing data model

[0083] D=J∪K∪M

[0084] (3) Personnel location monitoring data model

[0085] G = J∪K∪N

[0086] Where J represents the data model for the common dimensions;

[0087] J = {Data ID, Equipment ID, Equipment Code, Equipment Name, Mine Area ID, Mine Area Name, Measuring Point Group ID, Measuring Point Group Name, Measuring Point Custom Type ID, Measuring Point Custom Type Name, Measuring Point ID, Measuring Point Code, Measuring Point Name, Measuring Point Address, Measuring Point Description, Spatial Latitude and Longitude Coordinates, Spatial Coordinate Elevation, Spatial Rectangular Coordinates, Data Source System Data Source Type, Data Source Measuring Point Code, Data Source_Substation ID, Whether it is a Major Equipment, Data Validity Identifier, Usage Mark}

[0088] K represents the data model at the device level.

[0089] K = {Sensor Disaster Classification, Sensor Disaster Classification Name, Sensor Type Code, Sensor Type Name, Sensor Association, Sensor Type Subtype Code, Upper Range Limit, Lower Range Limit, On Description, Off Description, Protective State Description, Non-Protective State Description, Controlled State Description, Non-Controlled State Description, Monitoring Item Name, Monitoring Value Type, Monitoring Value Unit, Monitoring Value, Monitoring Value Time, Status Value Description, Status Value Time, Acquisition Time, Alarm Status, Alarm Resolution Status, Power Off Status, Controllable Status}

[0090] L represents the data model for the monitored data items.

[0091] L={ι1,ι2,…,ι n |ι=<name, monitoring value, unit of value>, 1≤n<50}

[0092] M is the data model for monitoring and alarm dimensions.

[0093] M = {Alarm type, Monitoring unit, Anomaly type, Anomaly content, Anomaly start time, Anomaly end time, Maximum value during the anomaly period, Maximum value time, Minimum value during the anomaly period, Minimum value time, Average value during the anomaly period, Anomaly cause, Handling measures}

[0094] N is a data model for personnel positioning dimensions.

[0095] N = {Personnel Card Code, Name, Job Type, Position, Department, Date of Birth, Labor Organization Method, Whether Mine Leader, Whether Special Personnel, Time of Entering the Mine, Travel Track Base Station, Time Set, Distance from Base Station, Time of Entering Base Station, Time of Entering Area, Previous Location Equipment Code, Previous Equipment Installation Address, Previous Equipment Type, Time of Previous Source Equipment, Personnel Entry Method, Personnel Work Status, Number of Personnel Approved for the Area}

[0096] 2. Conceptual Model of Time-Series Sliced ​​Statistical Data

[0097] Based on the conceptual model of detailed data, two time-sliced ​​data models are designed to store statistical data of monitoring values ​​at fixed time intervals, in order to reduce the computational overhead of statistical analysis from detailed data. The time slice unit can be defined according to the actual situation, such as 5 seconds, 30 seconds, 1 minute, 30 minutes, etc.

[0098] (1) Monitoring time-series fragmented data model

[0099] E = {Monitoring ID, Monitoring Item ID, Monitoring Equipment ID, Monitoring Address, Current Status, Current Value, Status Value, Status Occurrence Time, Status End Time, Maximum Value During Period, Maximum Value Time, Minimum Value During Period, Minimum Value Time, Average Value, Average Value Time, Statistical Time Slice Unit}

[0100] (2) Alarm timing data fragmentation model

[0101] F = {Monitoring ID, Monitoring Item ID, Monitoring Equipment ID, Monitoring Address, Disaster Type, Alarm Status Value, Alarm Start Time, Maximum / Minimum Value During Alarm Period, Maximum / Minimum Value Time During Alarm Period, Minimum Value During Abnormal Period, Minimum Value Time, Average Value During Alarm Period, Average Value Time, Statistical Time Slice Unit}

[0102] III. Constructing a Physical Database

[0103] Database systems using NoSQL technology (such as ClickHouse) design the above conceptual model into database tables, then create corresponding wide table structures in the database, adopt a column-based storage method, and set column compression strategies.

[0104] Once the database system is built, it can be used for equipment time-series data storage in coal mine multi-hazard fusion analysis.

[0105] Example 1

[0106] The purpose of this invention is to construct a conceptual model that centrally stores time-series data, based on the actual conditions of a coal mine, using the construction method described herein. Then, specific database technologies are used to create data tables to receive data written by the program, ensuring long-term online data storage and efficient data retrieval for multi-hazard fusion analysis. In practical applications, the model content can be tailored and the database system technologies used can be selected according to the needs of the coal mine to achieve better results.

[0107] Considering that a coal mine is building a multi-hazard integrated analysis business system, the steps for constructing a data model and putting it into use according to the method of this invention are as follows:

[0108] (1) First, after sorting out the sources of time series data required for fusion analysis, a device data mapping model is constructed.

[0109] Α={α,β,γ1,γ2,...,γn|γ=<k,v> n=16}

[0110] It can be determined that n is 16, meaning that each device generates a set of time-series data with a maximum of 16 monitoring values. For example, gas monitoring sensors are mainly distributed in fully mechanized mining faces and tunneling faces, and each sensor is responsible for generating time-series data for four monitoring items: gas concentration, carbon monoxide concentration, carbon dioxide concentration, and hydrogen sulfide concentration.

[0111] (2) Secondly, construct a conceptual model for storing time-series data.

[0112] Five conceptual models are designed here: a monitoring time-series data model, an alarm time-series data model, a personnel location monitoring data model, a time-series monitoring hierarchical data model, and a time-series alarm hierarchical data model. The content of each data model is as follows, where underlined fields indicate that they are primary keys in the relational model:

[0113] Monitoring time series data model: Monitoring data numberEquipment Number, Equipment Code, Equipment Name, Mine Area Number, Mine Area Name, Measuring Point Group Number, Measuring Point Group Name, Measuring Point Custom Type Number, Measuring Point Custom Type Name, Measuring Point Number, Measuring Point Code, Measuring Point Name, Measuring Point Address, Measuring Point Description, Spatial Coordinates (Longitude, Latitude), Spatial Coordinates (Elevation), Rectangular Coordinates (x, y, z), Sensor Disaster Classification, Sensor Disaster Classification Name, Sensor Type Code, Sensor Type Name, Sensor Association, Sensor Type Subtype Code, Measurement Range Upper limit, lower limit of range, upper control value, upper control recovery value, lower control value, lower control recovery value, upper limit warning value, upper limit alarm value, upper limit alarm recovery value, upper limit power failure value, upper limit power failure recovery value, lower limit warning value, lower limit alarm value, lower limit alarm recovery value, lower limit power failure value, lower limit power failure recovery value, on description, off description, protected state description, non-protected state description, controlled state description, non-controlled state description, monitoring item name, monitoring value type, monitoring value unit, monitoring value, monitoring value time, status value, status value description, status value time, acquisition time, alarm status Disconnection status, Power-off status, Controllable status, Extension key 1, Extension key 2, Extension key 3, Extension key 4, Extension key 5, Extension key 6, Extension key 7, Extension key 8, Extension key 9, Extension key 10, Extension key 11, Extension key 12, Extension key 13, Extension key 14, Extension key 15, Extension key 16, Extension value 1, Extension value 2, Extension value 3, Extension value 4, Extension value 5, Extension value 6, Extension value 7, Extension value 8, Extension value 9, Extension value 10, Extension value 11, Extension value 12, Extension value 13, Extension value 14, Extension value 15, Extended value 16, Extended value unit 1, Extended value unit 2, Extended value unit 3, Extended value unit 4, Extended value unit 5, Extended value unit 6, Extended value unit 7, Extended value unit 8, Extended value unit 9, Extended value unit 10, Extended value unit 11, Extended value unit 12, Extended value unit 13, Extended value unit 14, Extended value unit 15, Extended value unit 16, Data source system, Data source type, Data source measurement point code, Data source substation number, Whether it is a major equipment, Data validity identifier, Usage mark, Raw data.

[0114] Alarm timing data model: Monitoring data numberEquipment Number, Equipment Code, Equipment Name, Mine Area Number, Mine Area Name, Measuring Point Group Number, Measuring Point Group Name, Measuring Point Custom Type Number, Measuring Point Custom Type Name, Measuring Point Number, Measuring Point Code, Measuring Point Name, Measuring Point Address, Measuring Point Description, Spatial Coordinates Longitude, Spatial Coordinates Latitude, Spatial Coordinates Elevation, Rectangular Coordinates x, Rectangular Coordinates y, Rectangular Coordinates z, Sensor Disaster Classification, Sensor Disaster Classification Name, Sensor Type Code, Sensor Type Name, Sensor Association, Sensor Type Subtype Code, Upper Limit of Measurement Range, Measurement Range Lower limit, upper control value, upper control recovery value, lower control value, lower control recovery value, upper limit_warning value, upper limit alarm value, upper limit alarm recovery value, upper limit power failure value, upper limit_power failure recovery value, lower limit warning value, lower limit alarm value, lower limit alarm recovery value, lower limit_power failure value, lower limit_power failure recovery value, on description, off description, protected state description, non-protected state description, controlled state description, non-controlled state description, monitoring item name, monitoring value type, monitoring value unit, anomaly type, anomaly content, anomaly start time, anomaly end time, maximum value during anomaly, maximum value time, minimum value during anomaly. Minimum value time, average value during the abnormal period, cause of the abnormality, handling measures, data time, data entry person, extension key 1, extension key 2, extension key 3, extension key 4, extension key 5, extension key 6, extension key 7, extension key 8, extension key 9, extension key 10, extension key 11, extension key 12, extension key 13, extension key 14, extension key 15, extension key 16, extension value 1, extension value 2, extension value 3, extension value 4, extension value 5, extension value 6, extension value 7, extension value 8, extension value 9, extension value 10, extension value 11, extension value 12, extension value 13, extension value 1 4, Extended value 15, Extended value 16, Extended value unit 1, Extended value unit 2, Extended value unit 3, Extended value unit 4, Extended value unit 5, Extended value unit 6, Extended value unit 7, Extended value unit 8, Extended value unit 9, Extended value unit 10, Extended value unit 11, Extended value unit 12, Extended value unit 13, Extended value unit 14, Extended value unit 15, Extended value unit 16, Data source_system, Data source type, Data source_measuring point code, Data source_substation number, Whether it is a major equipment, Data validity identifier, Usage mark, Raw data.

[0115] Personnel location monitoring data model: Monitoring data numberEquipment Number, Equipment Code, Equipment Name, Area Number, Area Name, Measurement Point Group Number, Measurement Point Group Name, Measurement Point Custom Type Number, Measurement Point Custom Type Name, Measurement Point Number, Measurement Point Code, Measurement Point Name, Measurement Point Address, Measurement Point Description, Base Station Code, Base Station Name, Base Station Location, Spatial Coordinates Longitude, Spatial Coordinates Latitude, Spatial Coordinates Elevation, Rectangular Coordinates x, Rectangular Coordinates y, Rectangular Coordinates z, Sensor Disaster Classification, Sensor Disaster Classification Name, Sensor Type Code, Sensor Type Name, Sensor Association, Sensor Type Subtype Code, Personnel Card Code, Name, Job Title, Team / Department, Date of Birth, Labor Organization The data includes: whether the person is a mine leader, whether the person is a special personnel, time of going down the mine, shift number of the attendance, shift name of the attendance, base station of the movement trajectory, time set, distance from the base station, time of entering the base station, time of entering the area, equipment code of the previous location, installation address of the previous equipment, type of the previous equipment, time of the previous source equipment, personnel entry method, personnel working status, number of personnel approved for the area, status value, status value description, status value time, collection time, alarm time, alarm status, alarm cancellation status, power failure status, controllable status, data source_system, data source_type, data source_measuring point code, data source_substation number, whether it is a major equipment, data validity identifier, and usage mark.

[0116] Time-series monitoring hierarchical data model: Monitoring data number Equipment Number, Equipment Code, Equipment Name, Mine Area Number, Mine Area Name, Equipment Location Number, Equipment Location Name, Equipment Type Monitoring Item Number, Start Time, End Time, Maximum Value During Period, Maximum Value Time, Minimum Value During Period, Minimum Value Time, Average Value Time, Statistical Time Slice Unit.

[0117] Time-series alarm hierarchical data model: Monitoring data number Monitoring item number, monitoring equipment number, monitoring address, disaster type, alarm status value, alarm start time, alarm end time, maximum value during alarm period, maximum value time during alarm period, minimum value during abnormal period, minimum value time, average value during alarm period, average value time, statistical time slice unit, monitoring data number.

[0118] (3) Next, select ClickHouse as the database storage system and construct a physical database wide table based on the above conceptual model to store data. ClickHouse is a column-oriented database system. When creating a table, in addition to specifying the data compression method for the table, you also need to specify the data compression method for each field of the table.

[0119] The structure of the monitoring time series data table is shown in Table 1.

[0120] Table 1

[0121]

[0122]

[0123]

[0124] (4) Finally, the data is written into the corresponding ClickHouse table above using ETL tools or programs, and the data is then integrated and analyzed to connect to the ClickHouse database and read the data.

[0125] A wide-table data model was adopted to merge the coal mine safety time-series data that originally required multiple tables to store. At the same time, columnar storage technology was used to compress and store the data, enabling long-term online storage of the data.

[0126] Example 1: Construction and Implementation of Time-Series Data Storage Model for Coal Mine Equipment

[0127] 1. Construction of Equipment Data Abstraction Model

[0128] (1) The time-series data generated by underground coal mine equipment are classified by function as follows:

[0129] Personnel monitoring: Data on real-time location, movement trajectory, and time spent in a region are collected through positioning devices.

[0130] Equipment monitoring: Collect equipment health parameters such as fan operating voltage (e.g., 380V) and spray equipment on / off status (e.g., "on" / "off").

[0131] Production environment monitoring: Collects methane concentration (%) and dust concentration (mg / m³). 3 Environmental parameters such as temperature (unit: °C).

[0132] Geological environment monitoring: collecting geological parameters such as coal seam displacement (unit: mm) and hydrological pressure (unit: MPa).

[0133] (2) Abstracted into the following two data models:

[0134] Point-based data model: Defines a single device generating multi-parameter time-series data at a fixed location. For example, a gas sensor generates monitoring values ​​for gas concentration and carbon monoxide concentration, which can be abstracted as:

[0135] A = {α (spatial coordinates), β (timestamp), γ1 (gas concentration, 0.8%), γ2 (carbon monoxide concentration, 24 ppm)}

[0136] Linear data pattern: Defines the linear distribution of data generated by distributed devices (such as fiber optic temperature measurement systems). For example, the fiber optic cable generates an array of temperature monitoring values ​​and segment information every 100 meters, which can be abstracted as:

[0137]

[0138] 2. Data storage conceptual model design

[0139] (1) Time-series detailed data model:

[0140] Monitoring time series data model: integrates common dimensions (equipment number, measuring point location), equipment dimensions (sensor type, measuring range) and monitoring data items (monitoring value, unit).

[0141] Alarm timing data model: Expand alarm dimensions (anomaly type, handling measures) and record abnormal events such as gas exceeding limits (e.g., concentration ≥1.0%).

[0142] Personnel location monitoring data model: includes fields such as personnel card code, trajectory base station, and time of entry and exit from the area.

[0143] (2) Time-series fragmented statistical data model:

[0144] The statistics of the monitoring values ​​are pre-calculated based on 5-second time slices. For example, the maximum gas concentration in a certain area within 30 seconds is 1.2% (timestamp: 2023-10-01 08:00:25), the minimum is 0.5% (timestamp: 2023-10-01 08:00:10), and the average is 0.9%.

[0145] 3. Physical Database Construction

[0146] When creating a wide table using the ClickHouse database, the key configurations are as follows:

[0147] Table structure: Define the `monitor_data` table, which contains 110 fields including `data_id` (primary key), `geo_lat` (longitude, Decimal(18,8)), `monitor_value` (monitoring value, Decimal(18,3)), and `alarm_state` (alarm status, String).

[0148] Compression strategy: ZSTD compression is used for high-frequency fields (such as `monitor_name` and `alarm_state`); LZ4 compression is used for low-frequency fields (such as `facility_id`).

[0149] Partitioning and Indexing: Partition by month according to `monitor_value_time` (monitoring time), and create secondary indexes for `device_id` and `zone_id` to speed up queries.

[0150] 4. Data writing and fusion analysis

[0151] Data from downhole equipment is accessed in real time via the Flink stream processing engine, cleaned by ETL, and then written to ClickHouse.

[0152] The multi-hazard analysis platform can directly query the physical wide table, for example:

[0153] SELECT MAX(monitor_value)AS max_gas,zone_name

[0154] FROM monitor_data

[0155] WHERE sensor_safety_class='gas'AND monitor_value_time BETWEEN'2023-10-0108:00:00'AND'2023-10-01 08:05:00'

[0156] GROUP BY zone_name;

[0157] The above query returns the peak gas concentration for each region within 5 seconds, supporting real-time disaster early warning.

[0158] Example 2: Model Expansion and Performance Optimization

[0159] Dynamic expansion: By reserving 16 expansion keys (`key1`-`key16`) and expansion values ​​(`val1`-`val16`), it is compatible with newly added monitoring items (such as adding hydrogen sulfide monitoring).

[0160] Storage optimization: Enable ClickHouse's TTL (Time-To-Live) strategy to automatically transfer data from 10 years ago to cold storage, reducing online storage costs.

[0161] After implementation, the data analysis efficiency of a certain coal mine has been significantly improved: data loading time has been reduced from minutes to seconds; storage space usage has been reduced by 50% (column compression); and the response time for multi-hazard correlation analysis is ≤3 seconds, meeting real-time requirements.

[0162] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention and are not intended to limit it. Although the present invention has been described in detail with reference to preferred embodiments, those skilled in the art should understand that modifications or equivalent substitutions can be made to the technical solutions of the present invention without departing from the spirit and scope of the present invention, and all such modifications or substitutions should be covered within the scope of the claims of the present invention.

Claims

1. A device time series data storage model construction method suitable for coal mine multi-disaster fusion analysis, characterized in that: Comprising the following steps: S1. Constructing a device data abstraction model: classifying the time series data generated by the underground coal mine equipment into personnel monitoring, equipment monitoring, production environment monitoring, and geological environment monitoring, and abstracting it into a point-type data pattern or a line-type data pattern containing spatial features α, time series features β, and monitoring data items γ; the point-type data pattern is defined as: wherein, represents a set of dimensions of data generated by point devices at a certain time point, is a spatial feature value, is a temporal feature value, is a plurality of monitoring data items generated by the device, is the number of monitoring data items; a monitoring data item γ is composed of a monitoring item name k and a monitoring item value v, and is used to represent multi-parameter time-series data generated by the device. The line-type data pattern is defined as: wherein, represents a set of dimensions of the linear device generated data at a certain time; is a spatial feature value, is a temporal feature value, is the content of the device generated data, is the segment information of the device generated data; the monitoring data item γ is composed of a monitoring item name k and a monitoring item value v; k of γ is a fixed value "data", k of γ is a fixed value "segment"; S2. Building a data storage conceptual model: based on the device data abstraction model, designing a time series detail data conceptual model and a time series slice statistical data conceptual model; wherein the time series detail data conceptual model includes a monitoring time series data model, an alarm time series data model, and a personnel positioning monitoring data model, and the time series slice statistical data conceptual model includes a monitoring time series slice data model and an alarm time series slice data model; the time series detail data conceptual model further includes a public dimension data model J, an equipment dimension data model K, a monitoring data item model L, an alarm dimension data model M, and a personnel positioning dimension data model N, and each dimension data model is combined into the monitoring time series data model, the alarm time series data model, and the personnel positioning monitoring data model through an association relationship; the time series slice statistical data conceptual model stores statistical data of monitoring values based on a fixed time slice unit, including maximum value, minimum value, average value, and their corresponding time stamps; S3. Building a physical database: using columnar compression storage NoSQL database technology, mapping the time series detail data conceptual model and the time series slice statistical data conceptual model into a physical wide table structure, and configuring a data compression strategy.

2. The device time series data storage model construction method suitable for coal mine multi-disaster fusion analysis according to claim 1, characterized in that: In S3, the NoSQL database is a ClickHouse database, and the fields of the physical wide table structure are stored columnarly and configured with LZ4 or ZSTD compression algorithms.

3. The equipment time series data storage system for multi-disaster fusion analysis of coal mines using the construction method of claim 1 or 2, characterized in that: Comprising: Module A: a device data abstraction module for classifying and abstracting time series data generated by underground coal mine equipment into point-type or line-type data patterns; Module B: a conceptual model building module for generating a time series detail data conceptual model and a time series slice statistical data conceptual model; Module C: a physical database module for building a columnar compression storage physical wide table based on NoSQL database technology and configuring a data compression strategy; Wherein the output of module A is connected to module B, and the output of module B is connected to module C.

4. The equipment time series data storage system suitable for coal mine multi-disaster fusion analysis according to claim 3, characterized in that: The module B comprises: Sub-module B1 for constructing a monitoring time series data model according to public dimensions, equipment dimensions, and monitoring data item dimensions; Sub-module B2 for constructing an alarm time series data model according to public dimensions, equipment dimensions, and alarm dimensions; Sub-module B3 for constructing a personnel positioning monitoring data model according to public dimensions, equipment dimensions, and personnel positioning dimensions.

5. The equipment time series data storage system suitable for coal mine multi-disaster fusion analysis according to claim 3, characterized in that: In the physical database module, the fields of the physical wide table include spatial coordinates, equipment attributes, monitoring values, alarm states, and slice statistics, and are uniquely associated through a data number primary key.

6. The equipment time series data storage system suitable for coal mine multi-disaster fusion analysis according to claim 3, characterized in that: The system further comprises a data writing module for writing time series data into the physical wide table through an ETL tool or program, and connecting with a coal mine multi-disaster fusion analysis platform.

Images