Selecting column in data attribute table reliant from different column

Question

I have the problem because after extracting vertices from my linear geometry layer I have so many duplicates in my data attribute table (taking into account a unique "ID" of each line). However, I found, that each duplicate has its own unique number defined by the "vertex_index" column, which is quite good because I can count the number of vertices from the start of the given line to its end.

All the duplicates have been calculated with regards to this thread: Identifying duplicate attributes in field using QGIS? but again, in my case all the values in the element "ID" column are duplicates right now, as you can see below:

where I marked red ones as short lines (including only the start node - 0 and end node - 1) and long lines (with more than 2 nodes). What I am trying to do, I want to prepare the formula, which could remove all duplicates from the "SPAN_NAME" column and keep only these points, for which the vertex_index is 1 - for shortest lines (up to 4 duplicates) and vertex_index is 3 for longer lines (more than 4 duplicates).

In that case, when I click "zoom to feature" it will redirect me directly to the line (for which nodes were extracted, but now only one is available) but not at the very beginning of the end of that given line but more in the middle (i.e. for 3 nodes the vertex-index 1 is OK, but not necessarily for 10 nodes, where the vertex_index 3 will be better.

I tried to modify the duplicates formula by setting:

count(1, "FieldWithDuplicates") > 4

or something like this:

 CASE WHEN count(1,"FieldWithDuplicates") > 1 
 THEN "vertex_index" = 1 
 OR
 count(1,"FieldWithDuplicates") > 4 
 THEN "vertex_index" = 3 

but the "Select by expression" says, that the expression is invalid.

Is there any way to select the "vertex_index" value, which is reliant from the number of duplicates in the "SPAN_NAME" column?

Answer 1

^{Since I wrote the answer to the question you linked I feel mildly obliged to reply... :)}

It's not entirely clear from your post what you're trying to do, but is this the correct interpetation?

• If there's 2-4 vertices/duplicates, select the feature where vertex_index = 1
• If there's > 4 vertices/duplicates, select the feature where vertex_index = 3

Then you need to use something like the following expression in Select By Expression - could be made more concise but at least this makes the logic clear:

CASE
    WHEN count(1, "span_name") > 1 AND count(1,"span_name") <= 4
        THEN "vertex_index" = 1
    WHEN count(1, "span_name") > 4
        THEN "vertex_index" = 3 
END

Result with sample data:

---

But I have a feeling maybe you would like to select the last vertex from each line? Or the first and middle vertex from each line (but first and last when there's only two vertices)

In which case, the expressions are much simpler.

Select last vertex from each line:

"vertex_index" = maximum("vertex_index", "span_name")

Result:

Select middle vertex from each line (if there are an even number of vertices it will round up from the median number, e.g. 0 1 2 3 -> 2) - or last vertex if there are 2 vertices, or only vertex if there is only 1 vertex. If you want to round down, replace round() with floor().

"vertex_index" = round(median("vertex_index", "span_name"))

Result: