A method for connecting disconnected road tracks

Abstract

The present invention discloses a method for connecting disconnected road tracks, and the method comprises the following steps: S1, writing multiple sections of original road track data that meet the conditions into a track node table, taking the starting point and the end point of each original road track as the starting point and the end point of the node respectively, and initializing them into a database; S2, querying the track node table to associate the nodes under the same road with each other, and then calculating the track side length between nodes and writing them into the track side table; S3, querying the track side table in groups, obtaining all road codes, performing the shortest distance operation on all track side lengths under the same road and sorting them; S4, querying the track side lengths that can be connected in pairs after sorting, obtaining the completed track by connecting them by straight lines, and rewriting the completed track into the track side table; S5, summarizing the original road tracks and the completed track, and finally forming a continuous and complete road track, which is automatically connected by a program, thereby avoiding a large number of manual operations and improving efficiency.

Description

Technical Field

[0001] The present invention relates to the technical field of computer road track connection, and in particular to a method for disconnecting road tracks. Background Art

[0002] In the field of traffic geography, roads are the most important geographical entities. Roads are divided into up and down tracks according to opposite directions. In many cases, each up and down track cannot be a continuous straight line due to different road names, missing data collected on some sections, and other reasons. There are many disconnected line segments in the middle, especially the distance between some segments is very short. In the management and application scenarios of road network data, these disconnected tracks will cause road discontinuity. The locations of these disconnections affect the management of roads and the positioning of accident points relative to roads.

[0003] Currently, these disconnected roads are basically discovered and reconnected manually. For example, if the distance between the uplink section A and the uplink section B is less than 1 km, the two sections are connected to C by manual annotation. If the distance between A and B is greater than 1 km, no operation is performed. Since there are many roads in a city, a medium-sized city usually has thousands of them, the manual workload will be very large, and an automated program is needed to automatically reconnect the disconnected road tracks within a certain distance. One of the most core technical issues in reconnecting disconnected tracks is how to discover the order of the road tracks. Only by knowing the order of the tracks can the disconnected parts be connected.

[0004] The Traveling Salesman Problem (TSP) is one of the famous NP-hard (non-deterministic polynomial) problems in the field of combinatorial optimization. The classic formulation of this problem is: "A traveling salesman wants to go to various cities to sell goods. He starts from city A, passes through the remaining cities at least once, and finally returns to city A. How to plan the route so that the total journey is the shortest?" This problem has a relatively wide range of engineering applications and a background of real-world problems. Summary of the invention

[0005] The present invention aims to provide a method for connecting disconnected road tracks to solve the problems raised in the above background technology.

[0006] To achieve the above object, the present invention provides the following technical solution: a method for connecting disconnected road tracks, the method comprising the following steps;

[0007] S1. Write multiple segments of original road trajectory data that meet the conditions into the trajectory node table, and use the starting point and end point of each original road trajectory as the starting point and end point of the node, respectively, and initialize them into the database;

[0008] S2, query the trajectory node table to associate the nodes on the same road with each other, and then calculate the trajectory edge length between nodes and write it into the trajectory edge table;

[0009] S3, query the track edge table in groups, obtain all road codes, perform the shortest distance calculation on all track edge lengths on the same road and sort them;

[0010] S4, query the side lengths of the trajectories that can be connected in pairs after sorting, obtain the completed trajectories by connecting them with straight lines, and rewrite the completed trajectories into the trajectory edge table;

[0011] S5. Summarize the original road trajectory and the completed trajectory to finally form a continuous complete road trajectory.

[0012] As a priority, the step S1 includes the following sub-steps:

[0013] S11, querying original road trajectory data, and determining whether the number of original road trajectories is greater than or equal to two;

[0014] S12, writing the original road trajectory data greater than or equal to two into the trajectory node table.

[0015] As a priority, the step S2 includes the following sub-steps:

[0016] S21, performing a self-association query on the table with the same road code and different road tracks as conditions, to obtain a first self-association table n1 and a second self-association table n2;

[0017] S22, respectively obtain the lengths of the road trajectories of n1 and n2, and simultaneously obtain the straight-line distance from the end point of the road trajectory of n1 to the start point of the road trajectory of n2, and write them into the trajectory edge table.

[0018] As a priority, the step S3 includes the following sub-steps:

[0019] S31, querying the trajectory edge table by road code group, and obtaining a temporary table of the full set of road codes to be calculated;

[0020] S32. Use the TSP algorithm to perform an associated query on the temporary table of the entire set of road codes to obtain a result set.

[0021] As a priority, the result set includes fields for road code, TSP sequence, node code, cost, and total cost.

