/*Lab04 SQL Query: Evacuation Centers in Hyperlocal boundaries in Dar es Saalam
Names: Arielle Landau and Jacob Freedman
Date: 3/31/21 - 4/7/21
 */


-- Create school layer
 CREATE TABLE schools AS
 SELECT osm_id, amenity, name, operator, way
 FROM planet_osm_point
 WHERE amenity = 'school';

-- Update schools to add flooded column
ALTER TABLE schools
ADD COLUMN flooded text;

-- updates flooded schools
UPDATE schools
SET flooded = 'yes_flood'
FROM flood
WHERE st_intersects(flood.geom, st_transform(schools.way,32737));

-- Update non-flooded schools
UPDATE schools
SET flooded = 'no_flood'
WHERE flooded IS NULL;

--Add district to schools
ALTER TABLE schools
ADD COLUMN subward_name text;

--  Copy the subwards table for future analyses 
CREATE TABLE subwards2 AS
SELECT *
FROM subwards;

--Add subward to school data for future spatial join to polygons
UPDATE schools
SET subward_name = subward
FROM subwards2
WHERE st_intersects(st_transform(subwards2.geom,32737), st_transform(schools.way,32737));

--Alter subward2 table for joining in school data
ALTER TABLE subwards2
ADD COLUMN school_count float;

ALTER TABLE subwards2
ADD COLUMN flooded_school_count float;

--count how many schools are in each ward
UPDATE subwards2
SET school_count = countschool
FROM
(SELECT subward_name, count(schools) as countschool
from schools
group by schools.subward_name
order by countschool) AS a
WHERE a.subward_name = subwards2.subward;

UPDATE subwards2
SET school_count = 0
WHERE school_count IS NULL;

-- count how many flooded schools in each ward:
UPDATE subwards2
SET flooded_school_count = countfloodschool
FROM
(SELECT subward_name, count(schools) as countfloodschool
from schools
where flooded = 'yes_flood'
group by schools.subward_name
order by countfloodschool) AS a
WHERE a.subward_name = subwards2.subward;

UPDATE subwards2
SET flooded_school_count = 0
WHERE flooded_school_count IS NULL;


-- Calculate proportion of flooded schools in each subward
ALTER TABLE subwards2
ADD COLUMN flooded_school_proportion float;

UPDATE subwards2
SET flooded_school_proportion = (subwards2.flooded_school_count / subwards2.school_count)
WHERE school_count != 0 AND flooded_school_count != 0;

UPDATE subwards2
SET flooded_school_proportion = 0
WHERE flooded_school_proportion IS NULL;

UPDATE subwards2
SET flooded_school_proportion = subwards2.flooded_school_proportion * 100;
