Need to calculate accurate Running From, Ideal From and Stop From vehicle Time.
Check your indexes on tc_positions. You may need to create additional ones to match the queries.
We read tc_positions and tc_events into our own database, which holds a lot more data, as we keep all historical data, and performance on our DB works great, with properly designed queries and indexes.
Ignition on/off and device stopped / moving seem to be fairly complex, and dependent on the trackers as well. We have issues with this being accurate in our existing (commercial source) tracking system as well, not just a Traccar issue.
We deal with it by doing our own custom reporting, using the events but also analyzing vehicle positions to detect when it is 'stopped'. .Stopped' in our case is defined as moving less than a set distance (to cater for GPS scatter among other things), for longer than a given period. Not a trivial exercise, and can be a performance intensive task comparing all positions in a period.
You probably want to pull all records (positions and events) for the period and vehicles being examined back into your analysis routine, and then checking.
In our case, we have standardized on PostgreSQL and PostGIS, so do all that in the DB with using plpgsql functions and PostGIS functions to calc position distances from lat/lon data. Not instantaneous, but takes 2 seconds per vehicle to analyze a few weeks of data, using a very low end older server.
Thank you for your response,
So work around is doing indexing on tc_positions and calculation time based on status of that devices ?
I will try and output here soon.
Hello,
I want to create API from each and every vehicle Running From, Ideal From And stop From time.
For that, first of all i need to identify device status and then use below two options.
Is there any other way i can calculate time directly from mysql table using query OR do you have any suggestion for me?
Thanks you.