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.

  • This depends on two things that you don't mention in your question: how you identify rows in latlong_db and how you order them when reading. – mustaccio Jun 4 '19 at 18:13
  • @mustaccio: Sorry, I didn't mention all the columns of dB1 as there were too many. But I identify the rows in latlong_db by the send_time and imei. I have now modified the image under dB1 to reflect that. – shaw38 Jun 4 '19 at 18:44
  • So then you remember send_time and imei that you read last, no? – mustaccio Jun 4 '19 at 19:05
  • Fuel consumption fluctuating? Rather cold: -95 degrees? – Rick James Jun 4 '19 at 23:04

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

| improve this answer | |

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.