I need a small script written in either PHP or Perl that will process records in a MySQL table and delete similar looking records. Specifically, I have a device uploading GPS points (lat/long) into my database. When the device is sitting still, it is putting duplicate records into my database every couple of minutes. There is GPS drift, so all records do not have identical GPS coordinates.
Basically, there are two fields I am interested in filtering by in the table. One has a latitude one has a longitude. 5 of the records might look like this:
ID | LAT | LONG
id1 | 27.7755386666667 | [url removed, login to view]
id2 | 27.7755173333333 | [url removed, login to view]
id3 | 27.775456 | [url removed, login to view]
id4 | 27.775424 | [url removed, login to view]
id5 | 27.7753466666667 | [url removed, login to view]
id6 | 27.711264 | [url removed, login to view]
The first five records in the above example are all basically duplicate points from when the GPS is sitting still. They are all 27.775*, [url removed, login to view]* The script needs to filter those duplicate records where the first 5 digits in the lat/long are the same and leave one of the previously duplicated records remaining. The 6th item is a different location.
The script needs to be able to handle a table that has 20,000+ records.
Should not be a difficult task once you understand what I am trying to do. Hopefully the above example is decent :)
This is a simple task. All you have to do is to delete the records where the euclidian distance relative to a given coordinate is smaller than a value of your choice...