Skip to main content

A group of people looking at the computer

Save more time and money with the MyGeotab API Adapter Data Optimizer

Last updated on August 29, 2022 in Data and Analytics by Stephen Dietz |  6 minute read


Learn about this powerful new open-source data extraction solution that can save many months of development effort when integrating with MyGeotab.

Solution overview

The MyGeotab API Adapter (API=Application Programming Interface) is an open-source solution that utilizes the Geotab API to stream data from a MyGeotab database into a SQL Server, PostgreSQL, Oracle or SQLite database in your environment. The Data Optimizer enhances the API Adapter by optimizing the data for downstream consumption. It helps developers to address one of the primary challenges faced by those seeking to integrate with Geotab — linking discrete data points from various sources.

 

This post provides high-level information about the solution, along with links to additional resources.

What is the API Adapter?

The MyGeotab API Adapter uses data feeds to pull the most common data sets from a MyGeotab database (GCP=Google Cloud Platform) and stream the data into tables within a PostgreSQL, SQL Server, Oracle or SQLite database. It is comprised of a configurable application and a database, which are deployed in your environment, as shown in the following diagram:

 

The MyGeotab API Adapter uses the Geotab API to write to or retrieve data from the relevant MyGeotab database instance(s).

An image of the MYGeotab API Adapter Solution Architecture

The API Adapter application itself is platform-independent. Pre-packaged versions are made available for Windows and Linux-based environments with each release that is published to GitHub. Scripts are provided for each of the supported database types.

Types of data that can be retrieved

Geotab data extracted by the API Adapter is largely classified into one of two categories — reference data and feed data, each of which is described below.

 

Reference data

Reference data includes data that changes infrequently, or that is static in nature. This is generally user-added data. Records in reference data tables can change over time, and only the latest version of each record is maintained. Record counts in reference data tables tend to be small and relatively stable over time. 

 

Feed data

Feed data generally consists of data points collected from vehicles via Geotab GO or other devices. Records in feed data tables are not modified once written to the database. These tables can accumulate vast quantities of records within short periods.

List of tables in the adapter database

The following is a list of reference data and feed data tables included in the adapter database. It provides the data category assignment for each table along with a description linking to the corresponding Geotab API object type(s) from which the data is obtained. Additional tables may be added in the future. Refer to the List of Tables in the main MyGeotab API Adapter guide for the latest list at any given time.

 

Table NameCategoryDescription
BinaryDataFeed dataContains data corresponding to MyGeotab BinaryData objects.
ConditionsReference dataContains data corresponding to MyGeotab Condition objects.
DevicesReference dataContains data corresponding to MyGeotab Device objects.
DeviceStatusInfoFeed dataContains data corresponding to MyGeotab DeviceStatusInfo objects.
DiagnosticsReference dataContains data corresponding to MyGeotab Diagnostic objects.
DriverChangesFeed dataContains data corresponding to MyGeotab DriverChange objects.
DutyStatusAvailabilitiesReference dataContains data corresponding to MyGeotab DutyStatusAvailability objects.
DVIRDefectRemarksReference dataContains data corresponding to MyGeotab DefectRemark objects.
DVIRDefectsReference dataContains data corresponding to defects associated with DVIRLogs. It includes data derived from MyGeotab DVIRLog, DVIRDefect, Defect and Group objects.
DVIRLogsFeed dataContains data corresponding to MyGeotab DVIRLog objects.
ExceptionEventsFeed dataContains data corresponding to MyGeotab ExceptionEvent objects.
FaultDataFeed dataContains data corresponding to MyGeotab FaultData objects.
LogRecordsFeed dataContains data corresponding to MyGeotab LogRecord objects.
RulesReference dataContains data corresponding to MyGeotab Rule objects.
StatusDataFeed dataContains data corresponding to MyGeotab StatusData objects.
TripsFeed dataContains data corresponding to MyGeotab Trip objects.
UsersReference dataContains data corresponding to MyGeotab User objects.
ZoneTypesReference dataContains data corresponding to MyGeotab ZoneType objects.
ZonesReference dataContains data corresponding to MyGeotab Zone objects.

Staging database limitations

The adapter database has been designed as a staging database to allow data streamed through the API Adapter to be written to tables as quickly as possible. As such, there are several limitations, including:

  • No physical relationships have been implemented between tables where logical relationships exist.
  • The GeotabId columns, which represent object identifiers in the Geotab system, are character-based and not indexed.
  • No data clean-up mechanisms have been implemented to prevent unrestricted database growth.

As a result of this design, it is not viable to have applications connect directly to the adapter database, and querying it directly or adding views for analysis purposes is ill-advised. Instead, it is necessary to implement a process that moves data from the adapter database into a data warehouse or data lake, as outlined in the solution and implementation guide.

 

This is where the Data Optimizer really delivers value. 

What is the Data Optimizer?

The Data Optimizer enhances the adapter solution, by following the suggested strategy outlined in the API Adapter guide. It includes an application that migrates data from the adapter database into an “optimizer database” that can then be queried directly by applications. 

 

Additional services are included to enhance the data and overcome certain challenges such as linking data points with different timestamps from different tables. For example, the StatusData and FaultData tables have added Latitude, Longitude, Speed, Bearing, Direction and DriverId columns that can optionally be populated using LogRecords and interpolation techniques. By extension, in addressing these challenges, the Data Optimizer can add considerable time and cost savings, allowing integrators to focus on their business instead of the mechanics of extracting and harmonizing data from their MyGeotab database.

 