[0022] As a priority, in step S32, a cost matrix is ​​generated by using a function through the trajectory edge table, and the optimal order of node traversal is obtained using the TSP algorithm, and a result set is output.

[0023] As a priority, the completed trajectory rewritten into the trajectory edge table in step S4 is marked as a new connection trajectory.

[0024] As a priority, the step S5 includes the following sub-steps:

[0025] S51, querying the track edge table by road code grouping;

[0026] S52, calling a collection function to connect and merge the trajectories on the same road to form a complete trajectory.

[0027] Beneficial effects of the present invention: The present invention automatically identifies the disconnected road tracks between the starting point and the end point during a business trip, and connects the disconnected parts of the road tracks through program automation. For cities that edit thousands of roads, this can greatly save the work of operators, avoid a large amount of manual operations, and improve efficiency. After the program is completed, only single or batch confirmation is required, and the operation is convenient. BRIEF DESCRIPTION OF THE DRAWINGS

[0028] Figure 1 A schematic diagram of a flow chart of a method for disconnecting a road track according to an embodiment of the present invention;

[0029] Figure 2 Schematic diagram of the distance between nodes and edge lengths in an embodiment of the present invention;

[0030] Figure 3 is a schematic diagram of a result set in an embodiment of the present invention;

[0031] Figure 4 A schematic diagram of the distance from the starting point to the end point of the entire travel track in an embodiment of the present invention;

[0032] Figure 5 Schematic diagram of completing a trajectory in an embodiment of the present invention;

[0033] Figure 6 Schematic diagram of the database table structure in an embodiment of the present invention. DETAILED DESCRIPTION

[0034] The following will be combined with the drawings in the embodiments of the present invention to clearly and completely describe the technical solutions in the embodiments of the present invention. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention.

[0035] See also Figures 1 to 6 , the present invention provides a method for connecting disconnected road tracks, the method comprising the following steps;

[0036] S1. Write multiple segments of original road trajectory data that meet the conditions into the trajectory node table, and use the starting point and end point of each original road trajectory as the starting point and end point of the node, respectively, and initialize them into the database;

[0037] S2, query the trajectory node table to associate the nodes on the same road with each other, and then calculate the trajectory edge length between nodes and write it into the trajectory edge table;

[0038] S3, query the track edge table in groups, obtain all road codes, perform the shortest distance calculation on all track edge lengths on the same road and sort them;

[0039] S4, query the side lengths of the trajectories that can be connected in pairs after sorting, obtain the completed trajectories by connecting them with straight lines, and rewrite the completed trajectories into the trajectory edge table;

[0040] S5. Summarize the original road trajectory and the completed trajectory to finally form a continuous complete road trajectory.

[0041] Specifically, step S1 includes the following sub-steps:

[0042] S11, querying original road trajectory data, and determining whether the number of original road trajectories is greater than or equal to two;

[0043] S12, writing the original road trajectory data greater than or equal to two into the trajectory node table.

[0044] Specifically, step S2 includes the following sub-steps:

[0045] S21, performing a self-association query on the table with the same road code and different road tracks as conditions, to obtain a first self-association table n1 and a second self-association table n2;

[0046] S22, respectively obtain the lengths of the road trajectories of n1 and n2, and simultaneously obtain the straight-line distance from the end point of the road trajectory of n1 to the start point of the road trajectory of n2, and write them into the trajectory edge table.

[0047] Specifically, step S3 includes the following sub-steps:

[0048] S31, querying the trajectory edge table by road code group, and obtaining a temporary table of the full set of road codes to be calculated;

[0049] S32. Use the TSP algorithm to perform an associated query on the temporary table of the entire set of road codes to obtain a result set.

[0050] Specifically, the result set includes fields such as road code, TSP sequence, node code, cost, and total cost.

[0051] Specifically, in step S32, a cost matrix is ​​generated by using a function through the trajectory edge table, and the optimal order of node traversal is obtained by using the TSP algorithm, and a result set is output.

[0052] Specifically, the completed trajectory rewritten into the trajectory edge table in step S4 is marked as a new connection trajectory.

[0053] Specifically, step S5 includes the following sub-steps:

[0054] S51, querying the track edge table by road code group;

[0055] S52, calling a collection function to connect and merge the trajectories on the same road to form a complete trajectory.

[0056] The present invention includes the following embodiments:

[0057] Embodiment 1: writing multiple segments of trajectory data of the original road trajectory into the trajectory node table, taking the starting point of each original road trajectory as the starting point and the ending point of the original road trajectory as the end point, and initializing it into the database, specifically includes the following steps:

[0058] 1. Query the original road trajectory data that meets the conditions and needs to be connected. The judgment condition is: by aggregating the road codes, if the number of original road trajectories is greater than or equal to 2, it indicates that the road is disconnected.

