Conducting GIS Operations using SQL
In this exercise, some basic GIS functionalities are performed with SQL/PostGIS commands.
The basic data are layers symbolizing the roads in buildings in Salzburg. Firstly, the length of the road features is calculated and they are then classified by length. Then, the highways are extracted and each buildings distance to the highway is calculated. Furthermore, a 20m buffer is created around the roads and it is checked which buildings fall into that zone.
The road features are classified and visualized according to their calculated length.
SELECT r.*, CASE WHEN ST_Length(geom) <= 15 THEN 'very small' WHEN ST_Length(geom) > 15 AND ST_Length(geom) <= 40 THEN 'small' WHEN ST_Length(geom) > 40 AND ST_Length(geom) <= 80 THEN 'medium' WHEN ST_Length(geom) > 80 AND ST_Length(geom) <= 150 THEN 'large' WHEN ST_Length(geom) > 150 THEN 'very large' END AS road_category FROM salzburg_roads AS r;
Separating the Motorways from the Dataset and calculating distances
Next up, those road features classed as motorways or motorway links are extracted from the dataset. Then, the distance to a motorway is calculated for every building in Salzburg and the results visualized.
SELECT * FROM salzburg_roads WHERE fclass='motorway' OR fclass='motorway_link';
Now, the distance from each building to on of the motorways is calculated and stored in a new building layer. The distance is then visualized via a color ramp.
SELECT b.*, ST_Distance(m.geom,b.geom) AS distance FROM salzburg_buildings AS b, (SELECT ST_Union(geom) AS geom FROM salzburg_roads WHERE fclass='motorway' OR fclass='motorway_link') as m;
Buffer Creation and Intersect
Now, a 20m buffer around the roads is drawn, unionized and then intersected with the building layer in order to find out which buildings are within and which buildings are outside of the 20m buffer.
// Create 20m Buffer around Roads SELECT r.*, ST_Buffer(r.geom, 20, 'endcap=flat') AS potential_noise // Why not round endcaps, if we want distance to road? FROM salzburg_roads AS r;
Now, an intersect of the buildings and the buffer is performed in order to find out which buildings are within that zone.
// Selecting Buildings that intersect buffer feature SELECT b.* FROM salzburg_buildings AS b, potential_noise AS n WHERE ST_Intersects(n.buffer,b.geom) IS FALSE;
GIS actions – SQL Queries vs. GUI based approaches
Using SQL statement to perform GIS actions have several benefits, some of which are the following:
- Reproducibility: When designing a workflow which is likely to be repeated many times in the future, it is easy to create an executable script which can be run with one click or one command. Following a GIS workflow with many steps is prone to errors, also users without GIS experience might have more trouble following those steps than to just execute a program.
- Performance: Running GUI GIS programs usually requires the whole dataset to be loaded into the RAM, while the GUI, visualization of the dataset and Add-Ons consume further resources. Running SQL commands by themselves are much more efficient and usually faster.
- Server Compatibility: If the work is done on a server, there might not be an easy solution to implement a GUI based GIS. While solutions such as X Windows are available, it is a lot easier and less resource-intensive to use terminal commands.