A full-incremental synchronization method and system based on change data capture technology

Abstract

The application relates to a full-incremental synchronization method and system based on a change data real-time capturing technology, and belongs to the field of data synchronization. The method comprises the following steps: full-incremental synchronization accesses a power grid resource business middle station database through a Rest interface to obtain full-incremental graphic data; further, a synchronization conversion operation is performed on the full-incremental graphic data, and the converted GIS graphic data is batch-synchronized to a GIS database; incremental synchronization captures change data from the power grid resource business middle station through a preset data synchronization service, encapsulates the change data into a standardized data format, and pushes the change data to a message middleware; the message middleware acquires the change data in the standardized data format, inputs the change data into a synchronization conversion link, performs a synchronization conversion operation, and synchronizes the converted change data to the GIS database; and the application can realize data synchronization of the power grid resource business middle station database and the GIS database, and solves the problem of inconsistent graphics of the two sets of systems.

Description

Technical Field

[0001] This invention relates to a fully incremental synchronization method and system based on real-time change data capture technology, belonging to the field of data synchronization. Background Technology

[0002] In the modern power industry, the efficiency and reliability of power grid management are crucial to national welfare and people's livelihood, and their importance is self-evident. With rapid economic development and accelerated urbanization, electricity demand is growing rapidly, and users are placing increasingly stringent requirements on power supply reliability and power quality. Traditional power grid management methods have gradually revealed many shortcomings when dealing with complex power grid structures, massive amounts of data, and diverse user needs. For example, power grid planning cannot fully consider factors such as geographical environment and load distribution, and equipment operation and maintenance management cannot accurately and timely grasp the operating status of equipment.

[0003] Against this backdrop, the integration of Geographic Information Systems (GIS) and power grid management has emerged, bringing new opportunities for industry development. GIS can integrate geospatial information and attribute data, providing intuitive and accurate basis for decision-making with its powerful spatial analysis capabilities. In the power grid planning stage, GIS's spatial analysis capabilities can fully consider factors such as topography, meteorological conditions, and load forecasting, optimizing the power grid layout and reducing line losses and construction costs. In power grid operation monitoring, the combination of GIS and real-time monitoring technology can comprehensively monitor power grid equipment in real time, quickly locate fault points, improve fault handling efficiency, and ensure stable power grid operation. In the field of electricity marketing, GIS-based customer information management systems can intuitively display the geographical distribution of customers, helping to formulate differentiated marketing strategies and improve the quality of electricity services and customer satisfaction.

[0004] Therefore, there is an urgent need for a comprehensive power grid diagram and model data synchronization mode that can provide real-time and efficient anomaly data processing, and a power grid full incremental integrated data synchronization method and system that can realize conversion processing within seconds when anomalies occur at the source end. Summary of the Invention

[0005] To address the problems existing in the prior art, this invention proposes a fully incremental synchronization method and system based on real-time change data capture technology.

[0006] The technical solution of the present invention is as follows:

[0007] On the one hand, this invention proposes a full-incremental synchronization method based on real-time change data capture technology, the steps of which include full synchronization and incremental synchronization:

[0008] The full synchronization process accesses the power grid resource business platform through the REST interface to obtain the full set of graphic data; performs a synchronization conversion operation on the full set of graphic data, and then batches the converted GIS graphic data to the GIS database.

[0009] The incremental synchronization process uses a preset data synchronization service to capture incremental anomaly data from the power grid resource business platform using real-time change data capture technology. The incremental anomaly data is then encapsulated into a standardized data format and pushed to the message middleware. The message middleware obtains the incremental anomaly data in the standardized data format, inputs it into the synchronization conversion stage, performs the synchronization conversion operation, and then synchronizes the converted incremental anomaly data to the GIS database.

[0010] The synchronous conversion operation includes core model mapping, field conversion, coordinate conversion, and relationship matching. The core model mapping is used to establish a correspondence between the device model of the power grid resource business platform and the device model of the GIS database. The field conversion is used to convert the type and format of the fields of the device data. The coordinate conversion is used to convert the latitude and longitude coordinates of the power grid equipment into Mercator coordinates compatible with the GIS database. The relationship matching is used to determine the subordinate relationship between devices in the power grid resource business platform.

[0011] Preferably, the synchronous transformation operation includes a coordinate transformation step, specifically:

[0012] Convert the latitude and longitude coordinates of the equipment in the full graphic data and incremental anomaly data to Mercator coordinates.

[0013] Obtain the absolute coordinates of the station equipment stored in the WKT string, and get the longitude of the equipment. and latitude Convert the latitude and longitude, currently expressed in degrees, to radians using the following formula:

[0014] ;

[0015] ;

[0016] In the formula, Longitude of station equipment expressed in radians Longitude of station equipment in angular form. The latitude of the station equipment is expressed in radians. The latitude of the station's equipment is expressed in angular form;

[0017] Convert the latitude and longitude of the station equipment in radians to Mercator coordinates, expressed by the formula:

[0018] ;

[0019] ;

[0020] In the formula, The Mercator x-coordinate represents the equipment within the station. Represents the Earth's average radius. This represents the Mercator coordinate of the equipment within the station.

[0021] Preferably, when calculating the Mercator coordinate ordinate in high-latitude regions, the method is expressed by the following formula:

[0022] ;

[0023] ;

[0024] In the formula, The eccentricity of the Earth's ellipsoid is represented by its eccentricity. Represents the minor axis of the Earth's ellipsoid. It represents the semi-major axis of the Earth's ellipsoid.

[0025] Preferably, the synchronization conversion operation includes a relationship matching step, specifically:

[0026] Determine whether the current device performing the synchronization operation needs to handle the ownership relationship; if not, skip it directly.

[0027] If processing is required, first find the corresponding parent container table based on the device ID and type of the data, and then look up the OID of the parent device in the parent container table based on the parent device ID. If found, store the OID of the parent device in the parent container table into the relationship field; if not found, store the device data that failed to match the relationship into a temporary table, and use a scheduled task to periodically read the data from the temporary table for processing.

