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.

Out of town mapping

This weekend I went down to the West Palm Beach area to celebrate my sister-in-laws graduation from FAU. My father-in-law noticed that his son's neighborhood was not completely mapped in either Bing or on Google. Bing is missing some streets, and Google is missing some street names. I pulled up OSM and the situation was even worse. No streets where mapped at all. Apparently this place was only built around two years ago.

Like a good OSMer, I began tracing the missing streets using the areal imagery. When I was done with that I downloaded the area into JOSM, loaded the kids into the car, and drove around, marking the names of the streets as we campused the neighborhood. When we got back we uploaded the edit, and now OSM has the only 100% mapping of the area.

I mapped some other missing streets, but there is no chance of me getting the chance to visit the areas to get street names, mostly because of time issues, but also, these are gated communities where I wouldn't be allowed in to anyways.