[0059] 2. Write the original road trajectory data that meets the conditions into the trajectory node table. The trajectory node table uses the starting point and end point of the original road trajectory as the starting point and end point of the node. It is mainly used to filter the road trajectories that need to be processed. The filtering steps are:

[0060] 1) Query the original road trajectory information and group it by road code;

[0061] 2) If there are two or more original road trajectories on a road, it is considered to meet the conditions and is retained;

[0062] 3) Write the original road trajectory data that meets the conditions into the trajectory node table.

[0063] Example 2: Query the trajectory node table to generate the trajectory edge table to prepare basic data for the next step of the TSP algorithm. The specific method is as follows:

[0064] 1. Query the trajectory node table data generated in Example 1.

[0065] 2. Associate the nodes on the same road with each other and calculate the edge length between nodes. Take two trajectories A and B on a road as an example. The detailed description is as follows:

[0066] 1) Perform a self-association query on the table with the condition that the road codes are the same but the road track IDs are different, and the first self-association table is n1 and the second self-association table is n2;

[0067] 2) Get the length of the road track of n1 and convert it into a geographic object. The obtained length is based on the earth's spherical surface and is in meters. Similarly, get the length of the road track of n2, and then get the straight-line distance from the end point of the road track of n1 to the starting point of the road track of n2. The distance is also first converted into a geographic object. The distance from the road track of n1 to the road track of n2 is half the length of the road track of n1 + the straight-line length from the end point of the road track of n1 to the starting point of the road track of n2 + half the length of the road track of n2.

[0068] 3) Take the A and B trajectories of the same road as an example. Figure 2 As shown, the distance from the A road track to the B road track is Figure 2 In X+Y+Z, the road code, road name, n1 road trajectory id, n2 road trajectory id, and the distance from n1 to n2 calculated in the previous step are written into the trajectory edge table as fields, thus providing the data foundation for the next step of the TSP algorithm.

[0069] Embodiment 3: Apply the TSP algorithm to query the trajectory edge table to obtain the node order of the shortest path of the road trajectory, that is, the order of the road trajectory.

[0070] 1. Query the trajectory edge table in groups, obtain all road codes, and perform the shortest distance calculation of the TSP algorithm on the edge lengths of all road trajectories under the same road.

[0071] 1) querying the track edge table obtained in Example 2, grouping by road code, and obtaining a temporary table of all road codes that need to be calculated;

[0072] 2) Perform a CROSS JOIN LATERAL on the temporary table of all road codes and the method containing TSP calculation;

[0073] 3) CROSS JOIN LATERAL join operation is to perform Cartesian product join on each row of the main query and each result of the subquery (or table function). Features: Each row is passed from the main table to the LATERAL subquery or function; the result returned by the subquery forms a group with the current row of the main table; if the subquery does not return a result, the corresponding row of the main table will be filtered out;

[0074] 4) Through the above association query, a result set is obtained, the fields of which are road code, TSP sequence (representing the city travel order of the traveler algorithm), node code (trajectory id), cost (the distance from the previous node to the current node), and total cost (the total cost from the first node to the current node).

[0075] The cost matrix, i.e. the distance matrix between each node, is generated by using the pgr_dijkstraCostMatrix function of the pgRouting module, which relies on the trajectory edge table. This method has three parameters: 1: edge query SQL, 2: array of identifiers of the starting vertex, and 3: distance (directed). When it is true, Figure 4 As shown, Figure 4 Treated as a directed graph, currently trajectories are considered to be directed, so set this to true.

[0076] After the cost matrix is ​​generated, the pgr_TSP algorithm in the TSP method is applied to obtain the optimal order of node traversal. The edge query SQL will output the following: Figure 4 As shown, the best traversal order of nodes is obtained. Due to the traveler algorithm, it is necessary to return to the original city, such as Figure 4 The starting point 389780 is the starting point and also the end of the journey.

[0077] After the pgr_TSP algorithm is completed, the previous trajectory, current trajectory, and next trajectory of each trajectory are obtained through the lead and lag window functions, such as Figure 3 After processing, we get Table 1.

[0078] Table 1:

[0079]

[0080] Table 1 is reorganized again, and the records with null track ID are excluded, and the sorted records are put into Table 2;

[0081] Table 2:

[0082]

[0083] As described in Table 2, the trajectory forms a ring. The one with the largest distance is removed. Generally, it is the record from the end of the road to the starting point of the road, which needs to be deleted.

[0084] like Figure 4 As shown, the distance from 389785 to 389780 is half the length of the 389785 track + half the length of the 389780 track + the end point A of the 389785 track to the starting point of the 309780 track. This record is exactly the beginning and end of the road and does not need to be completed.