The following diagram provides an overview of the Data Optimizer architecture integrated within the overall MyGeotab API Adapter solution.

Data Optimizer architecture

The Data Optimizer is a separate application consisting of two types of services–processors and optimizers–and an optimizer database

 

Optimizer Database

The optimizer database contains tables similar to the adapter database but with added indexes to facilitate the creation of custom views and direct querying by applications or integrations. Some of the tables contain extra value-added columns that the optimizer services can optionally populate. Although named the “optimizer database,” the tables all have slightly different names than their “adapter database” counterparts. Both sets of tables can technically be included in the same physical database if desired.

 

Processors

Processor services iteratively move batches of data from tables in the adapter database to their counterpart tables in the optimizer database. Once a batch of records has been successfully written to an optimizer database table, those records are deleted from the corresponding table in the adapter database.

 

Optimizers

Optimizer services perform any processing necessary to populate value-added columns that have been added to tables in the optimizer database. For example, the StatusData Optimizer can be configured to populate the Latitude, Longitude, Speed, Bearing, Direction and DriverId columns that have been added to the StatusDataT table.

 

The processor and optimizer services are highly-configurable and can even be run on separate machines in order to improve the performance of individual services and operate within hardware limits.

Deployment options

Given the highly-configurable nature of the MyGeotab API Adapter solution, there are numerous possibilities when it comes to deploying the solution in a physical environment. This section outlines a few of the possible deployment options.

 

One application server and one database

MyGeotab API Adapter Deployment Example 1

The diagram above is one of the simplest MyGeotab API Adapter deployment options. In this scenario, the adapter and optimizer applications are both installed on a single application server, while the adapter database tables and optimizer database tables are deployed to a single physical database. In this scenario, SQL Server is currently the only database option since the optimizer database only supports SQL Server.

 

One application server and two databases

A diagram of MyGeotab API Adapter deployment example 2

While it is technically possible to use a single physical database to host the adapter and optimizer tables, this approach is likely to prove impractical. Due to the number of different applications and processes simultaneously accessing the database, the performance of the database and any downstream integrations will be sub-optimal. To address this concern, the adapter and optimizer tables can be separated into two physical databases, as shown in the above diagram. This also introduces the possibility of different database types. While the optimizer database can only be SQL Server (for now), the adapter database could be any one of the supported database types. See the MyGeotab API Adapter linked earlier in this document.

 

Two application servers and two databases

A graphic example of MyGeotab API Adapter deployment example 3

While forming part of a single overarching solution, the core MyGeotab API Adapter and the Data Optimizer are, in fact, two distinct executable applications.

 

Given that large quantities of data are likely to be processed on a sustained basis by both applications, it may make sense to run them on two separate machines, as shown in the diagram above. This type of configuration can facilitate greater performance and throughput by making more computing resources available to each application than might otherwise be available if both applications are sharing the resources of a single application server.

 

Three or more application servers and two databases

A graphic example of MyGeotab API Adapter deployment example 4

For implementations involving larger fleets, the Data Optimizer application can be distributed across a number of physical application servers (as shown in the previous diagram) to potentially achieve even greater performance and throughput.

The future of the Data Optimizer

In the initial release of the MyGeotab API Adapter solution, including the Data Optimizer, data is moved from only a limited set of the adapter database tables to corresponding tables in the optimizer database. Future releases will include the processing of additional tables until all tables are eventually handled. Additional optimizer services will also be included in future releases. Tables will include more value-added columns, linkages and indexes. Ultimately, the Data Optimizer will simplify work for those seeking to integrate Geotab data into their data warehouses, data lakes, applications, and data science initiatives.

More information

To learn more about the MyGeotab API Adapter solution, please refer to the following:

Get notified about new releases

Any time a new release of the MyGeotab API Adapter is published to GitHub, an update will be posted to Geotab’s Integrator’s Hub. To stay current on new releases, click the Join Group button on the Integrator’s Hub page to join, and choose the desired notification frequency (Every Post, Daily Digest, Weekly Digest, etc.)

 

Need more information about using Geotab APIs to synchronize your fleet management platform with other data-driven applications? Visit the MyGeotab Software Integration page. 


If you liked this post, let us know!


Disclaimer

Geotab's blog posts are intended to provide information and encourage discussion on topics of interest to the telematics community at large. Geotab is not providing technical, professional or legal advice through these blog posts. While every effort has been made to ensure the information in this blog post is timely and accurate, errors and omissions may occur, and the information presented here may become out-of-date with the passage of time.

Get industry tips and insights

Sign up for monthly news and tips from our award-winning fleet management blog. You can unsubscribe at any time.

Republish this article for free

Other posts you might like

Person looking at a computer screen with the refleciton in their glasses

Data security and privacy with Geotab Ace

Learn how Geotab Ace™ keeps your fleet data safe with advanced privacy and security measures, providing powerful AI-driven insights.

June 12, 2024

Transport trucks

In the Driver’s Seat: Mike Branch’s Insights from Geotab’s State of Commercial Transportation Report

Data insights are improving safety outcomes for organizations and people. Fleets using Geotab’s safety features have shown a 40% reduction in collision rates.

May 9, 2024

truck behind people standing together

How to build a fleet safety program – A step-by-step guide

Set up your fleet for success with this guide to building a best-in-class fleet safety program.

May 3, 2024

View last rendered: 06/18/2024 03:28:06