This guide assumes you have activated reports in GpsGate Server 3.0. If you have not done so, please see this post: Activate reports in GpsGate Server 3.0.

Introduction

A common request we get from our customers is for a way to access data from the GpsGate Server database. With the new reporting framework introduced as part of GpsGate Server 3.0, we have opened up the possibility to access odometer readings, maximum and average speeds, fuel levels, start and stop addresses, and much more. These values can be used by 3rd party developers to create value adding solutions to their customers. As an example, I am going to show you how to calculate the distance travelled per day by your vehicles.

Setup

For this task I will be using GpsGate Server 3.0 with reports activated, on top of MySQL. I am using MySQL Workbench to perform queries, which you can download from here. If you are using Microsoft SQL Server, you can follow along this guide by using Microsoft SQL Server Management Studio Express instead.

Connecting to the Database

The database to connect to will be the one you specified during installation. For my purposes, I will be using one called reportingtest.

Data Provider

Reporting data is generated by a data provider. When you activate reporting for your GpsGate Server you will have one data provider. These providers analyze historical data and build up the data store. We are going to use a default data provider for this task, one that calculates distances. You should know that it is possible to create your own data provider, for example one that handles other scenarios like event rules.

Data Store

All reporting data is stored in the same table, the reportv3_data_store table. This is a generalized table that just stores values with no meta data. Here’s an example from my database:

SELECT * FROM `reportingtest`.`reportv3_data_store`;

All rows are generated by the same data provider. You’ll see this if you have a look at the report_data_provider_id column. This is not always the case, if you have more than one data provider. For this post I am just going to assume all data comes from the same data provider. When this is not the case you need to add something like where report_data_provider_id = 5 to the queries.

As you can see, we are dealing with integers, doubles, booleans, etc. To make any use of this data we need to turn to another table: reportv3_data_provider_column. This table provides us with a column mapping for the reportv3_data_store table. Here’s how my table looks:

SELECT * FROM `reportingtest`.`reportv3_data_provider_column`;

From the above screenshot we can see that the dbl_01 column should be interpreted as distance calculated by gps, dbl_03 as max speed, dbl_04 as average speed, etc. Notice that int_01 is the UserID. This will be important later when we need to access the vehicle name.

Summing Distances

We now have enough information to make a simple query. Let’s show the distance by gps per day. For this query we will perform a grouping on the date part of the data_time_stamp_start column. There is a MySQL function for this: Date. Let’s name this column Date and the distance column TotalDistance. Here’s the result:

use reportingtest;
SELECT Date(data_time_stamp_start) as 'Date',
       sum(dbl_01) as 'TotalDistance'
FROM reportv3_data_store
group by Date

Distance Per User

Let’s say we want to know the distance each vehicle has travelled per day. We will need to add a grouping on the user. To do this we will join on the users table. For our purposes the users table contains two interesting columns: user_id and username. The username column is guaranteed to be unique, so let’s use it for the vehicle name. As we saw previously, the user_id is stored in the int_01 column, so we will need to join on that column. Here’s the final result:

use reportingtest;
SELECT Date(data_time_stamp_start) as 'Date',
       u.username as 'Username',
       sum(dbl_01) as 'TotalDistance'
FROM reportv3_data_store r join users u on r.int_01 = u.user_id
group by Date, Username

Summary

Hopefully this will have wet your appetite for what is possible with the new reporting data store. Of course we have only touched the surface here. Feel free to ask questions in the comments or the forums for more detailed discussions.

Blog categories