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 “the joining” 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 “relationship” between the tables, never a “juncture”.
For a long time, GIS software has, only, allowed to work with joins. The relationships do not stand most spatial operators applicable to joins.
One of the reasons for this limitation was the restricted subset of the SQL language for the geographic layers. Whether in shapefile files , or others , the requests applicable were limited to a pre-formatted clause of the following type “SELECT * FROM the WHERE table” and it was impossible to build the end of the query, i.e. the WHERE clause.
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, been the case), by using the “virtual layers”. They have appeared as plugin in version 2.10, and integrated into QGis body in version 2.14.
The virtual layers of QGis
Features of virtual layers
- You can create a new layer virtual with the button “New virtual layer”:
• One virtual layer can be created from a layer selection (right click in the window layer) - If you want to filter a layer with a join, Qgis will propose to create a virtual layer
- In DBMANAGER, there is a new entry “Virtual Layers” where you can use the SQL Window to create a virtual layer
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.
Using virtual layers
Let’s discuss the following example:
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.
We have a common shapefile in the form of polygons, where we find an INSEE_COM attribute with the INSEE code of the municipality.
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, 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.
To create the virtual layer, click on the button Add/EditVirtual layer
We use the Import button to add the two layers in the IntegratedLayers list.
In the window Query, enter the following SQL command:
SELECT * FROM Municipalities29, CCAreas where INSEE_COM = INSEEYou can (and must) test the syntax of your SQL query with the Test button
Click OK and the virtual layer is added to the map.
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:
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
We get only 148 registrations, corresponding to the number of campsites present in the Excel table.
