{"id":8449,"date":"2019-04-02T02:06:36","date_gmt":"2019-04-02T00:06:36","guid":{"rendered":"http:\/\/www.sigterritoires.fr\/?p=8449"},"modified":"2019-04-05T09:25:46","modified_gmt":"2019-04-05T07:25:46","slug":"requests-and-views-of-a-spatialite-database-with-qgis-2-8","status":"publish","type":"post","link":"https:\/\/www.sigterritoires.fr\/index.php\/en\/requests-and-views-of-a-spatialite-database-with-qgis-2-8\/","title":{"rendered":"Requests and views of a SpatiaLite database with QGis 2.8"},"content":{"rendered":"\n

One of the main differences between data management with shapefiles and\na database is the ability to create different \u00ab\u00a0views\u00a0\u00bb for the same\ndata. In this article, the last in the series devoted to SpatiaLite with QGis,\nwe’ll discuss how to build SQL queries to generate SpatiaLite views.
\nDB Manager has a SQL window that allows the construction and execution of SQL\nqueries on a database. This article shows how to use the SQL window to query a\ntable and create a SpatiaLite spatial view. <\/p>\n\n\n\n\n\n\n\n

How to create a SQL query<\/strong> <\/p>\n\n\n\n

The different databases support all (or some) SQL commands. SQLite supports\nmuch of the ANSI SQL92 standard, but not all. For a complete list of supported\nSQL operations, visit https:\/\/sqlite.org\/lang.html<\/a> \n\nTo create an SQL query, do the following:
\n1. Open the Database Manager<\/em><\/strong> by clicking Database Manager<\/em><\/strong>\nunder Database<\/em><\/strong> .
\n2. In the tree view, locate and select the database on which you want to run an\nSQL query.
\n3. Click Database |<\/em><\/strong>SQL window<\/em><\/strong> , or press F2<\/em><\/strong>\non your keyboard, to open the SQL window.
\n4. Enter an SQL query in the text box at the top. Click on the execute<\/em><\/strong>\nbutton or F5<\/em><\/strong> on your keyboard to execute the SQL query on the\ndatabase. The results of the query will be displayed in the results area at the\nbottom, and the number of lines processed and the run time will be displayed\nnext to the button.   Run<\/em><\/strong> . An example of a successful\nexecution request is shown in the following screenshot: \n\n\n\n<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

You can store any query by entering a name in the text box at the top,\nand then clicking the Store<\/em><\/strong> button . To load and execute the\nstored query, select the query name from the drop-down menu at the top. To\ndelete a stored query, select the query from the drop-down menu and then click\nthe Clear<\/em><\/strong> button (next to Stocker<\/em><\/strong> ). <\/p>\n\n\n\n

How to create a spatial view<\/strong> <\/p>\n\n\n\n

Creating a spatial view on a SpatiaLite database, with the SQL window in\nthe database manager, is a two-step process. <\/p>\n\n\n\n

  • The first step is to create a\nview containing a field with unique identifiers and the geometry<\/em> column <\/li>
  • The second step is to insert a\nnew record in the views_geometry_columns<\/em> table to save the view as a spatial view <\/li><\/ul>\n\n\n\n

    \n\n\n\nWe will create a spatial view on the Hotels\ntable to show all hotels in Brest and Quimper; To this end, proceed as follows\n:
    \n1. Open the database manager.
    \n2. In the tree view, locate<\/em><\/strong> and select the BaseTest.sqlite<\/em><\/strong>\ndatabase .<\/em><\/strong>
    \n3. Click Database |<\/em><\/strong>SQL window<\/em><\/strong> , or press F2<\/em><\/strong>\non your keyboard, to open the SQL window.
    \n4. Enter the following query:
    CREATE VIEW the_hotels_brest_quimper as<\/em><\/strong>
    SELECT h.pk as ROWID, h.Name, h.PostalCode, h.Commune, h.geom from hotels\nas h<\/em><\/strong>
    WHERE h.Commune in (‘Brest’, ‘Quimper’);<\/em><\/strong><\/p>\n\n\n\n

    \"\"<\/figure>\n\n\n\n

    h<\/em><\/strong> or any letter is suitable as a table prefix for the\nfield names. <\/p>\n\n\n\n

    The SELECT<\/em><\/strong> clause sets the fields that will be visible in\nthe view and the WHERE<\/em><\/strong> clause defines the selection criteria for\nthe records that will be visible in the view. <\/p>\n\n\n\n

    In the CREATE VIEW<\/em><\/strong> query ,   two fields are required in\nthe SELECT statement<\/em><\/strong> : the unique identifier field that must be\nrenamed to ROWID<\/em><\/strong> and the field   geometry. You must\nrename the unique identifier in   ROWID or you will not be\nable to save the view as a spatial view. \n\n5. Click the execute<\/em><\/strong> button to\ncreate the view.
    \nNow that the view is created, we need to save it as a spatial view by inserting\na new line into the views_geometry_columns<\/em> table . This table links the\ngeometry of the view to the geometry of the table in which the selection takes\nplace.
    \n6. In the SQL window, click the clear<\/em><\/strong> button to clear the text\nbox of the SQL query.
    \n7. Enter the following query:
    INSERT INTO views_geometry_columns (view_name, view_geometry, view_rowid,\nf_table_name, f_geometry_column, read_only)<\/em><\/strong>
    VALUES (‘les_hotels_brest_quimper’, ‘geom’, ‘rowid’, ‘hotels’, ‘geom’,\n1);<\/em><\/strong><\/p>\n\n\n\n

    \"\"<\/figure>\n\n\n\n

    The INSERT<\/em><\/strong> query has values for six fields: <\/p>\n\n\n\n

    • view_name<\/em> : This contains the name of the view that you want\nto register as a spatial view. <\/li>
    • view_geometry<\/em> : It contains the name of the geometry field of the\nview. <\/li>
    • view_rowid<\/em> : It contains the name of the rowid field. Note that he must be rowid. If the rowid field is named otherwise, you will\nneed to recreate the view with a named field   rowid <\/li>
    • f_table_name<\/em> : the name of the table whose view is a selection. <\/li>
    • f_geometry_column<\/em> : the name of the geometry field in the table whose\nview is a selection <\/li>
    • read_only<\/em> : in this field, enter 1 so that the spatial view\nis   read-only, or   enter 0 so that the spatial view\nis   in read \/ write. Note that since QGIS version 2.6.0, read \/ write views can not be edited\nin QGIS Desktop. However, views can be editable in\nsome plugins or with SQL queries. <\/li><\/ul>\n\n\n\n

      8. Click the execute<\/em><\/strong> button to create the view.
      \nThe view is now saved as a spatial view and can be added as a layer in the QGis\nmap window, just like any other SpatiaLite spatial table. <\/p>\n\n\n\n

      How to remove<\/strong>  <\/strong> a spatial view<\/strong> <\/p>\n\n\n\n

      To remove a spatial view requires that you delete the spatial view\ntable, but also the relevant entry in the view_geometry_columns<\/em><\/strong>\ntable .
      \nTo delete the table   spatial view, use the DROP VIEW SQL<\/em><\/strong>\ncommand . For example, to remove the view from the_hotels_brest_quimper<\/em>\n, you will need to execute the following SQL command: <\/p>\n\n\n\n

      DROP VIEW<\/em><\/strong> les_hotels_brest_quimper<\/em><\/strong>\n<\/p>\n\n\n\n

      With the view deleted, the last step is to delete the corresponding\nentry in the view_geometry_columns<\/em> table using the SQL DELETE<\/em><\/strong>\ncommand .
      \nFor example, to remove the writing about hotels in Brest and Quimper, you will\nneed to run the   following SQL command: <\/p>\n\n\n\n

      DELETE FROM views_geometry_columns<\/em><\/strong>
      \nWHERE<\/em><\/strong> view_name =\n‘les_hotes_brest_quimper’<\/em><\/strong> ;<\/em><\/strong> <\/p>\n","protected":false},"excerpt":{"rendered":"

      One of the main differences between data management with shapefiles and a database is the ability to create different \u00ab\u00a0views\u00a0\u00bb for the same data. In this article, the last in the series devoted to SpatiaLite with…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"give_campaign_id":0,"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"sfsi_plus_gutenberg_text_before_share":"","sfsi_plus_gutenberg_show_text_before_share":"","sfsi_plus_gutenberg_icon_type":"","sfsi_plus_gutenberg_icon_alignemt":"","sfsi_plus_gutenburg_max_per_row":"","_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_post_was_ever_published":false},"categories":[1260],"tags":[],"class_list":["post-8449","post","type-post","status-publish","format-standard","hentry","category-non-classe-en"],"aioseo_notices":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p6XU0A-2ch","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/posts\/8449","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/comments?post=8449"}],"version-history":[{"count":0,"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/posts\/8449\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/media?parent=8449"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/categories?post=8449"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/tags?post=8449"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}