A GNSS trajectory data storage and query method for rapid retrieval of position trajectories

Abstract

The present application belongs to the technical field of GNSS trajectory processing, and discloses a GNSS trajectory data storage and query method for rapid retrieval of position trajectory. After the user uploads the trajectory data and converts the position, the trajectory data is stored in a combination of a Redis database and a MySQL database; the Redis database stores the user trajectory data of the day, and the MySQL database stores the user trajectory data of the historical date; the background timing is performed at 0 o'clock every day, the user trajectory data of the day recorded in the Redis database is simplified, and then stored in the MySQL database; the number of stored record entries of the trajectory data table of the MySQL database is monitored at a fixed time every day, when the number of stored record entries of the trajectory data table reaches the upper threshold, a new trajectory data table is created for data storage; a query scheme is provided for real-time position and historical trajectory. The method meets the needs of long-period storage of user trajectory data and real-time position and historical trajectory query.

Description

Technical Field

[0001] The present invention relates to the technical field of GNSS trajectory processing, and in particular to a GNSS trajectory data storage and query method for rapid retrieval of position trajectories. Background Art

[0002] With the rapid development of intelligent transportation systems (ITS) and connected vehicle (IoV) technologies, the collection, storage, and analysis of vehicle trajectory data have become crucial support for smart cities, logistics management, public transportation regulation, and other fields. The widespread adoption of global positioning systems (GNSS), on-board sensors, and mobile communication technologies has led to an explosive growth in vehicle trajectory data. However, the storage, efficient compression, and rapid query of massive amounts of trajectory data still face challenges such as high storage costs and low query efficiency.

[0003] Traditional trajectory storage stores user locations one by one, but when the user usage cycle is too long, various problems will arise due to the storage and retrieval efficiency limitations of the database itself. Therefore, a sharding solution has emerged, which includes separating data according to different users, separating hot and cold data, and designing balanced database storage. However, none of these solutions can solve the series of problems caused by excessive data volume. At the same time, in order to meet the needs of current multi-field applications, it is often necessary to display real-time locations and historical trajectories on carriers such as websites. When the data volume is too large, it will bring new burdens and challenges to interface requests, map rendering, etc.

[0004] The technical solution described in CN117648391A aggregates individual trajectory data pieces during storage using random number matching, shifting from one-by-one database additions to batch additions. This avoids issues caused by repeated database connections and ensures a uniform distribution of GNSS trajectory data. Furthermore, multi-threaded, multi-table joint queries are used to query multiple groups of data, improving query efficiency. However, this approach introduces latency in trajectory storage and fails to meet the requirements of real-time queries.

[0005] The technical solution described in CN117591757A uses an HBase database to establish a multi-level index for data storage. During queries, natural language query information is converted into a target query request formed by a database query statement. However, its main focus is on compatibility between temporal and spatial queries, and it lacks good compatibility with real-time location and historical trajectory.

[0006] The technical solution described in CN115964449A provides a real-time dynamic trajectory simplification method that can effectively reduce the amount of trajectory data and meet the requirements of real-time processing. However, there are cases where the representation of trajectory feature points (such as corner points) is insufficient, and the geometric characteristics of the trajectory are not preserved as much as possible. Summary of the Invention

[0007] The purpose of the present invention is to provide a GNSS trajectory data storage and query method for rapid retrieval of position trajectories. This method can meet the needs of long-term recording of user trajectories, while meeting the needs of real-time position and historical trajectory query, and can also reduce the amount of trajectory data as much as possible while ensuring the presence of trajectory feature points.

[0008] The technical solution of the present invention is as follows: a GNSS trajectory data storage and query method for rapid retrieval of position trajectories, comprising the following steps: Step 1: after the location data uploaded by the user is converted to a location, the trajectory data is stored in a combination of a Redis database and a MySQL database; the trajectory data includes multiple location data; the Redis database stores the user trajectory data of the current day, and the MySQL database stores the user trajectory data of historical dates;

[0009] Step 2: At 0:00 every day, a backend scheduled task is used to simplify the user trajectory data recorded in the Redis database for that day. The simplified user trajectory data for that day is stored in the MySQL database.