[0028] Among them, the parent device oid is the identification information assigned to the parent device in the parent container table.

[0029] Preferably, the preset data synchronization service uses either intrusive or non-intrusive abnormal data capture, wherein:

[0030] Intrusive anomaly data capture: Access the power grid resource business middleware database to obtain anomaly data; use PostgreSQL database logical replication technology to copy the anomaly data to a preset shared database in real time; further use Flink CDC real-time change data capture technology to capture the anomaly data in the preset shared database.

[0031] Non-intrusive anomaly data capture: Access the anomaly submission record table in the power grid resource business middleware database. The anomaly submission record table records the ID of the anomaly device, the data before the change, the data after the change, and the anomaly submission time. Configure a timed task to traverse the anomaly submission record table at preset time intervals according to the anomaly submission time and capture the newly added anomaly data.

[0032] Preferably, the method utilizes the logical replication technology of the PostgreSQL database to replicate abnormal data to a preset shared database in real time, specifically as follows:

[0033] Create a pre-defined PostgreSQL database dedicated to the shared database, and configure the WAL logical log level of the PostgreSQL database to logical;

[0034] Adjust the WAL logical log level of the power grid resource business middleware database to logical;

[0035] Establish a logical replication relationship between the power grid resource business middleware database and the preset common database, and fully copy the graphical tables, resource tables, and asset tables of power grid equipment to the preset common database.

[0036] Preferably, in the incremental synchronization process, during the step of capturing incremental anomaly data from the power grid resource business platform through a preset data synchronization service, the method uses Flink CDC real-time change data capture technology to capture anomaly data in a preset shared database, specifically:

[0037] Configure the source data source of Flink CDC to the preset common database, and listen to the graph table, resource table and asset table of power grid equipment in the preset common database;

[0038] Flink CDC is used to parse the WAL logical log of the preset common database to capture the addition, update and deletion events of the graphical table, resource table and asset table of power grid equipment, and at the same time obtain the data before the change and the data after the change, which are the abnormal data;

[0039] The acquired anomaly data is encapsulated in JSON format and pushed to Kafka;

[0040] Configure a Kafka data source and register a Kafka listener to receive abnormal data through preset topics and partitions, and call the Handler to perform synchronous transformation operations.

[0041] On the other hand, the present invention also proposes a fully incremental synchronization system based on real-time change data capture technology, comprising the following modules:

[0042] Full synchronization module: The full synchronization process accesses the power grid resource business platform through the REST interface to obtain full graphic data; performs synchronization conversion operation on the full graphic data, and batch synchronizes the converted GIS graphic data to the GIS database;

[0043] Incremental synchronization module: The incremental synchronization process uses a preset data synchronization service to capture incremental anomaly data from the power grid resource business platform using real-time change data capture technology. The incremental anomaly data is then encapsulated into a standardized data format and pushed to the message middleware. The message middleware obtains the incremental anomaly data in the standardized data format, inputs it into the synchronization conversion stage, performs the synchronization conversion operation, and then synchronizes the converted incremental anomaly data to the GIS database.

[0044] In another aspect, the present invention also proposes an electronic device, including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the program to implement the method as described in any embodiment of the present invention.

[0045] In another aspect, the present invention also proposes a computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, implements the method described in any embodiment of the present invention.

[0046] The present invention has the following beneficial effects:

[0047] (1) This invention is a full-incremental synchronization method and system based on real-time capture technology of change data. Through the integrated collaboration of full and incremental synchronization, coupled with standardized synchronization conversion operations including core model mapping, field conversion, coordinate conversion and belonging relationship matching, it effectively solves the problems of low efficiency of full synchronization, untimely incremental synchronization and data format incompatibility with GIS in data synchronization. Full synchronization pulls power grid equipment data in batches through the REST interface in multiple threads to ensure efficient migration of massive map model data; incremental synchronization captures abnormal data in real time to avoid data lag. In the synchronization conversion process, coordinate conversion is carried out through three levels of latitude, longitude, radians and Mercator to ensure that spatial data is accurately adapted to the GIS platform; belonging relationship matching is achieved through parent container table query and temporary table timed compensation mechanism to solve the problem of broken equipment belonging relationship, and finally realizes seamless connection between power grid map model data and GIS database, ensuring the core requirements of data integrity, accuracy and spatial adaptability.

[0048] (2) This invention is a fully incremental synchronization method and system based on real-time capture technology for change data. Through the technical linkage of PostgreSQL logical replication and Flink CDC, an incremental synchronization architecture of source database, shared database, and data processing is constructed, which not only meets the real-time requirements of change data, but also avoids direct pressure on the source database. By configuring the WAL log level of the power grid resource business middle platform database and the shared database to logical, a logical replication relationship is established to realize real-time replication of power grid equipment tables and isolate the power grid resource business middle platform database from the data processing flow; Flink CDC parses the shared database WAL logical log, captures the addition, update, and deletion events and the data before and after the change, encapsulates it into JSON and pushes it to Kafka to ensure the response speed of change data; the shared database service receives data through the Kafka listener and performs transformation, further reducing the interaction frequency of the power grid resource business middle platform database, avoiding the performance loss of the power grid resource business middle platform database caused by real-time query or log parsing, and ensuring the stable operation of the core business database of the power grid.

[0049] (3) This invention is a fully incremental synchronization method and system based on real-time change data capture technology. Through a non-intrusive incremental synchronization scheme using an anomaly submission record table and timed scanning, it addresses the risks of WAL log bloat and database crashes that may result from modifications to the source database configuration required by logical replication and Flink CDC schemes. It adapts to complex scenarios where source database parameters cannot be adjusted. By directly accessing the existing anomaly submission record table in the power grid resource business platform and using timed scanning to filter newly added anomaly data, there is no need to configure a shared database or Flink CDC, avoiding intrusive operations on the power grid resource business platform database. Data is still pushed and synchronized into the database via Kafka, ensuring compatibility with the main incremental scheme. This scheme retains the real-time nature of incremental synchronization while avoiding risks such as database log bloat and replication interruptions caused by table structure changes in the power grid resource business platform database, improving the system's adaptability and operational reliability under different operating environments. Attached Figure Description

