Number of Files Locally and Externally Hosted
Number of Resources Percentage of Resources | |||||
| Type | 09/2016 | 01/2017 | 04/2017 | 07/2017 | 10/2017 |
|---|---|---|---|---|---|
| File Store | 2102 22% | 2472 24% | 2771 26% | 3036 17% | 3651 9% |
| CPS | 2459 26% | 2449 24% | 2449 23% | 2449 14% | 2449 6% |
| HXL Proxy | 2584 27% | 2584 25% | 2584 25% | 2584 15% | 2584 6% |
| ScraperWiki | 162 2% | 158 2% | 160 2% | 160 1% | 160 0% |
| Others | 2261 24% | 2544 25% | 2537 24% | 9203 53% | 32578 79% |
| Total | 9568 100% | 10207 100% | 10501 100% | 17432 100% | 41422 100% |
The SQL queries are:
select count(*) from dbresources where run_number = xxx;
select count(*) from dbresources where run_number = xxx and url like '%data.humdata.org%';
select count(*) from dbresources where run_number = xxx and url like '%manage.hdx.rwlabs.org%';
select count(*) from dbresources where run_number = xxx and url like '%proxy.hxlstandard.org%';
select count(*) from dbresources where run_number = xxx and url like '%scraperwiki%';
Number of Dataset Updates before and after introduction of Overdue email
There is a 46% increase in updates happening after the overdue email was introduced, with many related to problems with automated systems eg. in HOTOSM.
The SQL queries are:
60 days of runs before overdue emails sent:
SELECT DISTINCT a.id, c.title FROM dbdatasets a, dbdatasets b, dbinfodatasets c WHERE a.id = b.id AND a.run_number > b.run_number AND b.run_number > 89 AND b.run_number <= 149 AND a.fresh = 0 AND b.fresh = 2 AND a.id = c.id;
60 days of runs after overdue emails sent:
SELECT DISTINCT a.id, c.title FROM dbdatasets a, dbdatasets b, dbinfodatasets c WHERE a.id = b.id AND a.run_number > b.run_number AND b.run_number > 149 AND b.run_number <= 209 AND a.fresh = 0 AND b.fresh = 2 AND a.id = c.id;
60 days of runs after overdue emails sent
| Before | After | Reason | |
|---|---|---|---|
| HOTOSM | 12 | 36 | Likely failed export |
| FTS | 51 | Likely scraper failures during initial creation | |
| WFP | 10 | Wrong update frequency | |
| IDMC | 47 | Wrong update frequency | |
| InterAction | 36 | Wrong update frequency | |
| Other | 21 | 18 | |
| Total | 94 | 137 |
Baseline Crisis Data and new/updated Data on Crisis Onset
| Crisis | Day0 | Countries | ISO3 | Baseline | Updates | Creates |
|---|---|---|---|---|---|---|
| Rohingya | Myanmar, Bangladesh | MMR, BGD | 98 | 18 | 28 | |
| Irma/Maria | Anguilla, Antigua and Barbuda, Barbados, Bahamas, Bonaire, Saint Eustatius and Saba (The Netherlands), British Virgin Islands, Cuba, Dominica, Dominican Republic, Guadeloupe (France), Haiti, Martinique (France), Montserrat, Puerto Rico (The United States of America), Saint Barthélemy (France), Saint Kitts and Nevis, Saint Lucia, Saint Martin (France), Saint Vincent and the Grenadines, Sint Maarten (The Netherlands), Turks and Caicos Islands, United States Virgin Islands | AIA, ATG, BHS, BRB, BES, VGB, CUB, DMA, DOM, GLP, HTI, MTQ, MSR, PRI, BLM, KNA, LCA, MAF, VCT, SXM, TCA, VIR | 196 | 71 | 88 |
The SQL queries for the Rohingya Crisis MMR and BGD (in the order Baseline, Updates, Creates) are:
select b.name, a.last_modified, b.location from dbdatasets a, dbinfodatasets b, dbruns c where a.id = b.id and a.run_number = c.run_number and b.location ~~ ANY('{%mmr%,%bgd%}') and a.update_frequency != -1 and date(c.run_date) = '2017-08-24' and a.last_modified > '2016-02-24' order by a.last_modified;
with baseline as
(select a.id, b.name from dbdatasets a, dbinfodatasets b, dbruns c where a.id = b.id and a.run_number = c.run_number and b.location ~~ ANY('{%mmr%,%bgd%}') and a.update_frequency != -1 and date(c.run_date) = '2017-08-24' and a.last_modified > '2016-02-24')
select e.name, max(d.last_modified) as last_modified from dbdatasets d, baseline e where d.id=e.id and d.last_modified > '2017-08-24' and d.last_modified < '2017-10-25' group by e.name;
select e.name, max(d.last_modified) as last_modified from dbdatasets d, dbinfodatasets e where e.location ~~ ANY('{%mmr%,%bgd%}') and d.update_frequency != -1 and d.id not in (select distinct a.id from dbdatasets a, dbinfodatasets b, dbruns c where a.id = b.id and
a.run_number = c.run_number and b.location ~~ ANY('{%mmr%,%bgd%}') and c.run_date < '2017-08-25') and d.id=e.id and d.last_modified > '2017-08-24' and d.last_modified < '2017-10-25' group by e.name;
The SQL queries for the the Hurricane Irma and Maria Crises combined with affected countries taken from https://reliefweb.int/disaster/tc-2017-000125-dom and https://reliefweb.int/disaster/tc-2017-000136-atg (in the order Baseline, Updates, Creates):
select b.name, a.last_modified, b.location from dbdatasets a, dbinfodatasets b, dbruns c where a.id = b.id and a.run_number = c.run_number and b.location ~~ ANY('{%aia%,%atg%,%bhs%,%brb%,%bes%,%vgb%,%cub%,%dma%,%dom%,%glp%,%hti%,%mtq%,%msr%,%pri%,%blm%,%kna%,%lca%,%maf%,%vct%,%sxm%,%tca%,%vir%}') and a.update_frequency != -1 and date(c.run_date) = '2017-09-02' and a.last_modified > '2016-03-02' order by a.last_modified;
with baseline as
(select a.id, b.name from dbdatasets a, dbinfodatasets b, dbruns c where a.id = b.id and a.run_number = c.run_number and b.location ~~ ANY('{%aia%,%atg%,%bhs%,%brb%,%bes%,%vgb%,%cub%,%dma%,%dom%,%glp%,%hti%,%mtq%,%msr%,%pri%,%blm%,%kna%,%lca%,%maf%,%vct%,%sxm%,%tca%,%vir%}') and a.update_frequency != -1 and date(c.run_date) = '2017-09-02' and a.last_modified > '2016-03-02')
select e.name, max(d.last_modified) as last_modified from dbdatasets d, baseline e where d.id=e.id and d.last_modified > '2017-09-02' and d.last_modified < '2017-11-03' group by e.name;
select e.name, max(d.last_modified) as last_modified from dbdatasets d, dbinfodatasets e where e.location ~~ ANY('{%aia%,%atg%,%bhs%,%brb%,%bes%,%vgb%,%cub%,%dma%,%dom%,%glp%,%hti%,%mtq%,%msr%,%pri%,%blm%,%kna%,%lca%,%maf%,%vct%,%sxm%,%tca%,%vir%}') and d.update_frequency != -1 and d.id not in (select distinct a.id from dbdatasets a, dbinfodatasets b, dbruns c where a.id = b.id and
a.run_number = c.run_number and b.location ~~ ANY('{%aia%,%atg%,%bhs%,%brb%,%bes%,%vgb%,%cub%,%dma%,%dom%,%glp%,%hti%,%mtq%,%msr%,%pri%,%blm%,%kna%,%lca%,%maf%,%vct%,%sxm%,%tca%,%vir%}') and c.run_date < '2017-09-03') and d.id=e.id and d.last_modified > '2017-09-02' and d.last_modified < '2017-11-03' group by e.name;