A method for encoding optimization based on spatio-temporal type data

Abstract

The application discloses a kind of based on encoding optimization method of space-time type data, belong to big data and real-time computing field;The static attribute column of same object is combined into a record, and its space-time trajectory sequence in preset period is encoded as custom binary data type STDT;Encoding, sequence is sorted according to time, the data of same position in continuous time period is combined and the number of combination is recorded, 16 bytes data header containing partition date, sequence total etc. Field is constructed, and two-stage time index is formed by hour-level first index and minute-level second index, and the position encoding value after combination is stored in column priority mode;When inquiring, STDT conversion operator is automatically added after file scanning operator to decode and restore;The application optimizes and combines people, car static attribute column, and encodes time space attribute column, provides computing operator based on space-time type data, reduces data order of magnitude, reduces redundant data storage, improves computing efficiency.

Description

Technical Field

[0001] This invention belongs to the fields of big data and real-time computing, and in particular relates to a coding optimization method based on spatiotemporal data. Background Technology

[0002] Spatiotemporal data refers to the location information of moving objects (such as people, vehicles, or the ID card, mobile phone number, MAC address, IMSI of a specific person, license plate number of a vehicle, etc.) recorded over time. It is usually composed of a series of "timestamps + latitude and longitude coordinates (or spatial coordinates)". For example, data reported by an individual's mobile phone when it is located at a certain base station; data generated by an individual using ride-hailing, navigation, food delivery, etc., usually contains personal information and corresponding time and location information; vehicle information and corresponding time and location information generated by personal car positioning or vehicles passing through checkpoints are all spatiotemporal data.

[0003] Traditionally, database tables for personnel and vehicle location trajectories are defined to store spatiotemporal data related to people and vehicles. The data structure for each row of data is defined and stored as the specific time of a report or collection, location information, and detailed attribute columns for the person or vehicle. In some scenarios, behavioral attribute columns are also included, such as actions performed at a specific time and place. This spatiotemporal data can be used for location-related queries and statistics, such as calculating person-to-person and vehicle-to-vehicle associations and collision analyses. However, with the widespread application of 5G and IoT technologies, the daily increase in spatiotemporal data for a province or even a city can reach tens of billions of records. The row-based data model experiences a significant increase in storage consumption and a decrease in computing performance as the data volume increases, posing a considerable challenge to both storage and computing.

[0004] What are the shortcomings or disadvantages of these existing technologies?

[0005] 1. Insufficient utilization of spatiotemporal data characteristics: Spatiotemporal data is generated constantly and is massive in volume. In reality, the main changing attributes in this type of data are time- and location-related columns, while static attributes of people or vehicles, such as ID cards, phone numbers, MAC addresses, and IMSI, remain constant. Furthermore, most people spend a significant amount of time in a particular area each day, such as at home or at work, meaning only time changes in their spatiotemporal trajectory. These two characteristics are not fully utilized in storage or computation.

[0006] 2. High redundant data storage: Traditional row data models store a single collection time of a person or vehicle, latitude and longitude information, and detailed attribute columns of the person or vehicle as a row of data. In reality, most of the static attribute columns of a person or vehicle are unchanged, and the static attribute columns of the same person or vehicle are actually stored repeatedly in multiple rows of data records. If the attributes of people and vehicles are stored separately from spatiotemporal data, it will bring the computational cost of data association.

[0007] 3. Low computational efficiency of row data: When the current row data model calculates issues such as the accompaniment of personnel trajectories, it calculates the distance between two personnel trajectories within a specified time range one by one, which is inefficient; there is a lack of merging of personnel trajectories at the same location to avoid duplicate calculations; the AI ​​era brings abundant GPU computing power, but such intensive trajectory distance calculations do not make full use of the advantages of GPU multi-core computing to accelerate CPU+GPU hybrid computing.

[0008] 4. Increased data volume leads to significant performance degradation: The current structure uses a single row to store all spatiotemporal columns and static attribute columns for people and vehicles. If the data volume continues to grow, under the same hardware resources, the final calculation time of the row computing engine will significantly increase with the increase in data volume.

[0009] Therefore, there is an urgent need in this field for an efficient compression and fast parsing method for spatiotemporal data, to reduce the magnitude of spatiotemporal data, reduce the storage size of spatiotemporal data, and improve computational efficiency. Summary of the Invention

