Fast spatio-temporal join query method based on spatio-temporal partition and cross coding

Abstract

The application provides a fast space-time relation connection query method based on space-time partition and cross coding, and relates to the field of data mining.The fast space-time relation connection query method based on space-time partition and cross coding comprises the following steps:S1, data and environment preparation;S2, data preprocessing;S3, simultaneous space-time connection query strategy between two tables of objects;S4, end: the data connection query is completed through the operation of the S3 step.The DPCP_SEG_CROSS_JOIN algorithm is stable, and the connection query is efficient.This method can be used as an atomic algorithm of the space-time trajectory accompanying search and the relation closeness determination algorithm between different objects, and can be widely applied to the fields of maintaining national security, maintaining social order, preventing and combating crimes, and assisting urban and rural planning.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of data mining, in particular to a fast spatio-temporal relationship connection query method based on spatio-temporal partitioning and cross coding. BACKGROUND

[0002] With the accumulation and in-depth application of spatio-temporal trajectory data, the current mining and application of spatio-temporal trajectory data, especially the application demand of simultaneous space-time appearance between different objects and trajectory accompanying, is strong. Hive has added support for complex expression connection query since version 2.2.0, providing another option for mining and analysis of a large amount of spatio-temporal data. Hive is a distributed data warehouse based on Hadoop ecology, relying on HDFS, Yarn, MapReduce, TEZ, Spark and other framework engines. It queries and accesses data warehouse data through a SQL-like language, and the query process is divided into Map, Reduce and other sub-processes.

[0003] In the process of two-table and multi-table connection query under Hive, a large amount of storage and computing resources are usually consumed, especially when complex expression connection query is performed. Since the connection condition needs to be calculated in real time, the cluster resources cannot be effectively utilized in the connection query process, so a large amount of time is consumed for connection query.

[0004] In the prior art, spatio-temporal data and big data are still being continuously deepened and accelerated, and various optimization algorithms are emerging in an endless stream. There are broad application scenarios for spatio-temporal trajectory matching and fast searching. Therefore, it is very important and worth studying to research the fast query of spatio-temporal trajectory data. At present, there are relatively few studies on Hive connection query with complex expressions, especially the optimization of connection query for spatio-temporal data.

[0005] With the development of video structuring, mobile internet and Internet of Things technologies, a large amount of spatio-temporal trajectory data is generated. The mining and application of spatio-temporal trajectory data, especially the calculation demand of simultaneous space-time relationship and trajectory accompanying between different objects, is strong. However, the current methods for calculating simultaneous space-time relationship and trajectory accompanying relationship have problems such as too much query time consumption and low efficiency. Therefore, improving the calculation efficiency of spatio-temporal trajectory data relationship is a current research hotspot. SUMMARY

[0006] In view of the deficiencies of the prior art, the present application provides a fast spatio-temporal relationship connection query method based on spatio-temporal partitioning and cross coding, which solves the problems of too long query time consumption and low efficiency.

[0007] To achieve the above purpose, the present application is implemented by the following technical scheme: a fast spatio-temporal relationship connection query method based on spatio-temporal partitioning and cross coding, comprising the following steps:

[0008] S1. Data and environment preparation: Select spatiotemporal trajectory data samples and then partition and store them according to the date corresponding to the collection time, including fields such as object number, longitude, latitude, and collection time. Then, manage and store the collected data samples to obtain labeled data samples;

[0009] S2. Data preprocessing: Process the labeled data samples in step S1 as follows:

[0010] a. Fill the gaps in date partitions by inserting the last Δt of the day before the current date partition and the first Δt of the day after the current date partition into the current date partition to fill the gaps in the connection calculation of each date partition;

[0011] b. Position gridding and partitioning: Generate the position grid code c through Geohash coding, and calculate the grid area code cp based on the grid code c as the secondary partition field;

[0012] c. Encode the acquisition time of each record to generate three time code fields and three extended time code fields;

[0013] d. After processing, the data is stored according to the time and space partitions to obtain the preprocessed data samples, table name: t_ost_cc;

[0014] S3. Two-table join query strategy: Set the two-table join query steps and perform two-table join query based on the pre-processed data sample in step S2. The specific steps of the two-table join query step are as follows:

