Day 4 - Road Reservations

Road reservations

From discussions on Wednesday it was decided that we create a real world scenario that all APP’s can use. This scenario is a basic review of all the different query types we have used so far.

The main objective is to identify all the permanent structures within notional road reservations of 15 and 25 meters.

• Focusing on appropriate development distance from roads. Spec. Primary Roads.
• 15m either side from center line of road.
  • Building features
  • Power Lines
  • Any permanent structure.
• Statistics on the number of permanent structures within this reservation.

Key Steps

1. Select all the primary roads. planet_osm_lines
2. Create a 15m buffer around the roads.
3. Find all the buildings within the buffer zone.
4. Find all power lines (line, poles), sewer lines (line, manholes), trees, billboards.
5. Categorize structure distance from road centerline. (0-5m, 6-10m, 11-15m)

Some Notes

Throughout this guide when Open PgAdmin SQL window is stated this means we should have the following screen open.

Every Query should be execute unless otherwise stated! To execute you can press F5 on your keyboard or else click the same button indicated below:

Find all primary highways

First we need to do some data discovery and find out what are the tags used for roads in Lesotho. We will do a count on all the highway tags to see if primary exists in the highway column.

Open the PGadmin SQL window and paste in the following query:

**QUERY EXPLANATION**
Here we get all the unique values of the highway column in the table planet_osm_line
and then count them. Next the result is aggregated by each different value within 
the highway column such as primary, seconadary, track, etc. 

# display count of all highway tags
SELECT highway, COUNT (*)
FROM planet_osm_line
WHERE highway IS NOT NULL
GROUP BY highway

Now that we can see primary does exist we will now create a new table of just the primary roads.

To do this we will use the WHERE clause so that only rows where the value in column highway is equal to primary.

Open the PGadmin SQL window and paste in the following query:

**QUERY EXPLANATION**
In this query we find all the rows of the highway column in the table planet_osm_line
where the value is primary only.
We then add all these rows to a new table called primary_roads.

# find and create table the primary roads
CREATE TABLE primary_roads AS
SELECT highway, way
FROM planet_osm_line
WHERE highway = 'primary'

You can then open QGIS and load the primary_roads table as a new layer.

Add PostGIS Layer -> Connect -> primary_roads -> Add

The first step is now complete.

Create the Roads Reservation

This step follows on from the previous tutorials and follows the same basic query.

We need to create a 15 meter buffer around the centerline of the road to represent the 15 meter reservation on either side of primary roads in Lesotho.

To do this we will use the ST_Buffer(geom, 15m) function yet again. We will create a new table which contains the result of the buffering operation.

Again we open the PgAdmin SQL Query Window and paste in the query below.

**QUERY EXPLANATION**
Draw a buffer around the roads centerline that is 15 meters wide. 
The buffer is first created around every line segment. 
Then we use union to dissolve/merge the buffers into one. 
Then we insert this buffer into a new table called Create a table called road_buffer from the result of querying the primary 
roads table. 

This is how we can create just a single buffer.

DO NOT RUN THIS SCRIPT

-- DO NOT EXECUTE
CREATE TABLE road_buffer_15 AS
SELECT ST_Union(ST_Buffer(way, 15)) AS the_geom
FROM primary_roads

We will need buffers at the following distances 5, 10, 15 and 25 meters. To do this we use the same query as a above but add more rows to the table.

Copy and Paste this Query into you SQL Query Window.

**QUERY EXPLANATION**
This next query is in face 4 different queries. This query will create a table
which contains 4 rows, 1 for each buffer (5m, 10m, 15m and 25m)
In the first line we check if the table we wish to create is already in the
lesotho database or not.

The first statement we check if the table road_buffer_151 exists, 
if it does delete it.

Next we make a buffer around all the lines in the primary_roads 
table we created in the last step.

We then use union to dissolve all of these into one multi polygon.

This is then inserted into our new table we call 'road_buffer_151'

You should now Execute this query in the SQL Window

DROP TABLE IF EXISTS road_buffer_151;
CREATE road_buffer_151 AS 
SELECT ST_Union(ST_Buffer(way, 25)) AS the_geom, 25 AS dist
FROM primary_roads;

INSERT INTO road_buffer_151 (the_geom, dist)
SELECT ST_Union(ST_Buffer(way, 15)) AS the_geom, 15
FROM primary_roads;

INSERT INTO road_buffer_151 (the_geom, dist)
SELECT ST_Union(ST_Buffer(way, 10)) AS the_geom, 10
FROM primary_roads;

INSERT INTO road_buffer_151 (the_geom, dist)
SELECT ST_Union(ST_Buffer(way, 5)) AS the_geom, 5
FROM primary_roads;

Now Open QGIS and add the new table as a New PostGIS Layer .

Find the layer in the list of tables and Left Click to select it. Click Add.

Double Click on the layer in the Layers Panel on the left and Click Styles.

From the top bar select the drop down and select Graduated.

Next we will apply some thematic styling to our layer. We will add a graduated red to yellow color from the center of the road. The 5 meter reservation will be red and the 25 meter will be yellow.

On the right at Mode click the drop down and Equal Interval from the list. Change the number above this to 4.

Next check the Invert box beside colour ramp.

For the colour ramp select the YlOrRd one from the list.

Click the Symbol box above and then change the transparency to 50%

You should end up with something looking like this.

The resulting layer could look like below, where I have buildings already loaded along side our river buffers from yesterday.

We can now move onto the next step which is to quantify how many buildings are within each of the reservation zones.

Quantify number of structures in each zone

**QUERY EXPLANATION**
The aim of this query is to extract the houses which are located within the 25m
reservation. This will be repeated for each reservation distance (5m, 10m, 15m).
The buildings will be extracted to a new table called buildings_in_reserve.
When we insert the buildings we add a new column that refereces which buffer 
the building belongs to.

We use one extra function here which finds the center point (ST_Centroid) of 
each building. This allows us to find the intersection between the buildings
and the reservations much faster.

We supply Intersects with our first geometry (the 25m buffer only) and
the second geometry (the buildings center point).

Only those buildings which intersect this buffer will be added to the table.

--CREATE TABLE buildings_in_reserve AS
--	(SELECT building, way, 25 AS dist FROM lesotho_buildings
--	WHERE 
--		ST_Intersects(
--			(
--				SELECT the_geom
--				FROM road_buffer_151 
--				WHERE dist = 25
--			), ST_Centroid(way)
--		) AS the_geom) 

DROP TABLE IF EXISTS buildings_in_reserve;
CREATE TABLE buildings_in_reserve AS
SELECT DISTINCT ls.building, ls.way, rb.dist
FROM lesotho_buildings ls, road_buffer_151 rb 
WHERE ST_Intersects((rb.the_geom), ST_Centroid(ls.way)) 

Finally we can get a count of the number of buildings and their types within each of the reservations.

SELECT building, dist, COUNT(*)
FROM buildings_in_reserve
GROUP BY dist, building
ORDER BY dist