[0010] This invention aims to address the problems of large data volume, high redundant data storage, and long computation time associated with current spatiotemporal type data. By analyzing the distribution characteristics and evolution patterns of location trajectory data in the spatiotemporal dimension, an encoding method based on spatiotemporal type data is designed to achieve structured modeling and semantic expression of trajectory data. The method optimizes and merges the static attribute columns of people and vehicles, encodes the spatiotemporal attribute columns, provides computational operators based on spatiotemporal type data, reduces the number of data entries, reduces redundant data storage, and improves computational efficiency.

[0011] To solve the above-mentioned technical problems, the present invention adopts the following technical solution:

[0012] A coding optimization method based on spatiotemporal type data includes:

[0013] Step 1: Obtain spatiotemporal type data, which includes static attribute columns and spatiotemporal trajectory sequences. The static attribute columns include object identification information, and the spatiotemporal trajectory sequences include time attribute columns and location attribute columns.

[0014] Step 2: Merge the static property columns of the same object into one record;

[0015] The spatiotemporal trajectory sequence of the same object within a preset time period is encoded into a custom spatiotemporal data type, STDT data, wherein the STDT data is binary data, including:

[0016] The spatiotemporal trajectory sequence is sorted by time;

[0017] Merge spatiotemporal data with the same location attribute column within a continuous time period into one record and record the number of merges;

[0018] Step 3: Construct a data header, which includes the partition date, the total number of spatiotemporal sequences before merging, the number of rows and columns of the spatiotemporal sequence after merging, the spatiotemporal encoding type, the number of behavioral attribute columns, and the checksum. The data header is 16 bytes long.

[0019] Step 4: Construct a two-level time index, which includes a first-level index and a second-level index. The first-level index is divided by hour, with one data point recorded per hour. The number of spatiotemporal data entries appearing in the corresponding hour interval is encoded using 32-bit binary numbers, with a total length of 96 bytes. The second-level index is used to record the minute information of the time attribute column.

[0020] Step 5: Store the merged spatiotemporal sequence data in column-major order. The spatiotemporal sequence data includes the number of merges and the location attribute encoding value. The latitude and longitude are encoded as CellID values ​​using the Google S2 algorithm, the base station number is encoded as a long integer and stored as a normalized long integer.

[0021] Step 5: Store the static attribute column and the corresponding STDT data as a single row of data.

[0022] As a further preferred embodiment of the encoding optimization method based on spatiotemporal type data of the present invention, the second-level index adopts any of the following encoding methods:

[0023] Method 1: For each time attribute column of the sorted spatiotemporal data, use 1 byte to record the minute value it belongs to. When decoding, calculate the hour interval to which the time attribute column belongs by the cumulative count from the first-level index, and then directly look up the number of minutes of the kth record from the second-level index to restore the time.

[0024] Method 2: Divide by minute, with one record for each minute. Use 1 byte to store the number of spatiotemporal data records within that minute. During decoding, calculate the hour interval to which the time attribute column belongs from the first-level index by accumulating counts, and then calculate the minute interval to which it belongs from the second-level index by accumulating counts to restore the time. In this method, the number of spatiotemporal data records per minute does not exceed 256.

[0025] As a further preferred embodiment of the encoding optimization method based on spatiotemporal type data of the present invention, the STDT data further includes a behavioral attribute column index and a behavioral attribute data block. The behavioral attribute column index stores the starting position of each behavioral attribute column in the entire STDT binary data, and the behavioral attribute data block stores the original data of the behavioral attribute column in column-major order. During decoding, the offset is calculated based on the starting position recorded in the behavioral attribute column index and the starting position of the next column, and the binary content of the corresponding column is read and decoded. When a behavioral attribute column exists, the behavioral attribute column index occupies 14 times 4, a total of 56 bytes.

[0026] As a further preferred embodiment of the encoding optimization method based on spatiotemporal type data of the present invention, it also includes an input encoding step:

[0027] Convert the data to be imported into the database into key-value pairs of partitions and internal rows;

[0028] Sort the data by the static attribute column and the time attribute column, traverse and aggregate the data with the same static attribute column, and obtain the spatiotemporal trajectory sequence of the same object within a preset time period.

[0029] The spatiotemporal trajectory sequence is encoded as follows: the data header is updated, the two-level time index is updated, and it is determined whether the current position attribute is the same as the previous position attribute. If they are the same, the spatiotemporal data is merged and the merging count counter is updated; otherwise, a new position attribute record is written.

[0030] If there are behavioral attribute columns, write the data of each behavioral attribute column to the corresponding column cache, pack and store the data as a whole column, and record the offset position to the behavioral attribute column index.

[0031] The encoded STDT data is serialized into binary data. If the data exceeds the buffer size, it is written to a storage file; otherwise, the spatiotemporal sequence of the next object is encoded.

