4

I'm working in QGIS for a Digital Terrain Model.

I've got a table with point features, arguably vertices extracted from multipart iso-curves because the features are provided with a "vertex_par" value along with an "ID" (not unique, it recalls the original curve multipart feature ID).

Intent: I need to get the extremes of each original iso-curve lying on the property border into a table to feed my BIM-modeling app.

GOAL: I need to isolate all the points that are start and endpoints of the original curves (or parts).

Sketched in plain language:

For each group with the same "ID" value

    For each subgroup with same "vertex_par"

        I need to get the points with minimum/maximum "vertex_ind" value

QUESTION: How do I set an expression to filter these points?

Excerpt from the table of points with highlighted targets and grouping criteria

  • 2
    Do you just need to see those numbers, or do you want to store those values? If you extract grouped values over a table, then storing those values in a column will insert the same value for all rows belonging to a group; pretty redundand, but might be intended? – ThingumaBob Dec 6 '19 at 9:43
  • Intent is to store the filtered point features in a new layer, and merge them there with other internal points of the terrain. The idea is to control tweak the density of points generated from isocurves to have different "resolutions" for terrain mesh in the cad app. It seems everything easy, except for border points, very important for the seamless binding of neighbour terrains. – Marco D'Elia Dec 6 '19 at 12:25
3

You can use a Virtual Layer (Menu Database > Db Manager > Virtual Layer > Open a SQL Window with the sheet + wrench icon):

SELECT alti_tab.*
FROM "ALTI_Isok Punkte" AS alti_tab,
     (SELECT "ID",
             "vertex_par",
             MIN("vertex_ind") AS min_vertex_ind,
             MAX("vertex_ind") AS max_vertex_ind
      FROM "ALTI_Isok Punkte"
      GROUP BY "ID",
               "vertex_par"
     ) AS t1
WHERE alti_tab."ID" = t1."ID"
AND alti_tab."vertex_par" = t1."vertex_par"
AND (alti_tab."vertex_ind" = t1.min_vertex_ind OR alti_tab."vertex_ind" = t1.max_vertex_ind)

Explanations:

  • t1 is a subquery that select only minimum MIN and maximum MAX of vertex_ind group by ID ans vertex_par
  • the final query joins the ALTI_Isok Punkte table to t1 with the fields ID and vertex_par and selects only vertex_ind equals minimum or maximum.

A pure QGIS expression based, maybe :

minimum("vertex_ind", group_by:= "ID" || '_' || "vertex_par") = "vertex_ind"
OR
maximum("vertex_ind", group_by:= "ID" || '_' || "vertex_par") = "vertex_ind"

I tried to replace (with QGIS 3.10) the "ID" || '_' || "vertex_par" by `array("ID", "vertex_par"), but not seeing a great difference.

  • Thanks a lot for the precious suggestion! As my request probably shows, I'm neither an experienced DB-user nor an educated query builder. My fault! I'm getting from this neat response nevertheless a precious hint: to perform aggregations beyond the simple check of one field I must deepen my skills and understanding of Virtual Layers. Thanks a lot @J.Monticolo, I'll engage myself immediately! Any other ideas using solely expressions? – Marco D'Elia Dec 6 '19 at 10:58
  • 1
    I've edited my post. – J. Monticolo Dec 6 '19 at 14:50
  • Thanks! I can mark the answer as accepted because (despite in this case my approach to terrain modeling could be itself questionable) it drives me to a learning scope. That's like saying, I could neither test effectively both the solutions sofar, nor rate their effectivity on my issue, but I got an approach. I promise to post updates in case of good results! – Marco D'Elia Dec 6 '19 at 15:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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