Stream a Geotab database

A Geotab stream lands a MyGeotab database's trips, exception events, fill-ups, fuel used and driver changes in your warehouse, as often as every few minutes, with the vehicles, users, zones, rules and groups they point at. Each run reads only what Geotab's own feeds have added since the last run.

Geotab is a published source, like Postgres and Bite: sanda wrote the code that reads it and runs it as its own, so there is no connector for sam to write or for anyone to approve. You make a MyGeotab user for sanda, type its sign-in once, choose what to read, and set a schedule.

Before you start

• Make a service user for sanda in MyGeotab. Geotab has no "allow this app" sign-in, so sanda signs in as a MyGeotab user. In MyGeotab, go to People · Users & Drivers and add a user for sanda alone, choosing Service Account as its authentication type: a service account can call Geotab's API and can't sign in to MyGeotab itself. Geotab recommends View only clearance for a user that only reads, and sanda only reads. Adding a user costs nothing in Geotab, though some of its services can bring data costs: ask your Geotab reseller, who can also add the user for you.
• Give it every group. For its groups, choose the group every other group sits under. Geotab's feeds answer a user who sees only some groups so slowly that they can fail, so sanda won't connect one.
• Have the database's name. It's the part of MyGeotab's address after the server, and you type it with the user's username and password.
• One database at a time. Each connection reads one database. A business with two databases connects twice and makes a stream for each, because a stream keeps each database's records by Geotab's own ids.
• Tell your people. Trips, exception events and driver changes tie where a vehicle went, and how it was driven, to the person driving it. Check that your workplace's notice about vehicle tracking covers reporting on it.

Connect a database

sanda keeps the password encrypted, and it is never shown again, in the console or to sam. It sends it only to Geotab, to sign in, and keeps the session Geotab hands back for later runs, so a run signs in only when the last session has ended. Type it again asks for the username and password again (the server and the database stay as they were), and every stream on the database carries on. Disconnect forgets the sign-in. To end sanda's access in Geotab too, remove the service user in MyGeotab.

Make a stream

What it reads

The feeds

Geotab keeps a feed of each kind of record: trips, exception events, fill-ups, fuel used and driver changes. Each answer says how far into the feed it read, and each run carries on from exactly there, so nothing that arrived between runs is missed, however long a run waits. A first run, and Load everything again, starts the feed at the Read history from date.

Each request asks for up to 5,000 trips or fill-ups, or 10,000 exception events, fuel records or driver changes. A first run over a long history takes several requests, and may take several runs, each carrying on from the last.

Trips

A trip is a vehicle's drive from one stop to the next: when it started and stopped, how far it went, how long it drove, idled and stopped, its speeds, its driver and where it stopped (stopPoint). A trip still going when a run reads it is sent again as more of it arrives, under a new id. sanda keys trips by their vehicle and start time, as Geotab's own guide says to, so the newer trip replaces the older one's row.

Exception events

Each time a vehicle breaks one of your rules (speeding, harsh braking, idling too long, or a stop inside a zone), Geotab records an exception event with the rule, the vehicle, the driver, when it started and ended, and the distance. An event keeps its id when Geotab updates it, so it stays one row. Join rule to the rules table for the rule's name and type.

Geotab makes a zone stop rule for each zone that identifies stops (a zone does unless it is set not to), so that rule's exception events say when a vehicle stopped inside the zone and for how long. When the zone is a job site, that is the vehicle's time on site.

Fill-ups, fuel used and driver changes

A fill-up is a refuelling Geotab detected, with the litres added and, where a fuel card transaction matched it, the cost. Each matched transaction lands in fuelTransactions with its time, cost, currency, litres, product and site only. Fuel used is what each vehicle burned over time, with what it burned idling. A driver change is a driver signing in to or out of a vehicle, which helps name the driver of a trip that has none.

The lists

Vehicles, users, zones, rules and groups are read whole each run. Something Geotab no longer lists (a vehicle removed from the database) keeps its row, marked _deleted_at, and reports and answers stop counting it. Archived vehicles stay in the list, because old trips point at them.

Users land their username, first and last name, employee number, designation, whether they drive, their groups, their active dates, their time zone and when they last signed in. sanda never lands a user's password, phone number or settings. Rules land without their conditions, which are often larger than the rest of the rule.

Left out

• GPS points and engine readings (Geotab's log records and status data). They are Geotab's largest feeds by far, and sanda doesn't read them. For time on site, use the zone stop exception events, or a trip's stop point against a zone's boundary.
• Fuel card details. A fill-up's matched transactions land without the card's number, the cardholder's name, the card provider's own record of the purchase, their comments, or the vehicle's plate, serial number and VIN.
• Anything sanda would have to write. sanda only reads: it can't add, change or remove anything in MyGeotab.

What lands in your warehouse

Every field becomes a column, typed by its values, and the whole record is kept in _record, as for every pull. A few things are worth knowing when you model them:

• Times are UTC, as Geotab sends them. Convert them to your own time zone in a view.
• Units are metric, but not all the same: a trip's distance is in kilometres and its odometer in metres; a fill-up's distance is in metres and its volume in litres; speeds are in kilometres an hour; a trip's engineHours is in seconds.
• Durations are text, exactly as Geotab writes them (a trip's drivingDuration, idlingDuration and stopDuration, an exception event's duration).
• References are JSON with an id: a trip's device and driver, an exception event's rule. Join device->>'id' to the vehicles table's id, driver->>'id' to the users table's and rule->>'id' to the rules table's.
• An unidentified driver is Geotab's UnknownDriverId: a trip, exception event, fill-up or driver change no driver was identified for has it as its driver, as the bare name (whose driver->>'id' is empty) or as its id, and it joins no user. Driver changes can help name who drove.
• Check a fill-up's currency before you add up costs: Geotab's default currencyCode is US dollars.
• A fill-up's derivedVolume or totalFuelUsed of -1 means Geotab couldn't work it out. Leave those rows out before you add either up.
• A zone's boundary is its points, a JSON list of longitude (x) and latitude (y) pairs, and its externalReference is where your job system's site or job id goes, if you keep one there.
• Nothing in a feed is removed. Geotab works trips, exception events, fill-ups and fuel used out from what its devices send, and can change or remove one later as more arrives. The feed sends what changed but never says what was removed, so a record Geotab removes after it landed keeps its row. When exact totals matter, check for trips on the same vehicle whose times overlap.

Change a stream

Edit, on the stream's page, changes everything but the database and the warehouse. Something you take off keeps its table, with what it landed. A new Read history from date takes effect the next time everything is read from the start: press Load everything again on the stream's page.

When something goes wrong

A failed run counts toward the stream pausing itself, as any stream's does. See runs.

Limits

• A run makes at most 0.9 requests a second, because Geotab asks integrations to leave a little over a second between calls, and limits how often each kind of record may be asked for each minute.
• Geotab gives a feed request 180 seconds to answer, and sanda waits up to 170.
• A Geotab session lasts 14 days, a user may hold 100 at once, and Geotab allows a user 10 sign-ins a minute. That is why sanda keeps its session between runs, and why sanda's user should be its own: each sign-in past a user's 100 sessions ends the oldest, which could be another tool's or sanda's.
• A request asks for up to 5,000 vehicles or 1,000 zones.
• One run lands at most 20,000,000 records, so a long history is several runs.

A Geotab stream is billed like any pull: a charge for each run that does its work, and the rows it lands past what your edition includes. See what streams cost.