[0032] As a further preferred embodiment of the encoding optimization method based on spatiotemporal type data of the present invention, it also includes a query decoding step:

[0033] Read STDT binary data from the storage file;

[0034] Decode the data header of the STDT binary data to obtain the partition date, the total number of spatiotemporal sequences before merging, the number of rows and columns of the spatiotemporal sequence after merging, the secondary encoding type, the number of behavioral attribute columns, and the check code;

[0035] Decode the two-level time index to obtain the number of data records and minute information records for each hour interval;

[0036] Decode the merged spatiotemporal sequence data to obtain the number of merges and the corresponding location attribute data for each merged record;

[0037] The time attribute column of each spatiotemporal data is restored based on the partition date and the two-level time index. Specifically, the hour interval to which the k-th spatiotemporal data belongs is found from the first-level index by cumulative count, the minute number of the k-th spatiotemporal data is obtained from the second-level index or the minute interval to which it belongs is estimated by cumulative count, and the complete time attribute column is calculated by combining the partition date; the position attribute column of the corresponding row number is restored based on the number of merges.

[0038] If behavioral attribute columns exist, read the binary data of each behavioral attribute column according to the starting position and offset of each column in the behavioral attribute column index, and decode and restore the behavioral attribute columns;

[0039] Reorganize and output one or more complete lines of spatiotemporal data.

[0040] Compared with the prior art, the present invention, employing the above technical solution, has the following technical effects:

[0041] 1. This invention fully utilizes the characteristics of spatiotemporal type data: in terms of storage and computation, it utilizes the distribution characteristics and evolutionary laws of spatiotemporal type data in the spatiotemporal dimension. In terms of storage, it removes redundant storage of duplicate attribute columns; in terms of computation, it reduces the number of data rows by an order of magnitude and directly calculates intersection, adjoint and other problems based on spatiotemporal type operators to improve computational efficiency.

[0042] 2. This invention reduces duplicate data storage and improves the compression ratio: By merging static attribute columns of the same personnel and encoding location attribute columns of the same location in consecutive time periods, the storage of duplicate data is effectively reduced, and the data storage compression ratio is improved.

[0043] 3. This invention reduces the number of data entries and improves execution efficiency: Merging data attribute columns for the same personnel and encoding attribute columns for the same location effectively reduces the total number of data entries, reduces redundant calculations for repeated location trajectories, and significantly improves the efficiency of calculations such as filtering and querying for specified locations.

[0044] 4. The present invention has high computational performance and good scalability based on spatiotemporal type operators: For typical problems in spatiotemporal trajectory type data such as collisions, accompaniments, and whether they have appeared in a certain region, the computational performance of operators based directly on spatiotemporal type data is better, and it is also convenient to extend to specific domain problems and to extend to GPU-accelerated operators. Attached Figure Description

[0045] Figure 1 This is a schematic diagram of the table storage structure for the spatiotemporal type data encoding process of this invention;

[0046] Figure 2 This is a schematic diagram of the logical storage structure of the spatiotemporal type data STDT of the present invention;

[0047] Figure 3 This is a schematic diagram of the spatiotemporal data storage structure of the present invention;

[0048] Figure 4 This is a schematic diagram of the first encoding method for the secondary time index of the present invention;

[0049] Figure 5 This is a schematic diagram of the second encoding method for the secondary time index of the present invention;

[0050] Figure 6 This is a flowchart of the data entry coding process of this invention;

[0051] Figure 7 This is a flowchart of the data query and decoding process of this invention;

[0052] Figure 8 This is an example diagram of querying STDT data and using UDF to query and decode data according to the present invention. Detailed Implementation

[0053] The technical solution of the present invention will be further described in detail below with reference to the accompanying drawings:

[0054] 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 of ordinary skill in the art without creative effort are within the scope of protection of the present invention. The present invention will be described in detail below with reference to the accompanying drawings and preferred embodiments. The purpose and effects of the present invention will become clearer. It should be understood that the specific embodiments described herein are merely illustrative of the present invention and are not intended to limit the present invention.

[0055] The relevant concepts of this invention are as follows:

[0056] Spatio-Temporal Type data: This refers to location data that includes time, latitude and longitude (or altitude). In this document, it mainly refers to data containing time and latitude and longitude location attributes of people or vehicles.

[0057] User Defined Type (UDT): An interface for user-defined data types in the database, used to extend custom data types.

[0058] User-defined functions (UDFs) are functions that users define, implement, and register in the database based on their specific needs and calculation logic. When the database is called and executed, a row of data is calculated by the UDF and then output as a row of data. User-defined transformation functions (UDTFs) can achieve the effect of inputting a row of data and outputting multiple rows of data.

