3

I would like to dynamically populate a dropdown list based on the content of another field. While one approach involves using relations and querying a database, it can be time-consuming. Currently, my QGIS project operates as follows:

When a code is entered into field1, QGIS searches for corresponding codes in another layer. If multiple codes are found, they are listed with a separator (|).

For example: E5.1| E5.11| E5.12| E5.13 |E5.14

My goal is to split this information (with separator | ) and use it to populate another list in a different field. Do you have any suggestions on how to proceed with this task?


Data structure

As requested, here is an overview of how my project is currently working:

I use two layers :

  • SpatiaLite Layer (HABITATS_POLY) with the following fields:
    • EUNIS_1 - Main code
    • CORINE_1 - Should eventually have a single sub code
    • CHX_CORINE_1 - Used in QGIS to select a single sub code that is then overwritten into CORINE_1
  • Database (HABREF_JBI) with the following fields:
    • Habitat EUNIS12 - Main code
    • Habitat CORINE - Sub code
    • MEUNIS12-CORINE - Concatenation of all sub codes with the same main code

As shown below, the MEUNIS12-CORINE column in the HABREF_JBI database represents the concatenation of all other Habitat CORINE values in the database that have a matching Habitat EUNIS12 value.

For example, a Habitat EUNIS12 value of G4 corresponds with Habitat CORINE values of 43.9, 43.5, 43.2, etc., so the MENUS12-CORINE column is 43.9|43.5|43.2|... for each of those rows. (See image below)

Whereas a Habitat EUNIS12 value of G2.1213 returns only a Habitat CORINE value of 45.313, so the MENUS12-CORINE column is also 45.313.

Extract BD


SQL Trigger

In the HABITATS_POLY layer, I'm using this SQL trigger to update my field CORINE_1, using the value of EUNIS_1 to search the HABREF_JBI database in column Habitat EUNIS12, and return the corresponding value contained in MEUNIS12-CORINE.

CREATE TRIGGER update_corine1
AFTER UPDATE OF EUNIS_1 ON HABITATS_POLY
BEGIN
    UPDATE HABITATS_POLY
    SET CORINE_1 = (SELECT "MEUNIS12-CORINE" FROM HABREF_JBI WHERE "Habitat EUNIS12" = new.EUNIS_1)
    WHERE EUNIS_1 = new.EUNIS_1
    AND CORINE_1 IS DISTINCT FROM (SELECT "MEUNIS12-CORINE" FROM HABREF_JBI WHERE "Habitat EUNIS12" = new.EUNIS_1)
    AND pkuid = new.pkuid;
END;

When SQL trigger populates the CORINE_1 field with MEUNIS12-CORINE, this is fine when MEUNIS12-CORINE is a single value like 45.313.

However, when the corresponding MEUNIS12-CORINE value is a concatenation like 43.9| 43.5| 43.2| 43| 43.H| 43.B| 43.A I need to find a straightforward method for the user to manually select the appropriate code.


Select single value with Value Relation widget

When needed, I utilize the Value Relation widget in QGIS for the field CHX_CORINE_1 to present a list of all potential correspondences in the field. Upon selecting an entry from this field, the CORINE_1 field is automatically updated using the following default value expression:

CASE 
  WHEN "CHX_COR_1" is not NULL 
      THEN left(represent_value("CHX_COR_1"),
                strpos(represent_value("CHX_COR_1") ,' --')-1)
  ELSE "CORINE_1"
END

Issues

I am encountering some issues:

  • Performance: Even with a single feature, the system experiences a slight delay of approximately 1 second during loading and saving (probably more with more features).

  • Workflow Inefficiency: To access the list, I need to save the HABITATS_POLY layer, then navigate to the attribute table to search for CHX_CORINE_1, select an entry from the list, and save again. This process is both impractical and slow.

Current result

As advised by she_weeds, I created a VIEW. Here is the code I executed:

CREATE VIEW VIEW_EUN_COR AS 
SELECT "Habitat EUNIS12", GROUP_CONCAT(DISTINCT "Habitat CORINE") AS "CorineList"
FROM HABREF_JBI
WHERE "Habitat CORINE" IS NOT NULL AND "Habitat CORINE" <> ''
GROUP BY "Habitat EUNIS12"
HAVING COUNT(DISTINCT "Habitat CORINE") = COUNT(*);

Apparently I cannot create column indexes in VIEWS.

Then I used the triggers :

CREATE TRIGGER insert_corine1
AFTER INSERT ON HABITATS_POLY
FOR EACH ROW
BEGIN
    UPDATE HABITATS_POLY
    SET CORINE_1 = (SELECT "CorineList" FROM VIEW_EUN_COR WHERE "Habitat EUNIS12" = NEW.EUNIS_1)
    WHERE EUNIS_1 = NEW.EUNIS_1;
END;

