1

I have a layer my_layer containing a virtual field virtual_1. Next step I want to create a virtual layer, which list only unique values (as a bonus also dissolves the geometries by unique values).

The following query for the virtual layer doesn't work with virtual fields:

SELECT DISTINCT virtual_1 from my_layer

How could the desired outcome be achieved?

Some notes/ideas:

  • I need an dynamic solution, as the data in my_layer gets updated reguarly. So unfortunately the Vector>Analysis>Unique Values isn't an option. Also Dissolving by virtual field is therefore not an option.
  • Would it work to generate the virtual field inside the query itself? How could an expression of the field editor evaluated in the virtual-field-query?
Improve this question
0

1 Answer 1

Reset to default
5

If you created a virtual field (Virtual_1) using a CASE statement you may use the case statement in your SQL query

In the below exemple I create a virtual field (Virtual_1) using the following :

CASE WHEN "continent" IN ( 'North America' , 'South America' ) THEN 'On the Left' WHEN "continent" IN ( 'Africa' , 'Asia' , 'Europe' ) THEN 'On the Right' WHEN "continent" = 'Antarctica' THEN 'A the Bottom' ELSE 'Other' END

To use this Virtual field in a Virtual layer I could just use the following SQL statement where the virtual field is replaced by the CASE expression:

SELECT DISTINCT (CASE WHEN "continent" IN ( 'North America' , 'South America' ) THEN 'On the Left' WHEN "continent" IN ( 'Africa' , 'Asia' , 'Europe' ) THEN 'On the Right' WHEN "continent" = 'Antarctica' THEN 'A the Bottom' ELSE 'Other' END) AS Virtual_1 FROM Admin_0_polygons

In the below picture you can see the original table with the virtual field and the Virtual layer next to it.

enter image description here

The same principle will work with any fonction that SQLite/SpatiaLite engine could evaluate including on the geometry if needed

Improve this answer
4
  • Thanks for this answer. This helped a lot. Is it correct that not all QGIS expression functions work? e.g. concatenate(), or if()? Commented Apr 6, 2023 at 16:12
  • Also I realized, that i cannot use fields defined in the SQL query within subsequent field definitions of the SQL query? Is there a way around this? Commented Apr 6, 2023 at 16:15
  • For your first question IIF() and concat() will work (in fact you can use any expression from sqlite not from QGIS expression engine). For your second question it's unclear what you mean but I'm able to use the Virtual_1 field created in the query in ORDER BY or GROUP BY in the same query Commented Apr 6, 2023 at 16:32
  • Regarding first question: OK. I understand. Concatenate (!=concat) does not work (only QGIS expression, not sqlite) Regarding second: I try to achieve the following in the same SQL request: "SELECT somedefinition as virtual_1, Group_Concat(virtual_1) as virtual_2 FROM Admin_0_polygons Group by some_other_field" How would I achieve it? Commented Apr 6, 2023 at 16:49

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.