Mastering QGIS
上QQ阅读APP看书，第一时间看更新
上一章目录下一章

Creating queries and views

DB Manager has a SQL window that allows SQL queries to be executed against the database. This section will explain how to use the SQL window to query a table and create a spatial view in SpatiaLite.

Tip

Different databases support different SQL commands. SQLite supports much of, but not all, the standard SQL. For a complete listing of supported SQL operations, visit http://www.sqlite.org/sessions/lang.html.

Creating a SQL query

To create a SQL query, perform the following steps:

  1. Open DB Manager by clicking on DB Manager under Database.
  2. In the Tree panel, navigate to and select the database on which you wish to perform a SQL query.
  3. Navigate to Database | SQL window, or press F2 on your keyboard, to open the SQL window.
  4. Enter a SQL query in the textbox at the top. Click on the Execute button or F5 on your keyboard to execute the SQL query against the database. The results of the query will be displayed in the results box at the bottom, and the number of affected rows and execution time will appear next to the Execute button. An example of a successfully run query is shown in the following screenshot:
    Creating a SQL query

You can store any query by entering a name in the textbox at the top and then click on the Store button. To load and run the stored query, select the query name in the drop-down box at the top. To delete a stored query, select the query in the drop-down box and then click on the Delete button.

Creating a spatial view

Creating a spatial view on a SpatiaLite database using the SQL window in DB Manager is a two-step process. The first step is to create a view that includes 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 register the view as a spatial view. In this section, we will create a spatial view on the Waterfalls table to show all the waterfalls in the Mowich Lake quad; to do this, perform the following steps:

  1. Open DB Manager by clicking on DB Manager under Database.
  2. In the Tree panel, navigate to and select the GiffordPinochet.sqlite database.
  3. Navigate to Database | SQL window, or press F2 on your keyboard, to open the SQL window.
  4. Enter the following query: 
    CREATE VIEW mowich_lake_waterfalls AS
SELECT w.pk as ROWID, w.NAME, w.TYPE, w.geom from Waterfalls as w
WHERE w.quadname = 'Mowich Lake';

    In the CREATE VIEW query, two fields are required to be included in the SELECT statement: the unique identifier field should be renamed to ROWID and the geometry field. You must rename the unique identifier to ROWID or the view cannot be registered as a spatial view.

  5. Click on the Execute button to create the view. The following screenshot displays a successfully written and executed view of the Waterfalls table:
    Creating a spatial view

    Now that the view is created, we need to register it as a spatial view by inserting a new row in the views_geometry_columns table. This table links the view's geometry to the geometry of the table it selects from.

  6. In the SQL window, click on the Clear button to clear the SQL query textbox.
  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('mowich_lake_waterfalls', 'geom', 'rowid', 'waterfalls', 'geom', 1);

    In this INSERT query, six fields have values inserted in them.

    • view_name: This contains the name of the view that we wish to register as spatial.
    • view_geometry: This contains the name of the geometry field in the view.
    • view_rowid: This contains the name of the rowid field. Note that it must be rowid. If the rowid field is named something else, you will need to recreate the view with a rowid field.
    • f_table_name: The name of the table the view is selecting from.
    • f_geometry_column: The name of the geometry field in the table the view is selecting from.
    • read_only: In this field, enter 1 for the spatial view to be read-only and enter 0 for the spatial view to be read/write. Note that as of version 2.6.0 of QGIS, views set as read/write cannot be edited in QGIS Desktop. However, views may be editable in some plugins or with SQL queries.
  8. Click on the Execute button to create the view. The following screenshot displays a successfully written and executed view of the Waterfalls table:
    Creating a spatial view

The view is now registered as spatial and can be added to the QGIS Desktop canvas like any other SpatiaLite spatial table.

Dropping a spatial view

Dropping a spatial view requires that you drop the spatial view table and delete the relating entry in the view_geometry_columns table.

To drop the spatial view table, use the SQL DROP VIEW command. For example, to drop the mowich_lake_waterfalls view, you will need to execute the following SQL command:

DROP VIEW mowich_lake_waterfalls

With the view dropped, the final step is to delete the related entry in the view_geometry_columns table by using the SQL DELETE command. For example, to drop the entry related to the mowich_lake_waterfalls view, you will need to execute the following SQL command:

DELETE FROM views_geometry_columns
WHERE view_name = 'mowich_lake_waterfalls';

Tip

Downloading the example code

You can download the example code files from your account at http://www.packtpub.com for all the Packt Publishing books you have purchased. If you purchased this book elsewhere, you can visit http://www.packtpub.com/support and register to have the files e-mailed directly to you.

上一章目录下一章