[0050] Figure 1 This is a flowchart of the full and incremental data synchronization method proposed in Embodiment 1 of the present invention;

[0051] Figure 2 This is the station building power grid equipment mapping table proposed in Embodiment 2 of the present invention;

[0052] Figure 3 This is the mapping table between GIS graphic device types and subtypes proposed in Embodiment 2 of the present invention;

[0053] Figure 4 This refers to the mapping table between the power grid resource business middleware database and the GIS power grid model field proposed in Embodiment 2 of the present invention;

[0054] Figure 5 This is a flowchart of the incremental data synchronization alternative proposed in Embodiment 3 of the present invention. Detailed Implementation

[0055] The technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.

[0056] It should be understood that the step numbers used in the text are for ease of description only and are not intended to limit the order in which the steps are performed.

[0057] It should be understood that the terminology used in this specification is for the purpose of describing particular embodiments only and is not intended to limit the invention. As used in this specification and the appended claims, the singular forms “a,” “an,” and “the” are intended to include the plural forms unless the context clearly indicates otherwise.

[0058] The terms “comprising” and “including” indicate the presence of the described feature, whole, step, operation, element and / or component, but do not exclude the presence or addition of one or more other features, wholes, steps, operations, elements, components and / or collections thereof.

[0059] The term “and / or” refers to any combination of one or more of the associated listed items, as well as all possible combinations, and includes these combinations.

[0060] Example 1:

[0061] See Figure 1 This embodiment proposes a fully incremental synchronization method based on real-time change data capture technology, including the following steps:

[0062] A1. Create a thread pool and set the number of threads to twice the preset number of available CPU cores on the server. Use the threads to pull business data of power grid equipment from the power grid resource business middleware database in pages through the REST interface. The business data includes the equipment's graphic data and resource data (such as the equipment number, voltage level, relative coordinates, etc.). Pull 10,000 records in each batch.

[0063] Furthermore, extract the pre-configured mapping rule information for all power grid diagram device models from the Redis cache;

[0064] It should be noted that the mapping rule information is a correspondence between the "middle platform equipment model and GIS equipment model" specifically set for 168 types of power grid equipment. It includes mapping standards such as field names and data formats. Finally, the corresponding model mapping rule is matched according to the specific model of each type of equipment in the current batch of data.

[0065] A11. Based on the mapping rules of all extracted power grid diagram and equipment models, perform field parsing and filling on the power grid equipment business data pulled in the current batch, and verify the data type; for different data types, conversion processing is required; special fields such as symbol size, symbol angle, equipment type, and equipment subtype are also processed.

[0066] For equipment within the station, since the equipment graphic table in the power grid resource business platform maintains relative coordinates, and only the station building table has the absolute coordinates of the station building's center point, when performing station building conversion processing, it is also necessary to calculate the absolute coordinates of the equipment within the station based on the absolute coordinates of the station building's center point and the relative coordinates of the equipment within the station, and store them as WKT strings.

[0067] A12. Further, the absolute coordinates stored as WKT strings in the equipment graphic table of the power grid resource business middle platform database are converted into Mercator coordinates suitable for storing GIS databases.

[0068] In this embodiment, the absolute coordinates of the station equipment stored in the WKT string are obtained to determine the longitude of the equipment. and latitude Convert the latitude and longitude, expressed in degrees, to radians, as shown by the formula:

[0069] ;

[0070] ;

[0071] In the formula, Longitude of station equipment expressed in radians Longitude of station equipment in angular form. The latitude of the station equipment is expressed in radians. The latitude of the station's equipment is expressed in angular form;

[0072] Convert the latitude and longitude of the station equipment in radians to Mercator coordinates, expressed by the formula:

[0073] ;

[0074] ;

[0075] In the formula, The Mercator x-coordinate represents the equipment within the station. Represents the Earth's average radius. Indicates the Mercator coordinates of the equipment within the station;

[0076] It should be noted that in high-latitude regions, the latitude of station equipment in radians is converted to Mercator coordinates, expressed by the formula:

[0077] ;

[0078]

[0079] In the formula, The eccentricity of the Earth's ellipsoid is represented by its eccentricity. Represents the minor axis of the Earth's ellipsoid. It represents the semi-major axis of the Earth's ellipsoid.

[0080] Furthermore, by calling the Siji Map API, the calculated Mercator coordinates of the equipment within the station are input into the preset Mercator coordinate offset algorithm in Siji Map to obtain accurate Mercator coordinate data of the equipment within the station.

[0081] A13. Further, handle the subordinate relationships. Some power grid equipment has subordinate relationships, which need to be handled. Specifically:

[0082] For containerized power grid equipment (such as substations, distribution stations, feeders, transmission lines, physical poles, etc.), it is necessary to process the relationships between the sub-equipment it contains, such as the station, feeder, line, and pole.

[0083] The affiliation is a field attribute in the sub-device table of the GIS data model, and its value is the oid of the device to which it belongs;

[0084] When processing the ownership relationship, first determine whether the device needs to have its ownership relationship processed. If not, skip it directly. If it needs to be processed, first find the corresponding parent container table based on the device ID and type of the data, and then look up the data in the parent container table based on the parent device ID. If found, store the OID of the parent container in the ownership relationship field. If not found, it means that the parent container device has not yet been synchronized. Therefore, store the data in a temporary table and use a scheduled task to periodically read the data from this table for post-processing.

[0085] A14. Store the converted graphic data into a GIS database to complete the real-time processing and synchronization of all graphic data.

[0086] A2. Create a dedicated PostgreSQL database for shared databases, configure its WAL log level to logical, and synchronously adjust the WAL log level of the power grid resource business middleware database to logical to support the logical replication function of the database.