[0015] a. Generate SQL using Java or other programming language development tools, and cyclically submit the data corresponding to each date partition for connection query;

[0016] b. Perform a join query on only one date partition at a time. Each join query first determines whether the spatial grid area partitions are the same, then compares whether the spatial grids are equal, and then compares whether the time period code and extension meet the conditions.

[0017] S4. End: Complete the data connection query through the S3 step operation.

[0018] Preferably, in the step S1, Geohash is used to perform spatial position encoding on the longitude and latitude of all locations.

[0019] Preferably, the storage management method in step S1 is:

[0020] i. Only date is used as partition for storage without other optimizations, similar to the join query with a primary index;

[0021] ii. Store data in date partition and space grid area partition, similar to the connection query of building secondary index;

[0022] iii. Increase time cross coding related field, and store data in double partition of day and space grid area code, avoid real-time calculation of connection query condition, avoid MapReduce, TEZ and other engine optimization function failure, and can build connection query similar to three-level index.

[0023] Preferably, the gap filling method of the S2 step date partition is: only taking data of one date partition for connection query each time, inserting data corresponding to the last Δt of the previous date partition and the first Δt of the next date partition into the current date partition, and filling the gap existing in each date partition connection calculation.

[0024] Preferably, the position information gridding and partitioning of the S2 step is: using space grid area code: converting grid number c into decimal, and then performing mod 32 operation, the result is expressed as space grid area code, denoted as cp, formula 3, that is, the data of the same time partition (one day) is equally divided into 32 space grid areas, and the area code is mainly used as a partition field storage, and the formula is:

[0025]

[0026] Wherein L represents the length of the used GeoHash code, D(c i ) represents converting the i-th character of the Geohash code from right to left into the corresponding decimal number.

[0027] Preferably, the time period coding in the S2 step is:

[0028] Convert the collection time t into the corresponding Unix timestamp (the number of seconds elapsed from January 1, 1970, 0:00:00), denoted as: u=UT(t);

[0029] In order to solve the complex expression connection through the equal connection, the time needs to be divided and coded by time period;

[0030]

[0031] Using formula 4, the value of p is 0, the time period coding s corresponding to the timestamp u is calculated, wherein Indicates that the value in the symbol is rounded up, and the purpose is to use the length of the 2Δt slider number to code the time period;

[0032]

[0033] According to Formula 5, the value of p is set to 0, and the extended code se of s is calculated. Its purpose is to assign se value "1" to the corresponding records whose acquisition time corresponding to the same time period code falls within the first Δt range, and assign se value "0" to the corresponding records whose acquisition time falls within the second Δt range. That is, the time corresponding to each code is divided into two segments: the previous Δt and the next Δt.

[0034] Similarly, according to Formula 4 and Formula 5, the p values ​​are taken as Δt and -Δt respectively, and sa, sae, sb and sbe are calculated respectively.

[0035] Preferably, in step S3: the two-table join query step can also be performed in the following two ways:

[0036] The first method: Data is stored in a dual partitioning scheme of "date + grid area" (table name: t_ost_dpcp). A join query is performed on each time partition data in a loop. Each join query first determines whether the spatial grid area partitions are equal, then compares whether the spatial grids are equal, and finally compares whether the absolute value of the calculated acquisition time difference between the two objects meets the conditions.

[0037] The second method is to store data only by date partition, using the table name t_ost_dp. A loop is used to perform a join query on each time partition.

[0038] The present invention provides a fast spatiotemporal relationship connection query method based on spatiotemporal partitioning and cross coding. It has the following beneficial effects:

[0039] The present invention optimizes the spatiotemporal relationship data connection query through the "time-space secondary partition fusion time period cross coding" method, proposes a DPCP_SEG_CROSS_JOIN algorithm, realizes the three-level index of connection query, and reduces the time complexity of the spatiotemporal relationship connection query between n·n objects from O(n) to O(n). 2) is reduced to O(nlogn). When performing join queries on large-scale data using components such as Yarn, HDFS, MapReduce, Hive, and TEZ on a Hadoop cluster, the performance is stable, increasing join query efficiency by 30.66 times that of a normal partitioned table and overall performance by 21.38 times. On the same cluster, when the data reached 220.2GB and contained 5.96 billion records, join queries involving complex expressions could no longer be completed within the specified time using optimizations such as spatiotemporal partitioning alone. However, the proposed algorithm still completed the join query task with only a 32.45% increase in time. This demonstrates that, given cluster resources, there is no significant increase in join query time when comparing data scaled 1x to 10x. Therefore, the DPCP_SEG_CROSS_JOIN algorithm is relatively stable and efficient for join queries. This method can be used as an atomic algorithm for searching spatiotemporal trajectories and determining the closeness of relationships between different objects. It has broad applications in areas such as maintaining national security, maintaining public order, preventing and combating crime, and assisting in coordinated urban and rural planning. BRIEF DESCRIPTION OF THE DRAWINGS

[0040] Figure 1 This is an illustration of the trajectory of the present invention being close to the space time;

[0041] Figure 2 This is a schematic diagram of the data spatiotemporal partitioning and time period cross-coding processing and connection query process of the present invention;

[0042] Figure 3 This is a diagram illustrating the data time-space partitioning and time period connection query process of the present invention;

[0043] Figure 4 This is a schematic diagram of the time period cross-connect query process of the present invention;

[0044] Figure 5 The main process of the tool for implementing parallel execution of the present invention;

[0045] Figure 6 The DPCP_SEG_CROSS_JOIN method of the present invention is used to query cluster resource consumption;

[0046] Figure 7 Query cluster resource consumption for a common date partitioned table using the DP_JOIN method. DETAILED DESCRIPTION

[0047] The following will clearly and completely describe the technical solutions in the embodiments of the present invention in conjunction with the accompanying drawings. 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 making creative efforts are within the scope of protection of the present invention.

[0048] Example:

[0049] like Figure 1-Figure 7 As shown, an embodiment of the present invention provides a fast spatiotemporal relationship connection query method based on spatiotemporal partitioning and cross coding, comprising the following steps:

[0050] S1. Data and environment preparation: Select spatiotemporal trajectory data samples and then partition and store them according to the date corresponding to the collection time, including fields such as object number, longitude, latitude, and collection time. Then, manage and store the collected data samples to obtain labeled data samples;

[0051] The data format and size of the data sample are shown in Table 1:

[0052] Table 1 Attribute table of spatiotemporal data original data t_ost_dp

[0053]

[0054] Sensors were used to collect spatiotemporal data on objects from June 2022 to May 2023. The original data table, t_ost_dp (see Table 1), is partitioned and stored by date (dp). This data includes fields such as object ID (oid), longitude (lon), latitude (lat), and acquisition time (t). The total data size is 230GB, with a total of 6.3 billion records. The experiment and detailed records cover the 31 days of data from May 1 to 31, 2023 (21GB, 600 million records). The main content includes data such as the task division during the connection query process, the number of query execution tasks, the time consumed, and cluster resource usage.

[0055] Longitude and latitude are spatially encoded using Geohash. Geohash encoding is a public domain geographic coding system invented by Gustavo Niemeyer in 2008 and is a widely used method for spatial location gridding. This calculation retains the first 7 digits of accuracy. Each encoding corresponds to a grid covering an area of ​​approximately 153m x 152m. The encoding process outputs two results. The first is the Base32 encoding of the latitude corresponding to the Geohash binary data. The correspondence between Base32 encoding characters and decimal data is shown in Table 2. This results in a grid code string corresponding to the latitude and longitude, which is in the form of wk3n91u, and the result is c, as follows:

[0056] Table 2 Base32 encoding characters and decimal data correspondence

[0057] Decimal 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 base32 0 1 2 3 4 5 6 7 8 9 b c d e f g Binary 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 base32 h j k m n p q r s t u v w x y z

[0058] Geohash calculation formula:

[0059] c(lon, lat) = GH(lon, lat, base32) (1)

[0060] The above, the governance storage mode is:

[0061] i. Only with day partition storage, no other optimization, similar to the establishment of a connection query of index;

