r5-queries

bivariate-aal.sql

DROP VIEW IF EXISTS bivariate;
CREATE VIEW bivariate AS 
	SELECT gid, gdp, pop, aal_gdp, aal_pop, aal_gdp / gdp AS gdp_pct, aal_pop / pop AS pop_pct, geom
	FROM admin1
	INNER JOIN ( 
		SELECT aal AS aal_gdp, wb_adm1_co
		FROM flood
		WHERE analysis_sector = 'GDP' AND year = 2010 
	) AS fl ON admin1.adm1_code = fl.wb_adm1_co
	INNER JOIN (
		SELECT expval_mon AS gdp, wb_adm1_co
		FROM exposure
		WHERE analysis_sector = 'GDP' AND year = 2010
	) AS ex ON admin1.adm1_code = ex.wb_adm1_co
	INNER JOIN ( 
		SELECT aal AS aal_pop, wb_adm1_co
		FROM flood
		WHERE analysis_sector = 'Population' AND year = 2010 
	) AS fl1 ON admin1.adm1_code = fl1.wb_adm1_co
	INNER JOIN (
		SELECT expval_n AS pop, wb_adm1_co
		FROM exposure
		WHERE analysis_sector = 'Population' AND year = 2010
	) AS ex1 ON admin1.adm1_code = ex1.wb_adm1_co
	WHERE admin1.adm0_code = 253
	ORDER BY adm1_code;

SELECT *, 'c' || gdp_class || pop_class AS combined FROM (
	SELECT
		*,
		( SELECT MAX( gdp_pct ) FROM bivariate WHERE gdp_pct < ( SELECT MAX( gdp_pct ) FROM bivariate ) ) / 3 AS gdp_interval,
		( SELECT MAX( pop_pct ) FROM bivariate WHERE pop_pct < ( SELECT MAX( pop_pct ) FROM bivariate ) ) / 3 AS pop_interval,
		CASE
			WHEN gdp_pct IS NULL THEN NULL
			ELSE LEAST( 2, FLOOR( gdp_pct / ( ( SELECT MAX( gdp_pct ) FROM bivariate WHERE gdp_pct < ( SELECT MAX( gdp_pct ) FROM bivariate ) ) / 3 ) ) )
		END AS gdp_class,
		CASE
			WHEN pop_pct IS NULL THEN NULL
			ELSE LEAST( 2, FLOOR( pop_pct / ( ( SELECT MAX( pop_pct ) FROM bivariate WHERE pop_pct < ( SELECT MAX( pop_pct ) FROM bivariate ) ) / 3 ) ) )
		END AS pop_class
	FROM bivariate
) AS q;

combined-pop-aal.sql

SELECT gid, fl.aal AS flood_aal, eq.aal AS earthquake_aal, dr.aal AS drought_aal, expval_n AS population_exposure, ( fl.aal + eq.aal + dr.aal ) / expval_n AS value, geom
FROM admin1
INNER JOIN ( 
	SELECT aal, wb_adm1_co FROM flood WHERE analysis_sector = 'Population' AND year = 2010 
) AS fl ON admin1.adm1_code = fl.wb_adm1_co
INNER JOIN (
	SELECT aal, wb_adm1_co FROM drought WHERE analysis_sector = 'Population' AND year = 2010 
) AS dr ON admin1.adm1_code = dr.wb_adm1_co
INNER JOIN ( 
	SELECT aal, wb_adm1_co FROM earthquake WHERE analysis_sector = 'Population' AND year = 2010 
) AS eq ON admin1.adm1_code = eq.wb_adm1_co
INNER JOIN (
	SELECT expval_n, wb_adm1_co FROM exposure WHERE analysis_sector = 'Population' AND year = 2010
) AS ex ON admin1.adm1_code = ex.wb_adm1_co
WHERE admin1.adm0_code = 79
ORDER BY adm1_code

drought-bar-chart.sql

SELECT 
  analysis_sector year,
  SUM( aal )::bigint AS aal,
  SUM( rp10 )::bigint AS rp10,
  SUM( rp100 )::bigint AS rp100
FROM drought
WHERE wb_adm0_co = 79 AND ( wb_adm1_co = 79 OR analysis_sector = 'Hydr-DamLoss' )
GROUP BY analysis_sector, year
ORDER BY analysis_sector, year

drought-bivariate.sql

DROP VIEW IF EXISTS bivariate;
CREATE VIEW bivariate AS 
	SELECT gid, gdp_pct, pop_pct, geom
	FROM admin1
	INNER JOIN ( 
		SELECT aal_pcntregion_mon AS gdp_pct, wb_adm1_co
		FROM drought
		WHERE analysis_sector = 'Agr-income' AND year = 2010 
	) AS fl ON admin1.adm1_code = fl.wb_adm1_co
	INNER JOIN ( 
		SELECT aal_pcntregion_mon AS pop_pct, wb_adm1_co
		FROM drought
		WHERE analysis_sector = 'Agr-LaborDays' AND year = 2010 
	) AS fl1 ON admin1.adm1_code = fl1.wb_adm1_co
	WHERE admin1.adm0_code = 181
	ORDER BY adm1_code;

