Analysis of OpenStreetMap US board candidate edits

01_results.txt

      Name        │ First edit │ Changes │ Changesets │ US changes │ US changesets │   Changes    │  Changesets  │ Weeks where US edit  │ "Average" state[1]
                  │            │         │            │            │               │ in last year │ in last year │  made in last year   │                                    
 Martijn van Exel │ 2007-06-10 │  216635 │       3485 │     166989 │          2545 │        58763 │         1179 │                   52 │ Illinois
   Andrew Wiseman │ 2011-02-12 │   82543 │       1222 │       5200 │           198 │        34643 │          582 │                   19 │ Cape Verde[2]
    Richard Welty │ 2009-04-04 │  382843 │       8831 │     382589 │          8720 │        76168 │          838 │                   45 │ New York
       Dale Kunce │ 2013-05-30 │   63770 │        201 │      18432 │            92 │        53055 │          147 │                   13 │ Cape Verde
       Alex Barth │ 2012-02-10 │   82565 │       1192 │      33266 │           633 │        31436 │          493 │                   32 │ Middle of Atlantic[3]
Coleman McCormick │ 2009-02-28 │  408229 │       5952 │     356269 │          5698 │       106213 │          959 │                   48 │ East of Bahamas[4]
         Ian Dees │ 2007-12-30 │ 6066596 │       2123 │    4360026 │          1888 │        25172 │          277 │                   41 │ Wisconsin
    Alyssa Wright │ 2013-03-23 │   27897 │        159 │          0 │             0 │            0 │            0 │                    0 │ Rajasthan, India
     Gerald Hasty │ 2011-10-25 │     894 │         28 │        894 │            26 │           59 │            5 │                    1 │ Utah
   Robin Tolochko │ 2010-04-04 │     466 │         12 │         26 │             3 │            0 │            0 │                    0 │ Córdoba, Colombia
     Eleanor Tutt │ 2013-10-18 │    6247 │         83 │       6247 │            83 │         6247 │           83 │                    9 │ Missouri
    Bryce Nesbitt │ 2010-10-05 │   30580 │       1081 │      26428 │           921 │         7615 │          246 │                   45 │ Nevada

Entries with a 0 had zero edits in that category (e.g. zero edits in the US or zero edits last year)

[1]: In the same sense that Kansas is the "average" state of the US.

query.sql

SELECT * FROM 
  (SELECT user_id, min(created_at)::date "First edit", sum(num_changes) as "Changes", count(*) AS "Changesets" from canidate_changesets group by user_id) AS total_world
  LEFT JOIN (SELECT user_id, sum(num_changes) as "US changes", count(*) AS "US changesets" from canidate_us_changesets group by user_id order by user_id) AS total_us USING (user_id)
  LEFT JOIN (SELECT user_id, sum(num_changes) as "Changes 
in last year", count(*) AS "Changesets
in last year" from canidate_changesets WHERE created_at > '20130929'::date group by user_id) AS total_year USING (user_id)
  LEFT JOIN (SELECT user_id, COUNT(*) AS "Weeks where US edit 
made in last year" FROM (SELECT DISTINCT user_id, date_trunc('week',created_at) FROM canidate_us_changesets WHERE created_at > '20130929'::date) AS p GROUP BY user_id) AS weeks_edited USING (user_id)
  LEFT JOIN (SELECT user_id, round(avg((min_lat+max_lat)/2.0),1) AS "Average lat", round(avg((min_lon+max_lon)/2.0),1) AS "Average lon" FROM canidate_changesets WHERE (max_lat-min_lat)*(max_lon-min_lon)<10.0 group by user_id) AS location USING (user_id)
order by user_id;

views.sql

CREATE VIEW canidate_us_changesets AS SELECT * FROM canidate_changesets WHERE (max_lat-min_lat)*(max_lon-min_lon)<10.0
AND ((min_lat < 49
AND max_lat > 24
AND min_lon <-67
AND max_lon >-125)
OR
(min_lat < 30
AND max_lat > 18
AND min_lon <-153
AND max_lon >-180)
OR
(min_lat < 75
AND max_lat > 50
AND min_lon <-130
AND max_lon >-180));

CREATE VIEW canidate_changesets AS SELECT id, user_id, created_at, min_lat, max_lat, min_lon, max_lon, num_changes, tags
  FROM osm_changeset
  WHERE user_id in :uids;