1

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.

  • There is possibly no valid reason to use FLOAT(m,n). Consider plain FLOAT (for scientific values) or DECIMAL(m,n) (for money). – Rick James Apr 29 '17 at 6:06
0

You need to get confidence in GROUP_CONCAT with ORDER BY embedded.

Only way to do that is the following

SUGGESTION 01 : Proper Indexing

ALTER TABLE tracks ADD INDEX objectId_posTime_ndx (objectId,posTime);

This will help the query optimizer retrieve your data ordered properly.

SUGGESTION 02 : Display posTime as a Debug Step

SELECT   objectId, 
         group_concat(posTime ORDER BY posTime) as trail 
FROM     tracks 
WHERE    objectId > 0 
GROUP BY objectId;

If the posTime is ordered as desired, then you should be fine with

SELECT   objectId, 
         group_concat(CONCAT(X, ',', Y, ',', hdg, ',', spd) ORDER BY posTime) as trail 
FROM     tracks 
WHERE    objectId > 0 
group by objectId;
  • Perfect thank you. And thanks for the tip about indexing the table, I'd completely overlooked that ! – Philip Lee Apr 25 '17 at 15:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.