[OSM-dev] Autocorrectable data inconsistency

[Joachim Zobel]

Hi.

I took a closer look at the data and found that several ways (10000+) in
europe) have identical nodes on subsequent sequence_ids.

   SELECT wn1.id, wn1.node_id
     FROM current_way_nodes wn1
     JOIN current_way_nodes wn2
       ON wn2.id = wn1.id
      AND wn2.sequence_id = wn1.sequence_id + 1 
       -- We restrict to ways with multiple identical nodes 
       -- for performance reasons. This is not necessary,
       -- since it is already implied by the self join above.
     JOIN (
   SELECT id, 
          COUNT(DISTINCT node_id) AS cnt_nd,
          COUNT(1) AS cnt
     FROM current_way_nodes wn
 GROUP BY id
   HAVING cnt > cnt_nd) AS multi
       ON wn1.id = multi.id
    WHERE wn2.node_id = wn1.node_id

The nice thing is that these are obvious nonsense and can be
autocorrected.

Checking the created_by tells, that mostly Potlach is to blame. See the
statistics (europe again) below.

Sincerely,
Joachim

+------------------------------+------+
| v                            | cnt  |
+------------------------------+------+
| almien_coastlines            |    1 | 
| fixbot                       |    1 | 
| Raymond                      |    1 | 
| connectmulti.py              |    1 | 
| chris_w                      |    1 | 
| Martin Stoyanov              |    2 | 
| Merkaartor 0.12              |    2 | 
| Dodi1980                     |    2 | 
| shpupload                    |    3 | 
| vmap0_to_osm.pl version 0.02 |    5 | 
| mumpot                       |    5 | 
| osmajax                      |    7 | 
| RambaZamba                   |    8 | 
| mhoellein                    |    8 | 
| Potlatch 0.5b                |   15 | 
| Potlatch 0.5                 |   23 | 
| Potlatch 0.5c                |   34 | 
| Potlatch 0.5a                |   40 | 
| landyfahrer                  |   44 | 
| Potlatch 0.4b                |   45 | 
| Potlatch 0.6b                |   53 | 
| Potlatch 0.7a                |   59 | 
| Potlatch 0.6                 |   77 | 
| Potlatch 0.9                 |   87 | 
| Potlatch 0.4c                |   91 | 
| Potlatch 0.7                 |  113 | 
| Potlatch 0.8                 |  118 | 
| Potlatch 0.9b                |  134 | 
| GPSBabel-1.3.5               |  137 | 
| Potlatch 0.6c                |  150 | 
| Potlatch 0.8b                |  182 | 
| Merkaartor 0.10              |  185 | 
| Potlatch 0.6a                |  188 | 
| Potlatch 0.8c                |  201 | 
| Potlatch alpha               |  207 | 
| Potlatch 0.5d                |  273 | 
| Potlatch 0.10a               |  363 | 
| Merkaartor 0.11              |  527 | 
| Potlatch 0.9a                |  629 | 
| Potlatch 0.8a                |  685 | 
| Potlatch 0.7b                |  792 | 
| JOSM                         |  801 | 
| Potlatch 0.10                | 1194 | 
| Potlatch 0.10b               | 1355 | 
| Potlatch 0.9c                | 3290 | 
+------------------------------+------+