[0059] Spatio-Temporal DataType (STDT): An extension of the User-Defined Data Type (UDT) syntax, specifically designed to represent spatio-temporal data.

[0060] FileScan operator: An operator in the SQL execution plan that scans and reads data files from a specified data source, providing methods for parallel partitioning of file reading tasks and iterating over data reading results.

[0061] STDT Spatiotemporal Data Transformation Operator: A custom execution plan operator defined in the SQL execution plan. After the FileScan operator mentioned above, it automatically decodes and restores a single row of spatiotemporal data output by FileScan into multiple rows of original spatiotemporal sequence data, avoiding the need for UDFs to decode the binary data of the spatiotemporal type STDT when used externally.

[0062] FRC data source: Fiberhome is a data storage format and read / write interface based on Apache ORC (the main columnar data storage in the Hadoop domain), providing better compression, filtering, and read performance.

[0063] CellID: A coding representation of latitude and longitude in Google's S2 algorithm. Known latitude and longitude coordinates and corresponding levels can be converted into a unique CellID value. Known CellID value and levels can also be losslessly restored to the original latitude and longitude.

[0064] This invention implements a method for efficient encoding and fast parsing of spatiotemporal type data, which reduces the total amount of spatiotemporal type data, reduces data storage, and improves computational efficiency. It also realizes automatic encoding during table creation and database entry, and automatic decoding to restore the complete process during querying. Furthermore, it is convenient and efficient to expand spatiotemporal type operators and can be expanded with GPU-accelerated operators.

[0065] Coding approach

[0066] The total amount of location trajectory data of people in the two surveyed cities over a week, and the results after deduplication based on the static attribute columns (columns not related to time or location), are shown in Table 1:

[0067] Table 1

[0068] Total number of daily personnel location trajectory data entries 18.9 billion to 22.6 billion 34.8 billion to 47.5 billion Total number of duplicate entries in the daily personnel static attribute column 200 million to 300 million 1 billion to 1.8 billion

[0069] It can be seen that if the data under each daily partition is deduplicated according to the static attribute column of the personnel, and the time and space sequence of each person for a day is encoded into a spatiotemporal type data and stored together, then under ideal conditions, the number of original data rows can be compressed to the number of deduplicated data rows, with a compression ratio of more than 20:1.

[0070] Based on the above statistical survey, the core idea of ​​this invention is to utilize the invariant characteristic of static personnel data in spatiotemporal type data to merge columns of identical static attributes for the same person together, and to encode and store the corresponding spatiotemporal trajectory sequences together. The spatiotemporal data encoding process can be described as follows: Traditionally, the original person attribute plus a single spatiotemporal attribute is stored as a single row of data. Using spatiotemporal type data encoding, the implementation is: a single row of data is stored for each person's identical static attributes throughout the day plus all corresponding spatiotemporal trajectory sequences for that day. The encoding process is illustrated using a two-dimensional table logical storage structure as follows: Figure 1 As shown:

[0071] The original 10 rows of data are encoded into 5 rows. When partitioned by day, the static attribute column of the same person and its corresponding spatiotemporal sequence data for one day are encoded together and stored as STDT type (essentially binary) data.

[0072] Encoding Algorithm:

[0073] During encoding, the spatiotemporal trajectory sequence of a person's static attributes within a day is ultimately encoded and stored as a single row in STDT type data. The logical storage structure of the spatiotemporal STDT data is as follows: Figure 2 As shown:

[0074] The logical storage structure of spatiotemporal data consists of three parts: data header, secondary time index, and merged spatiotemporal sequence data. Some personnel location trajectory tables also store spatiotemporal behavior data (i.e., personnel doing something at a certain time and place), which will be added to the storage structure as behavior column index and behavior column data block.

[0075] A detailed storage structure diagram of each part is shown below. Figure 3 As shown:

[0076] The detailed definitions and encoding instructions for the five components of spatiotemporal type data encoding are as follows:

[0077] 1) The first part is the data header, which contains seven fields: the partition date (int type), the total number of spatiotemporal sequence entries before merging (int type), the number of rows in the merged spatiotemporal sequence (int type), the number of columns for each spatiotemporal data entry (8 bits), the spatiotemporal encoding type (8 bits), the number of behavioral attribute columns (8 bits), and the checksum (8 bits). The data header is 16 bytes long. The partition date is used to restore the date value of the ltime column; the number of spatiotemporal sequence entries before merging is used to find the number of records in the second-level time partition of ltime and the number of data in each column of the behavioral attribute column; the number of rows and columns after merging are used to calculate the number of rows, columns, and size of the spatiotemporal sequence; the encoding method is used to record the appropriate encoding method based on the number of spatiotemporal sequences. The number of behavioral attribute columns indicates whether it is necessary to read the behavioral attribute columns and calculate the length of the behavioral attribute column index.

