One of the main differences between data management with shapefiles and
a database is the ability to create different “views” for the same
data. In this article, the last in the series devoted to SpatiaLite with QGis,
we’ll discuss how to build SQL queries to generate SpatiaLite views.
DB Manager has a SQL window that allows the construction and execution of SQL
queries on a database. This article shows how to use the SQL window to query a
table and create a SpatiaLite spatial view.
How to create a SQL query
The different databases support all (or some) SQL commands. SQLite supports
much of the ANSI SQL92 standard, but not all. For a complete list of supported
SQL operations, visit https://sqlite.org/lang.html
To create an SQL query, do the following:
1. Open the Database Manager by clicking Database Manager
under Database .
2. In the tree view, locate and select the database on which you want to run an
SQL query.
3. Click Database |SQL window , or press F2
on your keyboard, to open the SQL window.
4. Enter an SQL query in the text box at the top. Click on the execute
button or F5 on your keyboard to execute the SQL query on the
database. The results of the query will be displayed in the results area at the
bottom, and the number of lines processed and the run time will be displayed
next to the button. Run . An example of a successful
execution request is shown in the following screenshot:
You can store any query by entering a name in the text box at the top, and then clicking the Store button . To load and execute the stored query, select the query name from the drop-down menu at the top. To delete a stored query, select the query from the drop-down menu and then click the Clear button (next to Stocker ).
How to create a spatial view
Creating a spatial view on a SpatiaLite database, with the SQL window in the database manager, is a two-step process.
- The first step is to create a view containing a field with unique identifiers and the geometry column
- The second step is to insert a new record in the views_geometry_columns table to save the view as a spatial view
We will create a spatial view on the Hotels
table to show all hotels in Brest and Quimper; To this end, proceed as follows
:
1. Open the database manager.
2. In the tree view, locate and select the BaseTest.sqlite
database .
3. Click Database |SQL window , or press F2
on your keyboard, to open the SQL window.
4. Enter the following query:
CREATE VIEW the_hotels_brest_quimper as
SELECT h.pk as ROWID, h.Name, h.PostalCode, h.Commune, h.geom from hotels
as h
WHERE h.Commune in (‘Brest’, ‘Quimper’);
h or any letter is suitable as a table prefix for the field names.
The SELECT clause sets the fields that will be visible in the view and the WHERE clause defines the selection criteria for the records that will be visible in the view.
In the CREATE VIEW query , two fields are required in
the SELECT statement : the unique identifier field that must be
renamed to ROWID and the field geometry. You must
rename the unique identifier in ROWID or you will not be
able to save the view as a spatial view.
5. Click the execute button to
create the view.
Now that the view is created, we need to save it as a spatial view by inserting
a new line into the views_geometry_columns table . This table links the
geometry of the view to the geometry of the table in which the selection takes
place.
6. In the SQL window, click the clear button to clear the text
box of the SQL query.
7. Enter the following query:
INSERT INTO views_geometry_columns (view_name, view_geometry, view_rowid,
f_table_name, f_geometry_column, read_only)
VALUES (‘les_hotels_brest_quimper’, ‘geom’, ‘rowid’, ‘hotels’, ‘geom’,
1);
The INSERT query has values for six fields:
- view_name : This contains the name of the view that you want to register as a spatial view.
- view_geometry : It contains the name of the geometry field of the view.
- view_rowid : It contains the name of the rowid field. Note that he must be rowid. If the rowid field is named otherwise, you will need to recreate the view with a named field rowid
- f_table_name : the name of the table whose view is a selection.
- f_geometry_column : the name of the geometry field in the table whose view is a selection
- read_only : in this field, enter 1 so that the spatial view is read-only, or enter 0 so that the spatial view is in read / write. Note that since QGIS version 2.6.0, read / write views can not be edited in QGIS Desktop. However, views can be editable in some plugins or with SQL queries.
8. Click the execute button to create the view.
The view is now saved as a spatial view and can be added as a layer in the QGis
map window, just like any other SpatiaLite spatial table.
How to remove a spatial view
To remove a spatial view requires that you delete the spatial view
table, but also the relevant entry in the view_geometry_columns
table .
To delete the table spatial view, use the DROP VIEW SQL
command . For example, to remove the view from the_hotels_brest_quimper
, you will need to execute the following SQL command:
DROP VIEW les_hotels_brest_quimper
With the view deleted, the last step is to delete the corresponding
entry in the view_geometry_columns table using the SQL DELETE
command .
For example, to remove the writing about hotels in Brest and Quimper, you will
need to run the following SQL command:
DELETE FROM views_geometry_columns
WHERE view_name =
‘les_hotes_brest_quimper’ ;
