prcGetAllRoutesForMap doesn't display the latest session location about gpstracker HOT 11 CLOSED

Hey man, thanks for bring this to my attention. Someone else had brought this up earlier in the week and I altered the procedure in my latest commit. Here is what I came up with. It seems to work the same as yours but without the self join. Basically using MAX(gpsTime) within the concat statement. Tell me if you agree that they both work the same. I could use a second set of eyes on this. Thanks, Nick.

SELECT DISTINCT(sessionId), MAX(gpsTime) gpsTime,
CONCAT('{ "latitude":"', CAST(latitude AS CHAR),'", "longitude":"', CAST(longitude AS CHAR), '", "speed":"', CAST(speed AS CHAR), '", "direction":"', CAST(direction AS CHAR), '", "distance":"', CAST(distance AS CHAR), '", "locationMethod":"', locationMethod, '", "gpsTime":"', DATE_FORMAT(MAX(gpsTime), '%b %e %Y %h:%i%p'), '", "userName":"', userName, '", "phoneNumber":"', phoneNumber, '", "sessionID":"', CAST(sessionID AS CHAR), '", "accuracy":"', CAST(accuracy AS CHAR), '", "extraInfo":"', extraInfo, '" }') json
FROM gpslocations
WHERE sessionID != '0' && CHAR_LENGTH(sessionID) != 0 && gpstime != '0000-00-00 00:00:00'
GROUP BY sessionID
ORDER BY gpsTime ASC;

from gpstracker.

I've seen the latest change and I actually tested it before I opened this ticket, but it didn't work for me. The problem is that MAX() will indeed select the maximum value for the gpsTime column, but that's all it does. It doesn't return the actual row from the table, which has this max gspTime. The rest of the values (latitude, speed, distance, etc) they all come from the first database record of the session. That's simply the first row with a new sessionID that was matched by mysql.

So the change only fixed the time displayed on the map, but the actual marker position on the map was still drawn based on the latitude and longitude from the first record in the database (which is the start of the session). You can verify this by simply adding the GPSLocationID to the select. You will then get the ID of the row which is actually returned by mysql and used to display the locations on the map. With the self join query that's indeed the row that has the max gpsTime.

from gpstracker.

Hey man, sorry for the incredibly long delay in responding. I spent the past two weeks replacing 30 bricks in a chimney and putting in a new chimney crown. It was so exhausting that I didn't have the energy to deal with my online life. By the way, what's your name? I'm Nick.

You are very right, my procedure is not working as it needs to be working and yours does seem to be. But I have encountered a problem with your procedure. When I test it against the websmithing.com test database (which currently has about 50,000 rows) it takes 57 seconds to complete. That's a problem.

50,000 rows is about 3 days of data for the test website which is not a lot of data. I run a cron job nightly to delete any data older than 3 days. I need to think on this a little further but if you have any ideas, I would be happy to hear about it. Once again, sorry for the delay.

from gpstracker.

Hi, I'm Hristo. No worries :) See if this one performs better:

SELECT sessionId, gpsTime, CONCAT('{ "latitude":"', CAST(latitude AS CHAR),'", "longitude":"', CAST(longitude AS CHAR), '", "speed":"', CAST(speed AS CHAR), '", "direction":"', CAST(direction AS CHAR), '", "distance":"', CAST(distance AS CHAR), '", "locationMethod":"', locationMethod, '", "gpsTime":"', DATE_FORMAT(gpsTime, '%b %e %Y %h:%i%p'), '", "userName":"', userName, '", "phoneNumber":"', phoneNumber, '", "sessionID":"', CAST(sessionID AS CHAR), '", "accuracy":"', CAST(accuracy AS CHAR), '", "extraInfo":"', extraInfo, '" }') json
FROM (SELECT MAX(GPSLocationID) ID
      FROM gpslocations
      WHERE sessionID != '0' && CHAR_LENGTH(sessionID) != 0 && gpstime != '0000-00-00 00:00:00'
      GROUP BY sessionID) AS MaxID
JOIN gpslocations ON gpslocations.GPSLocationID = MaxID.ID
ORDER BY gpsTime

An index will probably speed this up, but I am no expert to help you with this.
Another option may be to simply create a separate table, which has a reference to the latest location for each session and to update it on every insert. Then simply join the gpslocations table to this helper table to get all the info.
That's exactly what the sub-query above does - it creates a temporary table with the max IDs for each session, which basically matches the one below.
So you can either do this at query time with this sub-select or you can do it upfront with a helper table, which gets updated at insert time and always has the correct list of max IDs.

from gpstracker.

Thank you very much Hristo and pleased to meet you. I had begun working on a separate procedure using a temp table. I do have an index on sessionID. I just ran your new procedure and it's significantly improved. It took 0.25 seconds for 105,000 rows. That is very acceptable and I will go with that. I appreciate your help.

from gpstracker.

Hi Nick!

I'm still facing this issue (I'm running the last version).
On the first map, where it displays all the possible routes, the marker will be placed where the first record was made, not where the last one was (present location).

Any tips on how to fix it?

from gpstracker.

Hi

I think there is a regression error in the latest code. Did you try the stored procedure above from Hristo?

Nick

from gpstracker.

Hi Nick!
Just tried it, worked like a charm! : )
Thanks!

Eric

from gpstracker.

Excellent, I will update gps tracker with that fix in the next couple of days. Good luck with it, Eric.

n

from gpstracker.

Thanks Nick!
I have a doubt unrelated to this issue, where could I inquire about it? Is not an issue per se(therefore not to be opened here), more a logical/procedural doubt..

from gpstracker.

best to ask questions on websmithing.com so others can see it too.

from gpstracker.