[0078] 2) The second part is a two-level spatiotemporal index, using a two-level index structure to record the hour and minute count (or position sequence) of the ltime field of a single spatiotemporal data item. The function of the first-level index is to encode the time segment by hour and record the number of spatiotemporal data items appearing in each hour. The 24 hours of a day are divided into hours, and one data item is recorded for each hour. Each data item uses a 32-bit binary number to encode the number of spatiotemporal data items recorded in the corresponding hour interval. If there is no record, it is padded with 0s. The total length is 96 bytes. The second-level index is used to record the minute count of the ltime field of the spatiotemporal data item. That is, the original ltime timestamp precision is reduced to the minute level to avoid the record data volume being too large. The second-level index is implemented in two encoding methods:

[0079] Method 1: For example Figure 4 As shown, after sorting the ltime of all spatiotemporal sequence data, each ltime uses 8 bits of one byte to encode and record the minute (0~59 minutes). Since the spatiotemporal sequence is already sorted by time, when decoding the ltime of the kth spatiotemporal data record, we first start from the first-level index and use the cumulative count i to satisfy... It can be deduced that ltime falls within the i-th hour interval (or conversely, it can be calculated that ltime cumulatively decreases with respect to k starting from 0, satisfying the condition). (Stop); then directly search for the k-th record in the secondary index to get its minutes, thus restoring the hour and minute of ltime (seconds are 0 or random), and calculate ltime together with the date in the data header. This encoding method uses the number of bytes occupied by the secondary index equal to the number of records accumulated in the first-level index over 24 hours, making it suitable for scenarios where there aren't too many spatiotemporal data records per day.

[0080] Method 2: After dividing the first-level time index by hours, the second-level time index is similarly divided by minutes, with one record per minute, storing the number of spatiotemporal data entries within that minute, such as... Figure 5 As shown.

[0081] Since the spatiotemporal sequence has been sorted by time, when decoding and restoring the k-th spatiotemporal data ltime, the first-level index calculation method is the same as in Method 1 above. First, find out which hour interval k belongs to according to Method 1. Assuming it belongs to the i-hour interval, then the total number of hours before i is... A spatiotemporal sequence; similarly, from the second-level index, the cumulative count of the second-level index data j satisfies It is deduced that ltime falls within the j-th minute (same as above, using the reverse method to cumulatively decrease k to satisfy the condition). Find j), and reconstruct the hour and minute of ltime. This encoding method uses 8 bits of binary data to encode the number of data entries, and its limitation is that it cannot exceed [a certain number] within one minute. Each spatiotemporal data record (depending on the number of spatiotemporal sequences in each group) occupies 1 byte, totaling 1*60*24=1440 bytes, suitable for scenarios with a large amount of spatiotemporal data but not exceeding 256 records per minute.

[0082] 3) The third part stores the merged spatiotemporal sequence data. Data is stored column-majorly. The first column stores the number of spatiotemporal data entries with the same CellId merged together within consecutive time periods after sorting by time. This includes latitude and longitude corresponding to the CellId, the encoded base station number (the base station number is encoded data of type string, internally converted to long integer for storage), and the normalized IP address, etc. Figure 2 The third segment (cyan section). During decoding, since all data columns are integers or long integers, the length of each row is also fixed, and the data in the corresponding column can be decoded directly according to the length of the single row.

[0083] 4) The fourth part is the behavioral attribute column index information, storing the starting position of each column's data within the entire field's binary content. This is used to read the starting position of a specified column's data during decoding, and the reading length is the difference between the starting position of the next column and the current column. During encoding and writing, the total length of the first three parts and the length occupied by the fourth part itself are first calculated. Then, the length occupied by each column is accumulated sequentially, and each accumulated column serves as the starting position of the next column. The total storage occupation for all 14 fields is 14 * 4 = 56 bytes.

[0084] 5) The final part is the behavioral attribute data block. It stores data from 14 columns (id, actype, entype...serveripv6, etc.) sequentially according to the table's field order, prioritizing columns. This serves as the content of the spatiotemporal behavioral attribute columns. All data in each column is directly packaged and stored without encoding optimization. When decoding and reading the data, the starting index information of the behavioral attribute columns in the fourth part is used. The binary content is read directly from the starting position (start) plus the difference between the starting position of the next column and the starting position of the current column (offset) (if it's the last column, the entire binary length of the field is used as the end).