drought-sector-small-multiples.sql

SELECT
	gid,
	income_aal,
	income_exposure,
	income_aal_total,
	labor_aal,
	labor_exposure,
	labor_aal_total,
	geom
FROM admin1
INNER JOIN (
	SELECT 
		wb_adm1_co,
		SUM( aal )::bigint AS income_aal
		FROM drought
		WHERE analysis_sector = 'Agr-income' AND year = 2010
		GROUP BY wb_adm1_co
) AS inc ON admin1.adm1_code = inc.wb_adm1_co
INNER JOIN (
	SELECT
		wb_adm1_co,
		SUM( expval_mon )::bigint AS income_exposure
		FROM exposure
		WHERE analysis_sector = 'Agr-income' AND year = 2010
		GROUP BY wb_adm1_co
) AS incx ON admin1.adm1_code = incx.wb_adm1_co
LEFT JOIN (
	SELECT
		wb_adm0_co,
		SUM( aal )::bigint AS income_aal_total
		FROM drought
		WHERE analysis_sector = 'Agr-income' AND year = 2010
		GROUP BY wb_adm0_co
) AS inc0 ON admin1.adm0_code = inc0.wb_adm0_co
INNER JOIN (
	SELECT 
		wb_adm1_co,
		SUM( aal )::bigint AS labor_aal
		FROM drought
		WHERE analysis_sector = 'Agr-LaborDays' AND year = 2010
		GROUP BY wb_adm1_co
) AS labor ON admin1.adm1_code = labor.wb_adm1_co
INNER JOIN (
	SELECT
		wb_adm1_co,
		SUM( expval_mon )::bigint AS labor_exposure
		FROM exposure
		WHERE analysis_sector = 'Agr-LaborDays' AND year = 2010
		GROUP BY wb_adm1_co
) AS laborx ON admin1.adm1_code = laborx.wb_adm1_co
LEFT JOIN (
	SELECT
		wb_adm0_co,
		SUM( aal )::bigint AS labor_aal_total
		FROM drought
		WHERE analysis_sector = 'Agr-LaborDays' AND year = 2010
		GROUP BY wb_adm0_co
) AS labor0 ON admin1.adm0_code = labor0.wb_adm0_co
WHERE adm0_code = 79

gdp-bar-chart.sql

SELECT
	fl.aal / expval_mon AS flood_pct,
	eq.aal / expval_mon AS earthquake_pct,
	ls.aal / expval_mon AS landslide_pct,
	dr.aal / expval_mon AS drought_pct
FROM exposure AS ex
INNER JOIN(
	SELECT aal, analysis_sector FROM flood WHERE wb_adm0_co = 79 AND wb_adm1_na = 'n/a - admin1' AND year = 2010
) AS fl ON ex.analysis_sector = fl.analysis_sector
INNER JOIN(
	SELECT aal, analysis_sector FROM drought WHERE wb_adm0_co = 79 AND wb_adm1_na = 'n/a - admin1' AND year = 2010
) AS dr ON ex.analysis_sector = dr.analysis_sector
INNER JOIN(
	SELECT aal, analysis_sector FROM earthquake WHERE wb_adm0_co = 79 AND wb_adm1_na = 'n/a - admin1' AND year = 2010
) AS eq ON ex.analysis_sector = eq.analysis_sector
INNER JOIN(
	SELECT aal, analysis_sector FROM landslide WHERE wb_adm0_co = 79 AND wb_adm1_na = 'n/a - admin1' AND year = 2010
) AS ls ON ex.analysis_sector = ls.analysis_sector
WHERE
wb_adm0_co = 79
AND wb_adm1_co = 79
AND year = 2010
AND ex.analysis_sector = 'GDP'

hazard-bar-chart.sql

SELECT 
	CASE
		WHEN analysis_sector = 'Education' OR analysis_sector = 'Energy' OR analysis_sector = 'Health' THEN '_Critical Facilities'
		WHEN analysis_sector = 'Private Buildings' OR analysis_sector = 'Public Buildings' THEN '_Buildings'
		WHEN analysis_sector = 'Rail' OR analysis_sector = 'Road' THEN '_Transport'
		ELSE analysis_sector
	END AS sector,
	year,
	SUM( aal )::bigint AS aal,
	SUM( rp10 )::bigint AS rp10,
	SUM( rp100 )::bigint AS rp100
