Solution and Implementation Guide

Latest Update: 2026-02-18

Revision History

Refer to the Change Log section for information about changes to the MyGeotab API Adapter solution and this document.

Introduction

Streaming of data from the MyGeotab platform into external systems via the Geotab API is accomplished using the data feed - a lightweight and highly-scalable incremental polling mechanism. Building a full-scale integration typically involves utilizing numerous data feeds to pull various types of data from a MyGeotab database. There are many complexities inherent in developing a solid integration.

The MyGeotab API Adapter solution serves as both an example of proper integration via data feeds and the potential foundation for those seeking to develop new integrations with the Geotab platform. Essentially, it uses data feeds to pull the most common data sets from a MyGeotab database and stream the data into tables within a SQL Server or PostgreSQL database; this could account for a significant portion of the work in terms of a unidirectional integration where the data from the database is further processed for integration into an external system.

This document provides detailed information about the MyGeotab API Adapter solution along with instructions related to its deployment.

Data Model 2 (DM2)

The original, now deprecated, data model, used exclusively prior to version 3.0.0 of the MyGeotab API Adapter (and supported through until version 3.14.0), was based on the premise of the adapter database serving only as a staging database where “raw” data extracted from the Geotab platform was deposited and there was a need to develop some sort of ETL process to move the data into some downstream “usable” format. Among other limitations, it did not contain indexes or physical relationships required to maintain query performance over time as data volume increases.

The Data Model 2 (DM2) version of the adapter database represents an evolution from the pure staging database paradigm. It is designed with greater performance, scalability and maintainability in mind. Specifically:

1. It is normalized with many indexes and relationships between tables.
2. Where possible, the string values of Geotab Entity Ids are converted to their underlying numeric values and used as the primary key on the subject table, facilitating fast queries even with many joins and large data volumes. See “GeotabId” and “id” Columns for more information.
3. “Feed Data” tables are partitioned by month, week or day. See Database Partitioning for details.
4. The MyGeotab API Adapter includes Automated Database Maintenance functionality that helps to keep tables and indexes optimized through regular maintenance while the application is in operation.

5. With the DM2 version of the adapter database, it is possible to achieve fast throughput speed while also providing the ability to query the data with fast results even with large data volumes.

6. MyGeotab API Adapter Highlights

7. When contemplating a new MyGeotab integration, there are many potential options. This section identifies some key features of the MyGeotab API Adapter solution which may serve both to highlight the benefit of its use as well as to identify likely requirements for a new integration being built from scratch.

8. In contrast to starting a brand new integration from the ground up, the most obvious benefit of utilizing the adapter solution is that it’s already available and open source on GitHub. So, it can be used as-is, or modified as necessary to meet specific requirements. A custom-built solution will most likely need to incorporate many of the features that are built into the MyGeotab API Adapter. Thus, at a bare minimum, the solution can serve to demonstrate ways by which such requirements may be implemented.

9. Highlights of the MyGeotab API Adapter solution are as follows:

10. Efficiency

11. 1. The number of MyGeotab API calls being made has been minimized via use of data feeds and caching to the extent possible.
    2. Chattiness with the MyGeotab API Adapter database has also been minimized.
    3. Asynchronous methods and parallel processing have been incorporated where possible.

12. Data Integrity

13. 1. Feed tokens are tracked and persisted to the MyGeotab API Adapter database.
    2. Write operations are executed within transactions to ensure all-or-none processing of FeedResult batches and tokens for individual feeds are persisted to the database only upon successful commit.
    3. Feeds will continue from the last feed versions upon restart of the MyGeotab API Adapter for any reason.
    4. Safeguards are in-place to prevent missing or duplicating data or inadvertently mixing data from multiple MyGeotab databases.

14. Database-Agnosticity

