<html><head><meta http-equiv="Content-Type" content="text/html charset=utf-8"></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;" class="">This is fabulous Tapani, thanks much for your help. When Maryam is back from vac let’s walk her through it. If other e-team folks have any thoughts or assistance to offer please do.<div class=""><br class=""></div><div class="">Best</div><div class=""><br class=""></div><div class="">Bill<br class=""><div class=""><br class=""><div><blockquote type="cite" class=""><div class="">On Aug 6, 2015, at 8:32 AM, Tapani Tarvainen <<a href="mailto:ncuc@TAPANI.TARVAINEN.INFO" class="">ncuc@TAPANI.TARVAINEN.INFO</a>> wrote:</div><br class="Apple-interchange-newline"><div class="">Dear all,<br class=""><br class="">In order to get member counts per region Bill wanted,<br class="">I added a region table to the database like this:<br class=""><br class="">create table regions (code text, country text, physregion text, icannregion text);<br class=""><br class="">I picked the data from here:<br class=""><br class=""><a href="https://meetings.icann.org/en/regions" class="">https://meetings.icann.org/en/regions</a><br class=""><br class="">and added a few aliases (pesky unitedstatesians insist<br class="">on writing their country as "USA" instead of ICANN-approved<br class="">"United States", likewise unitedkingdomites as "UK",<br class="">russianfederationists as mere "Russia" &c)<br class="">as well as an entry for Serbia.<br class="">(Other such additions may be needed later, but those covered<br class="">our current membership.)<br class=""><br class="">I also fixed some inconsistent capitalizations &c.<br class=""><br class="">Now regional distribution can be obtained like this:<br class=""><br class="">ncuc=> select count(*), icannregion from (select name, n.country, icannregion from ncucers n left outer join regions r on (n.country=r.country) where state<3 and role!='AddRep') as x group by icannregion;<br class=""> count | icannregion<br class="">-------+-------------<br class=""> 21 |<br class=""> 44 | LAC<br class=""> 76 | AF<br class=""> 99 | NA<br class=""> 103 | EUR<br class=""> 85 | AP<br class="">(6 rows)<br class=""><br class="">I also put that in PHP here:<br class="">http://www.ncuc.org/members/diversity.php<br class=""><br class="">That is intended to be temporary, should be moved inside the<br class="">regular about/members page, but first it'd be nice to clean<br class="">up the 21 unknowns and think a bit about formatting it prettier.<br class="">Suggestions for the format would be welcome.<br class=""><br class="">Also, especially veteran NCUCers might want to help by taking<br class="">a look at the unknowns, who've mostly been around since forever.<br class="">You can see them by looking at blanks in country column here:<br class="">http://www.ncuc.org/about/members/<br class="">or ask me privately for a list.<br class=""><br class="">Gender is harder, as there's no similar means for determining it<br class="">automatically. I guess the easiest solution is adding a new column to<br class="">ncucers with allowed values "m", "f" and "o" (for other) and have<br class="">someone (...) fill it manually.<br class="">Gender should then also be added to the membership application form.<br class=""><br class="">-- <br class="">Tapani Tarvainen<br class="">_______________________________________________<br class="">E-team mailing list<br class="">E-team@lists.ncuc.org<br class="">http://lists.ncuc.org/cgi-bin/mailman/listinfo/e-team<br class=""></div></blockquote></div><br class=""><div apple-content-edited="true" class="">
<div style="color: rgb(0, 0, 0); letter-spacing: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px; word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;" class="">*********************************************************<br class="">William J. Drake<br class="">International Fellow & Lecturer<br class=""> Media Change & Innovation Division, IPMZ<br class=""> University of Zurich, Switzerland<br class="">Chair, Noncommercial Users Constituency, <br class=""> ICANN, <a href="http://www.ncuc.org" class="">www.ncuc.org</a><br class=""><a href="mailto:william.drake@uzh.ch" class="">william.drake@uzh.ch</a> (direct), <a href="mailto:wjdrake@gmail.com" class="">wjdrake@gmail.com</a> (lists),<br class=""> <a href="http://www.williamdrake.org" class="">www.williamdrake.org</a><br class=""><i class="">Internet Governance: The NETmundial Roadmap </i><a href="http://goo.gl/sRR01q" class="">http://goo.gl/sRR01q</a><br class="">*********************************************************</div>
</div>
<br class=""></div></div></body></html>