Hi everyone,
Is there a way to get the total distance driven between two different dates (startDate and endDate) for all vehicles across multiple databases (10 different databases) one time? Maybe using Python? What/Where do I look to get this information via a Python script?
You need to be logged in to reply to this post and participate in the Geotab Community discussions.
Edited by Joseph-1062
Hi @Seth-1151 !
Though Trip data can be used to retrieve distance travelled information, it can lead to a lot of overhead for you when it comes to taking in all the data and processing it.
To keep sanity, I think the easiest way to retrieve distance driven between two dates would be to pull odometer values for the start and end dates. You can do so by performing a Get<StatusData> call and searching for the diagnostic id of DiagnosticOdometerAdjustmentId. An example in Python of this call would be like so:
GetOdometerValues = api.get("StatusData", search = {"deviceSearch": {"id":"b1"},"diagnosticSearch":{"id":"DiagnosticOdometerAdjustmentId"},"fromDate":"2020-03-30T00:00:00.000Z","toDate":"2020-03-31T00:00:00.000Z"})The code above will return the odometer values (both engine-based & GPS-based for those vehicles that do not report odometer) for device id "b1" for a 24 hour period. If you would like to have it for all vehicles, just remove the deviceSearch from the main search parameter.
Take the data value from the first and last objects in the array that is returned to find the distance (in meters) that the vehicle has driven. Keep in mind that when having any date range between the fromDate and toDate, the call's response will always have at least two records returned (in the case that the vehicle hasn't moved) and up to potentially thousands of records, depending on how big the range is. If you do require having all this data, I would then recommend using our data feed (aka GetFeed) instead of the simpler Get. You can read more about the data feed at this link and more specifically regarding the syntax of the method GetFeed that is used at this link.
If you do not require having all this data and just care about the odometer values for the ends of the date range, I suggest making two Get<StatusData> calls per device where the fromDate & toDate are the same timestamps. This will only return one record with the odometer value at the timestamp specified. As an example:
# fromDate & toDate both set to '2020-03-30T00:00:00.000Z'
GetOdometerValueAtStart = api.get("StatusData", search = {"deviceSearch": {"id":"b1"},"diagnosticSearch":{"id":"DiagnosticOdometerAdjustmentId"},"fromDate":"2020-03-30T00:00:00.000Z","toDate":"2020-03-30T00:00:00.000Z"})
# fromDate & toDate both set to '2020-03-31T00:00:00.000Z'
GetOdometerValueAtEnd = api.get("StatusData", search = {"deviceSearch": {"id":"b1"},"diagnosticSearch":{"id":"DiagnosticOdometerAdjustmentId"},"fromDate":"2020-03-31T00:00:00.000Z","toDate":"2020-03-31T00:00:00.000Z"})
DistanceDriven = GetOdometerValueAtEnd[0]['data'] - GetOdometerValueAtStart[0]['data]If you were to do this for numerous devices, you would have many repetitive calls that could contribute to network overhead. In this case, I would recommend housing them within a multicall. Multicalls allow you to group and send over more than one API call at at time which helps to reduce the amount of overhead in situations where many small requests need to be made to a server. You can read more about multicalls at this link.
An example of how this would look as a multicall:
GetOdometerValues = api.multi_call(calls=[("Get", dict(typeName="StatusData", search = {"deviceSearch": {"id":"b1"},"diagnosticSearch":{"id":"DiagnosticOdometerAdjustmentId"},"fromDate":"2020-03-30T00:00:00.000Z","toDate":"2020-03-30T00:00:00.000Z"})), ("Get", dict(typeName="StatusData", search = {"deviceSearch": {"id":"b1"},"diagnosticSearch":{"id":"DiagnosticOdometerAdjustmentId"},"fromDate":"2020-03-31T00:00:00.000Z","toDate":"2020-03-31T00:00:00.000Z"}))])
OdometerValueAtStart = GetOdometerValues[0][0]['data']
OdometerValueAtEnd = GetOdometerValues[1][0]['data']
DistanceDriven = OdometerValueAtEnd - OdometerValueAtStartIn regards to your question about performing this type of data extraction from multiple databases, you would have to declare separate instances per database, i.e.:
import mygeotab
db1 = mygeotab.API(username = geotab_username, password = geotab_password, database = geotab_database_name_1)
db2 = mygeotab.API(username = geotab_username, password = geotab_password, database = geotab_database_name_2)
try:
#authenticate to create a session id that is stored within the 'db1' & 'db2' objects
db1.authenticate()
db2.authenticate()
except Exception as e:
print("Error in authentication. Message:
%s", e)I believe you were receiving the 'client' not defined error as the API instance was not yet declared when trying to call it. I would suggest moving the code on line 5 to after the get_geotab_dataframe function is called.
I hope this helps with getting you started!
Best,
Joseph
Update:
I think I've been able to get the driven km by calling 'Trip', which contains the distance for the specified period.
However, I'm getting an error and hoping anyone here can assist. The error is NameError: name 'client' is not defined and the code snippet is below. Thanks in advance.
def get_geotab_dataframe(db):
client = mygeotab.API(username=GEOTAB_USERNAME, password=GEOTAB_PASSWORD, database=db)
client.authenticate()
results = client.get("Trip", search = {"fromDate": "2020-01-01T00:00:00.000Z",
"toDate": "2020-01-31T00:00:00.000Z"})
for db in GEOTAB_DATABASES:
df = get_geotab_dataframe(db)
df = pandas.DataFrame(results)
df.to_csv("sgiodometer3.csv", encoding='utf-8', index=False)
print(df)
Hi Seth!
You can use the SDK to get this done. Calling different database to get all the data
The SDK reference page is a good start https://geotab.github.io/sdk/ You have very nice examples (see the Runner) with JavaScript and dotnet.
Take a look at the TripSearch object (fromDate / toDate): https://geotab.github.io/sdk/software/api/reference/#T:Geotab.Checkmate.ObjectModel.Trip
This article is also very good in order to know how to pull data and generate reports using Python https://www.geotab.com/blog/mygeotab-python-api/
I hope this could shed some light on it.
Bestm
Ron
Edited by Joseph-1062
Hi @Seth-1151 !
Though Trip data can be used to retrieve distance travelled information, it can lead to a lot of overhead for you when it comes to taking in all the data and processing it.
To keep sanity, I think the easiest way to retrieve distance driven between two dates would be to pull odometer values for the start and end dates. You can do so by performing a Get<StatusData> call and searching for the diagnostic id of DiagnosticOdometerAdjustmentId. An example in Python of this call would be like so:
GetOdometerValues = api.get("StatusData", search = {"deviceSearch": {"id":"b1"},"diagnosticSearch":{"id":"DiagnosticOdometerAdjustmentId"},"fromDate":"2020-03-30T00:00:00.000Z","toDate":"2020-03-31T00:00:00.000Z"})The code above will return the odometer values (both engine-based & GPS-based for those vehicles that do not report odometer) for device id "b1" for a 24 hour period. If you would like to have it for all vehicles, just remove the deviceSearch from the main search parameter.
Take the data value from the first and last objects in the array that is returned to find the distance (in meters) that the vehicle has driven. Keep in mind that when having any date range between the fromDate and toDate, the call's response will always have at least two records returned (in the case that the vehicle hasn't moved) and up to potentially thousands of records, depending on how big the range is. If you do require having all this data, I would then recommend using our data feed (aka GetFeed) instead of the simpler Get. You can read more about the data feed at this link and more specifically regarding the syntax of the method GetFeed that is used at this link.
If you do not require having all this data and just care about the odometer values for the ends of the date range, I suggest making two Get<StatusData> calls per device where the fromDate & toDate are the same timestamps. This will only return one record with the odometer value at the timestamp specified. As an example:
# fromDate & toDate both set to '2020-03-30T00:00:00.000Z'
GetOdometerValueAtStart = api.get("StatusData", search = {"deviceSearch": {"id":"b1"},"diagnosticSearch":{"id":"DiagnosticOdometerAdjustmentId"},"fromDate":"2020-03-30T00:00:00.000Z","toDate":"2020-03-30T00:00:00.000Z"})
# fromDate & toDate both set to '2020-03-31T00:00:00.000Z'
GetOdometerValueAtEnd = api.get("StatusData", search = {"deviceSearch": {"id":"b1"},"diagnosticSearch":{"id":"DiagnosticOdometerAdjustmentId"},"fromDate":"2020-03-31T00:00:00.000Z","toDate":"2020-03-31T00:00:00.000Z"})
DistanceDriven = GetOdometerValueAtEnd[0]['data'] - GetOdometerValueAtStart[0]['data]If you were to do this for numerous devices, you would have many repetitive calls that could contribute to network overhead. In this case, I would recommend housing them within a multicall. Multicalls allow you to group and send over more than one API call at at time which helps to reduce the amount of overhead in situations where many small requests need to be made to a server. You can read more about multicalls at this link.
An example of how this would look as a multicall:
GetOdometerValues = api.multi_call(calls=[("Get", dict(typeName="StatusData", search = {"deviceSearch": {"id":"b1"},"diagnosticSearch":{"id":"DiagnosticOdometerAdjustmentId"},"fromDate":"2020-03-30T00:00:00.000Z","toDate":"2020-03-30T00:00:00.000Z"})), ("Get", dict(typeName="StatusData", search = {"deviceSearch": {"id":"b1"},"diagnosticSearch":{"id":"DiagnosticOdometerAdjustmentId"},"fromDate":"2020-03-31T00:00:00.000Z","toDate":"2020-03-31T00:00:00.000Z"}))])
OdometerValueAtStart = GetOdometerValues[0][0]['data']
OdometerValueAtEnd = GetOdometerValues[1][0]['data']
DistanceDriven = OdometerValueAtEnd - OdometerValueAtStartIn regards to your question about performing this type of data extraction from multiple databases, you would have to declare separate instances per database, i.e.:
import mygeotab
db1 = mygeotab.API(username = geotab_username, password = geotab_password, database = geotab_database_name_1)
db2 = mygeotab.API(username = geotab_username, password = geotab_password, database = geotab_database_name_2)
try:
#authenticate to create a session id that is stored within the 'db1' & 'db2' objects
db1.authenticate()
db2.authenticate()
except Exception as e:
print("Error in authentication. Message:
%s", e)I believe you were receiving the 'client' not defined error as the API instance was not yet declared when trying to call it. I would suggest moving the code on line 5 to after the get_geotab_dataframe function is called.
I hope this helps with getting you started!
Best,
Joseph
Hi @Ron-1094 and @Joseph-1062
Thanks for following up! @Joseph-1062, really appreciate all the input (I'm a Newbie with Python - Coding in general), but I'll definietly be spending some time over the next few days to get this done!
Have a great evening guys and stay safe!