[0010] Step 3: Monitor the number of records stored in the trajectory data table of the MySQL database on a daily basis. When the number of records stored in the trajectory data table reaches the upper threshold, create a new trajectory data table for data storage.

[0011] Step 4: Provide query solutions for real-time location and historical trajectory.

[0012] The storage method of the Redis database is as follows:

[0013] The key name format of the Redis database is "user type: user ID: date". The user type and user ID are determined according to the actual needs of different systems. The date is used to distinguish the trajectory data of the same user on different dates.

[0014] The Redis database value type is a list collection, which is used to store each user's daily trajectory data;

[0015] Calculate user-uploaded location data The position data of the previous record at the end of the list set corresponding to the key in the Redis database When the distance is greater than the threshold, the location data uploaded by the user will be Placed at the end of the list set under the corresponding key in the Redis database, the record is When a user uploads location data for the first time on a given day, the location data will be directly recorded and marked as ; The recorded location data is arranged in chronological order.

[0016] The step 2 is specifically as follows:

[0017] A scheduled task is executed at 0:00 every day, which includes data simplification and data storage. After data simplification, data storage begins. After data storage is completed, the user trajectory data of the corresponding key of the user in the Redis database is deleted.

[0018] Get all the trajectory data of each user from the previous day one by one.

[0019] The data simplification is to perform Douglas-Peucker algorithm on all trajectory data, and the distance threshold is set to 10m. Considering that the user's daily trajectory data contains multiple tracks, the track is split before the data simplification operation, and each of the split tracks is simplified separately.

[0020] The trajectory splitting is as follows:

[0021] (1) Traverse the user's daily location data point by point;

[0022] (2) Calculate the distance and time difference between two points;

[0023] (3) When the distance is greater than 5m and the time is greater than 5 minutes, splitting is performed;

[0024] (4) The start and end time of each trajectory is recorded.

[0025] The MySQL database storage method is as follows:

[0026] Create a data table summary index table, which mainly records the start and end dates and the number of records of each trajectory data table; set an upper threshold for the number of records stored in each trajectory data table;

[0027] Create a track data table; the track data table is named in the format of "track_{serial number}", including user type, user ID, and simplified user track data for the day;

[0028] The total number of user trajectory data for the day after statistical data simplification , the number of stored records in the current trajectory data table is When the sum is greater than the upper threshold, the relevant information is recorded in the data table summary index table, and a new trajectory data table is created to store the user's trajectory data for the previous day. .

[0029] The query scheme includes real-time location query and historical trajectory query;

[0030] The real-time location query specifically includes: querying the user type and user ID range of the real-time location, obtaining the user's real-time location data based on the user type and user ID, and combining the method of obtaining the last element of the list set in the Redis database, and performing data aggregation;

[0031] The historical trajectory query specifically includes: determining whether the query is for the current day's trajectory or the past day's trajectory; when the query period includes the current day's historical trajectory, querying the user's trajectory data in the Redis database; when the query period includes past dates, querying the involved trajectory data tables through the data table summary index table, and extracting data one by one for merging; the merged data are arranged in chronological order; and the data are obtained by intercepting the millisecond timestamp field in the location data structure.

[0032] The position conversion is specifically as follows: after receiving the user position data, recording the current millisecond timestamp and constructing a data management unit; the data management unit includes three fields: longitude, latitude, and millisecond timestamp.

[0033] The beneficial effects of this invention: Compared with existing technologies, the GNSS trajectory data storage and query method proposed in this invention for rapid location trajectory retrieval more effectively balances the real-time nature of data query with the long-term storage cycle, meeting the needs of users for real-time location and historical trajectory queries. Furthermore, to address the large number of redundant coordinate points that may appear during trajectory collection, the Douglas-Peucker algorithm is used to simplify and store them, significantly reducing the amount of trajectory data. Experimental verification has shown that the total amount of vehicle trajectory data can be reduced by 80%. BRIEF DESCRIPTION OF THE DRAWINGS

[0034] Figure 1 Flowchart of the GNSS trajectory data storage and query method for fast retrieval of position trajectories. DETAILED DESCRIPTION

[0035] Figure 1 This is the overall solution flow chart of the present invention. Figure 1 As shown, the GNSS trajectory data storage and query method for rapid location trajectory retrieval proposed by the present invention includes the following steps:

[0036] Step 1: After the location data uploaded by the user is converted to a location, the trajectory data is stored in a combination of Redis and MySQL databases. The Redis database stores the user trajectory data for the day, and the MySQL database stores the user trajectory data for historical dates.

[0037] Furthermore, given the application requirements of this technical solution, which require simultaneous querying of real-time location and historical trajectory, a combination of Redis and MySQL databases is most suitable. Because Redis is an in-memory database with microsecond read and write speeds, it is suitable for high-concurrency systems. MySQL is the most popular relational database, widely used in web applications and suitable for storing and managing structured data. Therefore, considering the advantages of improved read performance, high-concurrency writes, and enhanced scalability, the combination of these two databases best meets the requirements of this technical solution.

[0038] Step 2: At 0:00 every day, a backend scheduled task is used to simplify the user trajectory data recorded in the Redis database for that day. The simplified user trajectory data for that day is stored in the MySQL database.

[0039] Step 3: Monitor the number of records stored in the trajectory data table of the MySQL database on a daily basis. When the number of records stored in the trajectory data table reaches the upper threshold, create a new trajectory data table for data storage.

[0040] Step 4: Provide query solutions for real-time location and historical trajectory.

[0041] Further, real-time location query:

[0042] Filter the user types and user IDs that meet the requirements according to the screening conditions;

[0043] Query the corresponding values ​​of the user type and user ID in the Redis database (list set);

[0044] Get the last item of the list set in the Redis database;

[0045] Returns (user ID, longitude, latitude, update time) in the specified data format (JsonArray).

[0046] Further, historical track query:

[0047] This section can only query the historical trajectory of a specific user. At the same time, considering the large amount of data and the heavy pressure on the front-end rendering, the query is restricted and can only query the user trajectory of a specific day.

[0048] Determine whether the query date is today.

[0049] If the query is for the current day, the value is obtained from the Redis database based on the user type and user ID.

[0050] If the query is for the past, the MySQL database is queried based on the user type and user ID to obtain the trajectory data corresponding to the record.

[0051] According to the start and end time of the query, the trajectory data obtained in the above steps is trimmed according to the time period and returned.

[0052] Specifically, after receiving the user's location data, the location conversion records the current millisecond timestamp and constructs a data management unit. The data management unit Position is an entity class used in the development process, including three fields: Long (longitude), Lat (latitude), and Timestamp (millisecond timestamp), which are used to record the location data uploaded by the user.

[0053] The data storage of the Redis database is as follows:

[0054] Step 1: Create a Redis key named as "user type: user ID: date". The user type and user ID are determined based on the actual needs of different systems. The date is used to distinguish the trajectory data of the same user on different dates.

[0055] Step 2: The value type is a list collection, which is used to store each user's daily trajectory data.

[0056] Step 3: The location data uploaded by the user With previously recorded location data Calculate the distance. If it is greater than the threshold (e.g. 5m), put it at the end of the list set under the corresponding key in the Redis database and record it as When a user uploads location data for the first time on the day, the location data is recorded directly and recorded as ; Therefore, the location data recorded in this part is arranged in chronological order.

[0057] The data simplification is specifically as follows:

[0058] Step 1: Execute the scheduled task at 0:00 every day;

[0059] Step 2: Obtain all the trajectory data of each user from the previous day one by one;

[0060] Step 3: Execute the Douglas-Peucker algorithm to simplify all trajectory data, and set the distance threshold to 10m.

[0061] The storage method of the MySQL database is as follows:

[0062] Step 1: Create a data table summary index table. This table is used to record information such as the start and end dates, number of records, and other information for each trajectory data table. Set the upper threshold for the number of records that can be stored in each trajectory data table (limit). This value should be as high as possible while ensuring efficient trajectory data table retrieval. It should exceed the number of users to avoid insufficient data storage for a day's user trajectories).

[0063] Step 2: Create a track data table. The track data table is named in the format of "track_{serial number}" and includes fields such as user type, user ID, and simplified track data.

[0064] Step 3: The total number of user trajectory data for the day after statistical simplification , such as the number of records stored in the current trajectory data table + >limit, record relevant information in the data table summary index table, and create a new trajectory data table to store the user's trajectory data from the previous day .

