Analyzing Driving Behavior Data

Application Scenarios

Cloud computing and big data provide companies with data analysis and mining capabilities required in the Internet of Vehicle (IoV) field, helping companies or department of motor vehicles manage and analyze vehicle and driving behavior data quickly and scientifically.

Solution Architecture

DLI can query the records of vehicle driving features based on the detail records and freight order data regularly reported by the freight forwarder.

Data Types describes the data types used by DLI to record the data.

Figure 1 Solution Overview

Process

To use DLI to analyze driving behavior data, perform the following steps:

Step 1: Uploading Data. Upload the data to OBS.

Step 2: Analyzing Data. Use DLI to query the data.

Example Code

Download the data package for sample data and detailed SQL statements.

Solution Advantages

• Free of data migration: DLI can interconnect with multiple data sources. You only need to create SQL tables and map data sources.
• Easy to use: You can use standard SQL statements to compile metric analysis logic without paying attention to the complex distributed computing platform.
• Pay-per-use: Log analysis is scheduled periodically based on time-critical requirements. There is a long idle period between every two scheduling operations. DLI uses the pay-per-use billing mode, which effectively reduces your costs.

Resource Planning and Costs

Data Types

• Detail records

  Detail records include the regularly reported location records and data of alarms triggered by abnormal driving behavior.

  Table 2 Detail records

  Field	Data Type	Description
  driverID	string	Driver ID
  carNumber	string	License plate number
  latitude	double	Latitude
  longitude	double	Longitude
  speed	int	Speed
  direction	int	Direction
  siteName	string	Site name
  time	timestamp	Report time of the records
  isRapidlySpeedup	int	Whether the vehicle rapidly speeds up. 1 indicates that the vehicle suddenly speeds up, and 0 indicates that the vehicle does not.
  isRapidlySlowdown	int	Whether the vehicle suddenly slows down.
  isNeutralSlide	int	Whether the vehicle is coasting.
  isNeutralSlideFinished	int	Whether vehicle coasting has stopped.
  neutralSlideTime	bigint	Time length of vehicle coasting.
  isOverspeed	int	Whether the vehicle is speeding.
  isOverspeedFinished	int	Whether the vehicle stops speeding.
  overspeedTime	bigint	Duration of the vehicle speeding
  isFatigueDriving	int	Whether fatigue driving occurs.
  isHthrottleStop	int	Whether the driver revs the engine in neutral.
  isOilLeak	int	Abnormal oil consumption

• Order data

  Order data refers to the records of freight orders.

  Table 3 Order data

  Field	Data Type	Description
  orderNumber	string	Order ID
  driverID	string	Driver ID
  carNumber	string	License plate number
  customerID	string	Customer ID
  sourceCity	string	Departure
  targetCity	string	Destination
  expectArriveTime	timestamp	Expected delivery time
  time	timestamp	Time when a record is generated.
  action	string	Event type, including creating an order, dispatching goods, delivering packages, and signing orders.

Step 1: Uploading Data

Step 2: Analyzing Data

Use DLI to query the data for analysis.

1. Creating a Database and a Table
   1. On the homepage of the management console, choose Service List > Analytics > Data Lake Insight.
   2. On the DLI console, click SQL Editor.
   3. In the left pane of the SQL Editor, select the Databases tab and click to create the demo database.
      Figure 2 Creating a database
      
      

      Database Name cannot be set to default because default is the built-in database.

   4. Choose the demo database, and enter the following SQL statement in the editing box:

      create table detail_records(
        driverID String,
        carNumber String, 
        latitude double,
        longitude double,
        speed int,
        direction int,
        siteName String,
        time timestamp,
        isRapidlySpeedup int,
        isRapidlySlowdown int,
        isNeutralSlide int,
        isNeutralSlideFinished int,
        neutralSlideTime long,
        isOverspeed int,
        isOverspeedFinished int,
        overspeedTime long,
        isFatigueDriving int,
        isHthrottleStop int,
        isOilLeak int) USING CSV OPTIONS (PATH 'obs://dli-demo/detail-records/');

      

      Replace the file path in the preceding statement with the actual OBS path where the detail records are stored.

   5. Click Execute to create the detail_records table. See Figure 3.
      Figure 3 Creating the detail_records table
      
   6. Run the following SQL statements to create the event_records table in the demo database. The operation is similar to 1.d and 1.e.

      create table event_records(
        driverID String,
        carNumber String, 
        latitude double,
        longitude double,
        speed int,
        direction int,
        siteName String,
        time timestamp,
        isRapidlySpeedup int,
        isRapidlySlowdown int,
        isNeutralSlide int,
        isNeutralSlideFinished int,
        neutralSlideTime long,
        isOverspeed int,
        isOverspeedFinished int,
        overspeedTime long,
        isFatigueDriving int,
        isHthrottleStop int,
        isOilLeak int) 

   7. Run the following SQL statements to extract the alarm and event data from the detail records and insert it into the event_records table.

      insert into table event_records
      (select *
      from detail_records
      where isRapidlySpeedup > 0
      OR isRapidlySlowdown > 0
      OR isNeutralSlide > 0
      OR isNeutralSlideFinished > 0
      OR isOverspeed > 0
      OR isOverspeedFinished > 0
      OR isFatigueDriving > 0
      OR isHthrottleStop > 0
      OR isOilLeak > 0)

   8. Use another method to create the order_records table.
      On the left of the SQL job editor, click the Databases tab and click the demo database. Click the plus icon (+) on the right of Table to create a table, and set Data Location to DLI. Set the column types according to Order data.

      Figure 4 Creating the order_records table
      
   9. Import the OBS data to the order_records table. Choose Data Management > Databases and Tables. Click the demo database to go to the table management page. In the Operation column of the order_records table, choose More > Import. Set File Format to CSV, the data storage path to obs://DLI-demo/order-records/, and retain default values for the rest parameters. Click OK.
      

      The default timestamp format is yyyy-MM-dd HH:mm:ss. To use other formats, select Advanced Settings and enter the desired timestamp format (not modified in this example).

      Figure 5 Importing table data
      