[0085] Encoding process:

[0086] Spatiotemporal data encoding is completed automatically during the data import process. There are two import methods: a default daily scheduled task to archive batch data, or importing via SQL insertion. Detailed data import encoding procedures are as follows... Figure 6 As shown.

[0087] The core process of data entry coding is as follows:

[0088] 1) Data cleaning is converted into <partition, InternalRow> pairs, and after shuffling, it is sorted by personnel static attribute column + ltime.

[0089] Sort and iterate through the data that have the same attributes for the same person to form a spatiotemporal sequence of data for that person for one day, which will be processed by encoding.

[0090] 2) Encode the spatiotemporal sequence of the same personnel attribute, update the data header and secondary time index column, and simultaneously determine whether...

[0091] If the position attributes are the same, merge the spacetime and update the merged spacetime sequence counter; otherwise, write a new position attribute.

[0092] 3) For behavioral attribute columns, each record is written to the corresponding column cache separately, and finally packaged and stored as a whole column, and recorded.

[0093] The corresponding offset position is assigned to the row column index;

[0094] 4) After writing all the spatiotemporal sequence data for the current person, serialize it into binary data. If the data exceeds the buffer size, then...

[0095] Flush to the FRC file; otherwise, encode the spatiotemporal sequence of the next person's attributes and proceed to step 2) until the end.

[0096] Decoding process:

[0097] Decoding of spatiotemporal type data is completed during querying. If the query data is encoded into spatiotemporal type data, decoding and conversion are performed automatically. The main process is as follows: Figure 7 As shown.

[0098] The core process of query decoding is as follows:

[0099] 1) Parse the SQL query to obtain the columns to be read. If the columns are encoded in a time-space type, add a Transform operator after reading the data from the original file using Scan, and internally perform decoding to restore the data. During decoding, first iterate through the Scan results to retrieve one row of data, and then begin decoding the binary data of the STDT:

[0100] 2) First, decode the data header to extract the partition date, total number of spatiotemporal sequences, total number of rows and columns of the merged spatiotemporal sequence, secondary encoding type, number of columns for spatiotemporal rows, and checksum (7 columns in total). Then, decode the primary and secondary time indexes to obtain hour and minute records. Finally, decode the number of merges of each identical spatiotemporal sequence and the corresponding spatiotemporal sequence.

[0101] 3) Each time, restore the corresponding C rows of data according to the same number of spatiotemporal sequences C. The hour and minute counts of the time column ltime are obtained by looking up Sumc00->ci <= k < Sumc00->ci+1 from the first-level time index to get the hour i. Then, take the minute count of the kth data in the second-level time index and calculate the time column ltime together with the partition date. Restore all spatiotemporal sequences one by one.

[0102] 4) If there are behavioral attributes, start by restoring the corresponding columns based on the offset of each column, and reassemble a complete row of data; then iterate through the next row of data in Scan and go to 2) until the end.

[0103] Definition of the STDT interface for spatiotemporal type data:

[0104] As a custom data type, the Spatiotemporal Type STDT supports basic encoding, decoding, and equality checks. It can also be extended to support operators such as spatiotemporal data merging, intersection, count, and filtering. For time-positional shaped data in spatiotemporal sequences, GPU-accelerated operators can be added for computationally intensive scenarios such as trajectory similarity and adjoining. The spatiotemporal type data interface is defined as follows.

[0105] / **

[0106] * Spatio-Temporal Data Type (STDT) Definition

[0107] * @param ltime The acquisition time of the location trajectory points

[0108] * @param longitude Longitude of the location trajectory point

[0109] * @param latitude The dimension of the location trajectory point

[0110] * @param bsid Base station number

[0111] * @param bstype Base station type

[0112] * @param ipint Normalized IP address

[0113] /

[0114] @SQLUserDefinedType(udt=classOf[SpatioTemporalUDT])

[0115] case class STDT(ltime: Array[Long],

[0116] longitude: Array[Double],

[0117] latitude: Array[Double],

[0118] bsid: Array[String],

[0119] bstype: Array[String],

[0120] ipint: Array[Long])

[0121] / **

[0122] * Definition of Spacetime Type UDT

[0123] /

