Optimizing Internet of Vehicles Data with the Window Function

By Digoal

This article describes how to optimize the data collected about the virtual tracks traveled by a vehicle by removing unnecessary data and separating data by vehicle.

One common scenario for Internet of Vehicles (IoV) applications is to collect the virtual tracks traveled by a vehicle. Virtual tracks are usually not generated and reported in real time, but rather consist of aggregated data that is reported at some interval. This aggregated data is from multiple vehicles and often may also represent multiple points in time at the same position. To make sense of this data, it is important that you optimize it.

Typically, the data of virtual tracks consists of the following parameters:

This data includes information such as the vehicle identification information, its position, and the timestamp of the data collected.

From the data collected, you can also infer other information, such as when there were traffic jams and red traffic lights, by a lack of change in the position of the vehicle over time. Data reports are outputted and reported as follows:

In order to save space, reducing the size of data collected, it is more efficient to clean tracks. For example, by only retaining two records for a particular position, specifically the first and last times recorded, or the arrival and departure points, you can significantly reduce the size of the data collected.

You can optimize the data can using the window function. Next, it is recommended to clean tracks using a terminal window.

1. Design a table structure.

In this table, columns will show the car identification information, the position of the car, and time that this information was recorded.

2. Generate 10 million test data records, assuming 1,000 vehicles are available. Having this large dataset will make it easier to repeat the data and test the effect, 25 points are used at the position.

3. Create an index with the car identification number and the timestamp.

4. Run the following command to query the data layout for the car with the identification number of 1:

5. Filter the records that were generated at a single position using the window. At most, only two records for arrival and departure at this position will be kept.

Two window functions are used here:

The method you can use to determine the arrival and departure points can be broken up into the following steps:

For this method, you can run following command using the parameters “lag” and “lead”. This involves the methods discussed above.

If the track dataset cleaning is not performed, the obtained results are as follows:

The parameters Lag and lead are used to clear records during the stay of the vehicle between its arrival and departure points.

Because data collected involves many vehicle IDs, the data of different vehicles is usually aggregated and written into the database. If this data is not optimized, the data of different vehicles may be mixed together or scattered in a confusing way. That is, data for different vehicles may be stored in the same data block.

There are two ways to optimize the data so not have the data of different vehicles mixed together:

These two methods both relate to reorganizing the data based on query requirements to achieve the purpose of decreasing scanning IO amplification.

Related posts:

Rajasthan a glorious city in India also known as ‘The Land of Kings’, offers a royal experience to all visitors. At the same time, the luxury resorts in Rajasthan are also known for its hospitality…

Cervical cancer remains one of the leading causes of cancer-related death among women globally. It remains one of the third most pressing medical problems for women. The natural history of cervical…

when God Gives. Have you ever received a gift that caught you off guard, yet you were asking for it the whole time? This is how are prayers can sometimes….