THIS IS A DRAFT AND THEREFORE NOT COMPLETE

Requirements

• Must have OpenGeo Suite Installed - [Download OpenGeoSuite][open_geo]
• QGIS Installed - [Download QGIS][qgis]
• Must have loaded the lesotho database into PostGIS. For instructions see - Day 2 - Loading OSM Data to Database

Contents

1. Extra Resources

Today we are going to find out how many buildings there are within 100 meters of road (highway) in all of Lesotho. To do this we will use the functionality of PostGIS and then we will use QGIS to view the results of this.

Furthermore we can then update our query to show us the numbers of each type of building which are within 100m of the road.

First we must understand one thing about PostGIS. This is how does it store it’s geometry. It does this in a format called WKB (Well Known Binary). It the column of our table which contains this information which makes our GIS database so powerful. We run all our spatial queries against it.

To do this we will use the ST_Buffer function from PostGIS. This create a buffer around the objects just like you would do in a desktop GIS.

Using ST_Buffer is as simple as below. We supply it some geometry and then set the distance we would like to buffer. In our case we will use 100m as we know the data is stored with a CRS where the unit of measurement is in meters.

geometry ST_Buffer(geometry g1, float radius_of_buffer);

Above are two examples of how the buffer works visually. You can apply it to points, lines and polygons. The query will return polygon geometry type.

Creating Buffers

We can create buffers of a certain distance around any spatial row within the database. To do this we use a function call ST_Buffer(). So first a bit about how the SQL queries work when using ST_Buffer().

You need to supply it the geometry for each row and then the GIS create a polygon buffer of as many meters you specify around the features. An illustration of how this works is in the image below:

The query takes this form below, where:

• geometry = the input table geometry
• geometry g1 = the input geometry field
• float radius_of_buffer = a real number in distance such as 100 or 200.

geometry ST_Buffer(geometry g1, float radius_of_buffer);

For a full reference manual on buffering see ST_Buffer_Manual

Find potential flood zones

The key steps for this are as follows:

• Open QGIS and add your buildings and waterways as separate layers.
• Open PgAdmin and load up the lesotho database.
• Create table of 300m buffers around each of the waterways.
• Use Postgres to count the number of buildings located in this flooding buffer area.
• Show only the buildings located within the flooding buffer area.

Load buildings and waterways

Add PostGIS Layers of the

Right click on the planet_osm_line then click filter and then filter for the streams and rivers.

Use the following query to do so:

"waterways" IN ('stream', 'river')

This will allow us to only show streams and rivers throughout Lesotho.

Load Lesotho Database

Extract all features for Leribe.

CREATE TABLE leribe AS
SELECT * FROM planet_osm_line
WHERE ST_Intersects(
(SELECT way AS the_geom
FROM planet_osm_polygon
WHERE 	admin_level = '5'  AND 
	boundary = 'administrative' AND 
	name = 'Leribe District'), way) 

Create the 300m waterway buffers / potential flood areas

CREATE TABLE river_buffer_300 AS
SELECT ST_Union(ST_Buffer(way, 300)) AS the_geom
FROM planet_osm_line
WHERE waterway IN ('stream', 'river')

Count the buildings within flood area.

CREATE TABLE buildings_in_danger AS
(SELECT bs.way, bs.building
FROM 
	lirebe_river_buffer_300 as rb, 
	lesotho_buildings as bs
WHERE 
	ST_Intersects(bs.way,rb.the_geom))

Count all buildings in Leribe

This query will count all of the buildings within Leribe.

First we select the buildings column from lesotho_buildings

Next we find where the buildings intersect with the leribe boundary polygon.

Finally we group the count result by the building type.

SELECT building, COUNT(*) FROM lesotho_buildings
WHERE ST_Intersects(
	(SELECT way AS the_geom
FROM planet_osm_polygon
WHERE 	admin_level = '5' AND 
	boundary = 'administrative' AND 
	name = 'Leribe District'), ST_Centroid(way))
GROUP BY building

Count all buildings in the danger area.

SELECT COUNT(*) 
FROM buildings_in_danger
GROUP BY building

ss

Buffer all buildings by 100m

CREATE TABLE test AS
SELECT ST_Union(ST_Buffer(way, 100)) AS the_geom
FROM planet_osm_polygon
WHERE building = 'yes'

Extra Resources

• An Introduction Course to OpenGeo Suite. This will introduce you to the different tools which come with this package and how you can use them to serve up a web map
• Introduction to PostGIS. This course will show you each of the geospatial functions that are part of PostGIS and how you can use them under different scenarios