Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Baseline Crisis Data and new/updated Data on Crisis Onset


CrisisDay0
Country
CountriesISO3BaselineUpdatesCreates
Rohingya Myanmar, BangladeshMMR
54711Rohingya Bangladesh
, BGD
76
98
11
18
18
35
Irma/Maria Anguilla
AIA8111Irma/Maria Antigua and BarbudaATG26714Irma/Maria BahamasBHS4377Irma/Maria BarbadosBRB3556Irma/Maria 
, Antigua and Barbuda, Barbados, Bahamas, Bonaire, Saint Eustatius and Saba (The Netherlands)
BES000Irma/Maria British Virgin IslandsVGB1159Irma/Maria CubaCUB5577Irma/Maria DominicaDMA17321Irma/Maria Dominican RepublicDOM47814Irma/Maria Guadeloupe (France)GLPIrma/Maria HaitiHTIIrma/Maria Martinique (France)MTQIrma/Maria MontserratMSRIrma/Maria 
, British Virgin Islands, Cuba, Dominica, Dominican Republic, Guadeloupe (France), Haiti, Martinique (France), Montserrat, Puerto Rico (The United States of America)
PRIIrma/Maria 
, Saint Barthélemy (France)
BLMIrma/Maria 
, Saint Kitts and Nevis
KNAIrma/Maria Saint LuciaLCAIrma/Maria 
, Saint Lucia, Saint Martin (France)
MAFIrma/Maria 
, Saint Vincent and the Grenadines
VCTIrma/Maria 
, Sint Maarten (The Netherlands)
SXMIrma/Maria 
, Turks and Caicos Islands
TCAIrma/Maria 
, United States Virgin IslandsAIA, ATG, BHS, BRB, BES, VGB, CUB, DMA, DOM, GLP, HTI, MTQ, MSR, PRI, BLM, KNA, LCA, MAF, VCT, SXM, TCA, VIR19671114

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 like~* '%mmr%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 like~* '%mmr%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, maxmin(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-1011-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, maxmin(d.last_modified) as last_modified from dbdatasets d, dbinfodatasets e where e.location like~* '%mmr%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 like~* '%mmr%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-1011-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 like '%aia%~* '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 like '%aia%~* '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, maxmin(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-1112-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, maxmin(d.last_modified) as last_modified from dbdatasets d, dbinfodatasets e where e.location like '%aia%~* '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 like '%aia%~* '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-1112-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;