[0087] Establish a logical replication from the power grid resource business middle platform database to the preset common database, and fully copy the 168 types of power grid equipment graphic tables, resource tables and asset tables that need to be synchronized to the preset common database.

[0088] When abnormal data is generated in the power grid resource business middleware database, the abnormal data will be copied to the preset common database in real time through the database's logical replication function.

[0089] A21. Configure Flink CDC for data synchronization service. Configure the source data source as a preset common database and listen for the 168 types of device tables that need to be synchronized.

[0090] The configured Flink CDC is used to parse the WAL logical logs of the preset shared database, accurately capturing the time of table addition, update and deletion, and obtaining the data before and after the change;

[0091] It accurately captures the add, update, and delete event data in the configured monitoring graph table, and can obtain the change data (data before and after the change); it encapsulates this change data (data before and after the change, table name, device ID) into JSON format and pushes it to Kafka.

[0092] A22: After configuring the Kafka data source in the shared database service, register a Kafka listener in the service. The listener can accurately receive the abnormal data pushed by the Broker through the specified topic and partition, and call the Handler corresponding to the abnormal data for processing.

[0093] A23. After receiving the abnormal data pushed by Kafka, the shared database service retrieves the graphical, resource, and asset table data of the corresponding abnormal device ID from the shared database and then performs core processing logic such as model mapping, field conversion, coordinate conversion, and relationship matching.

[0094] The model mapping, field transformation, coordinate transformation, and relationship matching are all the same as the full synchronization in step S100, and will not be repeated here.

[0095] A24: Store the converted anomaly data into the GIS database to complete the incremental real-time processing and synchronization of the anomaly data.

[0096] Example 2: Based on Example 1, this example proposes a method for synchronizing all graphic data by combining the station equipment mapping table, in-station equipment mapping table, out-of-station equipment mapping table, power grid resource business middleware database and GIS power grid model field mapping table, and GIS graphic equipment type and subtype mapping comparison table in actual engineering practice. Specifically:

[0097] S1. Create a thread pool, setting the number of threads to twice the preset number of available CPU cores on the server; use the threads to retrieve data from a power grid substation in the power grid resource business middleware database in pages via the REST interface, including equipment identification, equipment type and attribute, time and attribution, and coordinate and graphical data, where:

[0098] A. Equipment identification data includes:

[0099] Internal unique identifier (FID): BoJRjYTecIuQ40cU5wp4e0;

[0100] Device global unique ID (dev_id): OY8GzFFhkIGRDwtW8SjyLU;

[0101] The globally unique ID (parent_id) of the parent device to which this device belongs is: OY8GzFFhkIGRDwtW8SjyLU;

[0102] Unique identifier (block_id) of the block to which the device belongs: OY8GzFFhkIGRDwtW8SjyLU;

[0103] Parent device class name: PWStationPSR;

[0104] The class name of the block it belongs to (block_class_name): PWStationPSR;

[0105] It should be noted that the device's internal unique identifier is in string format and is only used for data association and indexing within the power grid resource business middleware database; the device's globally unique ID is the core identifier for cross-system data synchronization and is directly mapped to the sbid field of the GIS database; the globally unique ID of the device's parent device is consistent with dev_id, indicating that the substation is an independent top-level device; the unique identifier of the block to which the device belongs is in string format and is used to associate device block management data within the power grid resource business middleware database; the class name of the parent device is in string format and is consistent with the station building class in the power grid resource business middleware database, defining the parent device type; the class name of the block to which it belongs is in string format and is consistent with the station building class, indicating that the block is a station building type block.

[0106] B. Equipment type and attribute data include:

[0107] Device class name (class_name): PWStationPSR;

[0108] Device subtype name encoding (subtype_name): 252401;

[0109] Device subtype value: 0;

[0110] Voltage level identifier (voltagelevel_id): 21;

[0111] Device running status code (run_status): 10;

[0112] Equipment operation status indicator (opstatic): 1;

[0113] Rated capacity of equipment: 0.0;

[0114] Does the device have an outer frame identifier (hasouterframe): false;

[0115] Equipment Name: Zhongjun Jingfeng No. 2 Public Transformer Distribution Room;

[0116] It should be noted that the device's class name is a string, indicating that the device belongs to the station type (PWStationPSR) in the power grid resource business platform database; the device subtype name is encoded as a string, used to further subdivide station types within the power grid resource business platform database; the device subtype numerical identifier is an integer, used in conjunction with the device subtype name encoding for type determination within the power grid resource business platform database; the voltage level identifier is a string, corresponding to a specific voltage level, and needs to be converted to the integer type dydj in the GIS database; the device operating status is encoded as a string, a preset enumeration value within the power grid resource business platform database; the device operation status identifier is an integer, a preset value within the power grid resource business platform database; the device rated capacity is a floating-point number, the unit of which needs to be defined according to specific business requirements; here, "0.0" indicates that the substation has not yet been configured with a rated capacity value; the device whether it has an outer frame is identified by a boolean value (true / false), where false indicates that the substation has no outer frame configuration.

[0117] C. Time and attribution data include:

[0118] Last modified time (modify_time): 2025-06-14 20:04:30;

[0119] Equipment's district code: 15D92224C348748DE0537612D00A7407;

[0120] Equipment operating unit ID (depart): 8a5093a85dd14f7801609c928b914774;

[0121] Data version ID (version_id): 2158493;

[0122] It should be noted that the last modification time of the data is in string format, following the "yyyy-MM-dd HH:mm:ss" time standard, for data version traceability; the region code of the equipment, in string format, corresponds to the administrative and power supply area division in the power grid resource business platform database, and is directly mapped to the ssds field in the GIS database; the equipment operating unit ID, in string format, is associated with the operating unit ledger in the power grid resource business platform database, and is directly mapped to the yxdw field in the GIS database; the data version ID, in string format, is used for version control of data updates in the GIS database to avoid conflicts caused by modifications from multiple devices.

[0123] D. Coordinate and graphical data include:

[0124] Space Reference System ID (SRID): 4490;

[0125] Equipment center point coordinates (coord): 108.928452232811, 34.8972719244315;

