This chapter covers the creation and editing of spatial databases using QGIS. The core concepts of databases will be briefly reviewed; however, we have assumed that you are generally familiar with database concepts and SQL for most of the content covered in this chapter. The topics that we will cover in this chapter are as follows:
- Core concepts of database construction
- Creating spatial databases
- Importing and exporting data
- Editing databases
- Creating queries
- Creating views
A database is a structured collection of data. Databases provide multiple benefits over data stored in a flat file format, such as a shapefile or KML. These benefits include complex queries, complex relationships, scalability, security, data integrity, and transactions, to name a few. Using databases to store geospatial data is relatively easy, considering the aforementioned benefits.
A relational database stores data in tables. A table is composed of rows and columns, where each row is a single data record and each column stores a field value associated with each record. A table can have any number of records; however, each field is uniquely named and stores a specific type of data.
A data type restricts the information that can be stored in a field, and it is very important that an appropriate data type, and its associated parameters, be selected for each field in a table. The common data types are as follows:
- Integer
- Float/real/decimal
- Text
- Date
Each of these data types can have additional constraints set, such as setting a default value, restricting the field size, or prohibiting null values.
In addition to the common data types mentioned previously, some databases support the geometry field type, allowing the following geometry types to be stored:
- Point
- Multi-point
- Line
- Multi-line
- Polygon
- Multi-polygon
The multi-point/line/polygon types store multi-part geometries so that one record has multiple geometry parts associated with it.
A table relationship connects records between tables. The benefit of relating tables is reducing data redundancy and increasing data integrity. In order to relate two tables together, each table must contain fields that have values in common. For example, one table may list all of the parcels in a city, while a second table lists all of the land owners in the city. The parcels table can have an OwnerID field that also exists in the owners table, so that you can relate the correct owner from the owners table to the correct parcel in the parcels table. This is beneficial in cases where one owner owns multiple parcels, but we only have to store the owner information one time, which saves space and is easier to update as we only have to update owner information in one record.
A field can be defined as an index. A field set as an index must only contain values that are unique for each record, and therefore, it can be used to identify each record in a table uniquely. An index is useful for two reasons. Firstly, it allows records to be quickly found during a query if the indexed field is part of the query. Secondly, an index can be set to be a primary key for a table, allowing for table relationships to be built.
A primary key is one or more fields that uniquely identify a record in its own table. A foreign key is one or more fields that uniquely identify a record in another table. When a relationship is created, a record(s) from one table is linked to a record(s) of another table. With related tables, more complex queries can be executed and redundancy in the database can be reduced.
Structured Query Language (SQL) is a language designed to manage databases and the data contained within them. Covering SQL is a large undertaking and is outside the scope of this book, so we will only cover a quick refresher that is relevant to this chapter.
SQL provides functions to select, insert, delete, and update data. Four commonly used SQL data functions are:
SELECT: This retrieves a temporary set of data from one or more tables based on an expression. A basic query isSELECT <field(s)> FROM <table> WHERE <field> <operator> <value>;, where<field>is the name of the field from which values must be retrieved and<table>is the table on which the query must be executed. The<operator>part checks for equality (such as=,>=, andLIKE) and<value>is the value to compare against the field.INSERT: This inserts new records into a table. TheINSERT INTO <table> (<field1>, <field2>, <field3>) VALUES (<value1>, <value2>, <value3>);statement inserts three values into their three respective fields, where<value1>,<value2>, and<value3>are stored in<field1>,<field2>, and<field3>of<table>.UPDATE: This modifies an existing record in a table. TheUPDATE <table> SET <field> = <value>;statement updates one field's value, where<value>is stored in<field>of<table>.DELETE: This deletes record(s) from a table. The following statement deletes all records matching theWHEREclause:DELETE FROM <table> WHERE <field> <operator> <value>;, where<table>is the table to delete records from,<field>is the name of the field,<operator>checks for equality, and<value>is the value to check against the field.
Another SQL feature of interest is view. A view is a stored query that is presented as a table, but is actually built dynamically when the view is accessed. To create a view, simply preface a SELECT statement with CREATE VIEW <view_name> AS and a view named <view_name> will be created. You can then treat the new view as if it were a table.