FROM flood
WHERE wb_adm0_co = 79 AND wb_adm1_co = 79
GROUP BY sector, year
ORDER BY sector, year

hazard-sector-small-multiples.sql

SELECT
	gid,
	critical_aal,
	critical_exposure,
	critical_aal_total,
	building_aal,
	building_exposure,
	building_aal_total,
	transport_aal,
	transport_exposure,
	transport_aal_total,
	geom
FROM admin1
INNER JOIN (
	SELECT 
		wb_adm1_co,
		SUM( aal )::bigint AS critical_aal
		FROM flood
		WHERE ( analysis_sector = 'Education' OR analysis_sector = 'Energy' OR analysis_sector = 'Health' )
			AND year = 2010
		GROUP BY wb_adm1_co
) AS crit ON admin1.adm1_code = crit.wb_adm1_co
INNER JOIN (
	SELECT
		wb_adm1_co,
		SUM( expval_mon )::bigint AS critical_exposure
		FROM exposure
		WHERE ( analysis_sector = 'Education' OR analysis_sector = 'Energy' OR analysis_sector = 'Health' )
			AND year = 2010
		GROUP BY wb_adm1_co
) AS critx ON admin1.adm1_code = critx.wb_adm1_co
LEFT JOIN (
	SELECT
		wb_adm0_co,
		SUM( aal )::bigint AS critical_aal_total
		FROM flood
		WHERE ( analysis_sector = 'Education' OR analysis_sector = 'Energy' OR analysis_sector = 'Health' )
			AND year = 2010
		GROUP BY wb_adm0_co
) AS crit0 ON admin1.adm0_code = crit0.wb_adm0_co
INNER JOIN (
	SELECT 
		wb_adm1_co,
		SUM( aal )::bigint AS building_aal
		FROM flood
		WHERE ( analysis_sector = 'Private Buildings' OR analysis_sector = 'Public Buildings' )
			AND year = 2010
		GROUP BY wb_adm1_co
) AS bldg ON admin1.adm1_code = bldg.wb_adm1_co
INNER JOIN (
	SELECT
		wb_adm1_co,
		SUM( expval_mon )::bigint AS building_exposure
		FROM exposure
		WHERE ( analysis_sector = 'Private Buildings' OR analysis_sector = 'Public Buildings' )
			AND year = 2010
		GROUP BY wb_adm1_co
) AS bldgx ON admin1.adm1_code = bldgx.wb_adm1_co
LEFT JOIN (
	SELECT
		wb_adm0_co,
		SUM( aal )::bigint AS building_aal_total
		FROM flood
		WHERE ( analysis_sector = 'Private Buildings' OR analysis_sector = 'Public Buildings' )
			AND year = 2010
		GROUP BY wb_adm0_co
) AS bldg0 ON admin1.adm0_code = bldg0.wb_adm0_co
INNER JOIN (
	SELECT 
		wb_adm1_co,
		SUM( aal )::bigint AS transport_aal
		FROM flood
		WHERE ( analysis_sector = 'Rail' OR analysis_sector = 'Road' )
			AND year = 2010
		GROUP BY wb_adm1_co
) AS trans ON admin1.adm1_code = trans.wb_adm1_co
INNER JOIN (
	SELECT
		wb_adm1_co,
		SUM( expval_mon )::bigint AS transport_exposure
		FROM exposure
		WHERE ( analysis_sector = 'Rail' OR analysis_sector = 'Road' )
			AND year = 2010
		GROUP BY wb_adm1_co
) AS transx ON admin1.adm1_code = transx.wb_adm1_co
LEFT JOIN (
	SELECT
		wb_adm0_co,
		SUM( aal )::bigint AS transport_aal_total
		FROM flood
		WHERE ( analysis_sector = 'Rail' OR analysis_sector = 'Road' )
			AND year = 2010
		GROUP BY wb_adm0_co
) AS trans0 ON admin1.adm0_code = trans0.wb_adm0_co
WHERE adm0_code = 79

landslide-pop-aal.sql

SELECT gid, aal, expval_n AS population_exposure, aal / expval_n AS value, geom
FROM admin1
INNER JOIN ( 
	SELECT aal, wb_adm1_co FROM landslide WHERE analysis_sector = 'Population' AND year = 2010 
) AS ls ON admin1.adm1_code = ls.wb_adm1_co
INNER JOIN (
	SELECT expval_n, wb_adm1_co FROM exposure WHERE analysis_sector = 'Population' AND year = 2010
) AS ex ON admin1.adm1_code = ex.wb_adm1_co
WHERE admin1.adm0_code = 79
ORDER BY adm1_code

summary-gdp-small-multiples.sql

SELECT
	gid,
	expval_mon::bigint AS gdp_exposure,
	dr.aal::bigint AS drought_aal,
	fl.aal::bigint AS flood_aal,
	eq.aal::bigint AS earthquake_aal,
	ls.aal::bigint AS landslide_aal,
	geom