[0126] Longitude of the equipment center point (x): 108.928452232811;

[0127] Latitude (y) of the equipment center point: 34.897271924432;

[0128] Equipment spatial shape: POINT(108.92845223281134.8972719244315);

[0129] The spatial shape of the device in the GCJ02 coordinate system (shape_gcj): POINT(108.9366986780234.8931806301466);

[0130] Device port shape (port_shape): MULTIPOINT(108.9284657117534.8972384104208);

[0131] The device port in the GCJ02 coordinate system (port_shape_gcj): MULTIPOINT((108.93671217264334.8931471274085));

[0132] The rotation angle of the device graphic symbol (symbol_rotation): 0.0;

[0133] The scaling factor (symbol_scale) of the device graphic symbol: 1.0;

[0134] Device connection information code (connection): OEc4H79QlYuq2Xa75AwDzk;

[0135] It should be noted that the spatial reference system ID is in string format, corresponding to the CGCS2000 coordinate system (4490 is the EPSG code for this coordinate system), defining the reference for the coordinates; the latitude and longitude coordinates of the equipment center point are in string format (longitude, latitude), based on the CGCS2000 coordinate system, and are the basic representation of spatial location; the longitude value of the equipment center point is a floating-point number, consistent with the longitude portion of the equipment center point latitude and longitude coordinates, and is stored separately for rapid calculation; the latitude value of the equipment center point is a floating-point number, consistent with the latitude portion of the equipment center point latitude and longitude coordinates, and is stored separately for rapid calculation; the spatial shape of the equipment is described in WKT (Well-Known Technology). The point coordinates in WKT format store the latitude and longitude of the center point and are the core field of spatial location in the power grid resource business platform database. The spatial shape of the equipment in the GCJ02 coordinate system is in WKT format point coordinates. The equipment port graphic is in WKT multi-point coordinate format, storing the latitude and longitude of each port in the substation, used to display the equipment connection relationship. The graphic of the equipment port in the GCJ02 coordinate system is in WKT multi-point coordinate format. The rotation angle of the equipment graphic symbol is floating point (unit: degree). Here, "0.0" indicates that the symbol has no rotation and needs to be converted to a standard angle according to the GIS database rules. The scaling ratio of the equipment graphic symbol is floating point. Here, "1.0" indicates that the symbol is the original size and needs to be converted to the integer type fhdx in the GIS database. The equipment connection information is encoded in string format and is an encrypted identifier used in the power grid resource business platform database to record the connection relationship between equipment. Because the format is incompatible with the GIS database, the connection field in the GIS database is set to null after conversion.

[0136] S2. Further, extract all pre-configured power grid diagram device model mapping tables from the Redis cache;

[0137] See Figure 2Data from a certain power distribution station belongs to the power distribution station table g_pd_stationpsr in the power grid resource business middleware database. Based on the station power grid equipment mapping table cached in Redis, it is mapped to the station primary-power station table t_tx_znyc_dz in the GIS database, thus determining the storage table belonging to this data in the GIS database.

[0138] See Figure 3 According to the mapping table of equipment type and subtype in GIS graphics, the equipment subtype name code "252401" in the data of a certain substation is mapped to the sbzlx field value "30000005" in the GIS database, thus completing the standardized definition of equipment type;

[0139] Furthermore, by utilizing the pre-defined primary key (oid) sequence generation rules of the GIS database, a unique integer primary key (oid) of "958512462" is generated for the converted data, replacing the unique internal identifier (fid) of the device in the power grid resource business middleware database, thus ensuring the uniqueness of the data and the validity of the index in the GIS database.

[0140] S3, see also Figure 4 Based on the field mapping table between the power grid resource business platform database and the GIS power grid model, field value migration is achieved through mapping relationships. This includes direct field mapping, data type conversion of fields, and padding / emptying of fields.

[0141] Directly mapped fields:

[0142] The globally unique device ID (dev_id): OY8GzFFhkIGRDwtW8SjyLU is directly assigned to the device id field sbid in the GIS database to ensure device identification consistency.

[0143] Equipment name (name): Zhongjun Jingfeng No. 2 public transformer distribution room is simultaneously mapped to the equipment name field sbmc and the remark field bznr in the GIS database, which satisfies the dual storage requirements of the GIS database for equipment names;

[0144] The device's district code (district): 15D92224C348748DE0537612D00A7407 is directly mapped to the city / prefecture field (ssds) in the GIS database, maintaining consistency in the district affiliation information.

[0145] The equipment operation unit ID (depart): 8a5093a85dd14f7801609c928b914774 is directly assigned to the operation unit field yxdw in the GIS database to ensure that the operation unit association is correct.

[0146] Data type conversion field:

[0147] For the voltage level identifier (voltagelevel_id), the preset string-to-integer conversion algorithm is called to convert the data "21" of field type varchar in the power grid resource business middleware database to the data "21" of field type int8 in the GIS database, and then assign it to the voltage level field dydj in the GIS database;

[0148] For the rotation angle (symbol_rotation) of the equipment graphic symbol, the standardized configuration of the station symbol angle is called (the station symbol angle is preset to 90° in the project). Through special field processing logic, the floating-point "0.0" in the power grid resource business platform database is converted into the integer "90" in the GIS database and assigned to the symbol angle field fhjd in the GIS database.

[0149] For the scaling ratio (symbol_scale) of the device graphic symbol, the preset floating-point to integer conversion algorithm is called to convert the "1.0" field type of the power grid resource business middle platform database, which is of floating-point type, into the data "1" field type of the GIS database, which is of integer type, and assign it to the symbol angle field fhdx in the GIS database.

[0150] Complete empty fields:

[0151] For fields in the power grid resource business middleware database that do not have corresponding GIS database fields: such as operation number yxbh, complete with "zyf"; system type apptyp, complete with "3"; status ID (state_id), complete with "71945726"; and the date sszrq, convert it to the number "1675462" according to the data synchronization date.

[0152] For the device connection information encoding (connection): OEc4H79QlYuq2Xa75AwDzk, the format does not meet the GIS database's storage standard for connection information (GIS databases require structured connection data), so set the connection in the GIS database to null;

[0153] For equipment within the station, since the equipment graphic table in the power grid resource business platform maintains relative coordinates, and only the station building table has the absolute coordinates of the station building's center point, when performing station building conversion processing, it is also necessary to calculate the absolute coordinates of the equipment within the station based on the absolute coordinates of the station building's center point and the relative coordinates of the equipment within the station, and store them as WKT strings.

[0154] S4. Further, obtain the coordinates of the station's center point. for And the preset relative coordinates of the equipment within the station, where: Equipment 1 ( , Equipment 2 , );

[0155] Based on the current latitude, obtain the latitude and longitude conversion factor from meters to degrees: (meters → longitude) (meters → latitude); The conversion relationship between latitude and longitude and distance units is existing technology, and the specific conversion steps will not be elaborated here.

[0156] Based on the coordinates of the station's center point, the relative coordinates of the equipment within the station, and the conversion factor between latitude / longitude and distance units, the absolute latitude / longitude of the equipment within the station is calculated, expressed by the formula:

[0157] ;

[0158] In the formula, Indicates the first The absolute longitude coordinates of each device Indicates the first The absolute latitude coordinates of each device This represents the absolute longitude coordinates of the station's center point. This represents the absolute latitude and longitude coordinates of the station's center point. This indicates the conversion factor from meters to degrees for longitude. Indicates the conversion factor from meters to degrees in the latitude direction;

[0159] The absolute latitude and longitude coordinates of equipment 1 within the station are obtained as follows: The absolute latitude and longitude coordinates of equipment 2 within the station are: ;

[0160] A 0.5m physical buffer is set for the absolute latitude and longitude of the obtained equipment within the station. The 0.5m physical buffer is then converted into angular increments of latitude and longitude using a latitude-longitude conversion factor (meter to degree). The longitude buffer angle is... Latitude buffer angle is ;

[0161] S41. Further obtain the four boundaries (latitude and longitude) of the station building (i.e., the border range), expressed by the formula:

[0162] ;

[0163] In the formula, This represents the minimum longitude of the station building's perimeter. This indicates the maximum longitude of the station building's perimeter. This represents the minimum latitude of the station building's perimeter. Indicates the maximum latitude of the station building's perimeter;

[0164] S42. Arrange the vertices [(Lon_min,Lat_min),(Lon_min,Lat_max),(Lon_max,Lat_max),(Lon_max,Lat_min),(Lon_min,Lat_min)] according to the WKT rules, resulting in (108.9283579,34.8972108),(108.9283579,34.8973331),(108.9285466,34.8973331),(108.9285466,34.8972108),(108.9283579,34.8972108).

[0165] S43. Convert the latitude and longitude coordinates of the vertex to radians, expressed by the formula:

[0166] ;

[0167] ;

[0168] In the formula, Longitude of station equipment expressed in radians Longitude of station equipment in angular form. The latitude of the station equipment is expressed in radians. The latitude of the station's equipment is expressed in angular form;

[0169] Convert the latitude and longitude of the station equipment in radians to Mercator coordinates, expressed by the formula:

[0170] ;

[0171] ;

[0172] In the formula, The Mercator x-coordinate represents the equipment within the station. Represents the Earth's average radius. Indicates the Mercator coordinates of the equipment within the station;

[0173] The universal Mercator coordinates of the equipment within the station are obtained, expressed by the formula:

[0174] ;

[0175] In the formula, , , , These represent the four general Mercator coordinates of the equipment within the station;

[0176] S44. Further, call the Siji Map API to input the calculated general Mercator coordinates of the devices within the station into the Mercator coordinate offset algorithm in Siji Map, expressed as the formula:

[0177] ;

[0178] In the formula, This represents the horizontal axis of the State Grid Mercator graph. This represents the Mercator coordinate of the State Grid. Represents the universal Mercator x-axis. Represents the general Mercator ordinate. This indicates the preset offset of the Mercator x-axis for the State Grid Corporation of China. This indicates the preset offset of the State Grid Mercator coordinate system;

[0179] In this embodiment, the State Grid Mercator horizontal coordinate preset offset is... State Grid Mercator ordinate preset offset Substituting these values ​​into the Mercator coordinate offset algorithm, we obtain the Mercator coordinate data of the equipment within the station in the GIS database: POLYGON((12126765.869699754, 4149368.023012099), (12126765.872498278, 4149380.4764570207), (12126789.781444246, 4149380.5306266504), (12126789.778645791, 4149368.0771795833), (12126765.869699754, 4149368.023012099)).

[0180] S5. Further, handle the subordinate relationships. Some power grid equipment has subordinate relationships, which need to be handled. Specifically:

[0181] For containerized power grid equipment (such as substations, distribution stations, feeders, transmission lines, physical poles, etc.), it is necessary to process the relationships between the sub-equipment it contains, such as the station, feeder, line, and pole.

[0182] The relationship is a field attribute of the sub-device table in the GIS data model. Its value is the oid of the device to which it belongs. The parent device oid is the identification information assigned to the parent device in the parent container table.

[0183] When processing the ownership relationship, first determine whether the device needs to have its ownership relationship processed. If not, skip it directly. If it needs to be processed, first find the corresponding parent container table based on the device ID and type of the data, and then look up the data in the parent container table based on the parent device ID. If found, store the OID of the parent container in the ownership relationship field. If not found, it means that the parent container device has not yet been synchronized. Therefore, store the data in a temporary table and use a scheduled task to periodically read the data from this table for post-processing.

[0184] S6. Store the converted graphic data into the GIS database to complete the real-time processing and synchronization of all graphic data.

[0185] Example 3: Based on Example 1, this example proposes an incremental synchronization alternative method that eliminates the preset shared database and the Flink CDC mechanism, specifically as follows:

[0186] See Figure 5 The power grid resource business middleware database has an anomaly commit record table, `modelcenter_edit.commit_record`, which records the ID of each anomaly device, the data before the change, the data after the change, and the commit time (`commit_time`). In the data synchronization service, a scheduled task scans the anomaly commit record table every 5 seconds based on the commit time. If new anomaly data is found, it is encapsulated into JSON and sent to Kafka for processing by the shared database service. This ensures the integrity of the power grid resource business middleware database and allows for timely acquisition of anomaly data.

[0187] It should be noted that while the incremental anomaly data synchronization technology based on PostgreSQL logical replication and Flink CDC real-time capture mechanism can accurately and efficiently capture and process anomaly data, this architecture requires intrusion into the power grid resource business middleware database system to modify the database WAL log level to Logical. The default WAL level is replica, and Logical is higher than replica, leading to a significant increase in the database WAL log volume. Furthermore, PostgreSQL's logical replication mechanism does not support automatic synchronization of table structure DDL changes. If the upstream database undergoes table structure changes and generates anomaly data, and the downstream database fails to synchronize in time, it will cause replication to stall. The upstream database will retain fragments of the WAL log at the point of interruption, and as the anomaly data increases, the WAL log will gradually expand. Both of these factors will cause the disk of the power grid resource business middleware database to gradually expand, eventually exhausting the disk and causing the database to crash. Therefore, an alternative solution of canceling the preset shared database and the Flink CDC mechanism is proposed.

[0188] Example 4: This example proposes a fully incremental synchronization system based on real-time change data capture technology, including the following modules:

[0189] Full synchronization module: used to access the power grid resource business platform through the REST interface to obtain full graphic data; further perform synchronization conversion operation on the full graphic data, and batch synchronize the converted GIS graphic data to the GIS database;

[0190] Incremental synchronization module: used to capture abnormal data from the power grid resource business platform through a preset data synchronization service, encapsulate the abnormal data into a standardized data format and push it to the message middleware. The message middleware obtains the abnormal data in the standardized data format, inputs it into the synchronization conversion stage, performs the synchronization conversion operation, and synchronizes the converted abnormal data to the GIS database.

[0191] Example 5:

[0192] This embodiment proposes an electronic device, including a memory, a processor, and a computer program stored in the memory and executable on the processor. When the processor executes the program, it implements the method described in any embodiment of the present invention.

[0193] Example 6:

[0194] This embodiment proposes a computer-readable storage medium storing a computer program thereon, which, when executed by a processor, implements the method described in any embodiment of the present invention.

[0195] In this application embodiment, "at least one" refers to one or more, and "more than one" refers to two or more. "And / or" describes the relationship between related objects, indicating that three relationships can exist. For example, A and / or B can represent the existence of A alone, A and B simultaneously, or B alone. A and B can be singular or plural. The character " / " generally indicates that the preceding and following related objects are in an "or" relationship. "At least one of the following" and similar expressions refer to any combination of these items, including any combination of singular or plural items. For example, at least one of a, b, and c can represent: a, b, c, a and b, a and c, b and c, or a and b and c, where a, b, and c can be single or multiple.

[0196] Those skilled in the art will recognize that the units and algorithm steps described in the embodiments disclosed herein can be implemented using electronic hardware, computer software, or a combination of electronic hardware and software. Whether these functions are implemented in hardware or software depends on the specific application and design constraints of the technical solution. Those skilled in the art can use different methods to implement the described functions for each specific application, but such implementation should not be considered beyond the scope of this application.

[0197] Those skilled in the art will understand that, for the sake of convenience and brevity, the specific working processes of the systems, devices, and units described above can be referred to the corresponding processes in the foregoing method embodiments, and will not be repeated here.

[0198] In the several embodiments provided in this application, any function, if implemented as a software functional unit and sold or used as an independent product, can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of this application, in essence, or the part that contributes to the prior art, or a part of the technical solution, can be embodied in the form of a software product. This computer software product is stored in a storage medium and includes several instructions to cause a computer device (which may be a personal computer, server, or network device, etc.) to execute all or part of the steps of the methods described in the various embodiments of this application. The aforementioned storage medium includes various media capable of storing program code, such as USB flash drives, portable hard drives, read-only memory (ROM), random access memory (RAM), magnetic disks, or optical disks.

[0199] The above description is merely an embodiment of the present invention and does not limit the patent scope of the present invention. Any equivalent structural or procedural transformations made based on the content of the present invention's specification and drawings, or direct or indirect applications in other related technical fields, are similarly included within the patent protection scope of the present invention.

Claims

1. A fully incremental synchronization method based on real-time change data capture technology, characterized in that, The steps include a full synchronization process and an incremental synchronization process: The full synchronization process accesses the power grid resource business platform through the REST interface to obtain the full set of graphic data; performs a synchronization conversion operation on the full set of graphic data, and then batches the converted GIS graphic data to the GIS database. The incremental synchronization process uses a preset data synchronization service to capture incremental anomaly data from the power grid resource business platform using real-time change data capture technology. The incremental anomaly data is then encapsulated into a standardized data format and pushed to the message middleware. The message middleware obtains the incremental anomaly data in the standardized data format, inputs it into the synchronization conversion stage, performs the synchronization conversion operation, and then synchronizes the converted incremental anomaly data to the GIS database. The synchronous conversion operation includes core model mapping, field conversion, coordinate conversion, and relationship matching. The core model mapping is used to establish a correspondence between the equipment models of the power grid resource business platform and the equipment models of the GIS database. The field conversion is used to convert the types and formats of the fields of the equipment data. The coordinate conversion is used to convert the latitude and longitude coordinates of the power grid equipment into Mercator coordinates compatible with the GIS database. The relationship matching is used to determine the subordinate relationships between the equipment in the power grid resource business platform. The preset data synchronization service uses either intrusive or non-intrusive abnormal data capture, wherein: Intrusive anomaly data capture: Access the power grid resource business middleware database to obtain anomaly data; use PostgreSQL database logical replication technology to copy the anomaly data to the preset common database in real time; further use Flink CDC real-time change data capture technology to capture the anomaly data in the preset common database. Non-intrusive anomaly data capture: Access the anomaly submission record table in the power grid resource business middleware database. The anomaly submission record table records the ID of the anomaly device, the data before the change, the data after the change, and the anomaly submission time. Configure a timed task to traverse the anomaly submission record table at preset time intervals according to the anomaly submission time and capture the newly added anomaly data.

