Last modified: 2014-01-03 15:55:10 UTC
This issue was converted from https://jira.toolserver.org/browse/DBQ-103. Summary: Wikimedia Commons / NASA pictures Issue type: Task - A task that needs to be done. Priority: Minor Status: Done Assignee: Dereckson <dereckson@espace-win.org> ------------------------------------------------------------------------------- From: Dereckson <dereckson@espace-win.org> Date: Wed, 22 Sep 2010 21:14:21 ------------------------------------------------------------------------------- How many NASA pictures have we on Wikimedia Commons? How many of them are valued images? How many of them are featured pictures?
------------------------------------------------------------------------------- From: Dereckson <dereckson@espace-win.org> Date: Wed, 22 Sep 2010 21:16:21 ------------------------------------------------------------------------------- Results: 27180 pictures and amongst them 183 featured pictured and 34 valued images. 7 of those pictures have the two labels.
------------------------------------------------------------------------------- From: Dereckson <dereckson@espace-win.org> Date: Wed, 22 Sep 2010 21:17:08 ------------------------------------------------------------------------------- SQL queries to compute the statistics: #Creates a temporary table to store the result CREATE TABLE images_NASA ( `page_id` bigint(20) DEFAULT NULL, `page_namespace` tinyint(4) DEFAULT NULL, `vic` tinyint(4) DEFAULT NULL, `fp` tinyint(4) DEFAULT NULL ); #Inserts files containing the `PD-USGov-NASA` template into our table INSERT INTO images_NASA (page_id, page_namespace) SELECT tl_from, page_namespace FROM commonswiki_p.templatelinks t, commonswiki_p.page p WHERE tl_namespace = 10 AND tl_title = 'PD-USGov-NASA' AND p.page_id = t.tl_from; #Deletes non files entries DELETE FROM images_NASA WHERE page_namespace != 6; #Finds features picture (they've the template `Assessments`) UPDATE images_NASA SET fp = 1 WHERE page_id IN (SELECT tl_from FROM commonswiki_p.templatelinks WHERE tl_namespace = 10 AND tl_title = 'Assessments'); #Finds VIC pictures (they've the template `VI}, {{Vi` or `Valued image`}) UPDATE images_NASA SET vic = 1 WHERE page_id IN (SELECT tl_from FROM commonswiki_p.templatelinks WHERE tl_namespace = 10 AND (tl_title = 'Vi' || tl_title = 'VI' || tl_title = 'Valued image')); #Gets the total number of images SELECT count![][1] FROM images_NASA; SELECT count![][1] FROM images_NASA WHERE fp = 1; SELECT count![][1] FROM images_NASA WHERE vic = 1; [1]: https://jira.toolserver.org/images/icons/emoticons/star_yellow.gif
------------------------------------------------------------------------------- From: Dereckson <dereckson@espace-win.org> Date: Wed, 22 Sep 2010 21:17:41 ------------------------------------------------------------------------------- Query done.
This bug was imported as RESOLVED. The original assignee has therefore not been set, and the original reporters/responders have not been added as CC, to prevent bugspam. If you re-open this bug, please consider adding these people to the CC list: Original assignee: dereckson@espace-win.org CC list: dereckson@espace-win.org