[0065] The present invention proposes a joint storage solution using Redis and MySQL databases. By combining scheduled tasks, trajectory data simplification, and database and table sharding technologies, trajectory data simplification and long-term storage are achieved, meeting the needs of long-term storage of user trajectory data and real-time location and historical trajectory query.

Claims

1. A GNSS trajectory data storage and query method for rapid retrieval of position trajectories, characterized in that: The following steps are involved: Step 1: After the user's uploaded location data is converted, the trajectory data is stored in a combination of Redis database and MySQL database; the trajectory data includes multiple location data; the location conversion specifically includes recording the current millisecond timestamp after receiving the user's location data and constructing a data management unit; the data management unit includes three fields: longitude, latitude, and millisecond timestamp; The Redis database stores the current day's user trajectory data, and the MySQL database stores the historical date's user trajectory data; The MySQL database storage method is as follows: Create a data table summary index table, which mainly records the start and end dates and the number of records of each trajectory data table; set an upper threshold for the number of records stored in each trajectory data table; Create a track data table; the track data table is named in the format of "track_{serial number}", including user type, user ID, and simplified user track data for the day; The total number of user trajectory data for the day after statistical data simplification , the number of stored records in the current trajectory data table is When the sum is greater than the upper threshold, the relevant information is recorded in the data table summary index table, and a new trajectory data table is created to store the user's trajectory data for the previous day. ; Step 2: At 0:00 every day, a backend scheduled task is used to simplify the user trajectory data recorded in the Redis database for that day. The simplified user trajectory data for that day is stored in the MySQL database. Step 3: Monitor the number of records stored in the trajectory data table of the MySQL database on a daily basis. When the number of records stored in the trajectory data table reaches the upper threshold, create a new trajectory data table for data storage. Step 4: Provide query solutions for real-time location and historical trajectory; The query scheme includes real-time location query and historical trajectory query; The real-time location query specifically includes: querying the user type and user ID range of the real-time location, obtaining the user's real-time location data based on the user type and user ID, and combining the method of obtaining the last element of the list set in the Redis database, and performing data aggregation; The historical trajectory query is specifically as follows: determining whether the query is for the current day's trajectory or the past day's trajectory; when the query period includes the current day's historical trajectory, querying the user's trajectory data in the Redis database; when the query period includes past dates, querying the involved trajectory data tables through the data table summary index table, and extracting data one by one for merging; the merged data are all arranged in chronological order; Obtained by intercepting the millisecond timestamp field in the location data structure.

2. The GNSS trajectory data storage and query method for rapid location trajectory retrieval according to claim 1 is characterized in that: The storage method of the Redis database is as follows: The key name format of the Redis database is: "user type: user ID: date". The user type and user ID are determined according to the actual needs of different systems. The date is used to distinguish the trajectory data of the same user on different dates. The Redis database value type is a list collection, which is used to store each user's daily trajectory data; Calculate user-uploaded location data The position data of the previous record at the end of the list set corresponding to the key in the Redis database When the distance is greater than the threshold, the location data uploaded by the user will be Placed at the end of the list set under the corresponding key in the Redis database, the record is When a user uploads location data for the first time on a given day, the location data will be directly recorded and marked as , the recorded location data are arranged in chronological order.

3. The GNSS trajectory data storage and query method for rapid location trajectory retrieval according to claim 1 is characterized in that: The step 2 is specifically as follows: A scheduled task is executed at 0:00 every day, which includes data simplification and data storage. After data simplification, data storage begins. After data storage is completed, the user trajectory data of the corresponding key of the user in the Redis database is deleted. Obtain all the trajectory data of each user from the previous day one by one; The data simplification is to perform Douglas-Peucker algorithm on all trajectory data, and the distance threshold is set to 10m; before the data simplification operation, the trajectory is split and each of the split trajectories is simplified separately.

4. The GNSS trajectory data storage and query method for rapid position trajectory retrieval according to claim 3 is characterized in that: The trajectory splitting is as follows: (1) Traverse the user's daily location data point by point; (2) Calculate the distance and time difference between two points; (3) When the distance is greater than 5m and the time is greater than 5 minutes, splitting is performed; (4) The start and end time of each trajectory is recorded.

Images