[0085] Example 4: After the data processing of Example 3, the road trajectories that can be connected in pairs are found, and the road trajectories that are disconnected in pairs are directly connected by straight lines to obtain new completed trajectories to obtain Table 3;

[0086] Table 3:

[0087]

[0088] As shown in Table 3 above, the current trajectory ID and the next trajectory ID are the trajectory IDs that can be connected, that is, the disconnected part in the middle of the road. The specific steps are as follows:

[0089] 1) The original road track table stores the geom information of the track. The original road track table is queried through the current track ID to obtain the track object this_track_geom;

[0090] 2) Query the original road track table through the post-track ID to obtain the track object next_track_geom;

[0091] 3) Use the function ST_MakeLine(ST_Endpoint(this_track_geom), ST_Startpoint(next_track_id)) in the relational database management system postgresql to generate a new track, that is, a straight line connecting the track of the current track ID and the track corresponding to the next track ID.

[0092] 4) Write the above connection trajectory into the trajectory node table, which belongs to the currently calculated road, and mark it as a new connection trajectory, such as Figure 5 As shown;

[0093] The connection between road trajectories depends on the actual business scenario. Straight-line connection can be used, or in a similar road network scenario, the link of the road network can be used to complete the trajectory to reflect the actual road trajectory curve.

[0094] Example 5: Summarize the original road trajectory and the newly added completion trajectory to form a continuous complete road trajectory, the steps are as follows:

[0095] 1) Query the road trajectory table and group by road code. For example, road X has three trajectories: A, B, and C. Trajectory C is a new connection trajectory obtained by connecting the TSP algorithm process in Example 4.

[0096] 2) After grouping by road code, call the collection function st_collect(geom) to merge the trajectories into a MultiLineString collection.

[0097] 3) Calling the Postgresql function ST_LineMerge(geom,true) through the MultiLineString collection will automatically connect the tracks A, B, and C in order from beginning to end to form a continuous track D. The object type of this track is generally LineString, that is, a new and complete track that merges A, B, and C. At this point, the work of merging the broken road tracks is completed.

[0098] The above description is only a preferred embodiment of the present invention and is not intended to limit the present invention. Any modification, equivalent substitution or improvement made within the spirit and principle of the present invention should be included in the protection scope of the present invention.

Claims

1. A method for connecting disconnected road tracks, characterized in that; The method comprises the following steps: S1. Write multiple segments of original road trajectory data that meet the conditions into the trajectory node table, and use the starting point and end point of each original road trajectory as the starting point and end point of the node, respectively, and initialize them into the database; S2, query the trajectory node table to associate the nodes on the same road with each other, and then calculate the trajectory edge length between nodes and write it into the trajectory edge table; S3, query the track edge table in groups, obtain all road codes, perform the shortest distance calculation on all track edge lengths on the same road and sort them; S4, query the side lengths of the trajectories that can be connected in pairs after sorting, obtain the completed trajectories by connecting them with straight lines, and rewrite the completed trajectories into the trajectory edge table; S5, summarizing the original road trajectory and the completed trajectory to finally form a continuous complete road trajectory; The step S2 includes the following sub-steps: S21, performing a self-association query on the table with the same road code and different road tracks as the condition, the first self-association table is n1, and the second self-association table is n2; S22, respectively obtain the lengths of the road trajectories of n1 and n2, and simultaneously obtain the straight-line distance from the end point of the road trajectory of n1 to the start point of the road trajectory of n2, and write them into the trajectory edge table; The step S3 includes the following sub-steps: S31, querying the trajectory edge table by road code group, and obtaining a temporary table of the full set of road codes to be calculated; S32, query the temporary table of the entire set of road codes by using the TSP algorithm to obtain a result set; The result set includes fields such as road code, TSP sequence, node code, cost, and total cost; In step S32, a cost matrix is ​​generated by using a function through the trajectory edge table, and the optimal order of node traversal is obtained by using the TSP algorithm, and a result set is output.

2. A method for connecting disconnected road tracks according to claim 1, characterized in that; The step S1 includes the following sub-steps: S11, querying original road trajectory data, and determining whether the number of original road trajectories is greater than or equal to two; S12, writing the original road trajectory data greater than or equal to two into the trajectory node table.

3. A method for connecting disconnected road tracks according to claim 1, characterized in that; The completed trajectory rewritten into the trajectory edge table in step S4 is marked as a new connection trajectory.

4. A method for connecting disconnected road tracks according to claim 1, characterized in that; The step S5 comprises the following sub-steps: S51, querying the track edge table by road code grouping; S52, calling a collection function to connect and merge the trajectories on the same road to form a complete trajectory.

Images