[0124] class SpatioTemporalUDT extends UserDefinedType[STDT] {

[0125] / **

[0126] * Metadata type corresponding to STDT

[0127] * @return BinaryType

[0128] /

[0129] override def sqlType: DataType = BinaryType

[0130] / **

[0131] * Serialize the spatiotemporal data STDT into a binary

[0132] * @param obj STDT object

[0133] @return Array[Byte]

[0134] /

[0135] override def serialize(obj: STDT): Any = ???

[0136] / **

[0137] * Deserialize Binary data into STDT objects

[0138] * @param datum: The binary STDT data stored.

[0139] * @return STDT object

[0140] /

[0141] override def deserialize(datum: Any): STDT = ???

[0142] / **

[0143] * STDT type name

[0144] * @return string STDT

[0145] /

[0146] override def typeName: String = "STDT"

[0147] override def userClass: Class[STDT] = classOf[STDT]

[0148] override def equals(other: Any): Boolean = ???

[0149] }

[0150] 1. Usage example:

[0151] The table creation statement for spatiotemporal type data encoding is as follows:

[0152] CREATE table zhw.zorc1 (

[0153] id BIGINT,

[0154] name STRING,

[0155] age SMALLINT,

[0156] address STRING,

[0157] btime BIGINT,

[0158] note STRING,

[0159] bdate string,

[0160] locationTrajectory (SCDT)

[0161] using orc

[0162] TBLPROPERTIES ('table.encode.convert.schema'='locationtrajectory:ltime, longitude, latitude, bsid, ipint');

[0163] 1, 'zz', 21, 'sc', 1747708200, 'fmdb', 20250520, 113.23321, 32.23321, 'bsid1', 168105768

[0164] 1, 'zz', 21, 'sc', 1747708600, 'fmdb', 20250520, 113.56126, 32.88312, 'bsid2', 168105780

[0165] The backend inputs two rows of test data, and the coding is completed internally:

[0166] Examples of querying STDT data and using UDF to query and decode data are as follows: Figure 8 As shown.

[0167] When creating the table, the spatiotemporal attribute columns that need to be encoded are configured through the attribute 'table.encode.convert.schema'='locationtrajectory:ltime, longitude, latitude, bsid, ipint';

[0168] After inserting two rows of data into the database from the backend, executing a select * query directly will show that the result of querying the locationTrajectory field is garbled because SCDT is essentially stored as binary data.

[0169] After selecting and querying using the custom scdt_decode UDF, it can be seen that the multiple columns of the decoded SCDT data correspond to array types, representing multiple merged spatiotemporal sequences. After being expanded using the custom conversion function UDTF, it is restored to the original two rows of data.

[0170] Note: In this use case, the optimization rules of the Transform operator are not enabled for the sake of illustrating the original data type. Therefore, the decoding needs to be performed internally using a UDF. In a production environment, the Transform operator is enabled and the UDF is not needed.

[0171] Actual test results:

[0172] Actual testing verified that the compression ratio per record is approximately 94:1, compared to a current FRC storage size compression ratio of 2.4:1. Detailed test data is as follows:

[0173] The input partitions for one day totaled 21.255 billion data entries, and the total size of the FRC data directory was 361G.

[0174] After using spatiotemporal type encoding, the output table has 226 million rows and a storage size of 145GB.

[0175] It will be understood by those skilled in the art that the above descriptions are merely preferred examples of the invention and are not intended to limit the invention. Although the invention has been described in detail with reference to the foregoing examples, those skilled in the art can still modify the technical solutions described in the foregoing examples or make equivalent substitutions for some of the technical features. All modifications and equivalent substitutions made within the spirit and principles of the invention should be included within the scope of protection of the invention. All technical features in this embodiment can be freely combined according to actual needs.

[0176] Finally, it should be noted that the above description is only a preferred embodiment of the present invention and is not intended to limit the present invention. Although the present invention has been described in detail with reference to the foregoing embodiments, those skilled in the art can still modify the technical solutions described in the foregoing embodiments or make equivalent substitutions for some of the technical features. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of the present invention should be included within the protection scope of the present invention.

Claims

1. A coding optimization method based on spatiotemporal type data, characterized in that, include: Step 1: Obtain spatiotemporal type data, which includes static attribute columns and spatiotemporal trajectory sequences. The static attribute columns include object identification information, and the spatiotemporal trajectory sequences include time attribute columns and location attribute columns. Step 2: Merge the static property columns of the same object into one record; The spatiotemporal trajectory sequence of the same object within a preset time period is encoded into a custom spatiotemporal data type, STDT data, wherein the STDT data is binary data, including: The spatiotemporal trajectory sequence is sorted by time; Merge spatiotemporal data with the same location attribute column within a continuous time period into one record and record the number of merges; Step 3: Construct a data header, which includes the partition date, the total number of spatiotemporal sequences before merging, the number of rows and columns of the spatiotemporal sequence after merging, the spatiotemporal encoding type, the number of behavioral attribute columns, and the checksum. The data header is 16 bytes long. Step 4: Construct a two-level time index, which includes a first-level index and a second-level index. The first-level index is divided by hour, with one data point recorded per hour. The number of spatiotemporal data entries appearing in the corresponding hour interval is encoded using 32-bit binary numbers, with a total length of 96 bytes. The second-level index is used to record the minute information of the time attribute column. Step 5: Store the merged spatiotemporal sequence data in column-major order. The spatiotemporal sequence data includes the number of merges and the location attribute encoding value. The latitude and longitude are encoded as CellID values ​​using the Google S2 algorithm, the base station number is encoded as a long integer and stored as a normalized long integer. Step 5: Store the static attribute column and the corresponding STDT data as a single row of data.

2. The encoding optimization method based on spatiotemporal type data according to claim 1, characterized in that, The second-level index uses any of the following encoding methods: Method 1: For each time attribute column of the sorted spatiotemporal data, use 1 byte to record the minute value it belongs to. When decoding, calculate the hour interval to which the time attribute column belongs by the cumulative count from the first-level index, and then directly look up the number of minutes of the kth record from the second-level index to restore the time. Method 2: Divide by minute, with one record for each minute. Use 1 byte to store the number of spatiotemporal data records within that minute. During decoding, calculate the hour interval to which the time attribute column belongs from the first-level index by accumulating counts, and then calculate the minute interval to which it belongs from the second-level index by accumulating counts to restore the time. In this method, the number of spatiotemporal data records per minute does not exceed 256.

3. The encoding optimization method based on spatiotemporal type data according to claim 1, characterized in that, The STDT data also includes a behavioral attribute column index and a behavioral attribute data block. The behavioral attribute column index stores the starting position of each behavioral attribute column in the entire STDT binary data. The behavioral attribute data block stores the original data of the behavioral attribute column in column-major order. During decoding, the offset is calculated based on the starting position recorded in the behavioral attribute column index and the starting position of the next column, and the binary content of the corresponding column is read and decoded to restore the data. When a behavioral attribute column exists, the behavioral attribute column index occupies 14 times 4, a total of 56 bytes.

4. The encoding optimization method based on spatiotemporal type data according to claim 1, characterized in that, It also includes the data entry coding step: Convert the data to be imported into the database into key-value pairs of partitions and internal rows; Sort the data by the static attribute column and the time attribute column, traverse and aggregate the data with the same static attribute column, and obtain the spatiotemporal trajectory sequence of the same object within a preset time period. The spatiotemporal trajectory sequence is encoded as follows: the data header is updated, the two-level time index is updated, and it is determined whether the current position attribute is the same as the previous position attribute. If they are the same, the spatiotemporal data is merged and the merging count counter is updated; otherwise, a new position attribute record is written. If there are behavioral attribute columns, write the data of each behavioral attribute column to the corresponding column cache, pack and store the data as a whole column, and record the offset position to the behavioral attribute column index. The encoded STDT data is serialized into binary data. If the data exceeds the buffer size, it is written to a storage file; otherwise, the spatiotemporal sequence of the next object is encoded.

5. The encoding optimization method based on spatiotemporal type data according to claim 1, characterized in that, It also includes a query decoding step: Read STDT binary data from the storage file; Decode the data header of the STDT binary data to obtain the partition date, the total number of spatiotemporal sequences before merging, the number of rows and columns of the spatiotemporal sequence after merging, the secondary encoding type, the number of behavioral attribute columns, and the check code; Decode the two-level time index to obtain the number of data records and minute information records for each hour interval; Decode the merged spatiotemporal sequence data to obtain the number of merges and the corresponding location attribute data for each merged record; The time attribute column of each spatiotemporal data is restored based on the partition date and the two-level time index. Specifically, the hour interval to which the k-th spatiotemporal data belongs is found from the first-level index by cumulative count, the minute number of the k-th spatiotemporal data is obtained from the second-level index or the minute interval to which it belongs is estimated by cumulative count, and the complete time attribute column is calculated by combining the partition date; the position attribute column of the corresponding row number is restored based on the number of merges. If behavioral attribute columns exist, read the binary data of each behavioral attribute column according to the starting position and offset of each column in the behavioral attribute column index, and decode and restore the behavioral attribute columns; Reorganize and output one or more complete lines of spatiotemporal data.

Images