[0062] ii. With day partition and spatial grid area partition storage data, similar to the establishment of a connection query of two-level index;

[0063] iii. Increase the time cross coding related field, and then store the data in the day and spatial grid area code double partition, through the time cross coding field, avoid the real-time calculation of the connection query condition, avoid the optimization function failure of MapReduce, TEZ and other engines, can establish a connection query similar to three-level index;

[0064] In summary, the space-time relationship between objects is defined as: the space-time relationship between objects in this study refers to the relationship between two objects (personnel, vehicles, electronic equipment, etc.) When object a appears in a certain space grid c at a certain time t, another object b also appears in the same space grid area c within a certain time [Δt-t, Δt+t] before and after it. For example Figure 1 Trajectory space-time proximity diagram, a and b space-time definition see formula 2.

[0065] F(a, b) = E(a, t, c) ^ E(b, t', c) (2)

[0066] Where:

[0067] (1) Δt-t ≤ t' ≤ t+Δt;

[0068] (2) E(a, t, c) represents that a object appears in c space grid at t time.

[0069] In summary, the experimental hardware and software environment is as follows: The big data components for this study were deployed on Tencent's private cloud, consisting of 75 nodes, each with 24 VCores and 40GB of memory. The allocated experimental tenant memory was 1.8TB, and the HDFS storage capacity was 200TB. Prior to this experiment, various Hadoop backend parameters were repeatedly optimized and adjusted. Hive version 3.1.2 was used. See Table 3 for the relevant big data components and their versions.

[0070] Table 3 Main big data components and programming environment of the experimental environment

[0071]

[0072] S2. Data preprocessing: Process the labeled data samples in step S1 as follows:

[0073] a. Fill the gaps in date partitions by inserting the last Δt of the day before the current date partition and the first Δt of the day after the current date partition into the current date partition to fill the gaps in the connection calculation of each date partition;

[0074] b. Position gridding and partitioning: Generate the position grid code c through Geohash coding, and calculate the grid area code cp based on the grid code c as the secondary partition field;

[0075] c. Encode the acquisition time of each record to generate three time code fields and three extended time code fields;

[0076] d. After processing, the data is stored according to the time and space partitions to obtain the preprocessed data samples, table name: t_ost_cc;

[0077] S3. Two-table join query strategy: Set the two-table join query steps and perform two-table join query based on the pre-processed data sample in step S2. The specific steps of the two-table join query step are as follows:

[0078] a. Generate SQL using Java or other programming language development tools, and cyclically submit the data corresponding to each date partition for connection query;

[0079] b. Perform a join query on only one date partition at a time. Each join query determines whether the spatial grid area partitions are the same, then compares whether the spatial grids are equal, and then compares whether the time period code and extension meet the conditions.

[0080] Based on the above, this strategy uses time encoding to avoid real-time calculation of connection conditions for each connection, thus avoiding the optimization failure of engines such as MapReduce and TEZ, thereby further improving the efficiency of connection queries. Connection query results are also stored by date + grid area partition. The main process is shown in Figure 2 ;

[0081] like Figure 3 As shown, in step S3, the time partition set D is looped, and the data corresponding to each time partition d is retrieved and stored in temporary tables t1 and t2 respectively. First, a join query is performed on t1 and t2, with the join condition:

[0082] (1) The spatial grid area partition fields of the corresponding records of t1 and t2 are the same;

[0083] (2) The spatial grid numbers of the corresponding records of t1 and t2 are the same;

[0084] (3) The time period codes of the corresponding records of t1 and t2 are the same.

[0085] Then filter the connection results, the condition is: (ts←|u-u2|)≤Δt

[0086] Through these steps, we can get the time period code of each record in table t1 and t2 and perform an equi-join query. Figure 3 , the result is recorded as: γ1, cross-connect query is performed by two different cross-codings of time period. Figure 4 , the results are recorded as: γ2 and γ3;

[0087] S4. End: Complete the data connection query through the S3 step operation.

[0088] The method for filling the gaps in the date partitions in step S2 is as follows: each time, only one date partition's data is taken for a join query, and the data corresponding to the last Δt of the previous date partition and the first Δt of the next date partition are inserted into the current date partition to fill the gaps in the join calculation of each date partition;

[0089] See Algorithm 1 (DP_SP_FILL) for details.

[0090]

[0091]

[0092] in:

[0093] (1) Symbols such as σ, ρ, and ∧ represent selection, renaming, and conditional conjunction in relational operations;

[0094] (2) FT(d) represents the time corresponding to the last moment of the day corresponding to time d, accurate to the second. For example, when d is 20230502, no matter what time t is on May 2, 2023, FT(d) is 00:00:00 on May 2, 2023, and FT(d+1) is 00:00:00 on May 3, 2023.

[0095] (3) abs(value) means taking the absolute value of value.

[0096] (4) “#” is a comment for the algorithm steps.

[0097] The gridding and partitioning of the location information in step S2 is specifically as follows: using the spatial grid area code: convert the grid number c into a decimal, then perform a mod 32 operation, and express the result as the spatial grid area code, denoted by cp, as in Formula 3. That is, the data of the same time partition (one day) is approximately divided into 32 spatial grid areas. The area code is mainly stored as a partition field, and the formula is:

[0098]

[0099] Where L represents the length of the GeoHash code used, D(c i ) means converting the ith character of the Geohash code from right to left into the corresponding decimal number. For example, the 7th character "w" of wk3n91u corresponds to the decimal number 28, the 6th character "k" corresponds to 18, and so on. The cp code of the Geohash code wk3n91u with a length of 7 can be calculated as follows

[0100] (28×32 6 +18×32 5 +3×32 4 +20×32 3 +9×32 2 +1×32 1 +26)%32

[0101] The formula is calculated to get cp(wk3n91u)=26. See Algorithm 2 for spatial gridding and storage by grid area.

[0102]

[0103]

[0104] in:

[0105] (1) c is calculated according to the latitude and longitude gridding formula 1;

[0106] (2) cp is calculated according to the grid area partition formula 3.

[0107] According to the calculation rules, one grid code corresponds to the longitude and latitude of several acquisition devices, and one spatial grid area contains several spatial grids.

[0108] The specific steps of time segment coding in step S2 are as follows:

[0109] Convert the acquisition time t to the corresponding Unix timestamp (the number of seconds since 00:00:00 on January 1, 1970, with the value incremented by 1 every second), and record it as: u = UT(t);

[0110] In order to solve complex expression connections through equi-join, time needs to be partitioned and coded into time periods;

[0111]

[0112] Using formula 4, with p value set to 0, calculate the time segment code s corresponding to the timestamp u, where Indicates that the value in the symbol is rounded up, and its purpose is to encode the time period using a slider sequence number with a length of 2Δt;

[0113]

[0114] Then, according to Formula 5, with p set to 0, the extended code se of s is calculated. The purpose is to assign a value of "1" to the corresponding records whose acquisition time corresponding to the same time period code falls within the first Δt range, and assign a value of "0" to the corresponding records whose acquisition time falls within the second Δt range. In other words, the time corresponding to each code is divided into two segments: the first Δt and the second Δt, as shown in Table 4.

[0115] Table 4 Data partition and time period coding (Δt = 5s)

[0116]

[0117]

[0118] Similarly, using Formula 4, with p taking Δt as the value, we calculate the cross-time segment code sa for u; and using Formula 5, with p taking Δt as the value, we calculate its corresponding extended code sae. Using Formula 4, with p taking -Δt as the value, we calculate another cross-time segment code sb for u; and using Formula 5, with p taking -Δt as the value, we calculate its corresponding extended code sbe. Cross-coding and its extended coding can be used to address gaps in connecting queries between two adjacent time segments. See Table 5.

[0119] Table 5 Data partition and time period cross coding (Δt = 5s)

[0120]

[0121] The location grid and time period cross coding are stored in the time and space secondary partitions. For details, see Algorithm 3 (DP_SP_TSEC):

[0122]

[0123] Wherein: s, se, sa, sae, sb, and sbe are calculated according to Formula 4 and Formula 5 respectively.

[0124] In step S3, the data of each date partition are equalized according to the grid area code, grid code, and time period code, realizing the effect of spatiotemporal secondary index superimposed on the tertiary index accelerated by Hadoop ecological component engines such as TEZ. The connection query result is recorded as: γ1. However, there are still problems such as omission of the spatiotemporal relationship between the second half of the time code s(1) of the t1 table, that is, the last Δt, and the first half of the time code s(2) of the t2 table, that is, the first Δt; and omission of the spatiotemporal relationship between the first half of the time code s(2) of the t2 table, that is, the first Δt, and the second half of the time code s(1) of the t1 table, that is, the last Δt.

[0125] The first omission problem is solved by cross-joining the second half of the time code s(1) in t1 and the first half of the time code s(2) in t2. The second omission problem is solved similarly. Figure 4 A join query using the conditions s = sa2 and se = sae2 is performed, resulting in γ2. A join query using the conditions s = sb2 and se = sbe2 is performed again, resulting in γ3. γ1, γ2, and γ3 are implemented using parallel execution of tools developed in Java, achieving a four-level acceleration effect through three-level index stacking and parallel acceleration. See Algorithm 4 (DPCP_SEG_CROSS_JOIN) for details.

[0126]

[0127]

[0128] When DPCP_SEG_CROSS_JOIN joins records t1 and t2 for each date partition, each record in t1 first determines whether the spatial grid area partition of the t2 table record is the same. The time complexity is O(logn), that is, the time complexity of n join queries is O(nlogn); then, it determines whether the time codes are equal. The number of comparisons is repeated. Finally, the collection time difference between the two objects is calculated for each record in the join result table. The result filtering requires 2n comparisons (including filtering the n equal-join query results when calculating γ1 and filtering the n / 2 cross-join query results when calculating γ2 and γ3), with a time complexity of O(n). Therefore, the total time complexity of the algorithm is: O(nlogn).

[0129] In the same cluster and tenant resource allocation environment, the DPCP_SEG_CROSS_JOIN algorithm converts joins that require complex expression calculations into "equal value" joins using the cross-coding method, avoiding the real-time and frequent calculation of the absolute value of the time difference, one of the join conditions. The three sub-processes of algorithms γ1, γ2, and γ3 are independent of each other and have the conditions for parallel execution. Therefore, when cluster resources are relatively abundant, the three sub-processes can be executed in parallel (Table 6), further improving cluster resource utilization. Cluster CPU and memory usage and consumption during DPCP_SEG_CROSS_JOIN execution ( Figure 6 ) than ordinary date partitioned tables ( Figure 7 ) Complex expression connection query consumes less energy.

[0130] In one implementation of this example, a direct join query based on a complex expression, optimized only through date partitioning, took 161,127 seconds (approximately 44.76 hours). Using the DPCP_SEG_CROSS_JOIN algorithm, a join query of the three parts γ1, γ2, and γ3 for each date partition d, executed concurrently, took a total of 5255 seconds (approximately 1.46 hours), only 37.3% of the 14,809 seconds taken for each of the three parts shown in Table 7. This is due to the advantage of parallel acceleration. On average, a join query for one day's data took 169.53 seconds (approximately 0.05 hours).

[0131] Table 6 Average cluster resource allocation for DPCP_SEG_CROSS_JOIN date partition join query

[0132]

[0133] Table 7 DPCP_SEG_CROSS_JOIN query duration for joining three parts of a date partition (Δt=5s, unit: seconds)

[0134]

[0135] In step S3, the two-table join query step can be performed in the following two ways:

[0136] The first method: Data is stored in two-level partitions, "date + grid area," using the table name t_ost_dpcp. A join query is performed on each time partition. Each join query first determines whether the spatial grid area partitions are equal, then compares whether the spatial grids are equal, and finally compares whether the absolute value of the calculated acquisition time difference between the two objects meets the conditions.

[0137] According to the first method: According to the first method, the data time partition set D is calculated cyclically, and the data corresponding to each time partition d is obtained and stored in temporary tables t1 and t2 respectively. Use Algorithm 1 to perform a join query on t1 and t2, and the join condition is:

[0138] (1) The spatial grid numbers c and c2 are the same;

[0139] (2) The absolute value ts of the difference between the acquisition time u and u2 of each record t1 and t2 is less than or equal to Δt.

[0140] After the join, the names of the fields oid, c, t corresponding to t1 remain unchanged, and t2 (oid, c, t) are renamed to (oid2, c2, t2) respectively. The join query result is recorded as: α. See Algorithm 5 (DP_JOIN) for details.

[0141]

[0142] The second method is to store data only by date partition, using the table name t_ost_dp. A loop is used to perform join queries on each time partition data.

[0143] Based on the second data format, loop through the time partition set D, extract the data corresponding to each time partition d, and store them in temporary tables t1 and t2 respectively. Perform a join query on t1 and t2. The field naming rules of the t1 and t2 tables are the same as those in Algorithm 5. The join conditions are as follows:

[0144] (1) The spatial grid region partitions cp and cp2 are the same;

[0145] (2) The spatial grid numbers c and c2 are the same;

[0146] (3) The absolute value ts of the difference between the acquisition time u and u2 of each record t1 and t2 is less than or equal to Δt.

[0147] The result of the join query is recorded as β. For details, see Algorithm 6 (DPCP_JOIN).

[0148]

[0149] In summary, the DPCP_SEG_CROSS_JOIN algorithm for optimizing join queries has obvious advantages and significant performance, but it also has the following problems:

[0150] (1) Before performing the connection query in the S3 step, the data needs to be cross-coded, which takes a certain amount of time.

[0151] (2) After cross-coding, each record has 6 additional coding fields, which requires additional storage. The main experimental data of this experiment is 21.6GB, which is 47.4GB after cross-coding, and the storage space has increased by 1.19 times.

[0152] (3) The join query optimization method in step S3 is applicable to relational database environments with row-based storage, such as Hive, or other similar relational database environments. Its applicability in non-relational database environments, such as graph databases, and column-based storage database environments, such as HBase, needs further verification.

[0153] The DPCP_SEG_CROSS_JOIN algorithm is used to optimize the spatiotemporal relationship data connection query, reducing the time complexity of the spatiotemporal query between objects from O(n 2 ) is reduced to O(nlogn). When performing connection queries on large-scale data, the performance is stable, and the connection query efficiency can be improved to 30.66 times that of ordinary partitioned tables. In the process of implementing connection queries with this algorithm, although some additional storage space needs to be added, the overall performance is improved to 21.38 times, which is a good performance. The merits outweigh the disadvantages. This method can be used as an atomic algorithm for algorithms such as space-time trajectory accompanying search and relationship intimacy determination between different objects. It can be widely used in fields such as maintaining national security, preventing and combating crime, and can also be used in fields such as personnel flow, personnel community stability analysis, and auxiliary regional urban and rural planning. Of course, the present invention is mainly aimed at connection query optimization under Hadoop ecology and Hive data warehouse. Its adaptability in other database or data warehouse environments requires more scholars to conduct more in-depth research and practice.

[0154] While embodiments of the present invention have been shown and described, it will be appreciated by those skilled in the art that various changes, modifications, substitutions, and variations may be made to these embodiments without departing from the principles and spirit of the invention, and that the scope of the invention is defined by the appended claims and their equivalents.

Claims

1. A fast spatiotemporal relationship connection query method based on spatiotemporal partitioning and cross coding, characterized by: The following steps are involved: S1. Data and environment preparation: Select spatiotemporal trajectory data samples and then partition and store them according to the date dp corresponding to the collection time. The data samples include object number, longitude, latitude, and collection time. The collected data samples are then managed and stored to obtain labeled data samples. S2. Data preprocessing: Process the labeled data samples in step S1 as follows: a. Fill the gaps in date partitions by inserting the last Δt of the day before the current date partition and the first Δt of the day after the current date partition into the current date partition to fill the gaps in the connection calculation of each date partition; b. Position gridding and partitioning: Generate the position grid code c through Geohash coding, and calculate the grid area code cp based on the grid code c as the secondary partition field; c. Encode the acquisition time of each record to generate three time code fields and three extended time code fields; d. After processing, the data is stored according to the time and space partitions to obtain the preprocessed data samples, table name: t_ost_cc; S3. Two-table join query strategy: Set the two-table join query steps and perform two-table join query based on the pre-processed data sample in step S2. The specific steps of the two-table join query step are as follows: a. Generate SQL using Java or other programming language development tools, and cyclically submit the data corresponding to each date partition for connection query; b. Perform a join query on only one date partition at a time. Each join query first determines whether the spatial grid area partitions are the same, then compares whether the spatial grids are equal, and then compares whether the time period code and extension meet the conditions. S4. End: Complete the data connection query through the S3 step operation.

2. The fast spatiotemporal relationship connection query method based on spatiotemporal partitioning and cross coding according to claim 1 is characterized by: In the step S2, Geohash is used to perform spatial position encoding on the longitude and latitude of all locations.

3. The fast spatiotemporal relationship connection query method based on spatiotemporal partitioning and cross coding according to claim 1 is characterized by: The way to manage storage in step S2 is: i. Only date is used as partition for storage without other optimizations, similar to the join query with a primary index; ii. Storing data by date partition and spatial grid area partition is similar to establishing a join query with a secondary index; iii. Add time cross-coding related fields, and then double-partition and store data with day and space grid area codes. The time cross-coding fields avoid real-time calculation of join query conditions, prevent the failure of engine optimization functions such as MapReduce and TEZ, and establish join queries similar to three-level indexes.

4. The fast spatiotemporal relationship connection query method based on spatiotemporal partitioning and cross coding according to claim 1 is characterized by: The method for filling the gaps in the date partitions in step S2 is as follows: only take the data of one date partition for connection query each time, insert the data corresponding to the last Δt of the previous date partition and the first Δt of the next date partition into the current date partition, and fill the gaps in the connection calculation of each date partition.

5. The fast spatiotemporal relationship connection query method based on spatiotemporal partitioning and cross coding according to claim 1 is characterized by: The gridding and grid area partitioning of the position information in step S2 are specifically as follows: using the spatial grid area code: converting the grid number c into a decimal number, and then performing a mod 32 operation. The result is expressed as the spatial grid area code, denoted by cp, as in Formula 3. That is, the same time partition data is approximately divided into 32 spatial grid areas. The area code is mainly stored as a partition field. The formula is: Where L represents the length of the GeoHash code used, D(c i ) means converting the i-th character of the Geohash code from right to left into the corresponding decimal number.

6. The fast spatiotemporal relationship connection query method based on spatiotemporal partitioning and cross coding according to claim 1 is characterized by: The specific steps of time segment coding in step S2 are as follows: Convert the acquisition time t to the corresponding Unix timestamp, denoted as: u = UT(t); In order to solve complex expression connections through equi-join, time needs to be partitioned and coded into time periods; Using formula 4, with p value set to 0, calculate the time segment code s corresponding to the timestamp u, where Indicates that the value in the symbol is rounded up, and its purpose is to encode the time period using a slider sequence number with a length of 2Δt; Then, according to Formula 5, with p set to 0, we calculate the extended code se for s. The goal is to assign a value of "1" to the records whose acquisition times fall within the first Δt range, and a value of "0" to the records whose acquisition times fall within the second Δt range. This means that the time corresponding to each code is divided into two segments: the first Δt and the second Δt. According to Formula 4 and Formula 5, the p values ​​are Δt and -Δt respectively, and sa, sae, sb and sbe are calculated respectively.

7. The fast spatiotemporal relationship connection query method based on spatiotemporal partitioning and cross coding according to claim 1 is characterized by: In the S3 step, the two-table join query step can be performed in the following two ways: The first method: Data is stored in a dual partition system of "date + grid area" (table name: t_ost_dpcp). A join query is performed on each date partition in a loop. Each join query first determines whether the spatial grid area partitions are equal, then compares whether the spatial grids are equal, and finally compares whether the absolute difference in the collection time of the two objects meets the requirements. The second method is to store data only by "date" partition. The table name is t_ost_dp. A join query is performed on each date partition data in a loop. When joining, the spatial grids are first compared to see if they are equal. Then, the absolute value of the calculated acquisition time difference between the two objects is compared to see if it meets the conditions.

Images