Example of data modeling in the Internet of Vehicles scenario

This document is the third chapter of the "Temporary Data Modeling". YMatrix believes that the design of data models will directly affect the value of data consumption and use. Therefore, in addition to the technical introduction, we try to use the entire chapter to give you a clear understanding of the concept, application and development of Time-series Data Model.

The first article is "What is a time series data model?" By answering several in-depth questions, you will eventually have a clear understanding of the concept of the time series data model itself.
The second article "Timing Sequence Modeling Ideas" will try to give references to the design ideas of YMatrix from the perspective of theoretical guidance.
Articles 3 and 4 are examples of data modeling in the Internet of Vehicles scenario (i.e. this document) and smart home scenarios. Guided by "time sequence modeling ideas", they give best practices for modeling different temporal scenarios in YMatrix.

Notes!
This article is only a reference example. It is recommended to read at least in full detail YMatrix Architecture and component principles before formal design modeling. See the "Reference Guide" chapter for component principles.

1 What is Internet of Vehicles?

The Internet of Vehicles is one of the typical application scenarios of [Internet of Things (IoT, Internet of Things)](https://baike.baidu.com/item/Internet of Things/7306589?fromtitle=IoT&fromid=552548&fr=aladdin#reference-[1]-1136308-wrap). According to the Hong Kong Institute of Applied Science and Technology of China, [Internet of Vehicles] (https://www.astri.org/sc/tdprojects/%E8%BD%A6%E8%81%94%E7%BD%91%E6%8A%80%E6%9C%AF/) refers to low-latency communication systems between vehicles and vehicles, vehicles and pedestrians, vehicles and road infrastructure, and vehicles and the cloud. Through real-time information transmission in the Internet of Vehicles system, people, vehicles and road infrastructure can coordinate and cooperate with each other, and road conditions are reported and warned to road users in real time, thereby strengthening road safety and assisted driving. At the same time, the Internet of Vehicles technology can be applied in real-time traffic supervision, accident management, and driving route planning to improve traffic efficiency. In the long run, the Internet of Vehicles can cooperate with the development of autonomous driving technology, assist autonomous driving in judging hidden risks and improving road safety.

The main categories of the Internet of Vehicles are as follows:

| Communication terminal | Technical implementation | Application scenarios | |--|--|--|--| | Vehicles and vehicles | Information exchange and information sharing between vehicles, including vehicle status information such as vehicle location and driving speed | Judge road traffic conditions | | Vehicles and pedestrians | Users can communicate information with vehicles through wireless communication means such as Wi-Fi, Bluetooth, and cellular | enable users to monitor and control vehicles through corresponding mobile terminal devices | | Vehicle and road infrastructure | Use fixed communication facilities on the ground road to achieve information exchange between vehicles and roads | Monitor road conditions and guide vehicles to choose the best driving path | | Vehicles and cloud | Vehicles realize information transmission with vehicle networking service platforms through wireless communication technologies such as satellite wireless communication or mobile cellular | Vehicles accept control instructions issued by the platform; real-time sharing of vehicle data in the cloud | | Between the equipment in the car | Information data transmission between the equipment in the vehicle | Real-time detection and operation control of the status of the equipment in the vehicle to establish a digital in-vehicle control system |

2 Best practices for modeling in the Internet of Vehicles

It can be imagined that if you are a vehicle application research and development worker, you are responsible for managing and maintaining the vehicle index platform A of the company you work for. Now, you want to model and design the business of this platform in YMatrix, the possible ideas are as follows:

Serial number	Step
1	Demand Research
2	Modeling Design and Implementation
3	Model Test

2.1 Demand Research

Data characteristics of vehicle index platform A:
- The data size is huge: PB level.
- Large number of devices: Thousands of devices may collect and transmit data at the same time.
- Car-end indicators: relatively fixed but also need space for dynamic expansion indicators.
- Indicator value type:
  1. Int class indicator: Integer 0 - 100 represents 0 - 100% of the power, etc.
  2. float type indicators: vehicle cumulative mileage, etc.
  3. boolean type indicators: display the status of the left turn light switch, etc.
  4. Text class indicator: device unique identification number, etc.
Query features of Vehicle Index Platform A:
- Mainly based on device-based point-and-detailed query.

Notes!
The "device" semantics of this scenario refer to different sensors on each vehicle, each sensor is called a "device".

Complete data flow:

Pre-calculated sensor data/buried point data -> Cloud -> YMatrix -> Monitoring page

![](https://img.ymatrix.cn/ymatrix_home/The complete data flow of the Internet of Vehicles (screenshot)_1681725347.png)

in conclusion
After careful and comprehensive preliminary research, we believe that in YMatrix, the indicators of vehicle indicator platform A are highly certain, but a dynamic column is still needed to dynamically accommodate new or changing indicators. The data scale and number of devices are large. You should use the wide table variant model of structured + semi-structured to select the MARS3 storage engine with good compression performance as the basic table building.

2.2 Modeling Design and Implementation

2.2.1 Table Structure

According to the requirements research results, the table structure design example is as follows:

The MXKV data type can be selected for dynamic columns. Using this type requires the first creation of an extension.

=# CREATE EXTENSION matrixts;

=# CREATE TABLE V2X ( 
    ts timestamp with time zone, 
    device_id text,
    vehicle_type text,
    longitude float,
    latitude float,
    altitude float,
    speed float,
    left_turn_signal boolean,
    right_turn_signal boolean,
    emergency_flashers boolean,
    power int,
    gas int,
    windshield_wiper boolean,
    mileage float,
    signal_strength text,
    power_mode text,
    control_mode text,
    charging_status text,
    mxkv mxkv_int4
)
USING MARS3
DISTRIBUTED BY (device_id,vehicle_type)
ORDER BY (device_id,ts,vehicle_type)
PARTITION BY range(ts)
(
 START ('2023-01-15') INCLUSIVE 
 END ('2023-01-30') EXCLUSIVE 
 EVERY (interval '1 day'), 
 DEFAULT PARTITION default_p
);

In this example:

Tags are device_id, and vehicle_type are two columns;
Indicators are longitude, latitude, altitude, speed, left_turn_signal, right_turn_signal, emergency_flashers, power, gas, windshield_wiper, mileage, signal_strength, power_mode, control_mode, charging_status fifteen columns;
The above is a structured data column, and the semi-structured data column is a mxkv column, which is used to store dynamically expanded indicators and data;
The indicator types are int, float, text, and boolean;
Use device_id and vehicle_type as the distribution key;
Use ts, device_id, vehicle_type as sort keys;
Use ts as partition key;
From 2023-01-15 to 2023-01-30 each day is a partition.

2.2.2 Model Testing

| Serial number | Test plan | SQL statement |--|--|--|--| | 1 | View the latest 10 pieces of data | SELECT * FROM <Table Name> ORDER BY ts DESC LIMIT 10 | | 2 | Query the total number of rows | SELECT COUNT(*) FROM <table name> | | 3 | Query all the latest metrics reported by a certain device | SELECT * FROM <Table name> WHERE <Device tag column name> = '<Device tag value>' ORDER BY <Timestamp column name> DESC LIMIT 1 | | ... | | |

Insert 100 test statements for structured metrics.

=# INSERT INTO V2X (ts, device_id, vehicle_type, longitude, latitude, altitude, speed, left_turn_signal, right_turn_signal, emergency_flashers, power, gas, windshield_wiper, mileage, signal_strength, power_mode, control_mode, charging_status)
SELECT 
    timestamp '2023-01-15 00:00:00+00' + (random() * (timestamp '2023-01-30 00:00:00+00' - timestamp '2023-01-15 00:00:00+00')) AS ts, 
    'device_' || (random() * 100 + 1)::int AS device_id, 
    CASE (random() * 3)::int 
        WHEN 0 THEN 'car'
        WHEN 1 THEN 'truck'
        WHEN 2 THEN 'motorcycle'
        ELSE 'others'
    END AS vehicle_type, 
    -180 + random() * 360 AS longitude, 
    -90 + random() * 180 AS latitude, 
    random() * 500 AS altitude, 
    random() * 100 AS speed, 
    random() < 0.5 AS left_turn_signal, 
    random() < 0.5 AS right_turn_signal, 
    random() < 0.5 AS emergency_flashers, 
    random() * 100 AS power, 
    random() * 100 AS gas, 
    random() < 0.5 AS windshield_wiper, 
    random() * 10000 AS mileage, 
    CASE (random() * 3)::int 
        WHEN 0 THEN 'low'
        WHEN 1 THEN 'medium'
        WHEN 2 THEN 'high'
        ELSE 'others'
    END AS signal_strength, 
    CASE (random() * 3)::int 
        WHEN 0 THEN 'normal'
        WHEN 1 THEN 'eco'
        WHEN 2 THEN 'sport'
        ELSE 'others'
    END AS power_mode, 
    CASE (random() * 3)::int 
        WHEN 0 THEN 'auto'
        WHEN 1 THEN 'manual'
        ELSE 'others'
    END AS control_mode, 
    CASE (random() * 3)::int 
        WHEN 0 THEN 'not_charging'
        WHEN 1 THEN 'charging'
        WHEN 2 THEN 'charged'
        ELSE 'others'
    END AS charging_status
FROM generate_series(1, 100);

Test results:

View the latest 10 pieces of data

=# SELECT * FROM V2X ORDER BY ts DESC LIMIT 10;
           ts               | device_id  | vehicle_type |      longitude      |      latitude      |      altitude      |       speed        | left_turn_signal | right_turn_signal | emergency_flashers | power | gas | 
windshield_wiper |      mileage       | signal_strength | power_mode | control_mode | charging_status | mxkv 
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2023-01-29 18:26:28.2555+00   | device_7   | others       |    77.7250493925452 | -71.44711665175421 |  470.3520368782552 |  10.08919814806859 | t                | f                 | f                  |    91 |  60 | 
f                | 3335.6115546297715 | medium          | others     | manual       | not_charging    | 
2023-01-29 09:31:03.475343+00 | device_10  | truck        |  110.79930700971727 |  80.10310285177269 |  363.9362759505627 | 0.8470622257185312 | t                | t                 | f                  |    68 |  59 | 
f                |  8477.632404196207 | high            | normal     | others       | charged         | 
2023-01-29 08:13:59.117597+00 | device_99  | truck        | -109.73747608981242 | -89.35249080260903 |  48.85333218970267 | 37.727205912382544 | t                | f                 | t                  |    31 |  36 | 
t                |  7473.970943404033 | high            | eco        | others       | charging        | 
2023-01-29 05:58:26.571911+00 | device_46  | others       |    51.8998425853313 |  88.27679475399947 | 480.07287781433484 | 49.226744573126524 | f                | t                 | f                  |    42 |   4 | 
t                |  7143.216304908968 | high            | sport      | others       | not_charging    | 
2023-01-29 04:15:40.989367+00 | device_40  | motorcycle   | -155.34683651071745 |  6.175953177055931 |  181.5449423808442 |  50.05742613215105 | f                | f                 | t                  |    85 |  87 | 
f                |  3206.801779860875 | low             | normal     | others       | charging        | 
2023-01-29 00:00:25.771633+00 | device_100 | truck        |  151.84348070359067 |  61.94081118610583 |  79.71495904976855 |  37.50073341871314 | f                | f                 | f                  |    12 |  63 | 
f                | 3344.5220642645168 | others          | sport      | manual       | not_charging    | 
2023-01-28 20:35:29.229556+00 | device_29  | motorcycle   |   -90.3624239076261 | -9.007833078214986 | 62.318822164742826 |  20.92006269193405 | t                | t                 | t                  |    27 |  97 | 
f                |  9388.986611159318 | others          | eco        | manual       | charging        | 
2023-01-28 13:41:36.964772+00 | device_80  | car          | -18.009506474683548 |  64.13247721331942 |  452.8918234900896 | 19.506606969701323 | f                | t                 | t                  |    43 |  62 | 
t                |  9398.868263826578 | low             | normal     | manual       | charging        | 
2023-01-28 11:12:06.491849+00 | device_55  | car          |     88.971600816884 | -10.29589404625213 |  372.6116487798965 |  68.00809686681362 | f                | f                 | t                  |    21 |  76 | 
f                |  430.6571575941476 | others          | others     | others       | charging        | 
2023-01-28 09:24:02.536764+00 | device_5   | car          | -5.8755335883314785 | 14.557768126530704 | 351.21329889642715 |  9.202603983735003 | t                | t                 | f                  |     5 |  16 | 
t                | 1403.2798564383952 | others          | sport      | others       | others          | 
(10 rows)

Query the total number of rows

=# SELECT COUNT(*) FROM V2X;
count
-------
100
(1 row)

Query the latest non-null value of a certain indicator of a device

Check the latest speed of the equipment 5.

=# SELECT last_not_null(speed,ts) AS last_speed FROM V2X WHERE device_id = 'device_10' LIMIT 1;
                       last_speed                       
----------------------------------------------------------------------------------------------------------------------------------
 ["0.8470622257185312","2023-01-29 09:31:03.475343+00"]

Added a new MXKV type metric car_length to the dynamic expansion column mxkv, with the data type mxkv_int4 and the data value is 350 (unit cm). Before adding new indicators, in order to optimize compression and query performance, it is necessary to first use UDF mxkv_import_keys() to define key values to determine the set of key values contained in the data in advance.

=# SELECT mxkv_import_keys('{"car_length": 350}');
 mxkv_import_keys
------------------
 car_length
(1 row)

Insert this data.

=# INSERT INTO V2X(mxkv) values('{"car_length":350}');
INSERT 0 1

Query the inserted key value.

=# SELECT mxkv->'car_length' as car_length FROM V2X;

Notes!
For details on the usage method of MXKV, see [Extended Data Type] (/doc/5.2/reference/data_type/advanced_type).

Notes!
Only simple test examples are given here. For more scenario test examples, see the graphical interface [Easy to get started] (/doc/5.2/get_started/easy_to_get_started) section. In the actual environment, please design specific test statements based on the specific query requirements.