2. The full incremental synchronization method based on real-time change data capture technology according to claim 1, characterized in that, The synchronous transformation operation includes a coordinate transformation step, specifically: Convert the latitude and longitude coordinates of the equipment in the full graphic data and incremental anomaly data to Mercator coordinates. Obtain the absolute coordinates of the station equipment stored in the WKT string, and get the longitude of the equipment. and latitude Convert the latitude and longitude, currently expressed in degrees, to radians using the following formula: ； ； In the formula, Longitude of station equipment expressed in radians Longitude of station equipment in angular form. The latitude of the station equipment is expressed in radians. The latitude of the station's equipment is expressed in angular form; Convert the latitude and longitude of the station equipment in radians to Mercator coordinates, expressed by the formula: ； ； In the formula, The Mercator x-coordinate represents the equipment within the station. Represents the Earth's average radius. This represents the Mercator coordinate of the equipment within the station.

3. The full incremental synchronization method based on real-time change data capture technology according to claim 2, characterized in that, The method described above calculates the Mercator coordinate ordinate for high-latitude regions using the following formula: ； ； In the formula, The eccentricity of the Earth's ellipsoid is represented by its eccentricity. Represents the minor axis of the Earth's ellipsoid. It represents the semi-major axis of the Earth's ellipsoid.

4. The full incremental synchronization method based on real-time change data capture technology according to claim 1, characterized in that, The synchronous conversion operation includes a relationship matching step, specifically: Determine whether the current device performing the synchronization operation needs to handle the ownership relationship; if not, skip it directly. If processing is required, first find the corresponding parent container table based on the device ID and type of the data, and then find the OID of the parent device in the parent container table based on the parent device ID. If found, store the OID of the parent device in the parent container table into the field of the relationship. If no match is found, the data of the devices whose relationships failed to match will be stored in a temporary table, and the data in the temporary table will be read and processed periodically by a scheduled task. Among them, the parent device oid is the identification information assigned to the parent device in the parent container table.

5. The full incremental synchronization method based on real-time change data capture technology according to claim 1, characterized in that, The method utilizes the logical replication technology of PostgreSQL databases to replicate abnormal data to a preset shared database in real time, specifically as follows: Create a pre-defined PostgreSQL database dedicated to the shared database, and configure the WAL logical log level of the PostgreSQL database to logical; Adjust the WAL logical log level of the power grid resource business middleware database to logical; Establish a logical replication relationship between the power grid resource business middleware database and the preset common database, and fully copy the graphical tables, resource tables, and asset tables of power grid equipment to the preset common database.

6. The full incremental synchronization method based on real-time change data capture technology according to claim 5, characterized in that, In the incremental synchronization process, during the step of capturing incremental anomaly data from the power grid resource business platform through a preset data synchronization service, the anomaly data in the preset shared database is captured using Flink CDC real-time change data capture technology. Specifically: Configure the source data source of Flink CDC to the preset common database, and listen to the graph table, resource table and asset table of power grid equipment in the preset common database; Flink CDC is used to parse the WAL logical log of the preset common database to capture the addition, update and deletion events of the graphical table, resource table and asset table of power grid equipment, and at the same time obtain the data before the change and the data after the change, which are the abnormal data; The acquired anomaly data is encapsulated in JSON format and pushed to Kafka; Configure a Kafka data source and register a Kafka listener to receive abnormal data through preset topics and partitions, and call the Handler to perform synchronous transformation operations.

7. A fully incremental synchronization system based on real-time change data capture technology, characterized in that, Includes the following modules: Full synchronization module: The full synchronization process accesses the power grid resource business platform through the REST interface to obtain full graphic data; performs synchronization conversion operation on the full graphic data, and batch synchronizes the converted GIS graphic data to the GIS database; Incremental synchronization module: The incremental synchronization process uses a preset data synchronization service to capture incremental anomaly data from the power grid resource business platform using real-time change data capture technology. The incremental anomaly data is encapsulated into a standardized data format and pushed to the message middleware. The message middleware obtains the incremental anomaly data in the standardized data format, inputs it into the synchronization conversion stage, performs the synchronization conversion operation, and then synchronizes the converted incremental anomaly data to the GIS database. The synchronous conversion operation includes core model mapping, field conversion, coordinate conversion, and relationship matching. The core model mapping is used to establish a correspondence between the equipment models of the power grid resource business platform and the equipment models of the GIS database. The field conversion is used to convert the types and formats of the fields of the equipment data. The coordinate conversion is used to convert the latitude and longitude coordinates of the power grid equipment into Mercator coordinates compatible with the GIS database. The relationship matching is used to determine the subordinate relationships between the equipment in the power grid resource business platform. The preset data synchronization service uses either intrusive or non-intrusive abnormal data capture, wherein: Intrusive anomaly data capture: Access the power grid resource business middleware database to obtain anomaly data; use PostgreSQL database logical replication technology to copy the anomaly data to the preset common database in real time; further use Flink CDC real-time change data capture technology to capture the anomaly data in the preset common database. Non-intrusive anomaly data capture: Access the anomaly submission record table in the power grid resource business middleware database. The anomaly submission record table records the ID of the anomaly device, the data before the change, the data after the change, and the anomaly submission time. Configure a timed task to traverse the anomaly submission record table at preset time intervals according to the anomaly submission time and capture the newly added anomaly data.

8. An electronic device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that, When the processor executes the program, it implements the method as described in any one of claims 1 to 6.

9. A computer-readable storage medium having a computer program stored thereon, characterized in that, When the program is executed by the processor, it implements the method as described in any one of claims 1 to 6.

Images