Sustainable Cities and Communities is the 11th Sustainable Development Goal which aspires to make cities inclusive, safe, resilient and sustainable.The world is becoming increasingly urbanized. Since 2007, more than half the world’s population has been living in cities. This makes it very important for the cities to remain alert when there is a chance of disaster like floods. Local administration should know if their city is going to get affected by the rains which happen in their proximity so that they can raise an alert amongst the citizens. This exercise will solve one of such problems.
To determine the areas where if it rains will affect a city/town
If it rains in vicinity of a river connecting the city, the city will get affected by the rains.
Choose a city
Get the Rivers (Edges)
Create river components
Create a Buffer around the city
Finding the components intersecting the buffer
Finding the rain zones
For this exercise, Munshigang city from Bangladesh is chosen. This city has multiple rivers in its proximity which makes it an apt location to demonstrate this exercise. The exercise will try to find the areas, where if it rains the city will be affected. To define the location of this city and use it in for further steps, create a table to store the name along with latitude and longitude values of City’s location. This stores the city as a point.
1CREATE TABLE city_vertex (id BIGINT, name TEXT, geom geometry); 2INSERT INTO city_vertex(id, name, geom) VALUES ( 35,'Munshigang', ST_SetSRID(ST_Point(89.1967,22.2675),4326));
Latitude and longitude values are converted into
geometry form using
which returns a point with the given X and Y coordinate values.
ST_SetSRID is used
to set the SRID (Spatial Reference Identifier) on the point geometry to
First step is to pre-process the data obtained from Data for Sustainable Development Goals. This section will work the graph that is going to be used for processing. While building the graph, the data has to be inspected to determine if there is any invalid data. This is a very important step to make sure that the data is of required quality. pgRouting can also be used to do some Data Adjustments. This will be discussed in further sections.
First step in pre processing is to set the search path for
data. Search path is a list of schemas that helps the system determine how a
particular table is to be imported.
Inspect the schemas by displaying all the present schemas using the following command
List of schemas Name | Owner -----------+---------- public | postgres waterway | <user-name> (2 rows)
The schema names are
public. The owner depends on who has the rights to the database.
Display the current search path using the following query.
search_path ----------------- "$user", public (1 row)
This is the current search path. Tables cannot be accessed using this.
In this case, search path of roads table is set to
waterways schema. Following query
is used to fix the search path
SET search_path TO waterways,public; SHOW search_path;
search_path ------------------- waterways, public (1 row)
\dt is used to verify if the Schema have bees changed correctly.
List of relations Schema | Name | Type | Owner -----------+-----------------------------+-------+--------- public | spatial_ref_sys | table | <user-name> waterways | configuration | table | user waterways | waterways_pointsofinterest | table | user waterways | waterways_ways | table | user waterways | waterways_ways_vertices_pgr | table | user (5 rows)
The importance of counting the information on this workshop is to make sure that the same data is used and consequently the results are same. Also, some of the rows can be seen to understand the structure of the table and how the data is stored in it.
1SELECT count(*) FROM waterways_ways;
This exercise focusses only the areas in the mainland, where if it rains the city is
affected. Hence, the rivers which are there in the swamp area have to be removed
1DELETE FROM waterways_ways 2WHERE osm_id 3IN (721133202, 908102930, 749173392, 652172284, 126774195, 720395312);
For the next step
pgr_connectedComponents will be used. It is used to find the
connected components of an undirected graph using a Depth First Search-based approach.
pgr_connectedComponents(edges_sql) RETURNS SET OF (seq, component, node) OR EMPTY SET
pgr_connectedComponents Documentation can be found at this link for more information.
As the rivers in the data are not having single edge, i.e, multiple edges make up
a river, it is important to find out the connected edges and store the information
waterways_ways table. This will help us to identify which edges belong to
a river. First, the connected components are found and then stored in a new column
pgr_connectedComponents is used to complete this task.
A sub-query is created to find out all the connected components. After that,
component column is updated using the results obtained from the sub-query.
This helps in storing the component id in the
Next query uses this output and stores the component id in the waterways_ways
(edges) table. Follow the steps given below to complete this task.
Add a column named
componentto store component number.
1ALTER TABLE waterways_ways_vertices_pgr 2ADD COLUMN component INTEGER; 3 4ALTER TABLE waterways_ways 5ADD COLUMN component INTEGER;
Get the Connected Components of Waterways
1UPDATE waterways_ways_vertices_pgr SET component = subquery.component 2FROM (SELECT * FROM pgr_connectedComponents( 3'SELECT gid AS id, source, target, cost, reverse_cost FROM waterways_ways') 4) AS subquery 5WHERE id = node; 6 7UPDATE waterways_ways SET component=a.component FROM ( 8SELECT id, component FROM waterways_ways_vertices_pgr 9) AS a 10WHERE id = source;
With component id stored in both vertex and edge table of waterways, lets proceed to next step.
Create a buffer around the city to define an area, inside which the intersection
of rivers would be found.
ST_Buffer is used to create this buffer. Follow the
steps given below to complete this task.
Adding column to store Buffer geometry
1ALTER TABLE waterways.city_vertex 2ADD COLUMN city_buffer geometry;
Storing Buffer geometry
1UPDATE waterways.city_vertex 2SET city_buffer = ST_Buffer((geom),0.005) 3WHERE name = 'Munshigang';
Displaying the results of Buffer operation
1SELECT city_buffer FROM waterways.city_vertex;
A function can be created for the same task. This will be help when the table has more than one city.
1CREATE OR REPLACE FUNCTION get_city_buffer(city_id INTEGER) 2RETURNS geometry AS 3$BODY$ 4SELECT city_buffer FROM city_vertex WHERE id = city_id; 5$BODY$ 6LANGUAGE SQL;
Next step is to find the components of waterways which lie in the buffer zone of
the city. These are the waterways which will affect the city when it rains around
them. This is done using
ST_Intersects. Note that
is used in the query below.
1SELECT DISTINCT component 2FROM waterways.city_vertex, waterways.waterways_ways 3WHERE ST_Intersects(the_geom, get_city_buffer(5));
Output shows the distinct component numbers which lie in the buffer zone of the city. Next step is to get all the edges that have those components.
This is the final step of the exercise. In this, the area where if it rains, the
city would be affected, also can be called as
rain zone is being found. For this,
create a Buffer around the river components. First, add columns named
in waterways_ways to store buffer geometry of the rain zones. Then, find the buffer
for every edge which intersects the buffer area using
ST_Buffer and update the
rain_zone column. Follow the steps given below to complete this task.
Adding column to store Buffer geometry
1ALTER TABLE waterways_ways 2ADD COLUMN rain_zone geometry;
Storing Buffer geometry
1UPDATE waterways.waterways_ways 2SET rain_zone = ST_Buffer((the_geom),0.005) 3WHERE ST_Intersects(the_geom, get_city_buffer(5));
This will give us the requires area, where if it rains, the city will be affected.
Multiple polygons that are obtained can also be merged using
will give a single polygon as the output.
1SELECT ST_Union(rain_zone) AS Combined_Rain_Zone 2FROM waterways_ways;