CREATE TRIGGER update_corine1
AFTER UPDATE OF EUNIS_1 ON HABITATS_POLY
FOR EACH ROW
BEGIN
    UPDATE HABITATS_POLY
    SET CORINE_1 = (SELECT "CorineList" FROM VIEW_EUN_COR WHERE "Habitat EUNIS12" = NEW.EUNIS_1)
    WHERE EUNIS_1 = NEW.EUNIS_1;
END;

Here is the result I get for now:

result

When I code has one correspondence, the result will give the correct code (for instance: "G1.31" = "44.61") But when several correspondences are possible, I the result will be a list of all the possible codes. (for example: "C3.21" = "53.113,53.11")

9
  • I'm not sure how your "|"-separated list is generated here, but look into Value Relations - use your other layer (with all the codes) as the reference layer, the field with the codes as your Key and Description field, and filter the dropdown list with an expression like current_value('field1') = "other_layer_code_field" (for exact match) or "other_layer_code_field" ILIKE current_value('field1')||'%' (for partial match). Have a look at gis.stackexchange.com/a/362607/98784
    – she_weeds
    Commented Nov 22, 2023 at 1:12
  • I added a column in my database with the aggregation of all the correspondences when there are multiple ones. I got this aggregation when I use a code with multiple correspondences. I am currently using the solution you are presenting. But my database has 10k rows, it might create some latencies. Or I will have to create multiple databases, for each column, to make it faster.
    – Giene
    Commented Nov 22, 2023 at 4:52
  • Apologies, I'm still not sure how your data is structured - when you say you "added a column in my database" do you mean it's in another layer (call it Layer2), with a field (call itfield_agg) with the aggregated codes delimitrd by | ? And in your current layer (Layer1), when you type in E5.1 into field1, you want to get a dropdown list (in field1?) of each attribute in field_agg with a partial match to E5.1 (with the | intact)... Or you want a dropdown list of E5.1, E5.11, E5.12 as separate entries? Could you share some screenshots of your various attribute tables to make it clearer?
    – she_weeds
    Commented Nov 22, 2023 at 23:56
  • I have edited my post. Will add some picture eventualy !
    – Giene
    Commented Nov 23, 2023 at 17:10
  • Every solution I try is not working or too slow. I am way more efficient to work on Excel with VBA to process hundreds of thousands of lines at a time, than with QGIS to make a simple relation between two values in a spatialite database of 7k rows.
    – Giene
    Commented Nov 29, 2023 at 3:49

2 Answers 2

4

To answer the initial question, assuming your setup is non-negotiable:

QGIS widgets out of the box can only create a "dynamic list" from a single field in a layer. It cannot take a delimited string value, parse the individual values out on the fly into a temporary list, then present that list in the GUI. It might be possible with Python but that is adding unnecessary complexity and will probably be slow — especially on a feature-by-feature basis.

Trying to generate that "dynamic list" as a Virtual Layer is even more convoluted and impossible to run.


Your question is best answered more conceptually: rethink your overall process and data structures.

Most GIS programs work better using typical DBMS structures where the data is aggregated directly from rows and columns, rather than a convoluted concatenating-and-deconcatenating.

As far as I can understand, you already have a "lookup table" in the form of HABREF_JBI, where each combination of main code and sub code has its own row. That should be all you need.

  • There is no need to hardcode the one-to-many-relationships as delimited strings repeated row after row.
  • It follows that there is no benefit in setting up a trigger to "import" this delimited string.
  • When you generate a dropdown list it should just take the current main code value, and directly look up the corresponding sub code values in the other table.

With properly structured tables and appropriate data storage this should be fairly fast.


If the issue here is that HABREF_JBI table is too large and slow to use as a lookup table for the Value Relation widget, then that is the problem to address.

If that is the case, some considerations:

  • Since you are comfortable using SQL features why not create a VIEW that simplifies HABREF_JBI, and load that into QGIS? The VIEW can load only the fields you want, and dynamically update even if the original table is updated.

  • If HABREF_JBI has duplicate rows with the same main code and sub code combination (e.g. 50 rows with G4 and 43.9 values), use a GROUP BY to simplify your VIEW so you only see unique combinations of the relevant fields (i.e. one row with G4 and 43.9 values).

    • For example in my state's vegetation class vector dataset (373000 features), there are 660 unique habitat code combinations. 660 features is fast to look up even as a VIEW. Although your dataset might be much larger.
  • Presumably your data is properly indexed?

  • Consider Postgres/PostGIS instead of Spatialite if performance is just generally slow

7
  • SQL is actually quite new for me. I'm using SpatiaLite, because I didn't figure how to use PostGIS (it is asking for a connection and password, but I don't have a password). With SpatiaLite I was able to create a new layer and work with it as a vector layer. Right now, I don't know what is a VIEW, and how to create it. Like, this way ? CREATE VIEW ViewTest AS SELECT "Habitat EUNIS12", Habitat CORINE, ... FROM HABREF_JBI
    – Giene
    Commented Nov 29, 2023 at 12:52
  • I don't know if my data is properly indexed, but I indexed the column "EUNIS_1". I wouldn't dare trying a 373 000 features DB in QGIS, mine is actually only 7k, and it is already a problem. I didn't even try with the bigger ones.
    – Giene
    Commented Nov 29, 2023 at 12:58
  • Yeah, that is a VIEW. It is like Virtual Layer in QGIS, lets you see a filtered or adjusted version of your source data but it's created at the database level. You could select only the columns you need to use as a lookup table and cut down load time that way. There could be several reasons why QGIS is loading your layer so slow in general and in Value Relation widget. People routinely use QGIS to work with datasets of that size or larger even without postgis. That's better asked in a separate question maybe, but be careful to word it so you don't attract opinion-based answers.
    – she_weeds
    Commented Nov 29, 2023 at 21:30
  • As for postgis, bit more of a learning curve but have a look at setup guides. You need to install the postgres software + postgis extension locally, and during installation it will prompt you to create a username and password. You can use that to connect to the database in QGIS (or you can add more users later with restricted access) or use the pgAdmin GUI. It's worth looking into although the primary concern is first structuring your data efficiently and rethinking how you auto update/validate data, I think. Good luck!
    – she_weeds
    Commented Nov 29, 2023 at 21:41
  • Thank you for the clear and detailed explanations. I have concatenated the information at the beginning, because I don't know how to get a list of aggregated codes when multiple codes are possible. The way you present it, it seems possible, but I don't see how. Can I use the VIEW to populate a list (with value-relation widget)?
    – Giene
    Commented Nov 30, 2023 at 6:36
0

Here is the final result I chose. It is a bit different from what I was thinking at the beginning, but it is clear and easy to use.

I created 2 layers :

  • HABITATS_POLY with the following fields:

    • EUNIS_1 - Main code
    • CORINE_1 - Correspondence code of EUNIS
  • CORRESPONDANCES

    • EUNIS
    • CORINE
    • CHOIX CORINE

And I have one Database that I divided into several smaller ones. Each database is the correspondence between two nomenclatures. For example: EUNIS - CORINE. This way, I have smaller DB, which are faster to look up.

I used this trigger on HABITATS_POLY, to report the content of "EUNIS" into "EUNIS" of the layer CORRESPONDANCE.

CREATE TRIGGER insert_eunis1 AFTER INSERT ON HABITATS_POLY
BEGIN
  INSERT INTO CORRESPONDANCES (EUNIS)
  SELECT DISTINCT NEW.EUNIS_1
  FROM HABITATS_POLY
  WHERE NEW.EUNIS_1 IS NOT NULL
    AND NEW.EUNIS_1 NOT IN (SELECT EUNIS FROM CORRESPONDANCES);
END;

And this trigger to delete the content in CORRESPONDANCES, when I make changes in the HABITATS_POLY layer (if the code is no more used).

CREATE TRIGGER delete_corr
AFTER DELETE ON HABITATS_POLY
FOR EACH ROW
BEGIN
    DELETE FROM CORRESPONDANCES
    WHERE EUNIS NOT IN (SELECT EUNIS_1 FROM HABITATS_POLY WHERE EUNIS_1 IS NOT NULL)
        AND EUNIS NOT IN (SELECT EUNIS_2 FROM HABITATS_POLY WHERE EUNIS_2 IS NOT NULL)
        AND EUNIS NOT IN (SELECT EUNIS_3 FROM HABITATS_POLY WHERE EUNIS_3 IS NOT NULL)
        AND EUNIS NOT IN (SELECT EUNIS_4 FROM HABITATS_POLY WHERE EUNIS_4 IS NOT NULL);
END;

In this layer "CORRESPONDANCE", I use a value-relation widget to populate a list of CORINE Code, from the content of "EUNIS".

And it is only from this layer that I get the correspondence between EUNIS and CORINE. For this, I use this trigger:

CREATE TRIGGER insert_corine1
AFTER INSERT ON HABITATS_POLY
BEGIN
    UPDATE HABITATS_POLY
    SET CORINE_1 = (SELECT "CORINE" FROM CORRESPONDANCES WHERE "EUNIS" = new.EUNIS_1)
    WHERE EUNIS_1 = new.EUNIS_1;
END;

Finally, any time I make a change in "CORRESPONDANCE", another trigger is exececuted, in order to update HABITATS_POLY

CREATE TRIGGER update_corine_codes
AFTER UPDATE OF CORINE ON CORRESPONDANCES
FOR EACH ROW
BEGIN
    UPDATE HABITATS_POLY
    SET
        CORINE_1 = NEW.CORINE
    WHERE
        EUNIS_1 = NEW.EUNIS;

    UPDATE HABITATS_POLY
    SET
        CORINE_2 = NEW.CORINE
    WHERE
        EUNIS_2 = NEW.EUNIS;

    UPDATE HABITATS_POLY
    SET
        CORINE_3 = NEW.CORINE
    WHERE
        EUNIS_3 = NEW.EUNIS;

    UPDATE HABITATS_POLY
    SET
        CORINE_4 = NEW.CORINE
    WHERE
        EUNIS_4 = NEW.EUNIS;
END;

Your Answer

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

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