Leveraging the power of spatial databases

Another approach to geoprocessing is to use the functionality provided by spatial databases such as PostGIS and SpatiaLite. In the Loading data from databases section of Chapter 2, Viewing Spatial Data, we discussed how to load data from a SpatiaLite database. In this exercise, we will use SpatiaLite's built-in geoprocessing functions to perform spatial analysis directly in the database and visualize the results in QGIS. We will use the same SpatiaLite database that we downloaded in Chapter 2, Viewing Spatial Data, from www.gaia-gis.it/spatialite-2.3.1/test-2.3.zip (4 MB).

Selecting by location in SpatiaLite

As an example, we will use SpatiaLite's spatial functions to get all highways that are within 1 km distance from the city of Firenze:

1. To interact with the database, we use the DB Manager plugin, which can be enabled in the Plugin Manager and is available via the Database menu.

   Note

   If you have followed the Loading data from databases section in Chapter 2, Viewing Spatial Data, you will see test-2.3.sqlite listed under SpatiaLite in the tree on the left-hand side of the DB Manager dialog, as shown in the next screenshot. If the database is not listed, refer to the previously mentioned section to set up the database connection.

2. Next, we can open a Query tab using the SQL window toolbar button, by going to Database | SQL window, or by pressing F2. The following SQL query will select all highways that are within 1 km distance from the city of Firenze:

   SELECT * 
   FROM HighWays
   WHERE PtDistWithin(
     HighWays.Geometry,
     (SELECT Geometry FROM Towns WHERE Name = 'Firenze'),
     1000
   )

   The SELECT Geometry FROM Towns WHERE Name = 'Firenze' subquery selects the point geometry that represents the city of Firenze. This point is then used in the PtDistWithin function to test for each highway geometry and check whether it is within a distance of 1,000 meters.

   Tip

   An introduction to SQL is out of the scope of this book, but you can find a thorough tutorial on using SpatiaLite at http://www.gaia-gis.it/gaia-sins/spatialite-cookbook/index.html. Additionally, to get an overview of all the spatial functionalities offered by SpatiaLite, visit http://www.gaia-gis.it/gaia-sins/spatialite-sql-4.2.0.html.

3. When the query is entered, we can click on Execute (F5) to run the query. The query results will be displayed in a tabular form in the result section below the SQL query input area, as shown in the following screenshot:
   
4. To display the query results on the map, we need to activate the Load as new layer option below the results table. Make sure you select the correct Geometry column (Geometry).
5. Once you have configured these settings, you can click on Load now! to load the query result as a new map layer. As you can see in the preceding screenshot, only one of the highways (represented by the wide blue line) is within 1 km of the city of Firenze.

Aggregating data in SpatiaLite

Another thing that databases are really good at is aggregating data. For example, the following SQL query will count the number of towns per region:

SELECT Regions.Name, Regions.Geometry, count(*) as Count
FROM Regions
JOIN Towns
  ON Within(Towns.Geometry,Regions.Geometry)
GROUP BY Regions.Name

This can be used to create a new layer of regions that includes a Count attribute. This tells the number of towns in the region, as shown in this screenshot:

Note

Although we have used SpatiaLite in this example, the tools and workflow presented here work just as well with PostGIS databases. It is worth noting, however, that SpatiaLite and PostGIS often use slightly different function names. Therefore, it is usually necessary to adjust the SQL queries accordingly.