2. Querying Data
   1. Run the following SQL statements to query the alarm events of all drivers in a certain time period.
      

      You can save the frequently-used query statements as a template by clicking More > Save as Template in the upper right corner of the editing window. The template is available for future use or can be modified in the SQL editor again.

      Choose Job Templates > SQL Templates and click the Custom Templates tab. In the Operation column of the target template, click Execute to switch to the SQL editor. You can modify it as needed.

      select
        driverID,
        carNumber,
        sum(isRapidlySpeedup) as rapidlySpeedupTimes,
        sum(isRapidlySlowdown) as rapidlySlowdownTimes,
        sum(isNeutralSlide) as neutralSlideTimes,
        sum(neutralSlideTime) as neutralSlideTimeTotal,
        sum(isOverspeed) as overspeedTimes,
        sum(overspeedTime) as overspeedTimeTotal,
        sum(isFatigueDriving) as fatigueDrivingTimes,
        sum(isHthrottleStop) as hthrottleStopTimes,
        sum(isOilLeak) as oilLeakTimes
      from
        event_records
      where
        time >= "2017-01-01 00:00:00"
        and time <= "2017-02-01 00:00:00"
      group by
        driverID,
        carNumber
      order by
        rapidlySpeedupTimes desc,
        rapidlySlowdownTimes desc,
        neutralSlideTimes desc,
        neutralSlideTimeTotal desc,
        overspeedTimes desc,
        overspeedTimeTotal desc,
        fatigueDrivingTimes desc,
        hthrottleStopTimes desc,
        oilLeakTimes desc

      In the query result, click to view graphical results.

      • Set Graph Type to the bar chart.
      • Set X-AXIS to driverID.
      • Set Y-AXIS to rapidlySpeedupTimes.
      • Set Results to 10.

      The command output is as follows:

      Figure 6 Rapid acceleration
      
   2. Run the following SQL statement to query the detailed record of a driver in a certain time period.

      select
        *
      from
        event_records
      where
        driverID = "panxian1000005"
        and time >= "2017-01-01 00:00:00"
        and time <= "2017-02-01 00:00:00"

      In the query result, click to view graphical results.

      • Set Graph Type to the bar chart.
      • Set X-AXIS to driverID.
      • Set Y-AXIS to speed.
      • Set Results to 10.

      The command output is as follows:

      Figure 7 Speeding record
      
   3. Run the following SQL statement to query the order information.

      select
        *
      from
        order_records
      where
        orderNumber = "2017013013584419488"
      order by
        time desc

      Figure 8 Order information
      
   4. Run the following SQL statement to query a vehicle's driving feature according to the driver ID and time of departure.

      select
        driverID,
        carNumber,
        latitude,
        longitude,
        siteName,
        time
      from
        detail_records
      where
        driverID = "panxian1000005"
        and time > "2017-01-30 16:00:00"
        and siteName IS NOT NULL
      order by
        time desc

      In the query result, click to view graphical results.

      • Set Graph Type to the bar chart.
      • Set X-AXIS to time.
      • Set Y-AXIS to latitude.
      • Set Results to 10.

      The command output is as follows:

      Figure 9 Driving information