| Table of Contents |
|---|
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
137 datasets updated after becoming overdue. Of these:
HOTOSM: 36 (probably failed exports so a valid alert of being overdue)
IDMC: 47 (wrong update frequency, now changed to annual)
InterAction: 36 (wrong update frequency, now changed to live and hence no more overdue emails will be sent)
18 others:
0a2bae2a-9c09-4aa8-bdf5-f66e71512cb1 CBPF Allocations and contributions
283503e7-13de-4528-9e57-a804196eb57a Afghan Voluntary Repatriation 2017
29d1cc01-cc72-4249-ab15-a01b6d10bfe9 CERF Allocations
2f429840-4def-422f-8586-fb321ccbd832 Topline Colombia Figures
342140f2-0d95-4130-a916-9b75868b0b49 Somalia NGO Consortium Topline Figures
56f3ba93-1765-4a59-a8e0-45088a69b728 International migrant stock
5c963b06-2af1-4fd8-8fef-5f702685a191 Percentage of children less than 5 stunted (Female)
6804f230-c2d4-4482-97a1-568dce069051 Total number of people targeted and reached per region and per cluster
830b908d-0ef3-48b1-bb98-4d8495ded760 Zambia-healthsites
a2150ad9-2b87-49f5-a6b2-c85dff366b75 Rohingya Displacement Topline Figures
ab91c8ec-d6e4-4931-8e45-b42e41bf2ec8 Mount Agung Update - Bali, Indonesia
b6fed87c-9e2b-49e4-8bea-cbfea54238fb Lesotho-healthsites
c12b27ea-5be4-4064-b87c-624cbbb7b1e1 Yemen: Cholera Outbreak Epidemiology Update Data
d1cd6842-dfe1-44c7-a1f5-3769000073af Location of Rohingya Refugees in Cox's Bazar
dce2c3e6-1cbb-4361-8875-b43995fbdf44 Somalia - Water Price Monitoring , August 2017
edb07f53-1023-4bf3-aeb4-d3789fc00642 Total number of children with fever who received treatment with antimalarial
f35ffbfe-428e-4b1f-a41e-1cd31b77e8cc Caribbean Hurricanes: Regional Who is Doing What Where (3W)
fdad1a97-5ffe-42ef-8d60-24e9398f89a8 Total number of reported cases of cholera
60 days of runs before overdue emails sent
94 datasets updated after becoming overdue
HOTOSM: 12
FTS: 51
xxx Road Network: 10
| 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 | 35 | |
| 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 | 114 |
The SQL queries for the Rohingya Crisis MMR and BGD (in the order Baseline, Number of Updates, List of Updates, Number of Creates, List of 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 ~* '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 ~* '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, min(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-11-25' group by e.name;
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 ~* 'mmr|bgd' and a.update_frequency != -1 and date(c.run_date) = '2017-08-24' and a.last_modified > '2016-02-24')
select distinct e.name, d.last_modified from dbdatasets d, baseline e where d.id = e.id and d.last_modified > '2017-08-24' and d.last_modified < '2017-11-25' order by e.name, d.last_modified;
select e.name, min(d.last_modified) as last_modified from dbdatasets d, dbinfodatasets e where e.location ~* '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 ~* '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-11-25' group by e.name;
select distinct e.name, d.last_modified from dbdatasets d, dbinfodatasets e where e.location ~* '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 ~* '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-11-25' order by e.name, d.last_modified;
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 ~* '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 ~* '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, min(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-12-03' group by e.name;
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 ~* '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 distinct e.name, d.last_modified from dbdatasets d, baseline e where d.id = e.id and d.last_modified > '2017-09-02' and d.last_modified < '2017-12-03' order by e.name, d.last_modified;
select e.name, min(d.last_modified) as last_modified from dbdatasets d, dbinfodatasets e where e.location ~* '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 ~* '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-12-03' group by e.name;
select distinct e.name, d.last_modified from dbdatasets d, dbinfodatasets e where e.location ~* '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 ~* '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-12-03' order by e.name, d.last_modified;