Overview

This guide will explain how to synchronize telematics data from Samsara into a relational database using the API. This guide describes an integration that:

• Backfills the database with all historical telematics data
• Synchronizes any new telematics data into the database

Data Schema

In the physical world, each type of telematics data generates its own event series. For example: GPS pings, odometer readings, and changes in engine state all constitute their own series of events. This is how Samsara collects, stores, and serves telematics data. For each vehicle, each type of telematics data is stored as its own series of events.

TODO: potential illustration

If you want to process telematics event streams independently, then no extra work needs to be done. You can query the Telematics APIs directly.

If you want to stitch each of these event streams together into a single table in a database, then this guide is for you.

This guide will describe how to stitch telematics data together to construct a database that looks like this:

Note: you can select which types of telematics data you want to include in the table - ranging from just a few to all of them.

💡Guaranteeing no missing events

Because we are stitching disparate event series into a single row in a table, there is potential that events may occur in between rows. If you need to guarantee that no events are missed, then you should query and store the event series independently. (See types in the Telematics Guide).

APIs

Samsara provides a different set of APIs for each asset type:

Vehicles

• https://api.samsara.com/fleet/vehicles/stats
• https://api.samsara.com/fleet/vehicles/stats/feed
• https://api.samsara.com/fleet/vehicles/stats/history

Equipment

• https://api.samsara.com/fleet/equipment/stats
• https://api.samsara.com/fleet/equipment/stats/feed
• https://api.samsara.com/fleet/equipment/stats/history

Trailers

• https://api.samsara.com/fleet/trailers/stats
• https://api.samsara.com/fleet/trailers/stats/feed
• https://api.samsara.com/fleet/trailers/stats/history

Each of these sets of APIs uses the same conventions for querying data. The rest of this guide will simply refer to the /stats APIs in general, and you can add in the prefix for the asset type(s) relevant to your use case. The examples will use telematics from the /vehicles/stats APIs.

Integration Outline

Because we want to backfill historical data and synchronize new data, we will split the integration into two processes:

• Begin a /stats/feed of data. Store the time that the feed was initiated as feedStartTime. This feed will be the ongoing process that synchronizes any new telematics data to the database.
• Get the /stats/history. Set the startTime parameter to be the beginning of Samsara's history: 2015-01-01T00:00:00Z. Set the endTime parameter to the feedStartTime. This will allow you to backfill any data that was generated since the beginning of Samsara's history up until when you started the feed.

These two processes can be run in parallel. You can immediately begin the /stats/history job as soon as you know when you started the feed (feedStartTime). The /stats/feed job will continue to ingest new data while the /stats/history job processes any historical data that's been generated. (The /stats/history job should be a one-time backfill, but the /stats/feed job will be an ongoing process. We'll discuss handling application failures in a later section.)

Both these processes will use the same query patten described below to stitch telematics events together into a single row in the database.

Query Pattern

Both the /stats/history and /stats/feed jobs will use the same query pattern to stitch events together into a single database row.

To combine telematics events together, we will use the concept of decorations.

The /stats/history and /stats/feed endpoints have two query parameters for requesting telematics data:

• types
• decorations

types indicates the main event series you are most interested in. decorations indicates which data you want to decorate the main event series with.

For example, the query:

GET https://api.samsara.com/fleet/vehicles/stats/feed?types=gps&decorations=obdOdometerMeters,engineStates

will return a feed of GPS pings, where each GPS ping is decorated by the last known odometer reading and engine state at that time:

{
    "data": [
        {
            "id": "212014918174311",
            "name": "217322",
            "gps": [
                {
                    "decorations": {
                        "engineStates": {
                            "value": "On"
                        },
                        "obdOdometerMeters": {
                            "value": 360526145
                        }
                    },
                    "time": "2021-08-24T00:00:04.005Z",
                    "latitude": 29.71555136,
                    "longitude": -95.334823
                },
                {
                    "decorations": {
                        "engineStates": {
                            "value": "On"
                        },
                        "obdOdometerMeters": {
                            "value": 360526170
                        }
                    },
                    "time": "2021-08-24T00:00:10.008Z",
                    "latitude": 29.7148708,
                    "longitude": -95.33464721
                },
                ...
            ]
        },
        ...
    ],
    "pagination": {....}
}

This allows us to start building a couple of rows of our database:

At this point, the Engine Hours, Fuel Level, and other data types cells are still empty.

This is because decorations is limited to two types.

In order to fill in the rest of the row, we need to make another query for Engine Hours and Fuel Level. We'll set types to gps to get the same event series as before, but we'll set decorations to obdEngineSeconds,fuelPercents to get the new event decorations:

GET https://api.samsara.com/fleet/vehicles/stats/feed?types=gps&decorations=obdEngineSeconds,fuelPercents

The query will return the same of GPS pings we queried earlier, but instead, each GPS ping is decorated by the last known engine runtime reading (in seconds) and fuel percentage level at that time:

{
    "data": [
        {
            "id": "212014918174311",
            "name": "217322",
            "gps": [
                {
                    "decorations": {
                        "fuelPercents": {
                            "value": 34
                        },
                        "obdEngineSeconds": {
                            "value": 30539340
                        }
                    },
                    "time": "2021-08-24T00:00:04.005Z",
                    "latitude": 29.71555136,
                    "longitude": -95.334823
                },
                {
                    "decorations": {
                        "fuelPercents": {
                            "value": 34
                        },
                        "obdEngineSeconds": {
                            "value": 30539346
                        }
                    },
                    "time": "2021-08-24T00:00:10.008Z",
                    "latitude": 29.7148708,
                    "longitude": -95.33464721
                },
                ...
            ]
        },
        ...
    ],
    "pagination": {....}
}

This allows us to fill in the rest of the stats in our database:

We can update the rows using (Vehicle ID, Timestamp) as the composite key for each row. Since we are querying types=gps each time, then the GPS event series for each vehicle will have the same set of timestamps. We can match these timestamps across different decorations queries to combine X amount of decorations together (even though the API limits decorations to two types per query).

Summary

Here's the online for how to stitch multiple telematics events into a single row in the database:

1. Select one telematic event to decorate all other events off of. This could be the event that you are most interested in, or you could base this on the frequency of that event. For example, GPS pings every 5 seconds (most frequent event).
2. Make a series of /stats/feed or /stat/history queries, each with types set to the main telematic event, and each with 2 different decorations. You can select as many decorations as you want, split into pairs of twos:
   • /feed?types=gps&decorations=ambientAirTemperatureMilliC,auxInput1
   • /feed?types=gps&decorations=auxInput2,auxInput3
   • /feed?types=gps&decorations=auxInput4,auxInput5
   • …other sets of decorations...
   • /feed?types=gps&decorations=obdEngineSeconds,obdOdometerMeters
3. Insert or update rows of the database with the events from the queries using (id,gps[i].time) as the composite key.