15. 1. The Dapper ORM (https://github.com/DapperLib/Dapper, https://dappertutorial.net/) is used to map .NET objects to rows in corresponding database tables.
    2. A repository pattern has been used - separating data-access code from application logic.
    3. A MyGeotab API Adapter database schema has been created for SQL Server (also Azure SQL) and PostgreSQL.

16. Resilience

17. 1. Mechanisms are in place to allow the MyGeotab API Adapter to continue operation if connectivity is lost either to the MyGeotab API or to the adapter database (e.g. planned maintenance, network issue, etc.) - in such events, the MyGeotab API Adapter will continuously try reconnecting and will resume where it left off once connectivity is re-established. Any partially completed operations are rolled-back and re-executed as noted under the Data Integrity point.

18. Configurability

19. 1. Via appsettings.json, it is possible to configure the MyGeotab API Adapter at a very granular level:
    2. 1. Feeds can be configured for individual object types (LogRecord, StatusData, FaultData, Trip, ExceptionEvent, DVIRLog).
       2. Feed intervals can be set at a per-feed level.
       3. Individual feeds can be enabled or disabled as required.
       4. Update and refresh intervals can be set for individual caches of various object types (which also utilize feeds where available), including Controller, Device, Diagnostic, Defect, FailureMode, Group, Rule, UnitOfMeasure, User and Zone.
       5. Feed results may be filtered for specific devices and/or diagnostics so that only the data required for a given instance will be persisted to the adapter database.
       6. Feeds can be started at the current time, a specific time or based on the feed version, thereby enabling consumers to pull as little or as much data as desired into the adapter database.
       7. The above configuration options allow the MyGeotab API Adapter to reduce the number of MyGeotab API calls to only those that are required to meet the needs of a specific end-customer implementation, thereby easing the load on the MyGeotab platform as the solution is deployed for many customers.

20. Deployment Model

21. 1. The MyGeotab API Adapter is published using the self-contained deployment model targeting both the Windows 64-bit (win-x64) and the Linux 64-bit (linux-x64) runtimes.
    2. The solution is intended to be deployed on the basis of having one copy of the MyGeotab API Adapter with a paired adapter database per MyGeotab database.

22. Logging

23. 1. NLog has been incorporated as the logging mechanism.
    2. Log messages have been added strategically to assist with debugging issues once the solution has been deployed.
    3. Additional logging tools could be utilized for monitoring purposes.

24. Code Readability and Reusability

25. 1. One of the primary objectives in developing this solution was to ensure maximum reusability.
    2. In addition to creating this document, effort has been made to ensure extensive code commenting throughout in order to assist integrators.

26. Deploying the MyGeotab API Adapter

27. This section provides instructions on how to download and deploy the MyGeotab API Adapter.

28. Quick Start Guide

29. High-level steps are shown in the following table with details provided in the subsections below.

31. Step	Detail
    1	Ensure that Prerequisites are Met: Ensure that all prerequisites are met. See 1 Prerequisites for details.
    2	Download the MyGeotab API Adapter: Download the MyGeotab API Adapter application and database scripts. See 2 Download for details. Watch video:
    3	Set Up the Adapter Database: Set up the adapter database into which data extracted from the Geotab platform will be written. See 3 Database Setup for details. Watch video:
    4	Deploy and Configure the Application: Deploy and configure the MyGeotab API Adapter application. See 4 Application Deployment and Configuration for details. Watch video: Watch video: Watch video: Watch video:

33. 1 Prerequisites

34. The MyGeotab API Adapter requires the following:

36. Item	Detail
    Operating System	Windows 64-bit (win-x64) or Linux 64-bit (linux-x64). The deployment packages are self-contained and include the .NET runtime, libraries and dependencies needed to run on the respective platforms. As such, there is no need to pre-install any of these or worry about versions of .NET runtime, etc.
    Database	SQL Server or PostgreSQL. The MyGeotab API Adapter application must be paired with a database into which data extracted from the Geotab platform is written. The solution was developed and tested with MS SQL Server 2019 and PostgreSQL (Postgres) 16. It is recommended to use a version equal to or greater than this for the respective database type chosen. Although Geotab cannot provide support, it is highly likely that cloud-based (i.e. Google, Amazon, Microsoft) versions of these databases are also suitable. In Azure, if SQL Server is chosen, Azure SQL Database is not supported due to the adapter database being partitioned. As-is, an Azure SQL Managed Instance or SQL Server on a VM are the supported options. If SQL Server is chosen, it is recommended to have access to a tool such as SQL Server Management Studio to view data that the adapter writes to the database. If PostgreSQL is chosen, it is recommended to have access to a tool such as pgAdmin to view data that the adapter writes to the database.
    Networking / Firewall	If the application and database will reside on separate servers, appropriate security and networking steps will need to be undertaken to ensure the ability of the application to interact with the database. The application must be able to make requests over HTTPS to the Geotab API (e.g.https://my.geotab.com/apiv1) in order to retrieve Geotab data.
    MyGeotab Credentials	MyGeotab credentials with all “View” clearances enabled on any MyGeotab database with which the MyGeotab API Adapter is to be used. It is recommended that a Service Account be set up for this purpose and assigned to Company Group. See the Service Account Guidelines document for more details. Specific clearances required by the service account used by the MyGeotab API Adapter include: List devices List Users/Drivers View Asset Inspection logs View binary data View device status information View engine diagnostics View engine failure modes View engine measurement related features View engine units of measurement View exception rules View exceptions View groups View trailers View zones

38. 2 Download

39. Watch video:

40. The latest version of the MyGeotab API Adapter is available on GitHub as a pre-published release that can be deployed to Windows or Linux-based systems and use either PostgreSQL or SQL Server to store data extracted from the Geotab platform. Download instructions are as follows:

41. 1. Go to https://github.com/Geotab/mygeotab-api-adapter/releases
    2. The latest release will be shown first (at the top of the page). Scroll down to the Assets section under the latest release and download the required files. The following sections indicate which files are needed based on the deployment environment.

43. MyGeotab API Adapter - Application

44. The following table lists the files that should be downloaded based on the operating system of the machine where the MyGeotab API Adapter application is to be installed.

45. Operating System Type	FIle(s) to Download
    Windows 64-bit	MyGeotabAPIAdapter_SCD_win-x64.zip
    Linux 64-bit (Including Ubuntu)	MyGeotabAPIAdapter_SCD_linux-x64.zip

47. Database Scripts

48. The following table lists the files that should be downloaded based on the type of database that is to be used with the MyGeotab API Adapter application.

49. Database Type	FIle(s) to Download
    SQL Server	SQLServer.zip
    PostgreSQL	PostgreSQL.zip

51. 3 Database Setup

52. Watch video:

53. Out-of-the-box, the MyGeotab API Adapter supports SQL Server and PostgreSQL for use as the database into which data retrieved via the MyGeotab API is written, as described in the Database section. Database setup procedures differ depending on the type of database and are outlined below.

56. ! WARNING: It is possible for the database to grow very large very quickly, resulting in potential disk space and performance issues. In particular, the feed data tables can accumulate vast quantities of records within short periods of time. See the Database Maintenance section for more information.

58. PostgreSQL

59. If PostgreSQL is to be used with the adapter, it is necessary to have access to a PostgreSQL instance on which to set up the adapter database. Using pgAdmin, steps are provided in the following subsections:

60. Step 1: Create geotabadapter_client Login

61. Create a login/role named geotabadapter_client using the following script (first replacing <Password> with the desired password):

63. CREATE ROLE geotabadapter_client WITH

64. LOGIN

65. NOSUPERUSER

66. INHERIT

67. NOCREATEDB

68. NOCREATEROLE

69. NOREPLICATION

70. PASSWORD '<Password>';

72. Step 2: Create Database

73. Create a database named geotabadapterdb. The following shows the basic statement to create the database:

76. ✱ NOTE: Depending on the version of Postgres being used, it may be necessary to change the values of LC_COLLATE and LC_CTYPE (e.g. from ‘en_US.utf8’ tp ‘English_United States.1252’).

78. CREATE DATABASE geotabadapterdb WITH

79. OWNER = geotabadapter_client

80. ENCODING = 'UTF8'

81. LC_COLLATE = 'en_US.utf8'

82. LC_CTYPE = 'en_US.utf8'

83. TABLESPACE = pg_default

84. CONNECTION LIMIT = -1

85. IS_TEMPLATE = FALSE;

87. Step 3: Set Default Permissions

88. Connect to the newly-created geotabadapterdb database and execute the following statements. This will ensure that the geotabadapter_client user will have access to all of the relevant objects that get created in the future.

90. -- Grant full privileges on all future sequences to the role

91. ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO geotabadapter_client;

93. -- Grant execute privileges on all future functions to the role

94. ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT EXECUTE ON FUNCTIONS TO geotabadapter_client;

96. Step 4: Modify and Execute PG_0.0.0.1_spManagePartitions.sql Script

97. The PG_0.0.0.1_spManagePartitions.sql script, which can be found along with the source code in the ..\mygeotab-api-adapter\MyGeotabAPIAdapter\Scripts\PostgreSQL\v2 folder (where mygeotab-api-adapter is the folder containing the MyGeotabAPIAdapter.sln solution file), is used to create the spManagePartitions function in the adapter database.

98. This function will partition the “feed data” tables in the adapter database on a monthly basis. Advanced users may wish to alter the partitioning scheme, in which case this function can be modified accordingly.

99. Execute the PG_0.0.0.1_spManagePartitions.sql script to create the spManagePartitions function in the adapter database.

100. Step 5: Execute Database Schema Creation Script

103. ✱ NOTE: If a brand new adapter database is being set up and there are any database upgrade scripts available (as described in the next section), instead of having to run the initial schema creation script followed by the upgrade script(s), the PG_CumulativeSchemaCreation.sql can be run instead. This script will bring an empty database to the latest version of the adapter database. It is designed to simplify new deployments in the future when there may be many database upgrade scripts to run.

104. ✱ NOTE: After the PG_CumulativeSchemaCreation.sql script has been run, when deploying future upgrades to the API Adapter, it will be necessary to run any applicable database upgrade scripts (see next section).

106. Connect to the geotabadapterdb database and execute the PG_3.0.0.0_InitialSchemaCreation.sql script. This will create all of the required tables, sequences, indexes, views, functions, etc. in the adapter database.

107. Step 6: [IF APPLICABLE] Modify and Execute Database Upgrade Script(s)

108. Over time, as changes are made to the adapter database schema, additional scripts will be provided to facilitate the upgrading of an existing adapter database. Such scripts will be prefixed with “PG_x.x.x.x_” where “x” is used as a placeholder for the API Adapter version. For example, if there were database schema changes for a hypothetical version 3.1.0.0 of the API Adapter, the associated database upgrade script would be named “PG_3.1.0.0_<SomeDescription>.sql”.

109. Execute any available adapter database upgrade scripts in sequential order (based on the versions in the script file names) up until and including the script where the version number matches that of the API Adapter application.

112. ✱ NOTE: Not all updates of the API Adapter will include database schema changes. Database upgrade scripts are only provided for API Adapter versions that include database schema changes. As such, when running database upgrade scripts, simply ensure to run them up until and including the script where the version number matches that of the API Adapter application OR the closest version below the application version.

114. Step 7: Determine Data Collection Start Date and Execute spManagePartitions Function

117. ! WARNING: Before proceeding with this step, it is important to determine the desired date from which Geotab data will be collected. Partition tables will be created for feed data tables for each month, week or day starting from that specified when executing the spManagePartitions function. Once this function has been executed, there is no easy way to make changes, other than deleting and recreating the adapter database and Geotab is unable to provide PostgreSQL support.

119. MinDateTimeUTC:

120. Determine the desired date from which Geotab data should be collected. This should correspond with the values specified for the FeedStartOption and FeedStartSpecificTimeUTC settings in the AppSettings - GeneralFeedSettings section of the appsettings.json file.

121. PartitionInterval:

122. Determine the interval by which database tables and indexes should be partitioned. Partition interval options include: monthly, weekly and daily. See Choosing the Right Partition Interval for more information.

123. Once the MinDateTimeUTC and PartitionInterval values have been decided, execute the spManagePartitions function to partition the database by modifying (replacing <MinDateTimeUTC> and <PartitionInterval> with the desired values) and using the SQL below. For example, to start collecting data from January of 2022 and have the database partitioned on a monthly basis, the SQL statement would be changed to: SELECT public."spManagePartitions"('2022-01-01', 'monthly');

125. SELECT public."spManagePartitions"('<MinDateTimeUTC>', '<PartitionInterval>');

127. Step 8: Verify Database Partitions

128. After executing the spManagePartitions function, verify that the database has been partitioned as expected. Assuming a hypothetical current date of 2024-10-17, when examining the FaultData2 table in pgAdmin, and expanding its Partitions item, the following partitions should be listed:

129. 1. FaultData2_202201
     2. FaultData2_202202
     3. FaultData2_202203
     4. (additional partitions for the months in-between)
     5. FaultData2_202409
     6. FaultData2_202410
     7. FaultData2_202411

     8. Note that a partition is created for the month after the current month. This is to ensure that the partition will exist before any data needs to be allocated to it. The API Adapter will periodically execute the spManagePartitions function during normal operation to ensure the creation of future partitions.

     9. The following query can also be used for further validation:

     11. SELECT

     12. parent_table.relname AS parent_table,

     13. child_table.relname AS partition,

     14. pg_catalog.pg_get_expr(child_table.relpartbound, child_table.oid) AS partition_bound

     15. FROM

     16. pg_inherits AS pi

     17. JOIN

     18. pg_class AS parent_table

     19. ON pi.inhparent = parent_table.oid

     20. JOIN

     21. pg_class AS child_table

     22. ON pi.inhrelid = child_table.oid

     23. WHERE

     24. parent_table.relkind = 'p'

     25. AND child_table.relkind = 'r'

     26. ORDER BY

     27. parent_table.relname, child_table.relname;

     29. SQL Server

     30. Watch video:

     31. If SQL Server is to be used with the adapter, it is necessary to have access to a SQL Server instance on which to set up the adapter database. Using SQL Server Management Studio, steps are provided in the following subsections:

     34. ✱ NOTE: In Azure, if SQL Server is chosen, Azure SQL Database is not supported due to the adapter database being partitioned. As-is, an Azure SQL Managed Instance or SQL Server on a VM are the supported options.

     36. Step 1: Create Database

     37. Create a database named geotabadapterdb. The following shows the basic statement to create the database:

     39. CREATE DATABASE [geotabadapterdb];

     41. Step 2: Set Database Collation

     42. Ensure that the database collation is set to be case-sensitive by executing the following:

     44. ALTER DATABASE [geotabadapterdb] COLLATE SQL_Latin1_General_CP1_CS_AS;

     46. Step 3: Set Database Recovery Model

     47. Unless there is a specific need for point-in-time recovery of the adapter database, the database recovery mode can be set to simple. This will reduce disk space usage. To switch the database to simple recovery mode, execute the following:

     49. ALTER DATABASE [geotabadapterdb] SET RECOVERY SIMPLE;

     51. Step 4: Create Client Login and User

     52. Create a login named geotabadapter_client along with a user by the same name using the following script (first replacing <Password> with the desired password):

     54. USE [master];

     55. CREATE LOGIN [geotabadapter_client] WITH

     56. PASSWORD=N'<Password>',

     57. DEFAULT_DATABASE=[geotabadapterdb],

     58. DEFAULT_LANGUAGE=[us_english],

     59. CHECK_EXPIRATION=OFF,

     60. CHECK_POLICY=OFF;

     62. USE [geotabadapterdb];

     63. CREATE USER [geotabadapter_client] FOR LOGIN [geotabadapter_client] WITH DEFAULT_SCHEMA=[dbo];

     64. ALTER ROLE [db_owner] ADD MEMBER [geotabadapter_client];

     66. Step 5: Modify and Execute MSSQL_0.0.0.1_spManagePartitions.sql Script

     67. The MSSQL_0.0.0.1_spManagePartitions.sql script, which can be found along with the source code in the ..\mygeotab-api-adapter\MyGeotabAPIAdapter\Scripts\SQLServer\v2 folder (where mygeotab-api-adapter is the folder containing the MyGeotabAPIAdapter.sln solution file), is used to create the spManagePartitions stored procedure in the adapter database.

     70. ! WARNING: Before executing this stored procedure, ensure that the @filePath variable is set to the correct directory where your SQL Server data files should reside. By default, this variable is set to “C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\”. If this is not correct for your environment, change it and update the stored procedure before executing it.

     72. This procedure will partition the adapter database into monthly files with daily partitions and all data files will be written to the default C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\ directory. Advanced users may wish to alter the partitioning scheme, in which case this procedure can be modified accordingly. Note that files are created on the machine where the database resides, regardless of whether the script is executed from that machine or another machine that is connected to the database server.

     73. Execute the MSSQL_0.0.0.1_spManagePartitions.sql script to create the spManagePartitions stored procedure in the adapter database.

     74. Step 6: Determine Data Collection Start Date and Execute spManagePartitions Stored Procedure

     77. ! WARNING: Before proceeding with this step, it is important to determine the desired date from which Geotab data will be collected. Data files will be created for each month, week or day starting from that specified when executing the spManagePartitions stored procedure. Once this procedure has been executed, there is no easy way to make changes, other than deleting and recreating the adapter database and Geotab is unable to provide SQL Server support.

     79. MinDateTimeUTC:

     80. Determine the desired date from which Geotab data should be collected. This should correspond with the values specified for the FeedStartOption and FeedStartSpecificTimeUTC settings in the AppSettings - GeneralFeedSettings section of the appsettings.json file.

     81. PartitionInterval:

     82. Determine the interval by which database tables and indexes should be partitioned. Partition interval options include: monthly, weekly and daily. See Choosing the Right Partition Interval for more information.

     83. Once the MinDateTimeUTC and PartitionInterval values have been decided, execute the spManagePartitions function to partition the database by modifying (replacing <MinDateTimeUTC> and <PartitionInterval> with the desired values) and using the SQL below. For example, to start collecting data from January of 2022 and have the database partitioned on a monthly basis, the SQL statement would be changed to: EXEC [dbo].[spManagePartitions] @MinDateTimeUTC = '2022-01-01', @PartitionInterval = 'monthly';

     85. EXEC [dbo].[spManagePartitions] @MinDateTimeUTC = '<MinDateTimeUTC>', @PartitionInterval = '<PartitionInterval>';

     87. Step 7: Verify Database Partitions

     88. After executing the spManagePartitions stored procedure, verify that the database has been partitioned as expected. Assuming a hypothetical current date of 2024-10-17, the C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\ folder should contain the following files:

     8. geotabadapterdb.mdf
     9. geotabadapterdb_log.ldf
     10. FG_geotabadapterdb_202201.ndf
     11. FG_geotabadapterdb_202202.ndf
     12. FG_geotabadapterdb_202203.ndf
     13. (additional files for the months in-between)
     14. FG_geotabadapterdb_202409.ndf
     15. FG_geotabadapterdb_202410.ndf
     16. FG_geotabadapterdb_202411.ndf

130. Note that a file is created for the month after the current month. This is to ensure that the partition file will exist before any data needs to be allocated to it. The API Adapter will periodically execute the spManagePartitions stored procedure during normal operation to ensure the creation of future partitions.

131. The following queries can also be used for further validation. Explanations can be found via SQL Server support:

133. -- Check FileGroup and Partition data:

134. select * from sys.filegroups;

135. select * from sys.partition_functions;

136. select * from sys.partition_range_values;

137. select * from sys.partition_schemes;

138. select * from sys.data_spaces;

139. select * from sys.dm_db_partition_stats;

141. Step 8: Execute Database Schema Creation Script

144. ✱ NOTE: If a brand new adapter database is being set up and there are any database upgrade scripts available (as described in the next section), instead of having to run the initial schema creation script followed by the upgrade script(s), the MSSQL_CumulativeSchemaCreation.sql can be run instead. This script will bring an empty database to the latest version of the adapter database. It is designed to simplify new deployments in the future when there may be many database upgrade scripts to run.

145. ✱ NOTE: After the MSSQL_CumulativeSchemaCreation.sql script has been run, when deploying future upgrades to the API Adapter, it will be necessary to run any applicable database upgrade scripts (see next section).

147. Execute the MSSQL_3.0.0.0_InitialSchemaCreation.sql script. This will create all of the required tables, indexes, views, stored procedures, etc. in the adapter database.

148. Step 9: [IF APPLICABLE] Modify and Execute Database Upgrade Script(s)

149. Over time, as changes are made to the adapter database schema, additional scripts will be provided to facilitate the upgrading of an existing adapter database. Such scripts will be prefixed with “MSSQL_x.x.x.x_” where “x” is used as a placeholder for the API Adapter version. For example, if there were database schema changes for a hypothetical version 3.1.0.0 of the API Adapter, the associated database upgrade script would be named “MSSQL_3.1.0.0_<SomeDescription>.sql”.

150. Execute any available adapter database upgrade scripts in sequential order (based on the versions in the script file names) up until and including the script where the version number matches that of the API Adapter application.

153. ✱ NOTE: Not all updates of the API Adapter will include database schema changes. Database upgrade scripts are only provided for API Adapter versions that include database schema changes. As such, when running database upgrade scripts, simply ensure to run them up until and including the script where the version number matches that of the API Adapter application OR the closest version below the application version.

155. 4 Application Deployment and Configuration

156. Watch video:

157. Watch video:

158. Watch video:

159. Watch video:

160. This section covers deployment and configuration of the MyGeotab API Adapter application.

161. Step 1: Deployment Prerequisites

162. To provide for a smooth deployment, the following steps should be taken beforehand:

163. 1. Ensure that permission has been granted by the owner of the MyGeotab database with which the adapter will be interacting.
     2. A service account should be established for use by the adapter. See the Service Account Guidelines document for more details.
     3. Take appropriate steps (networking, firewall, etc.) to ensure connectivity between the server on which the adapter will be deployed and the MyGeotab platform (e.g. https://my.geotab.com).
     4. Make sure that the adapter database setup has been completed per the instructions in the Database Setup section.
     5. Make sure that the server hosting the adapter database has enough disk space and that a database maintenance strategy is implemented to prevent unlimited growth.

164. Step 2: Install MyGeotab API Adapter Application

165. The MyGeotab API Adapter is rolled-up into a self-contained package that includes the .NET runtime and all other dependencies. As such, there is no complicated installation process. To “install” the application:

166. 1. Copy the zip file containing the application that was downloaded earlier to the desired location and extract (“unzip”) the contents which will be a single folder (e.g. “MyGeotabAPIAdapter_SCD_win-x64” if deploying to Windows).

167. Step 3: Configure MyGeotab API Adapter Application

168. The deployment folder that was extracted in the previous step contains two files that are used to configure the MyGeotab API Adapter: appsettings.json and nlog.config.

169. 1. Modify the appsettings.json file as needed. See the appsettings.json section in this guide for more information.
     2. 1. The DatabaseSettings and LoginSettings sections are most important as they govern the application’s connectivity to the adapter database and the MyGeotab database.
     1. Review the nlog.config file and make any necessary changes. See the nlog.config section for more information.

170. Step 4: Run the MyGeotab API Adapter

171. There are multiple ways to run the API Adapter as shown below.

172. Option 1: Run Manually

173. For testing purposes, or in cases where there is a desire to only run the API Adapter for a limited period of time (e.g. to collect some data for ad-hoc analysis), the application can be launched by simply running the MyGeotab API Adapter application executable, MyGeotabAPIAdapter.exe (without the “.exe” extension if using the Linux version), which is contained in the folder that was “installed” earlier.

174. Option 2: Install as a Service

175. In situations where the API Adapter is intended to be run continuously, such as in a production environment, it is best to set the application up to run as a service. Instructions for doing so are provided for both Windows and Linux environments, below.

178. ✱ NOTE: Before installing the MyGeotab API Adapter as a service in the steps below, the steps in the previous sections must be completed, including:

179. 1. 1 Prerequisites
     2. 2 Download
     3. 3 Database Setup
     4. 4 Application Deployment and Configuration (Step 1: Deployment Prerequisites, Step 2: Install MyGeotab API Adapter Application, Step 3: Configure MyGeotab API Adapter Application)

181. Windows

182. Watch video:

183. The MyGeotab API Adapter can be installed as a Windows Service by opening PowerShell or Command Prompt as an Administrator and executing the following commands:

185. Step	Detail
     1	Create the Service: ✱ NOTE: Replace “C:\ABC” below with the path of the actual folder where the API Adapter was deployed. sc.exe create MyGeotabAPIAdapter binPath="C:\ABC\MyGeotabAPIAdapter_SCD_win-x64\MyGeotabAPIAdapter.exe" After creating the service, it can be managed (set to start automatically, etc.) from the Services console (services.msc). The commands in the next steps can also be used.
     2	Start the Service: sc.exe start MyGeotabAPIAdapter
     3	Stop the Service: sc.exe stop MyGeotabAPIAdapter
     4	Delete the Service: sc.exe delete MyGeotabAPIAdapter

187. Linux

188. To install MyGeotab API Adapter as a Linux (systemd) Service:

190. Step	Detail
     1	Create a Service Unit File: Use a text editor like nono or vim. sudo nano /etc/systemd/system/mygeotabadapter.service
     2	Create the Service: Paste the following configuration into the file. Then, save and close the file. ✱ NOTES: In ExecStart and WorkingDirectory, replace “/mnt/c/ABC” with the path of the actual folder where the API Adapter was deployed. In User, replace <User> with the user that the service should run as. [Unit] Description=MyGeotab API Adapter [Service] ExecStart=/mnt/c/ABC/MyGeotabAPIAdapter_SCD_linux-x64/MyGeotabAPIAdapter WorkingDirectory=/mnt/c/ABC/MyGeotabAPIAdapter_SCD_linux-x64 User=<User> Restart=always RestartSec=10 SyslogIdentifier=mygeotab-adapter Environment=ASPNETCORE_ENVIRONMENT=Production Environment=DOTNET_PRINT_TELEMETRY_MESSAGE=false [Install] WantedBy=multi-user.target
     3	Reload the systemd Daemon: sudo systemctl daemon-reload
     4	Enable the Service to Start on Boot: sudo systemctl enable mygeotabadapter.service
     5	Start the Service: sudo systemctl start mygeotabadapter.service
     6	Check the Status of the Service: sudo systemctl status mygeotabadapter.service

192. Option 3: Set-up a Scheduled Task / Cron Job

193. A third option for running the MyGeotab API Adapter is to set up a Scheduled Task (in Windows) or a cron job (in Linux). These will not be detailed further here since installing as a service is a better option.

194. Database Maintenance

196. ! WARNING: It is possible for the database to grow very large very quickly, resulting in potential disk space and performance issues. For example, running the adapter against a MyGeotab database with a fleet of ~20,000 devices and pulling data for all supported feeds could result in an empty associated PostgreSQL database growing to ~40 GB in size within 7 days. In such a scenario, the database might include ~225,000,000 StatusData, ~65,000,000 LogRecord and ~10,000,000 Trip records.

198. The “DM2” (Data Model 2) version of the adapter database is designed with performance, scalability and maintainability in mind. Specifically:

5. It is normalized with many indexes and relationships between tables.
6. Where possible, the string values of Geotab Entity Ids are converted to their underlying numeric values and used as the primary key on the subject table, facilitating fast queries even with many joins and large data volumes. See “GeotabId” and “id” Columns for more information.
7. “Feed Data” tables are partitioned by month, week or day. See Database Partitioning for details.
8. The MyGeotab API Adapter includes Automated Database Maintenance functionality that helps to keep tables and indexes optimized through regular maintenance while the application is in operation.

With the DM2 version of the adapter database, it is possible to achieve fast throughput speed while also providing the ability to query the data with fast results even with large data volumes.

Solution Information

This section provides detailed information about the architecture, database, configuration and logic of the MyGeotab API Adapter solution.

Solution Architecture

The following diagram provides an overview of the MyGeotab API Adapter solution architecture.

Geotab does not host the MyGeotab API Adapter. Rather, the solution must be hosted by the Geotab partner/reseller or customer. It consists of two components - the “API Adapter” application and the adapter database. The API Adapter is a collection of NET 9.0 Background Services (C#) that interface between the MyGeotab API and the database, essentially pulling data from the former and writing to tables in the latter. Each customer MyGeotab database must have a dedicated adapter and database pair; it is not possible to mix data from multiple MyGeotab databases. The Geotab partner/reseller or customer is responsible for integrating between the adapter database and any downstream applications or databases - potentially with assistance from a third-party solutions integrator.

Background Services

The MyGeotab API Adapter is essentially a collection of Background Services that operate in parallel while the application is running. Using the appsettings.json file, is possible to configure the various services - enabling the ones that are needed and disabling those that are not, setting thresholds and more.

Although there are a few different types of services, they largely operate in a very similar fashion using most of the same components. The following is an example of a “Feed Data” service that is responsible for continually retrieving Geotab data of a specific entity type and writing that data to the adapter database.

In the above diagram, there are a number of “generic” classes listed. These are single physical classes in the API Adapter source code that work with many types of entities. For example, the Generic Geotab Object Feeder is used to extract any type of entity from the Geotab platform via the GetFeed method. Using this approach instead of coding separate classes for each Geotab entity type is vastly more efficient and makes the solution much easier to maintain.

The general workflow of feed data processors is described below, using the numbered callouts in the above diagrams to draw attention to the relevant components.

1: Generic DB Object Caches

Generic DB Object Caches load data from “reference data” tables in the adapter database into application memory so that fast lookups can be executed without needing to query the adapter database for each lookup. This helps to greatly reduce “chattiness” of the application while improving efficiency. Using the StatusData Processor (StatusDataProcessor2) as an example, the list of diagnostics is cached in memory as the Ids of records in the DiagnosticIds2 table in the adapter database must be found in order to link to the DiagnosticId property of the StatusData object before it is inserted into the StatusData2 table in the adapter database.

2: Generic Geotab Object Feeders

After loading any required records from the adapter database into in-memory caches, the Background Service will use its Generic Geotab Object Feeder to retrieve a batch of Geotab entities from the MyGeotab database. The Generic Geotab Object Feeder uses the GetFeed method without any searches to retrieve data as quickly as possible and with the minimum possible impact on the overall performance of the MyGeotab database.

3: Geotab Object Filterers

The AppSettings - GeneralFeedSettings section in the appsettings.json file contains three settings - DevicesToTrack, DiagnosticsToTrack and ExcludeDiagnosticsToTrack - which are used to filter data of various entity types on Device Id and/or Diagnostic Id. After retrieving a batch of Geotab data using the Generic Geotab Object Feeder, the Background Service will engage one or more Geotab Object Filterers, if applicable, to discard unwanted records from the batch of data. This helps to ensure that only the desired data will be written to the adapter database.

4: Object Mappers

After retrieving and, if necessary, filtering a batch of Geotab entities, the Background Service utilizes an Object Mapper to create a DB Entity for each Geotab entity in the batch. There are Object Mappers for every type of Geotab Entity that gets written to the adapter database. An Object Mapper essentially maps properties of a Geotab entity to associated columns in an adapter database table, including performing any necessary data type conversions. The result is that the batch of Geotab entities is essentially converted into an equivalent batch of DB Entities.

5: Generic Entity Persisters

Finally, the Background Service takes the batch of DB Entities created by the Object Mapper and uses the Generic Entity Persister to write the entities to table(s) in the adapter database.

The Generic Entity Persister handles setting up database transactions and pushing the data down through a generic Base Repository which contains various database read/write methods - including bulk operations to make inserts and updates as fast as possible. From there, the Base Repository leverages Dapper (https://dappertutorial.net/) to perform much of the work involved in mapping C# objects (the “DB Entities”) to actual database records in the adapter database.

Data Enhancement Services

The MyGeotab API Adapter application contains numerous services that extract data from a MyGeotab database and write that data to corresponding tables in the adapter database.

1. Services that process “reference data” are configured in the AppSettings - Caches section of the appsettings.json file.
2. Services that process “feed data” are configured in the AppSettings - Feeds section of the appsettings.json file.

The AppSettings - DataEnhancementServices section of the appsettings.json file is used to configure services that enhance the data after it has been extracted from the Geotab platform and written to the adapter database. These services are described in the subsections below.

FaultData and StatusData Location Services

The FaultData and StatusData location services, configured via the AppSettings - DataEnhancementServices - FaultData and AppSettings - DataEnhancementServices - StatusData sections of the appsettings.json file, respectively, use LogRecords (“GPS data”) to interpolate location for FaultData and StatusData records.

The most complex and challenging part of the interpolation process is finding the closest lag (i.e. preceding) and lead (i.e. succeeding) LogRecords for each FaultData or StatusData record. Using a single StatusData record as an example, is is necessary to:

1. Find the LogRecord for the subject Device with the closest preceding or equal DateTime to that of the StatusData record.
2. Find the LogRecord for the subject Device with the closest succeeding DateTime to that of the StatusData record.

Complicating the process is the fact that these location services are running at the same time as the feed data services that are, in parallel, extracting LogRecords, FaultData and StatusData records from the MyGeotab database and bulk inserting them into tables in the adapter database. Challenges in this regard include:

1. Data Density: Over a given period of time, there may be many more LogRecords than FaultData records, for example. This means that, when extracting historical data for each of the feeds in parallel at the maximum rate of 50,000 records per GetFeed call, and writing the results to the adapter database, the FaultData processor will be caught-up to the present time much more quickly than the LogRecord processor. As such, care needs to be taken to ensure that interpolation is only attempted for FaultData records where LogRecords for the the subject Devices have already been downloaded.
2. Data Volume: Data volume can vary greatly by fleet size and by entity type. With a large fleet of 80,000 devices, for example, a batch of 100,000 “unprocessed” FaultData records might span 10 days, but in that same period, there might be tens of millions of LogRecords. Conversely, with a small fleet of 100 devices, the record counts might be trivial by comparison. Additionally, the sheer volume of data collected over time can present significant challenges with regard to query performance.
3. Concurrency/Contention: With various processes reading and writing potentially large quantities of data involving the same database tables at the same time, there is a high possibility of this leading to contention issues whereby services reading and writing data end-up blocking each other with table and row locks causing cascading issues.

To address the above challenges and allow the location services to interpolate locations for FaultData and StatusData records as quickly as possible, a number of mechanisms are employed including:

1. Database Partitioning: The Database Partitioning strategy employed helps to improve query performance by greatly reducing the amount of data that needs to be evaluated when querying to retrieve a batch of data for interpolation. By querying data within specific DateTime ranges, queries can be restricted to only the partitions containing the relevant records.
2. Automated Database Maintenance: The Automated Database Maintenance functionality helps to keep statistics updated and indexes optimized, in turn, helping the database engines to execute queries as quickly as possible.
3. EntityMetaData2 Table: The EntityMetadata2 table is used to store the combination of DeviceId + DateTime + EntityType + EntityId. Each time a record is inserted into one of the FaultData2, LogRecords2 or StatusData2 tables, a corresponding record is inserted into this table. It is used by stored procedures / functions (described below) that must relate these different entities based on DateTime and DeviceId to perform location interpolation. Having this information in a single table boosts query performance by reducing complex multi-table joins.
4. Parameterized Stored Procedures / Functions: The spFaultData2WithLagLeadLongLatBatch and spStatusData2WithLagLeadLongLatBatch stored procedures (in SQL Server, or functions in Postgres) in the adapter database perform the heavy-lifting of retrieving batches of FaultData or StatusData records with lag and lead LogRecord information attached so that interpolation can take place. These procedures are parameterized with “MaxDaysPerBatch”, “MaxBatchSize” and “BufferMinutes” parameters that are configured in the appsettings.json file and allow the procedures to effectively be scaled to operate in a performant manner for fleets of various sizes. Using these parameters, the procedures can be configured to optimally leverage the database partitions for fast query performance. Note that there are some differences between the SQL Server versions and their PostgreSQL counterparts due to differences in how the two database engines operate.

Database

Out-of-the-box, the MyGeotab API Adapter supports SQL Server and PostgreSQL for use as the adapter database into which data retrieved via the MyGeotab API is written.

List of Tables

The following table lists all of the tables contained in the database along with descriptions that include the associated MyGeotab API objects, where applicable. Further detail relating to the structure and fields of individual tables can be found in the Data Dictionary section.

Data Dictionary

Although both the SQL Server and PostgreSQL databases are supported, for simplicity, any database-specific references such as data types will relate to the SQL Server version of the schema. For information such as schema details related to other supported database types, refer to the Database Setup section. The tables and views included in the adapter database schema are detailed in the following subsections.

“GeotabId” and “id” Columns

Most of the tables in the adapter database have “GeotabId” and “id” columns.

The id is the unique identifier for the record in the adapter database table.

1. In some cases, the id is also the value of the GeotabId converted to its underlying numeric value. This is done to facilitate indexes, relationships and faster query performance.
2. In other cases, the id is generated automatically by the adapter database and is entirely unrelated to the Geotab system.
3. Details about the id column are provided in the sections below for each table in the adapter database.

The id column is of the bigint data type. It is also indexed and defined (in combination with the DateTime column where applicable) as the primary key. One of Its purposes is to allow downstream processes to delete records (from the feed data tables) as part of the suggested strategy while maintaining optimal performance and avoiding database concurrency/contention issues that could arise (if the GeotabId were to be used) when trying to delete records while the adapter is actively inserting/updating records.

The GeotabId is a string representation of the unique identifier for the specific Entity object in the Geotab system; it must be used when relating objects back to the Geotab system.

Entity Relationship Diagrams

The sections below provide information about all of the tables in the adapter database and the columns within. If a given table contains one or more columns that are related to columns in other tables, a mini Entity Relationship Diagram (ERD) is also provided to show any relationships between the subject table and the table(s) with which it is directly associated.

The following diagram describes object representations used in these ERDs. Notes:

1. Blue headers are used for “reference data” tables.
2. Yellow headers are used for “feed data” tables.
3. Black crow’s foot lines show physical relationships.
4. Red crow’s foot lines show logical relationships (i.e. where columns in two tables are logically related, but a physical foreign key relationship has not been established).

BinaryData2

The BinaryData2 table contains data corresponding to MyGeotab BinaryData objects. Return to List of Tables.

ChargeEvents2

The ChargeEvents2 table contains data corresponding to MyGeotab ChargeEvent objects. Return to List of Tables.

DBMaintenanceLogs2

The DBMaintenanceLogs2 table is a system table used by the MyGeotab API Adapter. Return to List of Tables.

DBPartitionInfo2

The DBPartitionInfo2 table is a system table used by the MyGeotab API Adapter. It is populated by the spManagePartitions stored procedure the first time it is executed during initial database setup. Return to List of Tables.

DefectSeverities2

The DefectSeverities2 table contains records corresponding to values of the MyGeotab DefectSeverity entity. Return to List of Tables.

Devices2

The Devices2 table contains data corresponding to MyGeotab Device objects. Return to List of Tables.

DeviceStatusInfo2

The DeviceStatusInfo2 table contains data corresponding to MyGeotab DeviceStatusInfo objects. Return to List of Tables.

DiagnosticIds2

The DiagnosticIds2 table contains data corresponding to MyGeotab Diagnostic objects. Return to List of Tables.

Diagnostics2

The Diagnostics2 table contains data corresponding to MyGeotab Diagnostic objects. Return to List of Tables.

DriverChanges2

The DriverChanges2 table contains data corresponding to MyGeotab DriverChange objects. Return to List of Tables.

DutyStatusAvailabilities2

The DutyStatusAvailabilities2 table contains data corresponding to MyGeotab DutyStatusAvailability objects. Return to List of Tables.

DutyStatusLogs2

The DutyStatusLogs2 table contains data corresponding to MyGeotab DutyStatusLog objects. Return to List of Tables.

DVIRDefectRemarks2

The DVIRDefectRemarks2 table contains data corresponding to MyGeotab DefectRemark objects. Return toList of Tables.

DVIRDefects2

The DVIRDefects2 table contains data corresponding to defects associated with DVIRLogs. It includes data derived from MyGeotab DVIRLog, DVIRDefect, Defect and Group objects. Return to List of Tables.

DVIRLogs2

The DVIRLogs2 table contains data corresponding to MyGeotab DVIRLog objects. Return to List of Tables.

EntityMetadata2

The EntityMetadata2 table is used to store the combination of DeviceId + DateTime + EntityType + EntityId. Each time a record is inserted into one of the FaultData2, LogRecords2 or StatusData2 tables, a corresponding record is inserted into this table. It is used by stored procedures / functions that must relate these different entities based on DateTime and DeviceId to perform location interpolation. Having this information in a single table boosts query performance by reducing complex multi-table joins. Return to List of Tables.

EntityType (Enumeration)

The EntityType enumeration is not represented in a physical table. It is used to identify MyGeotab Entity types. Return to List of Tables.

ExceptionEvents2

The ExceptionEvents2 table contains data corresponding to MyGeotab ExceptionEvent objects. Return to List of Tables.

fail_DVIRDefectUpdateFailures2

The fail_DVIRDefectUpdateFailures2 table is associated with the upd_DVIRDefectUpdates2 table. If a row is inserted into the upd_DVIRDefectUpdates2 table and it does not pass data validation checks, or if an exception occurs when the adapter attempts to execute the command, a copy of the original row in the upd_DVIRDefectUpdates2 table will be added to the fail_DVIRDefectUpdateFailures2 table along with the related error message which will appear in the FailureMessage column. For more detail, refer to the DVIRLog Manipulator section of this guide. Return to List of Tables.

FaultData2

The FaultData2 table contains data corresponding to MyGeotab FaultData objects. Return to List of Tables.

FaultDataLocations2

The FaultDataLocations2 table contains interpolated location data for MyGeotab FaultData objects.For each record inserted into the FaultData2 table, a corresponding record is inserted into this table. If the EnableFaultDataLocationService setting is set to true, records in this table will be updated with location information interpolated using data from the LogRecords2 table. Return to List of Tables.

FuelAndEnergyUsed2

The FuelAndEnergyUsed2 table contains data corresponding to MyGeotab FuelAndEnergyUsed objects. Return to List of Tables.

Groups2

The Groups2 table contains data corresponding to MyGeotab Group objects. Return to List of Tables.

LocationInterpolationResultReason (Enumeration)

The LocationInterpolationResultReason enumeration is not represented in a physical table. It is used to identify reasons why location interpolation was not possible for a given Entity.

LogRecords2

The LogRecords2 table contains data corresponding to MyGeotab LogRecord objects. Return to List of Tables.

MiddlewareVersionInfo2

The MiddlewareVersionInfo2 table is a system table used by the MyGeotab API Adapter. Return to List of Tables.

MyGeotabVersionInfo2

The MyGeotabVersionInfo2 table contains software version information (obtained from MyGeotab API VersionInformation) about the MyGeotab server and database that the subject adapter database is associated with via the MyGeotab API Adapter. Return to List of Tables.

OServiceTracking2

The OServiceTracking2 table is a system table used by the MyGeotab API Adapter. Return to List of Tables.

RepairStatuses2

The RepairStatuses2 table contains records corresponding to values of the MyGeotab RepairStatusType entity. Return to List of Tables.

Rules2

The Rules2 table contains data corresponding to MyGeotab Rule objects. Return to List of Tables.

StatusData2

The StatusData2 table contains data corresponding to MyGeotab StatusData objects. Return to List of Tables.

StatusDataLocations2

The StatusDataLocations2 table contains interpolated location data for MyGeotab StatusData objects.For each record inserted into the StatusData2 table, a corresponding record is inserted into this table. If the EnableStatusDataLocationService setting is set to true, records in this table will be updated with location information interpolated using data from the LogRecords2 table. Return to List of Tables.

Trips2

The Trips2 table contains data corresponding to MyGeotab Trip objects. Return to List of Tables.

upd_DVIRDefectUpdates2

The upd_DVIRDefectUpdates2 table may be used to send DVIRDefect updates to the MyGeotab database with which the adapter is configured to communicate. If a record is inserted into this table and it fails to result in the subject DVIRDefect update being propagated to the MyGeotab database, a copy of the record will be inserted into the fail_DVIRDefectUpdateFailures2 table and the reason for the failure will be provided in the FailureMessage column. Once a record has been processed, it will be deleted from the upd_DVIRDefectUpdates2 table. For more detail, refer to the DVIRLog Manipulator section of this guide. Return to List of Tables.

Users2

The Users2 table contains data corresponding to MyGeotab User objects. Return to List of Tables.