Databases in Context

“Spatial databases are enhanced for storage and fast retrieval of data that defines a geometric space. The data is stored as coordinates, lines, points, polygon and topology”

In this module, we will look at spatialite databases and explore ways in which QGIS can interact with them. We will also learn about database syntax and how to do simple geoprocessing.

You try:

Goal: To learn how to create and populate databases and run SQL queries

Problem: The government wants to expand OR Tambo international airport by a radius of 50km. Visualize the extent of the new airport do all processing in the database.

  • Load the listed layer in the table into QGIS.
  • Create a new spatialite database (use the trick shown in the table).
  • Filter the countries layer using the SA_filter.
  • Do a spatial selection to select urban areas and airports that are within South Africa.
  • Load layers into the database by using the DB manager. Use the import options specified right and name the layers as countries, airports and urban_areas respectively.
  • Open the SQL window in DB Manager.
  • Create a table 'buffer' using the expression and load the resultant table in QGIS.
  • Calculate the total urban area that will be affected by the airport expansion. Create a table 'urban_precinct' using the expression provided.
  • In QGIS add a column 'area' to the 'urban_precinct' layer and calculate the affected area.

Name Expectation

Layers from natural earth (ne.sqlite)

ne_10m_airports, ne_10m_urban_areas, ne_10m_admin_0_countries

Make a spatialite database

Use the create layer option in QGIS to make a new spatialite database.

SA_filter

"sov_a3" = 'ZAF'

Import Options

Toggle import selected, create spatial index, geom, reproject to target SRID 3857

buffer layer Expression

select id,st_buffer(geom,50000) as geometry from airports where name like 'OR T%'

urban_precinct layer expression

select a.id,st_union(st_intersection(a.geom,b.geom)) as geom from buffer as a join urban_areas as b where st_intersects(a.geom,b.geom)

More about

Databases are very flexible in handling large amounts of data. They offer many advantages over other data sources and it is encouraged to use them as a data source. Spatial databases have started to be used also storing raster data and besides acting as a data store they can also be used for geoprocessing. The syntax that is used in the database is called SQL (Structured Query Language). This allows easy manipulation of the data and derivation of new products using the same data source.

GeoSpatial databases extend the standard set of functions you can use in your SQL queries to support 'spatial queries' - functions that specifically deal with spatial data. There is a very long list of spatial functions that can do everything from generating new geometries, cleaning geometries, calculating areas and distances, checking the relationship between geometries and so on.

You can create views in your database that save and replay queries. You can also add 'query layers' in QGIS which are layers based on a specific SQL query and that can dynamically transform the data in your database as it is being loaded.

If you use a file-based database like spatialite or GeoPackage, they offer a big advantage: You can place multiple layers into that database and then share that database as a single file.

Check your knowledge:

  1. A database is:
    1. A storage device where GIS activities will take place
    2. A GIS database representing vector features.
    3. A type of data store that is used to store geographic data and non geographic
  2. In which instance would you prefer a spatial database:
    1. When you have a a lot of CSV files or spreadsheets containing data for analysis
    2. When you need to do some advanced processing of the data.
    3. Generally, whenever I get hold of a data source I should load it into a spatial database.
  3. Buffers calculated in the database are identical to the ones in QGIS:
    1. True
    2. False