FROM admin1
INNER JOIN ( 
	SELECT aal, wb_adm1_co FROM drought WHERE analysis_sector = 'GDP' AND year = 2010 
) AS dr ON admin1.adm1_code = dr.wb_adm1_co
INNER JOIN ( 
	SELECT aal, wb_adm1_co FROM flood WHERE analysis_sector = 'GDP' AND year = 2010 
) AS fl ON admin1.adm1_code = fl.wb_adm1_co
INNER JOIN ( 
	SELECT aal, wb_adm1_co FROM earthquake WHERE analysis_sector = 'GDP' AND year = 2010 
) AS eq ON admin1.adm1_code = eq.wb_adm1_co
INNER JOIN ( 
	SELECT aal, wb_adm1_co FROM landslide WHERE analysis_sector = 'GDP' AND year = 2010 
) AS ls ON admin1.adm1_code = ls.wb_adm1_co
INNER JOIN (
	SELECT expval_mon, wb_adm1_co FROM exposure WHERE analysis_sector = 'GDP' AND year = 2010
) AS ex ON admin1.adm1_code = ex.wb_adm1_co
WHERE admin1.adm0_code = 79
ORDER BY adm1_code

volcano-bar-chart.sql

SELECT 
	CASE
		WHEN analysis_sector = 'Education' OR analysis_sector = 'Energy' OR analysis_sector = 'Health' THEN '_Critical Facilities'
		WHEN analysis_sector = 'Private Buildings' OR analysis_sector = 'Public Buildings' THEN '_Buildings'
		WHEN analysis_sector = 'Rail' OR analysis_sector = 'Road' THEN '_Transport'
		ELSE analysis_sector
	END AS sector,
	year,
	SUM( COALESCE( exposed_mon, exposed_n ) )::bigint AS exposure
FROM volcano
WHERE wb_adm0_co = 79
	AND hazard = 'VO ASH'
GROUP BY sector, year
ORDER BY sector, year

volcano-bivariate.sql

SELECT gid, name, population, gdp, geom
FROM volcano_loc AS loc
INNER JOIN(
	SELECT wb_adm1_na, SUM( exposed_n ) AS population
	FROM volcano
	WHERE wb_adm0_co = 79
		AND analysis_sector = 'Population'
		AND year = 2010
		AND hazard = 'VO ASH'
	GROUP BY wb_adm1_na
) AS pop ON loc.name = pop.wb_adm1_na
INNER JOIN(
	SELECT wb_adm1_na, SUM( exposed_mon ) AS gdp
	FROM volcano
	WHERE wb_adm0_co = 79
		AND analysis_sector = 'GDP'
		AND year = 2010
		AND hazard = 'VO ASH'
	GROUP BY wb_adm1_na
) AS mon ON loc.name = mon.wb_adm1_na

volcano-points.sql

SELECT volcano_loc.* FROM volcano_loc
INNER JOIN (
	SELECT geom FROM admin1 WHERE adm0_code = 79
) AS adm ON ST_INTERSECTS( volcano_loc.geom, adm.geom )

volcano-sector-small-multiples.sql

SELECT
	crit.wb_adm1_na,
	crit.hazard,
	critical_exposure,
	building_exposure,
	transport_exposure
FROM (
	SELECT 
		wb_adm0_co,
		wb_adm1_na,
		hazard,
		SUM( exposed_mon )::bigint AS critical_exposure
		FROM volcano
		WHERE ( analysis_sector = 'Education' OR analysis_sector = 'Energy' OR analysis_sector = 'Health' )
			AND year = 2010
		GROUP BY wb_adm1_na, hazard, wb_adm0_co
) AS crit
INNER JOIN (
	SELECT 
		wb_adm1_na,
		hazard,
		SUM( exposed_mon )::bigint AS building_exposure
		FROM volcano
		WHERE ( analysis_sector = 'Private Buildings' OR analysis_sector = 'Public Buildings' )
			AND year = 2010
		GROUP BY wb_adm1_na, hazard
) AS bldg ON crit.wb_adm1_na = bldg.wb_adm1_na AND crit.hazard = bldg.hazard
INNER JOIN (
	SELECT 
		wb_adm1_na,
		hazard,
		SUM( exposed_mon )::bigint AS transport_exposure
		FROM volcano
		WHERE ( analysis_sector = 'Rail' OR analysis_sector = 'Road' )
			AND year = 2010
		GROUP BY wb_adm1_na, hazard
) AS trans ON crit.wb_adm1_na = trans.wb_adm1_na AND crit.hazard = trans.hazard
WHERE wb_adm0_co = 79
ORDER BY hazard, wb_adm1_na