Keep track of the last read record in MySQL

Question

I have 2 databases in MySQL:

1) An input Latitude-Longitude_dB ('latlong_db', henceforth): It has the latitude and longitude of each reading from a GPS tracking device.

2) A Weather_db: I read the input latlongs from dB1, and calculate 'current' weather data for each pair of latlongs (eg: humidity, cloud_coverage) . This weather data is written into a Weather_db.

The issue is: I need to keep track of which record (which 'input latlong') was read last. This is so that I don't recalculate weather_data for the latlongs that I've already covered. How do I keep track of the last read input_latlong?

Thank you so much.

Answer 1

6 decimal places for lat/lng give a precision of 16cm (1/2 ft). I would expect the GPS to fluctuate more than that. In other words, should you really watch the lat/lng at that resolution, or maybe round to 3 decimal places? That would give you 160m (520 ft) resolution, more like what the weather tracking is capable of.

Back to your question.

It is somewhat messy to fetch the "latest", especially if you want the "latest for each vehicle. So, instead, I suggest keeping another table of the "Current" location. (What you have is the "History" of the location.)

Do you need a history of the weather? Or is the latest all you need?

Caution: If you have thousands of vehicles and you are collecting lots of data every 10 minutes, you will rapidly fill up the disk. So try to minimize the size of the datatypes in use. Also, consider tossing data when a vehicle is 'idle'; this will cut in half the disk requirements?

More on lat/lng representation sizes versus representations: http://mysql.rjweb.org/doc.php/latlng#representation_choices