Contact

SQL for Spatial Queries

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.

Classification

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;
Image 1: SQL Command categorizing the roads, also visible in the resulting table is the classification of the features.
Image 2: Color-coded visualization of the categorization of the road features by length.
Image 2: Color-coded visualization of the categorization of the road features by length.

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';
Image 3: Roads classified as Motoways and Motorway Links extracted from the roads dataset in red, surrounded by the buildings layer.
Image 3: Roads classified as Motoways and Motorway Links extracted from the roads dataset in red, surrounded by the buildings layer.

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;
Image 4: SQL Query Window calculating the distance, please note the distance column.
Image 5: Distance from buildings to Motorways visualized.
Image 5: Distance from buildings to Motorways visualized.

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;
Image 6: Close-up of the 20m buffer around the roads, b/w satellite image in the background. The buffer is partially transparent in order to show the many overlapping features of the individual buffers.
Image 6: Close-up of the 20m buffer around the roads, b/w satellite image in the background. The buffer is partially transparent in order to show the many overlapping features of the individual buffers.
Image 7: Unionized buffer, now consisting of only one feature.
Image 7: Unionized buffer, now consisting of only one feature.

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;
Image 8: Visualized result of that query. The query results in a layer of buildings that do not intesect the buffer and are therefore out of the zone, shown here in green. In red, buildings that are within the zone are visualized.
Image 8: Visualized result of that query. The query results in a layer of buildings that do not intesect the buffer and are therefore out of the zone, shown here in green. In red, buildings that are within the zone are visualized.

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.
Further Reading
Recent Updates