I have been exploring the use of the Geotab Ace AI assistant and the output from the the AI analysing our data has been positive. I was able to generate some well structured outputs, and was wondering if the SQL that is generated as part of the output could be used to create a report or a dashboard item?
You need to be logged in to reply to this post and participate in the Geotab Community discussions.
Hi There, I'm the PM for Ace and really glad to hear you're liking the output. You're reading our mind!
Next steps is to allow you to save the queries and hopefully get the queries integrated into other parts of the software like reports and dashboard. Please stay tuned while we get these shipped.
Hi There, I'm the PM for Ace and really glad to hear you're liking the output. You're reading our mind!
Next steps is to allow you to save the queries and hopefully get the queries integrated into other parts of the software like reports and dashboard. Please stay tuned while we get these shipped.
Hi Sajad, (I assume... watched the "How to become an Ace Super User" video and well added 1+1 with the comment that you're the PM 😎 )
Thanks for checking in. Looking forward to the evolution of AI integration with the fleet data as a source for LLM based queries.
Do you perhaps know if there is a way (almost like the developer API runner interface) where an advanced user/developer/partner could run/execute the queries built by the Ace to validate some of the SQL results?
I know I'm going technical here, but to showcase a discrepancy, I threw this to ACE:
The aim is to generate a Carbon Footprint Report per defined city zone for each vehicle, when the vehicle was in that zone.
The expected output is a list of entries per vehicle, grouped per day that shows the total time the vehicle was in the zone (for the day) and the estimated CO2 kg calculation (using a mathematical factor value for kg/l).
My initial prompt:
"Using the Zones from the 'customers' zone group and each trip from 1 January 2025 until 31 January 2025, build an output grouped per zone then day and then by vehicle. For each row add the date in CET formatted yyyy-MM-dd, add the zone name vehicle name, make, model, fuel type, zone name, add the amount of times a vehicle entered a zone, the total amount of time spent in the zone for that day in the format hours:minutes"
Following prompt (broken up the query to first build a dataset then expand on it):
"Now add to each row the amount of fuel used in the zone in litres during that day, and CO2 emissions in kg. Take into consideration that one litre of diesel creates 2.54kg of CO2 and one litre of petrol will produce 2.31kg of CO2 on average. Finally order the results by zone name, then vehicle name, and finally trip start time in descending order"
The query returned looked like this:
WITH t_trips_in_zone AS
(SELECT t_t.TripId
, t_t.DeviceId
, t_t.TripStartTime
, t_t.TripEndTime
, t_t.StopPointX
, t_t.StopPointY
, t_t.IdlingDuration
, t_z.ZoneId
, t_z.ZoneName
FROM `Trip` AS t_t
JOIN `Zones` AS t_z ON ST_CONTAINS(t_z.Points, ST_GEOGPOINT(t_t.StopPointX, t_t.StopPointY))
WHERE DATE(t_t.TripStartTime) BETWEEN DATE_SUB(DATE_TRUNC(CURRENT_DATE(), MONTH), INTERVAL 1 MONTH) AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) )
, t_daily_fuel AS
(SELECT t_daily.DeviceId
, t_daily.UTC_Date
, t_daily.TotalFuel_Litres
, t_meta.FuelType
FROM `VehicleKPI_Daily` AS t_daily
JOIN `LatestVehicleMetadata` AS t_meta ON t_daily.DeviceId = t_meta.DeviceId
WHERE t_daily.UTC_Date BETWEEN DATE_SUB(DATE_TRUNC(CURRENT_DATE(), MONTH), INTERVAL 1 MONTH) AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) )
SELECT t_meta.DeviceName
, t_fuel.FuelType
, DATE(t_trips.TripStartTime) AS Date
, COUNT(t_trips.TripId) AS NumberOfEntries
, FORMAT_TIMESTAMP('%H:%M', TIMESTAMP_SECONDS(CAST(SUM(t_trips.IdlingDuration) AS INT64))) AS TotalDurationHHMM
, ROUND(SUM(t_trips.IdlingDuration) / 86400 * SUM(t_fuel.TotalFuel_Litres), 2) AS FuelUsedInZoneLitres
, ROUND(SUM(t_trips.IdlingDuration) / 86400 * SUM(t_fuel.TotalFuel_Litres) * CASE
WHEN LOWER(t_fuel.FuelType) = LOWER('Diesel') THEN 2.54
WHEN LOWER(t_fuel.FuelType) = LOWER('Gasoline') THEN 2.31
ELSE NULL
END, 2) AS CO2UsedInZoneKg
, t_trips.ZoneName
, t_meta.Manufacturer AS Make
, t_meta.Model
FROM t_trips_in_zone AS t_trips
JOIN `LatestVehicleMetadata` AS t_meta ON t_trips.DeviceId = t_meta.DeviceId
JOIN t_daily_fuel AS t_fuel ON t_trips.DeviceId = t_fuel.DeviceId
AND DATE(t_trips.TripStartTime) = t_fuel.UTC_Date
GROUP BY 1
, 2
, 3
, 8
, 9
, 10
ORDER BY 8
, 1
, 3 DESCI found that the calculated fuel values were quite high and my assumption was that there was something up with the grouping and/or summation of the fuel used, so I prompted ACE:
"modify the query to return each zone entry as a separate row and add the date and time for the zone entry and exit, while still returning the additional fields and calculations"
The output was:
WITH t_trips_in_zone AS
(SELECT t_t.TripId
, t_t.DeviceId
, t_t.TripStartTime
, t_t.TripEndTime
, t_t.StopPointX
, t_t.StopPointY
, t_t.IdlingDuration
, t_z.ZoneId
, t_z.ZoneName
FROM `Trip` AS t_t
JOIN `Zones` AS t_z ON ST_CONTAINS(t_z.Points, ST_GEOGPOINT(t_t.StopPointX, t_t.StopPointY))
WHERE DATE(t_t.TripStartTime) BETWEEN DATE_SUB(DATE_TRUNC(CURRENT_DATE(), MONTH), INTERVAL 1 MONTH) AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) )
, t_daily_fuel AS
(SELECT t_daily.DeviceId
, t_daily.UTC_Date
, t_daily.TotalFuel_Litres
, t_meta.FuelType
FROM `VehicleKPI_Daily` AS t_daily
JOIN `LatestVehicleMetadata` AS t_meta ON t_daily.DeviceId = t_meta.DeviceId
WHERE t_daily.UTC_Date BETWEEN DATE_SUB(DATE_TRUNC(CURRENT_DATE(), MONTH), INTERVAL 1 MONTH) AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) )
SELECT t_meta.DeviceName
, t_fuel.FuelType
, t_trips.TripStartTime
, t_trips.TripEndTime
, FORMAT_TIMESTAMP('%H:%M', TIMESTAMP_SECONDS(CAST(t_trips.IdlingDuration AS INT64))) AS IdlingDurationHHMM
, ROUND(t_trips.IdlingDuration / 86400 * t_fuel.TotalFuel_Litres, 2) AS FuelUsedInZoneLitres
, ROUND(t_trips.IdlingDuration / 86400 * t_fuel.TotalFuel_Litres * CASE
WHEN LOWER(t_fuel.FuelType) = LOWER('Diesel') THEN 2.54
WHEN LOWER(t_fuel.FuelType) = LOWER('Gasoline') THEN 2.31
ELSE NULL
END, 2) AS CO2UsedInZoneKg
, t_trips.ZoneName
, t_meta.Manufacturer AS Make
, t_meta.Model
FROM t_trips_in_zone AS t_trips
JOIN `LatestVehicleMetadata` AS t_meta ON t_trips.DeviceId = t_meta.DeviceId
JOIN t_daily_fuel AS t_fuel ON t_trips.DeviceId = t_fuel.DeviceId
AND DATE(t_trips.TripStartTime) = t_fuel.UTC_Date
ORDER BY 8
, 1
, 3 DESCThis result was more convincing - When I totalled up the values in the csv output per day, the fuel usage looked to be more correct.
I know it's early days, and that is why I like to validate the outputs to make sure the client gets the best data from the system.
Any insights would be appreciated.
Regards,
Pieter
oh darn... I just noticed that because of the text input limit, you cannot give the supplied query back to ACE to run
Thanks yes Sajad here :)
Query saving is on our roadmap but let me see what I can do in the short term to expand the character limit there for your to input queries to run
This is my email feel free to email me directly:
sajadshiravi@geotab.com