Finding duplicate MapDust bugs.

One big issue that the MapQuest app seems to have is that it sometimes will report a single button multiple times at the exact same lat/long. Often one of the duplicates will have the description the user entered, if one was entered at all, and the other bugs will have seemingly random Types, with the default text for that type in the bug report.

In my effort to squash all reported in Florida, sometimes I just go looking for invalid bugs so that when I'm in the mood to do some real fixing its easier to find actual problems. In that spirit I have whipped up a quick way to find duplicate bugs so that the dupes can easily be marked invalid so that the original can later be individually verified.

To find duplicate bugs, first download the latest MySQL database extract from http://www.mapdust.com/dumps/latest_mapdust.sql.tar.gz Unzip the database file in the tarball to wherever is convenient for you.

Install MySQL on your system and then run the following command:

CREATE DATABASE mapdust

Now edit the database extract and add the following to the first line. 
use mapdust;

Now, while in the same directory as the extract file run the following command

mysql -u root -ppassword < latest_mapdust.sql

Replace the word password above with your root password, being careful to not put a space between -p and your password.

Now we are ready to query our new database for duplicates.

use mapdust;
SELECT id,
    latitude,
    longitude,
    bug_count
    FROM
    (
        select latitude, 
            longitude, 
            count(*) bug_count 
            FROM osmexportbug 
            GROUP BY latitude, 
                longitude 
    ) foo
        JOIN osmexportbug 
            USING (latitude, longitude)
    WHERE bug_count > 1
        ORDER BY bug_count, 
            id;

You should now have the bug number and lat/long of the duplicates. The ones that belong together are easy to spot because they are sequentially together. I use the following query to only find duplicates in Florida.

SELECT id,
    latitude,
    longitude,
    bug_count
    FROM     ( 
        select latitude, 
            longitude, 
            count(*) bug_count 
            FROM osmexportbug 
            WHERE administrative_area = 'Florida' 
            GROUP BY latitude, 
                longitude 
    ) foo 
        JOIN osmexportbug 
            USING (latitude, longitude) 
    WHERE bug_count > 1 
    ORDER BY bug_count, 
        id;

After locating a strand of duplicate bugs I go through and mark all but the lowest numbered bug as a duplicate. I use language like the following:

Duplicate of bug 85064.
http://www.mapdust.com/detail/85064

Later when I come across the original bug, I'll only have the one to verify and squash, not a bunch after it to go through and tediously mark invalid.

I hope this helps any other bug squashers out there looking to address the bugs the awesome MapQuest users are out there reporting for us.