I have a system that tracks several thousand objects in realtime and stores their locations in a database table. Rather than store their location every few seconds I simply store their location, direction, speed and time whenever it changes. This keeps the table relatively small for the number of objects that I'm tracking.
Each row contains data on a single object at a moment in time and whilst these should go into the database in chronological I want to ensure that I pull the data out in chronological order before concat'ing them together so that I can plot out the data.
My table structure is:
CREATE TABLE tracks (
id int(11) auto_increment,
objectId int(11) default 0,
posTime bigint(15) default 0,
X float(6,4) default 0.0000,
Y float(6,4) default 0.0000
Z int(11) default 0,
hdg float(4,1) default 0.0,
spd float(5,1) default 0,
PRIMARY KEY(id));
By using this query:
SELECT objectId,
group_concat(X, ',', Y, ',', hdg, ',', spd separator ',') as trail
FROM tracks
WHERE objectId > 0
group by objectId;
I get the data that I want in the format that I want e.g.:
14979491 | -26.3286,-48.4679,206.0,362.0,-26.3684,-48.4892,205.0,357.0
i.e. ObjectId | X1, Y1, heading1, speed1, X2, Y2, heading2, speed2, .... etc
However I'm not 100% confident that the position reports are in the correct time order.
How can I sort the position reports for each id by increasing posTime before concat'ing them together ? In theory it's just a case of adding ORDER BY posTime ASC but I'm not sure how or where to add this to my query as all previous attempts have failed ...
EDITED TO CLARIFY: I know that group_concat supports an ORDER BY clause but I don't want the column that I sort by (posTime) to be returned in the results.
FLOAT(m,n)
. Consider plainFLOAT
(for scientific values) orDECIMAL(m,n)
(for money). – Rick James Apr 29 '17 at 6:06