I'm designing a project where I'll be storing (potentially hundreds of thousands of) lat/lon pairs in a database. The pairs are associated with other data. The catch is that in addition to users manipulating this data, I also want the locations to change over time. My initial instinct was to set up a cron job that will adjust every lat/lon by a certain amount every day, but I realize that such an operation would be insanely inefficient.
So, any ideas on how to efficiently adjust a bunch of lat/lon pairs over time? My best thought so far is associating a "last changed" timestamp with each pair and have a process running that fires every few seconds, grabs n (maybe order 100? 1000?) pairs with the oldest timestamps, adjusts those pairs and updates the times. This way I'm constantly moving small amounts of data, instead of moving an overwhelming amount once a day. I'm still not convinced this is the best way to go, though.
Thanks in advance!
Store the amount that is added to each pair somewhere else, and rather than using the values in the database directly, add this stored offset amount whenever you retrieve and subtract it whenever you insert.