Efficient Vehicle Tracking System Software Solution with Informix Dynamic Server

Although  it is possible to develop an efficient Vehicle Tracking System using any database server, and many such solutions are already available in the market, Informix offers many advantages. Informix can reduce your disk space requirement, improve your query performance, and reduce your application development efforts, without special training or buying additional technology. The built-in technologies of Informix will reduce your total cost of ownership, so if these benefits are important to you, keep reading!

A Vehicle Tracking System (VTS) tracks vehicles and provides different analytics, such as the total distance travelled, the total fuel consumption, the best routes, and so on. Certainly having more and more different kinds of alerts improves the efficiency of system. This system encompasses three phases of implementation: instrumentation, integration, and intelligence.

Instrumentation: Each vehicle has a special device that records different parameters related to a vehicle, such as, speed, current location, fuel status, ignition status, and so on.

Integration: Devices implemented in the field transmit information through an integrated system that includes a GPRS system and stores the information in a database.

Intelligence: The intelligence on the information collected from the field is stored in a database, including different kinds of MIS reports, dashboards, current locations of vehicles, travel information, and reports of exceptions like geo-fence violations and fuel theft.

Some of the characteristics of a VTS are:

• Information about vehicles is collected every few minutes or as often as every minute
• The volume of the data is huge
• Performance is critical
• Accuracy of the data matters the most
• The application logic is specific to the industry (for example, geo-fencing, distance calculations, and so on)

Now let's see the advantages of Informix built-in technologies over the traditional relational approach.

Disk Space Saving (62%)

A vehicle tracking system collects the vehicle-related information, including a time stamp, with a specific frequency. Informix provides a data type that stores this type of time series information more efficiently than any traditional RDBMS approach. The following tables compare both approaches.

Traditional RDBMS approach

This table shows the structure of the tracking table in a traditional RDBMS.

Informix TimeSeries data type approach

Informix stores time series data in a specialized data type, called TimeSeries, which stores all the time series data for a particular vehicle in the same row. Informix supports two different types of time series: regular time series that stores the data with a fixed frequency, and irregular time series that stores the data for arbitrary points in time. A regular time series is the most suitable for a VTS. In a regular time series, the time stamp is stored only once as the origin of the time series and the time stamps of the rest of the rows are calculated using the offset from the origin.

This table shows the structure of the tracking table in Informix, using the TimeSeries data type.

The data for each vehicle is stored in contiguous disk space on a page.

Traditional RDBMS approach

We create the traditional RDBMS table for storing vehicle tracking information, called vehicle_tracking:

create table vehicle_tracking

(

   vehicle_number integer, - 4 (bytes)

   timestamp datetime year to fraction(5) - 11(bytes)

   longitude decimal(10,8), - 6(bytes)

   latitude decimal(10,8)     - 6(bytes)

   speed decimal(5,2) - 4(bytes)

);

We create an index on the vehicle_number and timestamp columns.

Suppose we have 10,000 vehicles to be tracked every minute and we need to store information for 90 days. Let's calculate the storage requirements.

Record size                  = 4+11+6+6+4 = 31

Record/slot overhead    = 4 bytes

Total size of record       = 31 + 4 = 35 bytes

Page overhead for maintaining page information = 28bytes per page

Total free space on 2 K page size                                 = 2048-28 = 2020byte

Total records on each page                                           = 2020/35=57

Total number of records                                                = 10000*60*24*90 = 1296000000

Total number of pages used                                          = 1296000000 / 57 = 22736842

Total space required for data                                        = 22736842 * 2 KB = 43 GB

Total space required for the index                                 =  34 GB

The total space required for the vehicle_tracking table = 43 + 34 = 77GB

Informix TimeSeries data type approach

We create a TimeSeries data type, named vehicle_tracking_rt, and then create a table, named vehicle_tracking_ts, that includes the vehicle_tracking_rt column:

create row type vehicle_tracking_rt

(

   timestamp datetime year to fraction(5)

   longitude decimal(10,8),

   latitude decimal(10,8),

   speed decimal(5,2)

);

create table vehicle_tracking_ts

(

   vehicle_number integer,

   vehicle_info timeseries(vehicle_tracking_rt)

)

We create an index on only the vehicle_number column.

We store the vehicle number only once, hence the space required for the vehicle_number column = 10000*4 = 40000bytes=39 KB.

We create the index on only the vehicle_number column, hence the total space required for the index = 10000*4=40000 bytes = 39 KB.

We store the time stamp only once for each vehicle, hence the space required for the time stamp = 10000*11 = 110000 bytes = 107 KB.

The space required for all remaining columns is:

Record size                  = 6+6+4 = 16

Record/slot overhead    = 4 bytes

Total size of record       = 16 + 4 = 20 bytes

Page overhead for maintaining page information = 28bytes + 16 bytes slot overhead Total free space on 2 K page size                           = 2048-44 = 2004 bytes

Total records on each page                                           = 2004/20=100

