2

In the image example below:

Feature Line Layer = Transport - District

Lookup Table = TRAFFIC_IMPACTOR_CODE

Feature Layer with a Lookup Table

What is the most efficient way to "connect" (join/relation/query/field calculator) both tables so the DESCRIPTION column of the Lookup Table 'TRAFFIC_IMPACTOR_CODE' is returned for FROM_TRAFFIC_IMPACTOR_CODE and TO_TRAFFIC_IMPACTOR_CODE of 'Transport - District' feature layer?

A simple QGIS JOIN can only return an answer for ONE column in the 'Transport - District' feature layer.

A Relation is specific only for a project, and is useful only when using the Identify Features Tool as far as I can tell.

A Query (Virtual Layer) has the advantage of displaying a subset of the feature layer, how does one Query based on Both tables?

A Field Calculation (Virtual Column) would be nice to have as a persistent set of answers to multiple column relations between the Feature Layer and the Lookup Table. How does one ask for the DESCRIPTION answer from the Lookup Table for both FROM_TRAFFIC_IMPACTOR_CODE and TO_TRAFFIC_IMPACTOR_CODE columns?

Improve this question

2 Answers 2

Reset to default
2

The easiest way to get this is by going to the layer properties of the Transport - District layer and go to the "Attributes Form" tab, where you can click on the FROM_TRAFFIC_IMPACTOR_CODE field and select value relation as widget type, use TRAFFIC_IMPACTOR_CODE as layer, TRAFFIC_IMPACTOR_CODE as key and DESCRIPTION as value field. Repeat this for the TO_TRAFFIC_IMPACTOR_CODE.

You could also add two joins

or create two relations and use the relation reference widget instead of the value relation widget. This will have a better usability, especially for larger value lists. Note: this only uses a small subset of all the possibilities offered by relations.

Virtual layers could be used as well, but are likely an overkill configuration wise. I won't go into detail here.

A virtual field is indeed yet another possibility, combined with get_feature and attribute functions, but I can't see any advantage it would offer over a join or relation widget types, but it will perform worse than those, so I wouldn't recommend it for this use case.

Final note: All of these options are project specific (not just relations).

Improve this answer
4
  • Thank you! 1. Use of the Value Relation Widget is a very quick method of displaying related data from another table. Are calculations possible using the related values?
    – Mars
    Jan 6, 2020 at 0:45
  • 2. Add Two Joins. Is it possible to add Two Joins from a single table? (Table1 -> Table2)
    – Mars
    Jan 6, 2020 at 0:47
  • 3. Where does one find information and examples 'of all the possibilities offered by relations' using QGIS?
    – Mars
    Jan 6, 2020 at 0:49
  • 1. Yes, it's possible. Please ask another specific question if you are stuck. 2. Yes. 3. docs.qgis.org/3.4/en/docs/user_manual/working_with_vector/…
    – Matthias Kuhn
    Jan 6, 2020 at 6:42
1

You can make an SQL query from DB Manager to create a new "virtual" layer.

  1. Turn on DB Manager standard plug-in from the menu (Plugins/Manage and Install Plugins...) DB Manager plugin activation

  2. Load the necessary layers, tables for join.

  3. Open DB Manager from the Database menu and look for your table among Virtual layers and Project layers.

  4. Select the second icon in the toolbar (paper sheet and spanner)

  5. Insert your query (something like this):

    SELECT SS_RESTRICTION_, a.DESCRIPTION, b.DESCRIPTION FROM Transport-District, TRAFFIC_IMPACTOR_CODE a, TRAFFIC_IMPACTOR_CODE b WHERE Transport-District.FROM_TRAFFIC_IMPACTOR_CODE = a.TRAFFIC_IMPACTOR_CODE and Transport-District.TO_TRAFFIC_IMPACTOR_CODE = b.TRAFFIC_IMPACTOR_CODE

  6. Press Execute button

  7. Select Load as new layer checkbox

  8. Press Load button (near to lower right corner)

Here is an example with my data:

enter image description here

Note: This solution can be slow for large data sets, to speed up you can upload your data to a SpatiaLite or PostGIS database.

Improve this answer
1
  • Thank you! I am curious of the SQL syntax you use in Step 5. I am familiar with the syntax in your Image example.
    – Mars
    Jan 5, 2020 at 19:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.