{"id":6145,"date":"2018-08-28T09:00:34","date_gmt":"2018-08-28T07:00:34","guid":{"rendered":"http:\/\/www.sigterritoires.fr\/?p=6145"},"modified":"2018-10-17T12:05:34","modified_gmt":"2018-10-17T10:05:34","slug":"1-n-joins-in-qgis-virtual-layers","status":"publish","type":"post","link":"https:\/\/www.sigterritoires.fr\/index.php\/en\/1-n-joins-in-qgis-virtual-layers\/","title":{"rendered":"1-n joins in QGis: virtual layers"},"content":{"rendered":"\n

One of the most common techniques in GIS in order to treat non-spatial data is that of the join. We use a geographical layer having geometries, as support for another layer having no location data. To this end, we will use a common field to both tables, that allows \u201cthe joining\u201d the records from the geographic table to those of the non- geographic table. Therefore we created a new virtual table where the attributes of the non- geographic table can be used to be mapped with the geometry of the first table. For this join to work it is necessary that for every registration of geographical table corresponds a record in the non- geographic table, and only one. Because if more than one is found, the join cannot work. In that case we have a \u201crelationship\u201d between the tables, never a \u201cjuncture\u201d.<\/p>\n\n\n\n

For a long time, GIS software has, only, allowed to work with joins. The relationships do not stand most spatial operators applicable to joins.<\/p><\/p>\n\n\n\n

One of the reasons for this limitation was the restricted subset of the SQL language for the geographic layers. Whether in shapefile<\/a> files , or others , the requests applicable were limited to a pre-formatted clause \u00a0of the following type \u00ab\u00a0SELECT * FROM the WHERE table\u201d \u00a0 and it was impossible to build the end of the query, i.e. the WHERE clause.<\/p>\n\n\n\n

To be able to create a virtual table with several records in table B corresponding to a record in table A (cardinality type 1, n) you have to use the SQL table join capability and therefore use two tables in the query . Now, this is possible with QGis (that has, not always, \u00a0been the case), by using the \u00a0 \u201cvirtual layers\u201d. They have appeared as plugin in version 2.10, and integrated into QGis body in version 2.14.<\/p>\n\n\n\n

The<\/strong> virtual<\/strong> layers<\/strong> of QGis<\/strong><\/h2><\/p>\n\n\n\n

Features<\/strong> of<\/strong> virtual<\/strong> layers<\/strong><\/h3><\/p>\n\n\n\n
    \n
  • You can create a new layer virtual with the button \u00a0 \u201cNew virtual layer\u201d: \u2022 One virtual layer can be created from a layer selection (right click in the window layer)<\/li>\n
  • If you want to filter a layer with a join, Qgis will propose to create a virtual layer<\/li>\n
  • In DBMANAGER, there is a new entry \u201cVirtual Layers\u201d where you can use the SQL Window to create a virtual layer<\/li>\n<\/ul>\n\n\n\n

    The generation of the virtual layers is saved in the file project. The virtual layer behaves as a native table, especially with the fields originating in a join.<\/p>\n\n\n\n

    Using<\/strong> virtual<\/strong> layers<\/strong><\/h3><\/p>\n\n\n\n

    Let\u2019s discuss the following example:<\/p>\n\n\n\n

    We have an excel file containing the different municipal areas (campsites, large surfaces , natural , parking , …). This table has an INSEE (French National Institute for Statistical and Economic Studies) column where the code of the commune on which the area is located, can be found.<\/p>\n\n\n\n

    We have a common shapefile in the form of polygons, where we find an INSEE_COM attribute with the INSEE code of the municipality.<\/p>\n\n\n\n

    What we want is having a layer where each area is represented by the polygon of the corresponding commune. Instead of having the X polygons of the municipal layer, <\/em><\/strong>we want to have the Y polygons corresponding to the Y rows of the Excel table of the municipal areas.We load the Municipal layer in QGis, as well as the Excel CCAreas table. To load the Excel table you must activate and use the Spreadsheet Layers plugin.<\/p>\n\n\n\n

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

    To create the virtual layer, click on the button Add<\/em><\/strong>\/<\/em><\/strong>Edit<\/em><\/strong>Virtual<\/em><\/strong> layer\u00a0 <\/em><\/strong><\/p>\n\n\n\n

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

    We use the Import<\/em><\/strong> button to add the two layers in the Integrated<\/em><\/strong>Layers<\/em><\/strong> list.<\/p>\n\n\n\n

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

    In the window Query, enter the following SQL command:<\/p>\n\n\n\n

    SELECT * FROM<\/em> Municipalities29<\/em>, CCAreas<\/em> where<\/em> INSEE_COM = INSEE<\/em>You can (and must) test the syntax of your SQL query with the Test<\/em><\/strong> button \u00a0<\/p>\n\n\n\n

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

    Click OK and the virtual layer is added to the map.<\/p>\n\n\n\n

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

    Notice the two missing municipalities because they have no municipal area in the Excel file. If we display the attribute table of the virtual layer:<\/p>\n\n\n\n

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

    We can see that several records correspond to a same municipality, and that the registration number (419) is the number of rows in the Excel table and not the number of municipalities of the shapefile (283).We can go further and add a WHERE clause to select, just, a section of the municipal areas, e.g. camping<\/p>\n\n\n\n

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

    We get only 148 registrations, corresponding to the number of campsites present in the Excel table.<\/p>\n\n\n\n

    \"\"<\/figure>\n","protected":false},"excerpt":{"rendered":"

    One of the most common techniques in GIS in order to treat non-spatial data is that of the join. We use a geographical layer having geometries, as support for another layer having no location data. To…<\/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":[1296],"tags":[1433,1435,1386,1437,1439],"class_list":["post-6145","post","type-post","status-publish","format-standard","hentry","category-posts-en","tag-cardinality","tag-join","tag-qgis-en","tag-table-en","tag-virtual-layer-en"],"aioseo_notices":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p6XU0A-1B7","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/posts\/6145","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=6145"}],"version-history":[{"count":0,"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/posts\/6145\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/media?parent=6145"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/categories?post=6145"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/tags?post=6145"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}