Total number of pages used per vehicle per day =  1440 / 100 = 15

Total pages used per day                                              = 10000*15 = 150000

Total space required for data on first day                       = 150000 * 2 KB = 0.29 GB

Daily increment in data                                      = 0.27 GB

Total space required for data of 90 days                       = 25 GB

The total space required for the vehicle_tracking_ts table = 25 GB +39 KB +29 KB+ 107 KB = 25 GB

This is a 68% disc space saving compared to a traditional RDBMS approach.

Performance (up to 54 times faster)

Let's consider few use cases:

• Fuel theft information
• Geo-fence exceptions
• Total fuel consumption

For all of these use cases, the application scans through the data for one day.

Traditional RDBMS performance

The report generation requires the following disk reads:

At least 5 disk reads through the index to reach to leaf nodes

Each vehicle has 60*24 records per day = 1440

Each vehicle needs 1440 non-cached disk reads

Total non-cached distributed disk reads = 1440 * 10000 = 14.4 million

Informix TimeSeries data type performance

Because the index is only 39 KB, the scan effort is minimal.

Because the TimeSeries data type stores data in contiguous space on a page, for a 2 K page size each page contains at least 2004/20  = 100 rows.

Each vehicle needs 1440/100 = 15 pages = 15 disk reads.

Total non-cached distributed disk reads = (15+ 1) * 10000 = 160,000

Comparison table:

The advantages of using the Informix TimeSeries implementation are:

• Disk space reduction:  almost 62% disc space saving
• Performance improvement is multifold: up to 110 times faster
• Faster development: Informix has more than 80 built-in SQL functions to perform different operations on time series data, such as, aggregation, running average, slimming algorithms, interpolation and extrapolation, and so on.
• Customized development: Informix provides C and Java APIs to let developers develop their own time series functions.

Application Development

Because many operations in a VTS application are time based, the available built-in routines for the Informix TimeSeries data type makes development easy and fast.

If we want to see the average speed of vehicle every 30 minutes, the RDBMS approach requires a large programming effort and a performance challenge. Whereas, with the Informix TimeSeries data type, we use the simple AggregateBy function to roll-up the information to any required time frequency.

If we want to see the average speed of vehicle in last 30 days, the best method is to examine its running average (not its normal average). Again, showing the running average speed of vehicles with the RDBMS approach is a big programming and performance challenge. Whereas with the Informix TimeSeries data type, we use the simple TSRunningAvg function with the period and frequency that we want.

Another basic need of VTS is storing location-related information. Location information can be in the form of a point specifying the location of vehicle, a LineString specifying the route, or a Polygon specifying the geo-fence of the vehicle. The easiest way to handle this type of information is by using the Informix spatial data types. Informix spatial data types not only optimally maintain the data, but also provide built-in routines that improve the accuracy and ease the development of your solution.

Let's examine the use case of geo-fencing. Geo-fencing refers to the practice of limiting the vehicle movements to a specific geographical area by defining the latitude and longitude of the area in the form of a LineString or polygon.

A geo-fence system raises an alarm if one of the vehicles leaves the fenced area and deviates from its designated route. This might happen because of the personal interest of the vehicle driver or because the vehicle is stolen.

Traditional RDBMS approach

We identify the different locations on the route the vehicle is supposed to travel and insert multiple rows into a fence table, comprising information like the vehicle ID and its location (latitude and longitude) information. If we need to track 200 different locations on the designated route for a particular vehicle, we insert 200 rows for that vehicle. To check if the vehicle is outside the fence area, we develop our own logic and run the cursor through these 200 rows to check if the current location is within any two points.

To display the geo-fence on an online map, we create an XML file by running a cursor through all 200 records and then parse through the XML to display each point on the map.

Informix spatial data types approach

We define a column called geofence in the master table with a data type of either polygon or LineString. We store all 200 location co-ordinates in this single column.

To check if the vehicle is within the fence area, we use the SQL function ST_Within (curr_loc,geofence). If the function returns "True," the vehicle is within the fence area; if it returns "False," the vehicle is outside the fence area.

To display the geo-fence on an online map, we use SE_askml(geofence) function, which creates a KML file that we can import into the map.

Advantages of using Informix spatial data types

• Faster development:  functions like ST_within and SE_askml are readily available
• Better performance: we don't need to run through multiple records
• Assured accuracy:  we are using Open GIS Consortium-compliant function
• More analytics: we can find the nearest location or the distance between any two locations using functions like Nearest and ST_distance

Summary

Systems like a Vehicle Tracking System can be developed using any kind of database as a back-end. But using by the Informix TimeSeries and spatial built-in data types, the same system can be many times more efficient. The disk space saving of 60% and up to 54 times faster performance compared to traditional relational approach can enable you to increase the frequency of data collection, which means better accuracy.  The built-in functions for the  TimeSeries and spatial data types makes the application development task easier and provides for increased accuracy.

About the author: