This tutorial provides you with the necessary steps
to manage databases in QGIS. Although QGIS can handle several
types of databases, we used SpatiaLite because it provides a lot of features
without the need for a particular installation and very little administration
work.
By
using the database manager included in QGis, you can perform a certain number
of database operations.
The
main operations are: indexes creation, spatial and non-spatial views, import
and export data, queries execution. After
discussing the QGis database manager and the SpatiaLite usage guidelines in
this tutorial, you’ll be well equipped to write more complex queries and take
full advantage of the SQL and SpatiaLite SQL commands.
Ales Nordeen informed me about the availability of a set of tutorials on SQLite. The tutorial covers the basics of SQLite (introduction, installation guide, database and table operations). It also introduces data types, keys, clauses, queries, and joins, SQLite Index, Trigger, View, and Functions.
[/ stextbox]Database fundamentals
A database is a structured set of data. Databases
offer several advantages over data stored in a simple file format, such as
shapefile or KML. Benefits include complex
queries, complex relationships, scalability, security, and data integrity, to
name a few.
There
are several types of databases. However, the most common type of
database is the Relational Database. In
this tutorial we’ll refer to this type.
Database tables
A relational database stores the data in tables.
A table is made up 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 contain any number of records. However,
each field is uniquely named and stores a specific data type.
A data type restricts the information that can be stored in a
field, and it is very important that the appropriate data type and its
associated parameters be correctly chosen for each field in a table.
The common data types are:
•
Integer
•
Variable / real / decimal
•
Text
•
Date
Each
of these data types can have a set of additional constraints, such as setting a
default value, limiting the size of the field, or prohibiting null values.
In
addition to the common data types that were mentioned earlier, some databases
support the “geometry” field type, allowing the following types of
geometry to be stored:
•
Point
•
Multi-point
•
Line
•
Multi-line
•
Polygon
•
Multi-polygon
The
multipoint / line / polygon types store multipart geometries: for a single
record of the table, several geometry parts are associated with it.
Shapefiles
store geospatial data in multi-type geometry. Using
this type of geometry is a good practice if you plan to convert to or from this
format
Relationships between tables
A given relationship between tables connects the records of
these tables. The advantage of linking tables
is to reduce data redundancy and increase data integrity. In
order to link two tables, each table must contain an indexed key field.
Organizing
tables to reduce redundancy is called normalization. Usually,
normalization involves splitting large tables into smaller and, therefore, less
redundant tables, followed by defining relationships between tables.
A
field can be defined as an index. A field defined as an index must
contain only unique values for each record, and therefore, it can be used to
uniquely identify each record in a table. An
index is useful for two reasons. First, it allows records to be
quickly found during a query (if the indexed field is part of the query).
Second, an index can be defined to be a primary key for a
table, making it possible to build relationships between tables.
A
primary key consists of one or more fields that identify a record within a
table.
An
external key consists of one or more fields that identify a record in another table.
When
a relationship is created, a record in one table is linked to a record in
another table. With related tables, more
complex queries can be executed and redundancy in the database can be reduced.
Structured query language
(SQL)
Structured Query Language (SQL) is a language designed to
manage databases as well as the data they contain. Addressing
SQL exhaustively is a task beyond the scope of this tutorial. We
will resume here some basic elements of the language that we will use in this
tutorial.
Note: A complete list of SQLite SQL commands is available
at http://www.sqlite.org/lang.html
. A complete list of SQL commands for the SpatialLite
extension is available at http://www.gaia-gis.it/gaia-sins/spatialite-sql-4.2.0.html
.
SQL provides functions to select, insert, delete, and update
data. Four frequently used functions of SQL data are:
•
SELECT: This function retrieves a dataset of one or more tables based on an
expression. A query is basically a structure
of type
SELECT <field (s)> FROM <table>
WHERE <operator> <value>;
where <field (s)> is the name of the field whose
values are to be retrieved and <table> is the table on which the
query is to be executed. The <operator> part
checks the condition (such as =,> =, like) and <value> is the
value to compare to the field.
• INSERT: This function inserts new records into a table.
for example
INSERT INTO <table> (<field1>, <field2>,
<field3>) VALUES (<value1>, <value2>,
<value3>);
inserts three values in their respective domains, where <value1>
, <value2> , and <value3> are stored in
<field1> , <field2>
, and <field3> of <table> .
• UPDATE: This function modifies an existing record in a
table. for example
UPDATE <table>
SET <field> = <value>;
This statement updates the value of a field, where <value>
is stored in <field> of <table>
.
• DELETE: Deletes records from a table. For
example, the following statement deletes all records that satisfy the WHERE
clause:
DELETE FROM <table> WHERE
<field> <operator> <value>;
where <table> is the table where the records
must be deleted, <field> is the name of the field, <operator>
checks the equality, and <value> is the value to check for the
field.
Another SQL function of interest is VIEW .
A view is a stored query that looks like a table but is
actually dynamically built when called to view. To
create a view, we simply precede a SELECT statement with CREATE VIEW
AS <view_name> and this will create a view named <view_name>
. You can then process the new
view as if it were a table.
We will now turn to the following points:
Chapter
1: Creating a spatial database Spatialite – Creating a database, Importing
data from a shapefile
Chapter
2: Import Tabular (Non-Spatial) Data into a Database
spatial
– Import an Excel table, create geometry from XY fields, export data from
SpatiaLite to shapefile.
Chapter
3: Managing Tables with SpatiaLite – Create, Rename, Edit Properties,
Delete, Empty a SpatiaLite table
Chapter
4: Creating Queries and Spatial Views – Creating an SQL Query, Creating a
Spatial View, Removing a Spatial View
