Overlapping changesets analysis - nrenner/achavi GitHub Wiki

Some first analysis on issue #10.

See ChangesetMD.

Examples

select a.id, a.user_name, a.created_at, a.closed_at, a.closed_at - a.created_at durationA, trunc(ST_Area(a.bbox::geography)) sqmA, split_part(a.tags->'created_by', ' ', 1) editorA,
       b.id, b.user_name, b.created_at, b.closed_at, b.closed_at - b.created_at durationB, trunc(ST_Area(b.bbox::geography)) sqmB, split_part(b.tags->'created_by', ' ', 1) editorB,
       a.bbox && b.bbox intersects
  from osm_changeset as a,
       osm_changeset as b
 where a.created_at between date '2014-12-02' and date '2014-12-03'
   and b.created_at between a.created_at and a.closed_at
   and a.id != b.id
   and a.bbox && b.bbox
  • 27835112 (all same user)
          2015-01-01 06:21:56 - 2015-01-01 09:37:28 = 03:15:32
uploads: 2015-01-01T06:21:58 - 2015-01-01T08:37:23 (timeout, no overlap)
27836138 2015-01-01 08:50:20 - 2015-01-01 12:00:48 = 03:10:28

 27169290 2014-12-02 00:01:56 - 2014-12-02 01:02:13 Potlatch
          2014-12-02 00:02:13 last change (timeout, no overlap)
 27169302 2014-12-02 00:02:35 - 2014-12-02 00:02:35 iD

Not showing both created notes because they were moved in 27169302.

  • 27173853 (all same user)
 27173853 2014-12-02 08:56:43 - 2014-12-02 09:56:43 Potlatch
          2014-12-02 08:56:43  last change (timeout)
 27173861 2014-12-02 08:57:14 - 2014-12-02 08:57:15 iD (tag added)
 27173891 2014-12-02 08:59:29 - 2014-12-02 08:59:30 iD (tags added)

Statistics

for random day 2014-12-02

-- num changesets
select count(*)
  from osm_changeset
 where created_at between date '2014-12-02' and date '2014-12-03'

= 21973


-- num changesets >= 1h
select count(*)
  from osm_changeset
 where created_at between date '2014-12-02' and date '2014-12-03'
   and (closed_at - created_at) >= '01:00:00'::interval

= 2322


-- editor stats >= 1h
select editor, count(*) from (
    select id, user_name, created_at, closed_at, closed_at - created_at duration, split_part(tags->'created_by', ' ', 1) editor
      from osm_changeset
     where created_at between date '2014-12-02' and date '2014-12-03'
       and (closed_at - created_at) >= '01:00:00'::interval
 ) as x
 group by editor
 order by count(*) desc

=
"Potlatch";1559
"iD";264
"JOSM/1.5";156
"Merkaartor";119