Dar Es Salam vulnerability analysis lab:

In this lab we accessed data about Dar es Salam from the Resilience Academy from Ramani Huria and Open Street Map data to do a vulnerability anaylsis for Dar es Salam. https://resilienceacademy.ac.tz/ https://www.openstreetmap.org/search?query=Dar%20Es%20Salam#map=11/-6.8113/39.3053 The resilience academy layers can be directly downloaded into the PostGres database. The OSM data was exported from OSM Then we ran it through the program osm2pgsql which can be found here https://github.com/openstreetmap/osm2pgsql

We used the PostGIS extension of PostGres SQL to process our data. We used QGIS version 3.8.1 and [leaflet] (https://leafletjs.com/) to visualize the outputs.

Question: My partner and I chose to look at the density of storm drains by subward to determine whether storm drains are more dense in subwards that lie in wetland zones.

Data: We used the “storm drains as points” and “subward divsions” layers from the Resilence Academy. We also used data from Open Street Map to look at the wetlands extent. Our final drain density map with the wetlands extent can be found here: Drain Density Map

An explanation of our process: To find the density of drains it was necessary to asign a subward to each drain point based on the location. Add a column to the drains layer and perform a spatial intersect that assigns a subward to the drain using this sql function:

alter table drains add column subward integer;
update drains
set subward = fid
from subwards
where st_intersects(drains.geom, subwards.geom)

Then we selected every input where the subward was assigned as NULL because these were points where the drain had not been assigned a real place in space and so could not be used for analysis. This is a potential source of error since some drains had to be deleted.:

select* from drains
where drains.subward is null 

next we wanted to count all the drains that had the same subward identification and group them together under their subward id so that they can be joined to the subwards layer in the next step.

select count(subward) as subwardcount, subward
from drains
group by subward

we “created a view” from this information so that it this information could be added to the subwards layer. we named the view “subwardcount1”

next we added a column to the subwards layer and called it “draincount” then we joined the information from the “subwardcount1” layer to the “draincount” field through an update table function

alter table subwards add column draincount integer ;
update subwards 
set draincount = subwardcount 
from subwardcount1 
where subwardcount1.subward = subwards.fid

Now that we have the drains and the subwards on one layer and they are joined spatially, we can move on to calculating the area of the subwards which we will use to calucate the density of the drains by subward.

Calculate area geodesically:

select fid, draincount, st_area(geography(geom)) as area1 
from subwards

We created a view from this information and called it “subward_area”

then We added an area column to the subwards layer and named it aream2 because the area was calucualted in m^2 then we used the update table function to add this information onto the subwards layer:

alter table subwards add column aream2 float8;
update subwards 
set aream2 = subward_area.area1
from subward_area 
where subwards.fid = subward_area.fid

then we added a column for the drain density and named it “draindensitykm2” Then we calculated drain density by dividing the draincount by the area of the subwards and by 1000000 so that the calculation would be in km^2 so that it is easier to understand.

alter table subwards add column draindensitykm2 float8
select fid, aream2, draincount, draincount/(aream2/1000000) as draindensity 
from subwards 

we created a view from this information and called it “drain_density” and added this information to the subwards layer using an update table function

update subwards 
set draindensitykm2 = drain_density.draindensity 
from drain_density
where subwards.fid = drain_density.fid

Finally we wanted to select only the subwards on the map that had drain data so that our map would be smaller and easier to visulize. we did this by selecting all the subwards where the draindensitykm2 was not null.

select fid, draindensitykm2, geom
from subwards_drains
where draindensitykm2 is null

The next step is to pull the wetland information from the OSM data so that we can create a layer that shows which subwards lie in the wetlands. To do this, we know that every cell is assigned to a category of land type under the “natural” column in the planet_osm_polygon layer from OSM All cells that lie in within the wetland are designated “wetland” in the “natural” column. we also discovered that there is a wetland column that will designate some cells as wetland and all others will be labled as NULL. We selected cells that met ither of these two criteria.

select *
from planet_osm_polygon
where "natural" = 'wetland'
or wetland is not null

in our final map the wetland layer is placed over the subward density layer so that the viewer can analyze whether the subwards with the highest density are in fact in the wetland area.

here is a link to look at all the sql functions we used from start to finish SQl

Results and analysis: To visulize our results we used a color scale to show drain density where the lightest yellow are subwards with the leadt dense drain dispersal and the and the subwards with the darkest orange have the highest drain density. The wetlands layer is overlayed on top of the subwards with a lower opacity so that the viewer can see the subwards underneath the wetlands. From examining the map we can see that the subwards with the highest drain density are not necessarily the subwards that intersect the most with the wetlands. This is the case for example with the subward fid: 52 which has a drain density of 523 per km2 but has little to no intersect with the wetlands. This pushes us to wonder what the conditions are in that subward that allow it to have so many drains. Is it a wealthy residential area? A commerical district? There is another area- subwards fids: 79, 80, 81, 442- that has relatively high drain density, but relatively low intersection with the wetlands. Why are drains so highly concentrated in this area? On the other hand, there are subwards that have low drain density but a lot of overlap with wetlands. This is the case for subwards fid: 32, 211, 478 and others. We are lead to wonder what the conditions are in the these subwards and are these areas more vulnerable to floods because they have less storm drains? Is there a reason why these subwards require less storm drains? Although we have many questions that require futher analysis, we can conclude that storm drains are not evenly distributed across Dar Es Salam and not necessarily more dense in areas that lie in the weland areas. This could lead to more damaging flood is some areas versus others.