processing data from openstreetmap using postgis

about

I used OpenStreetMap data collected through Ramani Huria to analyze the spatial relationship between drains and waste sites in Dar es Salaam. In which subwards are drains more likely to be clogged due to being in close proximity to waste sites? PostGIS and the DB Manager of QGIS were used to find the average distance from drains to the nearest waste sites within a 50 m buffer by subward. The data was obtained from the web feature service (WFS) of Resilience Academy, a Ramani Huria project. Leaflet was used to visualize the output. The qgis2web plugin in QGIS allowed for the creation of the leaflet map.

sql and map

leaflet map

queries executed

/*fixing geometries and reprojecting*/
create table wards37s as
select id, fid, ward_name,
st_makevalid(st_transform(geom,32737)) as geom
from wards;

create table subwards37s as
select fid,
st_transform(geom,32737) as geom
from subwards;

create table waste37s as
select id,
st_transform(geom, 32737) as geom
from waste;

create table drains37s as
select id,
st_transform(geom,32737) as geom
from drains;

select populate_geometry_columns();

/*creating spatial indices*/
create index sidx_wards37s_geom
on wards37s
using GIST(geom);

create index sidx_subwards37s_geom
on subwards37s
using GIST(geom);

create index sidx_drains37s_geom
on drains37s
using GIST(geom);

create index sidx_waste37s_geom
on waste37s
using GIST(geom);

/*assigning drains and waste sites to wards and subwards*/
alter table drains37s add column ward text;

update drains37s
set ward=wards37s.ward_name
from wards37s
where st_intersects(drains37s.geom,wards37s.geom);

alter table drains37s add column subward float8;

update drains37s
set subward=subwards37s.fid
from subwards37s
where st_intersects(drains37s.geom,subwards37s.geom);

alter table waste37s add column ward text;

update waste37s
set ward=wards37s.ward_name
from wards37s
where st_intersects(waste37s.geom,wards37s.geom);


alter table waste37s add column subward float8;

update waste37s
set subward=subwards37s.fid
from subwards37s
where st_intersects(waste37s.geom,subwards37s.geom);

/* finding distance from drains to waste sites*/
create table distfromdrain as
select drains37s.id as drains, waste37s.id as waste,
st_distance(geography(st_transform(drains37s.geom, 4326)), geography(st_transform(waste37s.geom, 4326))) as dist
from drains37s, waste37s
where st_intersects(st_buffer(drains37s.geom,50), waste37s.geom);

/*finding minimum distance between drains and waste sites */
create table mindistfromdrain as
select drains, min(dist) as dist
from distfromdrain
group by drains;

/*averaging minimum distance for wards and subwards*/
create table wardminavg as
select b.ward, avg(dist)
from mindistfromdrain as a
left outer join drains37s as b
on a.drains=b.id
group by b.ward;

create table subwardminavg as
select b.subward, avg(dist)
from mindistfromdrain as a
left outer join drains37s as b
on a.drains=b.id
group by b.subward;

/*adding columns*/
alter table subwards37s
add column wastesites float8,
add column drains float8,
add column avgmindist float8;

/*counting number of drains and waste sites in each subward*/
update subwards37s
set wastesites=(
select count(waste37s.id)
from waste37s
where subwards37s.fid=waste37s.subward);

update subwards37s
set drains=(
select count(drains37s.id)
from drains37s
where subwards37s.fid=drains37s.subward);

/*adding average minimum distance to subwards*/
update subwards37s
set avgmindist=subwardminavg.avg
from subwardminavg
where subwards37s.fid=subwardminavg.subward;

/*adding columns*/
alter table wards37s
add column wastesites float8,
add column drains float8,
add column avgmindist float8;

/*counting number of drains and waste sites in each ward*/
update wards37s
set wastesites=(
select count(waste37s.id)
from waste37s
where wards37s.ward_name=waste37s.ward);

update wards37s
set drains=(
select count(drains37s.id)
from drains37s
where wards37s.ward_name=drains37s.ward);

/*adding average minimum distance to wards*/
update wards37s
set avgmindist=wardminavg.avg
from wardminavg
where wards37s.ward_name=wardminavg.ward;